GetDataCell in Cube View or Quick View with Dynamic Members

rjgoss
New Contributor III

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.

1 ACCEPTED SOLUTION

ChrisLoran
Valued Contributor

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

View solution in original post

2 REPLIES 2

ChrisLoran
Valued Contributor

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

rjgoss
New Contributor III

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.