Forum Discussion

WillVitale's avatar
WillVitale
Contributor
9 months ago

Row Column Intersection in a Cubeview

Hello,

I'm trying to sum up a calculated amount using Row Column Intersection and I cannot get it to work properly. I'm using Rows Override in order to sum up these calculated amounts (also using row override).

So the Input Inventory Amount, I put some dummy info in and the calculated reserves are taking those amounts and multiplying it by those percentages (name override as UD8#None). So the amount in LC column should be 265LC, but I cannot get it to populate. Below is my row override formula.

I've tried putting a row range but it comes up with an error message of Invalid calculation script.

I don't want to make a new UD8, because I don't really know how to do that, so if it can be done with a CVRC, or a different formula, that would be great!

Thanks,

Will

Cube View
Reports
  • JackLacava's avatar
    JackLacava
    9 months ago
    WillVitale wrote:

    I don't want to make a new UD8, because I don't really know how to do that

    Investing in learning how to build DynamicCalc accounts/Flows/UDs will reap big dividends. It's not very hard either. Start from this link and go forward for a couple of pages.

     

  • tuhler's avatar
    tuhler
    New Contributor III
    9 months ago

    You will want to build as much of the calculation logic as possible into the metadata. For example, you could have an account for each of your calculated reserves percentages that you use that would return inventory amounts * 0.1. From there, you could have those in a hierarchy that sums up to your LC parent.

    You won't be able to have a CVR or CVC that references another calculated cell like you are trying to do.

    Thanks,

    Thomas

  • JackLacava's avatar
    JackLacava
    Honored Contributor
    9 months ago
    WillVitale wrote:

    I don't want to make a new UD8, because I don't really know how to do that

    Investing in learning how to build DynamicCalc accounts/Flows/UDs will reap big dividends. It's not very hard either. Start from this link and go forward for a couple of pages.

     

    • WillVitale's avatar
      WillVitale
      Contributor
      9 months ago

      Hi Jack,

      So I followed that link and I tried creating a UD8 calc based off of something we already had but I'm not sure if this works or not.

      I'm guessing it's a lot easier than I make it out to be, but any direction would be great.

      Thanks,

      Will

    • WillVitale's avatar
      WillVitale
      Contributor
      9 months ago

      Hi Jack,

      So I was able to get it to work after some tinkering, but now I cannot figure out how to roll up those amounts into the parent I created. I made it set to Is Consolidated and ran a force consolidate but that didn't end up working. I compared it to a flow dimension parent, which I thought was the closest example I could use and it pretty much mirrors it identically.

      Thanks,

      Will

      • JackLacava's avatar
        JackLacava
        Honored Contributor
        9 months ago

        Dynamics don't aggregate "naturally" like stored accounts, and IsConsolidated has no effect - because there is no number stored, ever, against those accounts/flows/uds. BUT the good news is, now you know how they work, you can simply create your own additions in another UD: 

        Return api.data.getDataCell("U8#Something + U8#SomethingElse + U8#YetAnother")

        Alternatively, if you're after the sum of base Entities, you can "simulate" consolidation in another dynamic (typically on a U8) containing a call to api.Functions.GetEntityAggregationDataCell:

        Return api.Functions.GetEntityAggregationDataCell("U8#None")

        All of this, like CVC/CVR, will execute at report running time, i.e. for every user every time they look at the related Cube View. If you find this is slow, or you know the CV is executed a lot, it would be better to actually store those calculations, with formulas assigned to a Pass (which is executed during Calculation/Consolidation). That would make aggregation easier, but the rule is a bit more difficult to write, and I'd recommend to take some class first.

        Another approach: if your calculations are all simple multiplications, it might be easier to:

        1. create a new parent and copy the original members as children (you can do it with None too)
        2. set the Aggregation Weight for each child, in Relationship Properties, to the factor you're multiplying by:

        In this way, the parent will contain the aggregation of the original child values multiplied by their individual Aggregation Weight factor, i.e. (Child1 * AW1) + (Child2 * AW2) + (Child3 * AW3) etc.