Aggregate UD1 by entity dynamically across two cubes

Shetal_P
New Contributor II

Can we use an aggregated UD1 with a dynamic calc member in the Flow dimension across two cubes? 

We have dynamically calculated members called ‘AggDetail’ in Flow Dimension with a rule on member formulas which aggregate base UD1 by entity dynamically.  All our base cost centers (UD1) are in the Financial Detail cube in the dimension library and this ‘FinancialDetail’ cube is called out in the dynamic calc rule. However, we have recently added a new sub parent in flow dimension under the “Financial” cube and it seems like this dynamically calculated flow member “AggDetail” is not aggregating the result for this new sub parent from the Financial cube.

This is kind of close critical and see if anyone knows how to solve this in OneStream. If you know the solution, please reply to me here as quickly as possible.

Thank you

5 REPLIES 5

ChristianW
Valued Contributor

All dynamic calcs are taking place after the aggregation so the answer is no, but a node can be a dynamic calc as well, this might help to get the expected result.

You need to loop the base members and sum them up in a variable before returning the variable.

Sorry, I‘m travelling the next days and therefore can’t create a sample for you.

Cheers

Shetal_P
New Contributor II

Thank you for your feedback.

The flow member ‘AggDetail’  is already set to dynamic calc and working fine for all UD1 base members which are created under the 'Financial Detail' cube in dimension library. However, the same 'AggDetail' member is not pulling any results for Parents/SubParents UD1s members that are created under the ‘Financial’ cube in Dimension library.

Here is our situation:

>> All base UD1(Function/Cost Center) members are created under the ‘Financial Detail’ cube in Dimension Library

>> All Parents/Sub Parents UD1 (Function/Cost Center) members are created under the “Financial’ cube in Dimension Library

>> We have dynamic calc member in Flow Dimension call ‘AggDetail’ which aggregates the data on the fly for UD1 by entity. See below snapshots.

Dynamic Calc Flow Member “AggDetail” with Member Formula:

Shetal_P_0-1661190568808.png

Shetal_P_1-1661190568813.png

Base UD1 members are created under the ‘Financial Detail’ cube in Dimension Library:

Shetal_P_2-1661190568821.png

Parents and Sub Parents UD1 members are created under the ‘Financial’ cube in Dimension Library:

Shetal_P_3-1661190568829.png

 

 AggFlow’ Dynamic Calc member pulling the results on-the-fly for all base UD1 members (CC_ & G_) but not showing anything on Parent/SubParent UD1 (T_).

Shetal_P_4-1661190568831.pngThank you

ChristianW
Valued Contributor

You can’t us a dynamic calc member within API. Data.GetDataCell, but you can use it to populate variables, do the calc with them and return the result.

I hope this helps and cheers. 

Shetal_P
New Contributor II

Sorry for the confusion.  Both cost centers and parents that we are testing are under the Financial Details.

Setup looks fine for this ‘AggDetail’ dynamic calc member in Flow dimension.

All cost centers in (base members in UD1) are aggregating appropriately but it doesn’t work for the parent aggregation points.  So the question is, can we use the parents in dynamic calculation?

For example, dynamic calculation for base cost center CC_51199 works fine and we see the numbers on CC_51199 in our report but it doesn’t work for parents like “T_51500_F’. See screenshots below.

 

Shetal_P_0-1661196709146.png

 

Shetal_P_1-1661196709152.png

 

Thank you,

Shetal

ChristianW
Valued Contributor

You need to sum the base members of topflow as well. This works best, when you loop and sum the line items of a databuffer.