11-29-2023 07:10 AM
Hello,
How can I see the signage from aggregation weights. For example I have these 2 account groups in a hierarchy:
PL44000000 has aggregation weight 1
PL43700000 has aggregation weight -1
When I show these in a cube view with SG&A.Children.Base all numbers are showing as positive numbers, which makes it difficult to see how it adds up. This example is still straightforward, but I have some more complex use cases.
Is there a way to show the numbers based on the aggregation weight, or just show the value from the aggregation weight on the meta data member (-1, or 1 in this example)?
Thanks in advance
11-29-2023 09:44 AM
There is probably an approach using a UD8 member, but if the child has multiple parents it becomes difficult to pick the right one.
Another approach is to have a custom GetDataCell function, which can accept parameters using a technique like the one described here. You will have to hardcode the parent, but it will work.
' in a Finance business rule
Case Is = FinanceFunctionType.DataCell
Dim callELements As List(Of String) = StringHelper.SplitString( _
args.DataCellArgs.FunctionName, "~", StageConstants.ParserDefaults.DefaultQuoteCharacter)
If callELements(0).XFEqualsIgnoreCase("GetWeight") Then
' parse the string passed in
Dim params As New NameValueFormatBuilder(callElements(1))
' now retrieve the parameter value
Dim parentName As String = params.NameValuePairs("parent")
' look up weight
Dim weight As Decimal = api.Account.GetAggregationWeight( _
api.Members.GetMemberId(dimtype.Account.Id, parentName), _
api.Pov.Account.MemberId)
Return weight
End If
With this dimension:
In the Cube View:
11-29-2023 09:54 AM
Thanks Jack, I'll give it a try.
11-29-2023 09:59 AM - edited 11-29-2023 10:03 AM
Hi Ben,
Depending the complexity of your app and Account dimension setup, you could consider using a DynamicCalc for this. If you have a UD8 dimension reserved for dynamic calculations, you could set up a UD8 member with the following Dynamic Calc:
If Not api.View.IsAnnotationType() Then
Dim lParent As List(Of Integer) = api.Members.GetParents(api.Pov.AccountDim.DimPk, api.Pov.Account.MemberId, False).Select(Function(x) x.MemberId).ToList()
Dim dAggWeight As Decimal = api.Account.GetAggregationWeight(lParent.First, api.Pov.Account.MemberId)
Dim dcCell As DataCell = api.Data.GetDataCell("U8#None * " & dAggWeight.XFToString)
Return dcCell
End If
Based on the PoV Account, the UD8 calculation will attempt to find the aggregation weight based on the firstly found parent and use that weight as a multiplier to the data on U8#None. This example assumes the parent- and child account are all set up in the same Account dimension. If the parent Account is in a different dimension than its children, you may have an issue using the PoV Account DimPk. For the sake of the example I will assume both accounts are in the same Account dimension.
This method has some downsides. For one, an Account can have multiple parents and when you get the list of parents, it is hard to predict which one it will get (I recall reading once that the sort order is based on where they were created/member ID). Also, if you have a situation where the account rolls up positively to one parent, but negatively to another, this method will also not work. It will only find the aggregation weight of that one parent, even when you are pulling the child data in relation to another parent.
If you want to ensure the outcome of the dynamic calculation is always executed with a specific parent-child relationship, you could also add the parent name as a Text property to the child account. You can then identify the member ID of the parent, based on that Text property. While there are possibilities to automate such a setup, doing this manually may add more work to the account metadata maintenance.
Secondly, it is also good to consider how you are using the Account Types and against what signage the data is coming in (i.e. is a positive expense loaded as a positive or a negative?). Are the Account Types in the example above all Revenue, Expense or a mixture? The data on an Expense account will roll up with a *-1 to a Revenue account and vice versa. If you have a mixed use of Account Types, this element would also have to be considered in a Dynamic Calc.
I hope this provides you with some thoughts/ideas on how to proceed with this. Maybe someone else has a more simple suggestion on this, but wanted to pass on some immediate thoughts.
Good luck!
Best regards,
Paul
11-29-2023 11:38 AM
Thanks Paul, I think the UD8 calculation will not work because we have multiple hierarchies with different aggregation weights. Account type is also influencing the analysis. Right now I have found the difference, fortunately it was in the end 1 account where I could recognize the account value. We thought of a drilldown formula, but seems to have the same issue. Probably Jack's solution works best, but needs a lot of cube view rows.
12-01-2023 05:42 AM
Hi Ben,
Makes sense to follow the path that Jack suggested, in case the account are available through multiple hierarchies and with different aggregation weights.
To avoid the setup of manual cube view rows, you could consider writing an XFBR function that you can use in your cube view. Conceptually you could:
With this approach you can avoid static rows in the cube view, but will not have any indentation (not sure if that would matter).
Regards,
Paul
12-01-2023 05:56 AM
Thanks Paul! That's a really good idea. I was think of trying a |MFAccount| filter to identify the parent automatically, but need to work on it. I didn't find the time to focus on it yet.
11-30-2023 08:08 AM
I've actually filed this as an enhancement on IdeaStream, because I think it would be valuable. Feel free to upvote it, lol.