Skip to main content
C
Clarke_Hair
Contributor
May 18, 2023
Solved

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

  • May 18, 2023
  • 2 replies
  • 0 views

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
      • ....
Best answer by JackLacava

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.

2 replies

C
Clarke_HairAuthor
Contributor
May 18, 2023

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.  

J
OneStream Employee
JackLacavaOneStream EmployeeAnswer
OneStream Employee
May 18, 2023

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.

C
Clarke_HairAuthor
Contributor
May 22, 2023

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.

T
tomdarnall
Newcomer
May 20, 2023

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.

Terms & ConditionsAccessibility statement