Forum Discussion

drgerrow's avatar
drgerrow
New Contributor III
3 years ago

Subtotal of a Cube View expansion?

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:

  • E#Company1, E#Company2, E#Company3
    • A#Account1, A#Account2, A#Account3

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.

Cube View
  • drgerrow's avatar
    drgerrow
    New Contributor III
    3 years ago

    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.

  • NicolasArgente's avatar
    NicolasArgente
    Valued Contributor
    3 years ago

    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.


  • db_pdx's avatar
    db_pdx
    Valued Contributor
    3 years ago

    Hi drgerrow: arbitrary subtotals are not one of OneStream's strong suits.  There might be a few approaches to achieving your result.

    • Real hierarchies (if applicable) - group the entities and accounts into real segments of your hierarchy
    • Alternate hierarchies (again, if applicable) - outside of your standard CoA, but still allows you to get the aggregations you want
    • Metadata + BR-Formula - tag the entities/accounts with your filtering logic, use a custom member formula to sum based on the metadata
    • Dynamic Calcs (probably not applicable as you mention dynamic expansion)
    • Cube View Math - what you're already doing.  You might consider building the CV math with the maximum number of expansions and using IsRowVisible=False to hide blank rows.

    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.