06-09-2022
06:42 PM
- last edited
a week ago
by
JackLacava
Is there any way to do a GetDataCell in a Cube View or Quick View where the members are dynamic/based on some criteria? The formula below doesn't work, but hopefully give you an idea of what I wish did work.
GetDataCell(SUM(E#TopEntityMember.Base.Where(Text5 = 'Germany'))):Name(GermanySum)
I know you can add specific members together in the GetDataCell, but I'd like to be able to have one row that shows the sum, which is based on a property value that can be changed or be able to use it as a member filter and not have to display it in the grid.
Solved! Go to Solution.
06-10-2022 04:10 AM
You could do something like this in your cubeView row definition:
GetDataCell(XFBR(CubeViewLists,GetSumMemberFormula,DimName=[|WFEntityDim|],MemberFilter=[E#Clubs.Base.Where(Text2 = Mfg)])):Name("Total Mfg Entities")
And then in your Business Rules XFBR string, have a business rule like this:
____________________________________
Namespace OneStream.BusinessRule.DashboardStringFunction.CubeViewLists
Public Class MainClass
Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardStringFunctionArgs) As Object
Try
'-- Example Usage XFBR(CubeViewLists,GetSumMemberFormula,DimName=[|WFEntityDim|],MemberFilter=[])
If args.FunctionName.XFEqualsIgnoreCase("GetSumMemberFormula") Then
Dim strMbrFilter As String = args.NameValuePairs("MemberFilter")
Dim strDimName As String = args.NameValuePairs("DimName")
Dim dpk As DimPk = BRApi.Finance.Dim.GetDimPk(si, strDimName)
If dpk Is Nothing Then
Throw New XFException(si, "Error, specified dimension does not exist", strDimName)
Else
Dim lstMI As List(Of MemberInfo) = brapi.Finance.Members.GetMembersUsingFilter(si, dpk, strMbrFilter, True )
Dim strList As String = String.Join(" + ", (From l In lstMI Select "E#[" & l.Member.Name & "]").ToArray())
Return strList
End If
End If
Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
End Class
End Namespace
06-10-2022 04:10 AM
You could do something like this in your cubeView row definition:
GetDataCell(XFBR(CubeViewLists,GetSumMemberFormula,DimName=[|WFEntityDim|],MemberFilter=[E#Clubs.Base.Where(Text2 = Mfg)])):Name("Total Mfg Entities")
And then in your Business Rules XFBR string, have a business rule like this:
____________________________________
Namespace OneStream.BusinessRule.DashboardStringFunction.CubeViewLists
Public Class MainClass
Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardStringFunctionArgs) As Object
Try
'-- Example Usage XFBR(CubeViewLists,GetSumMemberFormula,DimName=[|WFEntityDim|],MemberFilter=[])
If args.FunctionName.XFEqualsIgnoreCase("GetSumMemberFormula") Then
Dim strMbrFilter As String = args.NameValuePairs("MemberFilter")
Dim strDimName As String = args.NameValuePairs("DimName")
Dim dpk As DimPk = BRApi.Finance.Dim.GetDimPk(si, strDimName)
If dpk Is Nothing Then
Throw New XFException(si, "Error, specified dimension does not exist", strDimName)
Else
Dim lstMI As List(Of MemberInfo) = brapi.Finance.Members.GetMembersUsingFilter(si, dpk, strMbrFilter, True )
Dim strList As String = String.Join(" + ", (From l In lstMI Select "E#[" & l.Member.Name & "]").ToArray())
Return strList
End If
End If
Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
End Class
End Namespace
06-10-2022 12:40 PM
Thank you. This works great. Coming from a finance/accounting background rather than IT/programming, my first inclination isn't to have to use code, so this is still a steep learning curve. Thanks again.