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 c...
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

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.