Forum Discussion

Clarke_Hair's avatar
Clarke_Hair
Contributor
2 years ago

Query Parent member less a child vs. Alt rollups in a Dim *** Can you have a query that works ***

My CFO would like to be able to select a combination of children in our Department Dim and see our Net Income.  Is there a way to dynamically calc that value.  In the list below, he is asking if he can select to exclude IT from his query.  We use FXGets alot but could also work in a QV member selection.  Was wondering if maybe a BR could be created but not sure how to reference which to exclude in a FXGet.  Would think it would have to be on the UD8 Dim or something.  He wants to be able to toggle between different views.  ie What does it look like without HR and Finance.  Trying not to create a ton of Alt rollups in the Dim for all the different combinations. 

 

Ex:  UD1

  • Total  BU's
    • Plant BU's
    • SGA BU's
      • Finance
      • HR
      • IT
      • ....
Excel Add In
Parameters
Reports
  • JackLacava's avatar
    JackLacava
    2 years ago

    You have two options:

    • Go full-Excel and do all the roll ups yourself, or
    • Get out of your Excel comfort zone and start building Cube Views and advanced Member Filters - e.g. U3#TotalBu.Tree.Remove('IT'). These filters can then be driven by Parameters or other means (XFBR etc). Cube Views then get pulled into Excel.

    In either case, you likely don't need a custom Dynamic Calc, they're not meant to allow user interaction with the formula.

  • Clarke_Hair's avatar
    Clarke_Hair
    Contributor
    2 years ago

    Was thinking.  Can you pass a parameter in FXGet.  Idea would be to create a Member with formula and you pass the values in that dim that you want to sum together.  So in the example above you would pass #Member Name#, HR, Finance.  The member would be a Dynamic Calc member where it would add Plant BU's + HR + Finance to get a new Total value.  Is that possible.  

    • JackLacava's avatar
      JackLacava
      Honored Contributor
      2 years ago

      You have two options:

      • Go full-Excel and do all the roll ups yourself, or
      • Get out of your Excel comfort zone and start building Cube Views and advanced Member Filters - e.g. U3#TotalBu.Tree.Remove('IT'). These filters can then be driven by Parameters or other means (XFBR etc). Cube Views then get pulled into Excel.

      In either case, you likely don't need a custom Dynamic Calc, they're not meant to allow user interaction with the formula.

      • Clarke_Hair's avatar
        Clarke_Hair
        Contributor
        2 years ago

        Thanks,  It was a long shot on the FXGet (My accounting grp loves them).  Think now we are going split into two methods.  Smaller reports (non-dynamic/licensed users) will use references back to fixed FXGet (we have an Excel guru who can build the logic) and for the more detailed reports go with the CV approach.  This is one of those asks that I am not sure will be used that much but when your CFO gets excited about something, you find a way to deliver.  Thanks.

  • tomdarnall's avatar
    tomdarnall
    New Contributor III
    2 years ago

    Does he always want to exclude IT, or does it periodically change?  it it's a consistent request, you could potentially load IT to a different U7 member.  This could give you the ability to to toggle.  Scratch this suggestion if the request inconsistent from period to period.