Skip to main content
C
CrispinC
Newcomer
November 23, 2021
Solved

Remove the value of a child

  • November 23, 2021
  • 6 replies
  • 0 views

Is there a way that you can exclude the value of a specific child in the account hierarchy without losing drill down functionality in cube views? For example to exclude bonuses from staff costs.

Best answer by ChristianW

You can create a dynamic calc (on the ud8 dimension) subtracting the members value from the nodes.

6 replies

L
LeeB
Contributor
November 23, 2021

You could create an alternate Account hierarchy for reporting which does not include Staff Costs. 

You could then use this hierarchy in your Cube View.

Lee

    C
    OneStream Employee
    ChristianWOneStream EmployeeAnswer
    OneStream Employee
    November 23, 2021

    You can create a dynamic calc (on the ud8 dimension) subtracting the members value from the nodes.

    R
    RamuV
    Member
    November 23, 2021

    CubeView will retain the Member filter assigned for the Account. The crude way is to mention the Accounts you want in the cubeview.

    An alternate account hierarchy can be created with the desired accounts only and also creating a UD8 member with a Stored value in the formula where you call the accounts and exclude the unwanted account.

     

    In cubeview Account member filter, you can also try the GetDataCell(ACC1+ACC2+.....):Name("MyAccounts")

    This might again look like lengthy string but might get you the desired cubeview.

    In the member filter, please try if you can get any output with the Where clause as well. I haven't tried it but will share feedback in case I test it. 

    C
    CrispinCAuthor
    Newcomer
    November 23, 2021

    Thanks for your comment. I'll have to try the first two suggestions. I did try GetDataCell but then I lose the drilldown functionality, unless I'm doing it wrong.

    C
    OneStream Employee
    ChristianWOneStream Employee
    OneStream Employee
    November 26, 2021

    You can add a drilldown path formula to the ud8 member. 

      L
      LeeB
      Contributor
      November 23, 2021

      You're not doing anything wrong there, GetDataCell will not allow drill-down. 

        D
        DeanHarper
        Newcomer
        November 26, 2021

        Hi, You can use the remove function in the member filter eg

        A#Staff_costs.tree.remove(A#Bonus_cost)

        Managing Consultant - AMCO Solutions

        C
        CrispinCAuthor
        Newcomer
        November 26, 2021

        Hi,

        Thanks for your advice. I've tried to apply the member filter 

        A#[Total Staff Costs].Tree.Remove.(A#R7101002)

        with R7101002 being the bonus account.

        However I get a result that says #ERROR - Unexpected Text.

        I'm not sure how to get around this.

         

        M
        MarcR
        Contributor
        November 26, 2021

        You should remove the . Before the bracket and the A# between the brackets. 
        .remove(R7101002) when you hover over the examples in the member selection pane you often see the correct syntax.

          C
          CrispinCAuthor
          Newcomer
          November 27, 2021

          Thanks for the great tips. I can see the data at the detailed level but was hoping to get to a single row called Staff costs excl Bonus. Using the remove function is there a way to deduct a single account from the group to get a single line that sums all other account lines in the group without showing those account lines, or does this have to be done through GetDataCell where drill down functionality is lost?

          Terms & ConditionsAccessibility statement