Return a different Column Set Based on Point of View

Thanks very much! I did get a hint from the services team - regarding the parameter, in that I needed to pass the XFBR string into the row/column sharing via a literal value parameter rather than just putting the xfbr string into the row/column sharing option.

You can pass the PovTime in the string format below...|PovTime|...Then derive the integer value of the time member id (yours might be different depending on your time dimension is setup) to return the appropriate column set in the formula below.

Here is my version below :)

#Region "External Flash Column Set"

'XFBR(JLL_ParamHelper_Mgmt_Book_Col_Set, FLASH_COL_SET, PovTime=[|PovTime|])

' FLASH_COL_SET is function
' PovTime is variable external
' PovTimeName is internal variable
If args.FunctionName.XFEqualsIgnoreCase("FLASH_COL_SET") Then 

'In the Rule "PovTime" = |PovTime| - reference to the string in parameter 
Dim PovTimeName As String = args.NameValuePairs.XFGetValue("PovTime")

'Get the Member ID of Time member. Member ID is numeric for example 2019M9 = 2019014000
Dim PovTime As Integer = BRApi.Finance.Members.GetMemberId(si, dimTypeId.Time, PovTimeName)

'Write message to OneStream Error Log
'Dim yourValue As String = PovTime '<-- Enter message to write to log
'BRApi.ErrorLog.LogMessage(si, yourValue)

'Set the MemberID to string to manipulate
Dim POVTimeFullString As String = PovTime

'Manipulate the Member ID to get the last six digits of Time Member ID, so you can ignore the year
Dim numChars As Integer = 6
Dim PovMonth As String = ""
PovMonth = POVTimeFullString.Substring(POVTimeFullString.Length - numChars) 

'Dim yourValue2 As String = PovMonth '<-- Enter message to write to log
'BRApi.ErrorLog.LogMessage(si, yourValue2)

'Use the time member ID's to return a column set in place of the paramter

Select Case PovMonth 

'Q1 Periods
Case "002000","003000","004000","005000"
Return "EXT_FLASH_Q1_COL_SET"
'Q2 Periods

Case "001000","006000","007000","008000","009000"
Return "EXT_FLASH_Q2_COL_SET"
'Q3 Periods

Case "011000","012000","013000","014000" 
Return "EXT_FLASH_Q3_COL_SET"
'Q4 Periods 
Case "000000","015000","016000","017000","018000"
Return "EXT_FLASH_Q4_COL_SET"

End Select

End If
#End Region 

James,

If you have not received a reply from the OneStream User Community in regards to this question, you can always submit a support ticket via the support portal to have a dedicated OneStream Representative assist with your cube view setup.

Jeff

Thanks Jeff!

I did something like that to show different sets of rows based on the Entity the user had selected. It was a long time ago but assuming that in your case Time is going to drive the Col set do something like this:

1. Create a Parameter with the Time Values the user will select: eg |!Time!| (will leave up to you the type you want that to be)

2. Create a second Parameter: eg |!Time_Selected!|, Type Literal Value, Default Value: |!Time!|

3. Create a third Parameter: eg |!Report Col Set!|, Type: Literal Value, Default Value: XFBR("Name of your BR", "Name of your Function", Time = |!Time_Selected!|). I had issues trying to bound this step with the first one so I had to create the second parameter for it to work. Also, there are limitations on XFBR business rules I couldn't figure out a way to pass the POV to the BR so I pass that through the arguments of the function. This is the Parameter that you are going to use on your CV Col Sharing.

4. Create your BR

If args.FunctionName.XFEqualsIgnoreCase("Name of your Function") Then

Dim TimePoV As String = args.NameValuePairs.XFGetValue("Time")
If TimePoV= "Q1" Then
Return "Q1 Col Set" 'This should be the name of the cv col set your will be using for Q1
ElseIf TimePoV= "Q1" Then
Return "Q2 Col Set"
'Continue listing your Time Periods and then return the Col Set you will be using for that
End If

End If

Hope that gives you some direction. If you are going to use this in a form, this will not work for Form Type Cube View, you will have to do a Form Type Dashboard and create a Dashboard with your CV and attach that to your form.

Hernan

Ps: not a consultant, just an FPA guy eager to learn new things and not afraid of testing stuff out...