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

[CEglinton]

Hi all,

I am building a CV that needs to show quarter data starting from Q1 2022 with YTD data after Q4 of each year and wanted to build it as dynamic as possible. I am trying to avoid having to update the filters each quarter if hardcoded.

I also have a parameter that prompts selection of a specific Quarter/Year.

The layout should look like the below.

Is there a way to do this dynamically?

I am also coming across an issue when calculating the growth % on rows where there is no data. It is giving an incorrect growth % of -100%. I have tried conditional formatting to try to hide these incorrect % but can't seem to get it to work and wondering if there is a better way.

Thanks in advance for any help!

[TheJonG · Accepted solution]

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.

[CEglinton]

Thank you so much for this! I will give it a shot!

[TheJonG]

---

I am also coming across an issue when calculating the growth % on rows where there is no data. It is giving an incorrect growth % of -100%. I have tried conditional formatting to try to hide these incorrect % but can't seem to get it to work and wondering if there is a better way.

 For this one, is the calc a dynamic calc, stored calc, or column/row math? Can you paste in the formula of the calc?

[CEglinton]

This is what I have for the formula. Open to suggestions!

GetDataCell(((Divide(CVC(QTD_CY), CVC(QTD_PY)))-1)*1000000)