Aggregation Weight Display in Cube View

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

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:

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

Thanks Jack, I'll give it a try.

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.