08-22-2022 11:33 AM - last edited 4 weeks ago by JackLacava
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.
08-22-2022 12:15 PM
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.
08-22-2022 01:52 PM
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:
Base UD1 members are created under the ‘Financial Detail’ cube in Dimension Library:
Parents and Sub Parents UD1 members are created under the ‘Financial’ cube in Dimension Library:
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_).
08-22-2022 02:16 PM
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.
08-22-2022 03:33 PM
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.
08-22-2022 04:26 PM - edited 08-22-2022 04:26 PM
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.