How to filter existing transactions based on a financial dimension value set

[Blog bot]

Источник: http://alexvoy.blogspot.com/2019/08/...nsactions.html
==============

Let's assume that we want to see our bank accounts balances grouped by a financial set, which may be different than that set up for your transactions.

For example, existing transactions presume to have values for the following dimension attributes.





However, our selection can be different. For this exercice sake I opted for Business unit and Department.

So, instead of one balance for the whole bank account, we want to get it distributed per every combination of Business unit and Department values.

Another point to consider is the fact that these attributes are not mandatory; in other words, we don't have values for all attributes in all transactions.

It can be easily achieved if we find a way to create a column with all sought dimension attribute values concatened as depicted.

Generally speaking this is a job a BI solution; however, it is possible to do in D365 by means of Views.

Let's solve this problem step by step.

Fisrt view is for all selected attributes that we should populate values for, exist them or not.

Second, collect all existing values which match our selected attributes.

Then, group all found dimension attribute sets.

Now we create a view based on the latter and sought attributes without relation in outer join. By that we will get a Carthesian product of all possible combinations between them.

At this step we outer join the latter with the found (existing) values. Here we can add a computed column if we want to see something else than just an empty value.

private static server str displayValueWithNA() // X++
{
str sRet;
tableName viewName = viewstr(CDPDimensionAttributeValuesUnionView);
str cDisplayValue = SysComputedColumn::comparisonField(viewName,
viewstr(CDPDimensionAttributeValuesSelectedView),
fieldStr(CDPDimensionAttributeValuesSelectedView, DisplayValue));

sRet =
SysComputedColumn::if(SysComputedColumn::isNotNullExpression(cDisplayValue),
cDisplayValue,
SysComputedColumn::returnLiteral('n/a')
);

return sRet;
}



Final view will contain all the values found on the previous step grouped by dimension attribute value set so that in a computed column all values be concatened in the required order.

private static server str finDimValues() // X++
{
return @"STUFF((SELECT '-' + t3.DisplayValue
from CDPDimensionAttributeValuesUnionView as t3
where
t1.DimensionAttributeValueSet = t3.DimensionAttributeValueSet
order by t3.DIMENSIONATTRIBUTE
for xml path('')), 1, 1, '')";
}


Having this view in inner join combination allows you to filter any transactional data by a set of dimenions without coding.

Источник: http://alexvoy.blogspot.com/2019/08/...nsactions.html