Forum Discussion

OSAdmin's avatar
OSAdmin
Valued Contributor II
6 years ago

Return a different Column Set Based on Point of View

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.

4 Replies

  • OSAdmin's avatar
    OSAdmin
    Valued Contributor II
    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 

     

  • OSAdmin's avatar
    OSAdmin
    Valued Contributor II
    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's avatar
    OSAdmin
    Valued Contributor II
    Originally posted by James Kirkby

    Thanks Jeff!

  • OSAdmin's avatar
    OSAdmin
    Valued Contributor II
    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...