Skip to main content
Member
Jun 9, 2022 10:42 PM UTC
Solved

GetDataCell in Cube View or Quick View with Dynamic Members

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.

OneStream Employee
Jun 10, 2022 8:10 AM UTC

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

2 replies

OneStream Employee
Jun 10, 2022 8:10 AM UTC

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

rjgossAuthor
Member
Jun 10, 2022 4:40 PM UTC

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.