09-27-2022
12:06 AM
- last edited on
05-23-2023
10:21 AM
by
JackLacava
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
09-27-2022 07:42 AM
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!
09-27-2022 11:56 AM
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".
09-27-2022 08:42 PM
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.