Cube View Parametric driven column

[FM]

I have a cube view that I need to modify so that in the second column we are getting data that is driven by the selection that the user makes for the first column.

For example if the user selects a forecast for February to be in the first column, then the second column of the cube view would have to be the previous month’s forecast.

Do you have any suggestions on how to achieve this view?

Thank you in advance for any help and suggestions!

I am going to attach some views of what I have in here so far (The second column reads OP plan as I have copied this view from another similar Cube View).

[LeAnnTDang · Accepted solution]

Our consultant showed me to use Text attribute for scenario to get prior forecast. So in Jan_FCST, if you assign Text1 attribute as Feb_FCST, then in your OP filter, you can retrieve S#Fcsts.base.Where(Text1 = |!param_FCST!|). This setup allow when users select Feb_FCST for Forecast column, OP column will retrieve S#Fcsts.base.Where(Text1 = Feb_FCST) which would be Jan_FCST). 

This works for cube view, but our users want to use this setup for quick view. However I don't know of a way to make parameter prompt in quick view. If anyone has any tips to apply this for quick view, please advise. Thank you!

[LeAnnTDang · Accepted solution]

Our consultant showed me to use Text attribute for scenario to get prior forecast. So in Jan_FCST, if you assign Text1 attribute as Feb_FCST, then in your OP filter, you can retrieve S#Fcsts.base.Where(Text1 = |!param_FCST!|). This setup allow when users select Feb_FCST for Forecast column, OP column will retrieve S#Fcsts.base.Where(Text1 = Feb_FCST) which would be Jan_FCST). 

This works for cube view, but our users want to use this setup for quick view. However I don't know of a way to make parameter prompt in quick view. If anyone has any tips to apply this for quick view, please advise. Thank you!

[aricgresko]

For cube views, you could write an XFBR to derive the prior forecast based on the forecast that was selected via parameter.  For quick views, I would recommend setting up some literal parameters.  This would be something the admin would maintain, but it's very easy and only a once a month update.

Example of how you could setup these literal parameters:

|!CurrFcstScenario!| = Feb_FCST

|!PriorFcstScenario!| = Jan_FCST 

Then you teach the users how to use the above two literal value parameters in their quick views and XFGetCell's to automate the correct forecast scenarios.  

[SamRichards]

I would agree with the XFBR approach, this will give you the most flexibility in what you want to display based on the users selection in the scenario parameter.

[LeAnnTDang]

Our users would like to manage the selection in Prior Fcst Scenario by themselves, because they could be selecting prior month, prior quarter, etc... They have multiple quick views on a spreadsheet and would like one scenario selection to apply to all quick views, rather than going to each quick view and update scenario. Do you have suggestions on how to make that work? 

[aricgresko]

I'm pretty sure there isn't a way to update multiple quick views simultaneously.  You'd only be able to accomplish this with XFGetCell. 

[LeAnnTDang]

Thank you Aric. I ended up setting up an XFBR like as suggested in this chain and use a literal parameter to set up the XFBR

S#XFBR(BusinessRuleName, FunctionName, NameValuePair=|POVScenario|), so the main POVscenario would be the driver for prior forecast. 

That seems to work for now.  Thanks again for your help. 

[t_kaplanis · Accepted solution]

I agree with @aricgresko on perhaps using an XFBR business rule. The below code could be optimized in many ways, but should work for what you need. This way you do not need to use/manage any text values.

"YourRuleNameHere" will equal what you named your XFBR business rule, ensure whatever you named it matches the XFBR call you place in the cube view.

XFBR Cube View Line Call (What you would put on your cube view line instead of "S#|!param_FCST!|"):

XFBR(YourRuleNameHere, GetPriorFcst, CurrentFcst=|!param_FCST!|)

Create an XFBR Business Rule and paste in the following code:

Imports System
Imports System.Data
Imports System.Data.Common
Imports System.IO
Imports System.Collections.Generic
Imports System.Globalization
Imports System.Linq
Imports Microsoft.VisualBasic
Imports System.Windows.Forms
Imports OneStream.Shared.Common
Imports OneStream.Shared.Wcf
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Database
Imports OneStream.Stage.Engine
Imports OneStream.Stage.Database
Imports OneStream.Finance.Engine
Imports OneStream.Finance.Database

Namespace OneStream.BusinessRule.DashboardStringFunction.YourRuleNameHere   '<--------- NEED TO CHANGE "YourRuleNameHere" AND ADD TO XFBR CALL WITHIN CUBE VIEW
	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
				If args.FunctionName.XFEqualsIgnoreCase("GetPriorFcst") Then
						Dim CurrFcst As String = args.NameValuePairs("CurrentFcst")
				   	    Dim PriorFcst As String = String.Empty
					
						Select Case CurrFcst
							Case "Jan_FCST"
								PriorFcst = "Dec_FCST"
							Case "Feb_FCST"
								PriorFcst = "Jan_FCST"
							Case "Mar_FCST"
								PriorFcst = "Feb_FCST"
							Case "Apr_FCST"
								PriorFcst = "Mar_FCST"
							Case "May_FCST"
								PriorFcst = "Apr_FCST"
							Case "Jun_FCST"
								PriorFcst = "May_FCST"
							Case "Jul_FCST"
								PriorFcst = "Jun_FCST"
							Case "Aug_FCST"
								PriorFcst = "Jul_FCST"
							Case "Sep_FCST"
								PriorFcst = "Aug_FCST"
							Case "Oct_FCST"
								PriorFcst = "Sep_FCST"
							Case "Nov_FCST"
								PriorFcst = "Oct_FCST"
							Case "Dec_FCST"
								PriorFcst = "Nov_FCST"						
						End Select

					Return "S#" & PriorFcst

				End If

				Return Nothing
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try
		End Function
	End Class
End Namespace

[FM]

Thank you very much to everyone that commented in here.

t_kaplanis I ended up using your solution and modifying the business rule slightly. I appreciate the input. Thank you very much

[CubeView]

Hello!  I have a similar use case and have been working on creating a version of the business rule for our use case (the above example has been extremely helpful - THANK YOU). 

The Cube View is a Year Over Year Income Statement, the requirement is that the user can select either the full year, or a quarter.  I have a parameter built that allows the user to select the current year quarter when the report is ran. Parameter name = CVQTR.  This is in the POV for the CubeView

The first column presents current year Q1, this column is working as intended.

The second column would return prior year Q1 -- this is where the business rule would come in and set the appropriate T#POV for that column.  

I have created the business rule and checked the syntax - that part I believe is ready. 

Business rule name is PriorYrQtr

Where I am struggling is in the Cube View design - where do I add the logic to run this rule?
I have added the rule to the Column Member Filter for the PY Column (see below), but am getting an error. I have tried various formatting and am a bit lost. 

Any advice on where to add / call out business rule and in what format?  Possibly I need to take a different approach on how the current year column is also working?

[aricgresko]

You could simplify this by having your column CY use T#|!CVQTR!| and your column PY use T#PeriodPrior4(|!CVQTR!|).  No need for an XFBR

[CubeView]

Great idea!  Tested it and it works the quarterly views. However the when selecting a full year the second column returns 2018).  Possible two Cube Views is the simplest solution, but still would like to have the option to run Full Year or Quarterly in one report (which may put me back into the XFBR where I can control the relationship definition)

Hunted around more on this site and this logic works for both quarters and full year
T#YearPrior1(|CVTime|)Period(|CVTime|)

Solution found!  So helpful to see the various use cases and solutions here!