10-04-2022 08:31 AM - last edited on 05-24-2023 11:50 AM by JackLacava
Is there an easy way to retrieve the subtotal of all the values returned in a Cube View expansion?
For instance, we might have on one Cube View Row with a nested expansion such as:
What I would like is to be able to add another row to my Cube View that is the sum of everything that retrieved in that first row expansion (could have returned anything from 0 to 9 rows of data). Today we do a klunky solution, such as : GetDataCell(CVR(Row1,0) + CVR(Row1,1) + CVR(Row1, 2) + ......), which unfortunately is not dynamic.
10-04-2022 11:30 AM
Hello drgerrow!
I do not know how your members are spawn so ...
Do you need this value to be stored? You could create a dummy member in your account dimension that is called total and put all its children in it. Then you put this total member in your cube view.
10-04-2022 12:16 PM
Hi drgerrow: arbitrary subtotals are not one of OneStream's strong suits. There might be a few approaches to achieving your result.
If you need these groupings for more than 1 report, I'd recommend options 1-3 (in that order). If its just a single report, I would probably go the CV math route.
10-07-2022 08:42 AM
Thank you both for the suggestions. I think a very simple solution would be to simply modify the CVR/CVC functions, such as allowing the syntax: GetDataCell(CVR(Row1,TOTAL)), so I that the report will dynamically provide a sub-total of all the rows that end up in the resulting expansion. I do not need to store the result, I do not want to be creating hierarchies in multiple dimensions for ad-hoc reporting, but I do need the subtotal to be dynamic.