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
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.
Thank you so much for this! I will give it a shot!
