Cube View That Dynamically Populates Quarters and YTD & Growth % Issue

Hi there - For the column issue, I am assuming the user would select a Year and Quarter and the desired columns would be - Full year of selected year, all quarters including and prior to the selected quarter & year, and then 2 prior years of quarters and full year. Is this correct?

If so, I can't think of a member filter/expansion that would generate that but there are two ways you could solve it. The first one would be to create 15 columns which would contain all possible columns. So it would be something like

• Col15 - T#Year(|!ParamForTime!|)
• Col14 - T#Year(|!ParamForTime!|)Q4
• Col13 - T#Year(|!ParamForTime!|)Q3
• Col12 - T#Year(|!ParamForTime!|)Q2
• Col11 - T#YearPrior1(|!ParamForTime!|)
• Col10 - T#YearPrior1(|!ParamForTime!|)Q4
• ...

So now you have 15 columns but you don't necessarily want to show all 15 because if the user selects 2024Q2 you would not want to show 2024Q3 & 2024Q4. You can create an XFBR rule which dynamically hides the column based on its relation to the selected year/quarter. It would look like this:

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

Namespace OneStream.BusinessRule.DashboardStringFunction.ParamHelpers
	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("HideColumnBasedOnTime") Then
					
					Dim selectedTime As String = args.NameValuePairs.XFGetValue("SelectedTime","None")
					Dim mfTime As String = args.NameValuePairs.XFGetValue("MFTime","None")
					
					Dim selectedTimeID As String = BRApi.Finance.Members.GetMemberId(si,dimtypeid.Time,selectedTime)
					Dim timeDimPk As DimPk = BRApi.Finance.Dim.GetDimPk(si, "Time")
					Dim convertedMFTimeList As List(Of MemberInfo) = BRApi.Finance.Members.GetMembersUsingFilter(si,timeDimPk,mftime,True)
					Dim convertedMFTimeID As String = convertedMFTimeList.Item(0).Member.MemberId
					
					If convertedMFTimeID > selectedTimeID Then
						Return "IsColumnVisible = False"
					Else
						Return "IsColumnVisible = True"
					End If
										
					Return Nothing
				End If

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

The XFBR rule will be referenced in the Header Format of each column:

XFBR(ParamHelpers, HideColumnBasedOnTime,SelectedTime = |!ParamForTime!|, MFTime = [T#Year(|!ParamForTime!|)])

The catch here is that the entire member filter for each column must be passed into the rule (see bolded part). So this will be different for each column.

XFBR(ParamHelpers, HideColumnBasedOnTime,SelectedTime = |!ParamForTime!|, MFTime = [T#Year(|!ParamForTime!|)])

This is because we need to resolve each member filter into an ID and compare it against the ID of the time period selected by the user. 

The second way would involve having one column and deriving the entire column set e.g. "T#Year(|!ParamForTime!|), T#Year(|!ParamForTime!|)Q3, T#Year(|!ParamForTime!|)Q2" with an XFBR rule.