Return a different Column Set Based on Point of View

[OSAdmin]

Originally posted by James Kirkby

We are working to build a set of cube views which will eventually become pages of a management operating deck. The cube views will contain the same set of rows month to month, however, the column set will change as the year progresses.  Example:

Q1 - Actual, Budget, PY Actual, Variance to Budget, Variance to Prior year

Q2 - Actual, Outlook, Budget, PY Actual, Variance to Budget, Variance to Outlook, Variance Prior Year

Q3 - Actual, Forecast, Budget, PY Actual, Variance...etc....

I would like to have our cube views return the correct column set based on the POV Period. I'm guessing there is more than one way to do this. Does anyone have an example of this? I was thinking an XFBR String directly in the column sharing name would work, but I'm not sure if that's allowed. Or can you only do this in Dashboards with literal value parameters?

Thanks.

[OSAdmin]

Originally posted by Jeff Jones

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

[OSAdmin]

Originally posted by James Kirkby

Thanks Jeff!

[OSAdmin]

Originally posted by Hernan Carvi

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... 

[OSAdmin]

Originally posted by James Kirkby

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