Forum Discussion

SWilyums's avatar
SWilyums
Contributor
3 years ago

Alternatives for conditional expansion - are there any?

I have a reporting requirement.
Projects rollup into customers in UD1.  UD6 contains employees.
Reporting requirement is to display both customers and projects U1#Total_Customers.DescendantsInclusive. Also  display employee U6#Total_Employees & U6#Total_Employees.Descendants .  This issue is when the report renders they only want to see Total_Employees (UD6) at the customer level and  Total_Employees.DescendantsInclusive at the project level.  

example:
--UD1-- --UD6----------- Amt
Cust1  Total Employees 250
  Proj1  Total Employees 100
            Dave                     50
           Scott                      50
 Proj2 Total Employee   150
          Mike                       75
          Tom                        75

I know expansion is set at the CV level and its not flexible, so that is not an option.

I tried an XFBR rule with the idea to evaluate the UD1 member. If it has children then return "Total_Employees" for the UD6 member, else return "Total Employees.DescendantsInclusive".
The issue with the XFBR is I can't use |MFUD1| to pass the current UD1 member. Somehow I need to have it evaluate the current member selected for UD1 and I am stumped as to how.  Seems like I need a loop.

If args.FunctionName.XFEqualsIgnoreCase("GetTotalEmployee") Then
BRApi.ErrorLog.LogMessage(si, "Entering check for Total Employee")
Dim ProjectNumber As String = args.NameValuePairs("ProjectNumber")
BRApi.ErrorLog.LogMessage(si, "ProjectNumber =" & ProjectNumber)
Dim ProjectMemberID As Integer = BRApi.Finance.Members.GetMemberId(si, dimType.UD1.Id, ProjectNumber)
BRApi.ErrorLog.LogMessage(si, "ProjectMemberID =" & ProjectMemberID)
Dim objDimPk As DimPk = BRApi.Finance.Dim.GetDimPk(si,"Project")
Dim Parent As Boolean = BRApi.Finance.Members.HasChildren(si,objDimPk,ProjectMemberID)
BRApi.ErrorLog.LogMessage(si, "Parent =" & Parent)
If Parent=True Then
'Desc: If current project (UD1) has children then return Total_Employees (UD6)
Return "Total_Employees"
Else
'Desc:Else return IDescendants of Total_Employees
Return"Total_Employees.TreeDescendantsInclusive"
End If
End If

 

Any suggestions on how address the XFBR rule or other approaches?

Thank you

 

Cube View
  • Henning's avatar
    Henning
    Valued Contributor II
    3 years ago

    Hi, please get in touch with the Partner Enablement team to discuss the design of this project! I assume that you are currently in build and not live yet? I am strongly suggesting this, on the basis of your example above. The alarm bell is to see individual employees in UD6. Adding employees into a dimension in OneStream is strongly advised against. This kind of information should be handled in solutions such as people planning or custom tables!

     

    On the actual question, may I ask you to also share a screenshot of the row in the Cube View where you call the XFBR? Why do you say |MFUD1| does not work with an XFBR? I assume you are using UD1#Parent.Base in conjunction with |MFUD1|, which does indeed not work as the system checks the member filter on a row by row basis and can only return one member for each row (i.e. in the CV builder rows, not the ones you see in the report).

     

    However, if you follow my strong advice, you need another approach anyway. Please reconsider your design and move employees out of the metadata!

    • SWilyums's avatar
      SWilyums
      Contributor
      3 years ago

      Hello,

      Thank you for your reply and recommendations.  

      " I assume you are using UD1#Parent.Base in conjunction with |MFUD1|, which does indeed not work as the system checks the member filter" is a correct assumption and is why I said "The issue with the XFBR is I can't use |MFUD1| to pass the current UD1 member".

       

  • db_pdx's avatar
    db_pdx
    Valued Contributor
    3 years ago

    Two thoughts:

    1. Custom member lists are defined in Finance Rule types, try putting your logic there.  If you need a sample starter check our the List Ranked snippet.

    2. This should be possible with regular member expansions and clever conditional formatting.  It requires the use of indentation, the setup:

    On your row pov:

    U1#Total_Customers.ChildrenInclusive,

        U6#Total_Employees.ChildrenInclusive

    Header Formatting:

    If ((RowE1IndentLevel = 0) AND (RowE2IndentLevel > 0)) Then
    TextColor = Red
    End If

    That should highlight the rows you want to hide in Red.  In theory, you should be able to replace the TextColor=Red with IsRowsVisible=False; but that doesn't seem to work.  Feels like a bug to me.