Aggregation Weight Display in Cube View

[BenvanBergen]

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

[JackLacava]

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:

[BenvanBergen]

Thanks Jack, I'll give it a try.

[phollander]

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

[BenvanBergen]

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.

[phollander]

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:

1. Pass through a top account in the XFBR function from where you want to build up the cube view;
2. In the XFBR function, identify the children of the top account. At this stage, you know the parent of each child;
3. Build a recursive function to get the children of the children, until you have reached base level. If the query calls itself, you will have to make sure you pass on the child as a parent, which allows you to work with the unique parent-child relationships;
4. In the recursive function you can create a member filter where you would add the GetDataCell suggestion from Jack for each account and you can pass on the parent account as a parameter;
5. End result would be a list of all accounts under a certain top account with the aggregation weights, relative to that structure.

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

[BenvanBergen]

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.

[JackLacava]

I've actually filed this as an enhancement on IdeaStream, because I think it would be valuable. Feel free to upvote it, lol.