Forum Discussion

CEglinton's avatar
CEglinton
New Contributor
15 days ago

Business Rule or UD8 Calculation in Place of Multiple Overrides

Hi!

I am building a CubeView and currently exceeding the 4 maximum column overrides. To work around this, I am thinking the next best option is to build a business rule or UD8 calculation that combines multiple formulas but need help putting together the language (unfortunately, coding is not in my wheelhouse). Does anyone have a current business rule or UD8 calculation that I can utilize that subtracts one column from another for multiple criteria?

For example, below is what I currently have in the Column Overrides. I think I would still need to put the UD8 calculation in an override because of conflicting GetDataCell formulas in my rows/columns but because there are various views (QTD & YTD) and scenarios (Actual & PYRates), ideally would need to combine some of the formulas together in the calculation as to not exceed the 4 maximum overrides. Hope that makes sense.

All of the column references is a gross margin calculation. For example:

QTD_CY = Current quarter gross margin (i.e. 2024M9)

QTD_PY = Prior year quarter gross margin (i.e. 2023M9)

QTD_CY_PYRate = Current quarter gross margin (i.e. 2024M9) + S#PriorYearRates

YTD_CY = YTD gross margin based on current quarter selected (i.e. 2024M9)

YTD_PY = YTD prior year gross margin (i.e. 2023M9)

YTD_CY_PYRate = YTD gross margin based on current quarter selected (i.e. 2024M9) + S#PriorYearRates

Open to all ideas. Thank you in advance!

Cube View
Reports
  • Henning's avatar
    Henning
    Valued Contributor II
    14 days ago

    Hi, everyone here started from zero, so I would say it is never too late to start looking into some basic coding ğŸ™‚

    As you say, using a dynamic member formula will be easier for the use case you describe. And the UD8 calculation can then also be easily re-used in other reports or Excel Quick Views.

     

    On your question, a good way to start is here:

    Dynamic Member Formulas and Cube Views

    Just start small, by pulling a single number and then add more dimensional details and / or calculation logics to it. This should give you a good feeling of how it works.

     

    Snippets in OneStream as well as the Blueprint also contain a variety of examples, as was discussed here:

    Solved: UD8 Reporting Helpers - OneStream Community

     

    On top of that, if you search for "UD8 dynamic" or "Dynamic calculation" or something similar in this forum, you will find quite a few discussions with a number of code samples you might be able to leverage as well.

     

    Keep in mind though UD8 is most commonly used for such reporting helpers, dynamic calculations can be used in the account dimension as well as all UDs. UD8 is just most commonly chosen as the ones "before" are typically used for data model related details such as cost center, audit, etc.