Custom Member Frequency filter for all previous periods

[Sophie]

Hi All,

We'd like to have a Form Template available only until 2022M9. To do so, we've entered the following parameters in the member Frequency filter of the Form Template:

Is it possible to have a more dynamic way to apply frequency to all previous periods, other than hard listing it? 

Thanks in advance!

[Sweez]

Sophie, the only thing I can think of, assuming 2022M9 is not a work flow time (T#WFTime) or Global Time (T#Global), is something like this.  However, I am not sure its any easer to populate what you proposed above.  

T#2022M9.AllPriorinYear, T#YearPrior1(2022).Months,, T#YearPrior2(2022).Months, T#YearPrior3(2022).Months, T#YearPrior4(2022).Months, T#YearPrior5(2022).Months, T#YearPrior6(2022).Months, T#YearPrior7(2022).Months

[Sophie]

@Sweez , Thanks for the suggestion!

That works too.

We can also use T#2022M3.AllPriorInYear, T#2022.Prior1.Base, T#2022.Prior2.Base, T#2022.Prior3.Base, T#2022.Prior4.Base, T#2022.Prior5.Base, T#2022.Prior6.Base, T#2022.Prior7.Base. 

All of this works. My only concern is that we are having a long list of hard listed elements. Maybe there is an option where we can use a BRString, or a Parameter, or something comparable?

Thanks in advance!

[Sweez]

The use of an XFBR string will work as well.  This would have two main beneifts I think.  1) If for some reason you need to modify the string you will only need to do it in one place, and 2) reducing the chance of an error typing in that long string numerouse times.

[Sophie]

That's the spirit.

I've tried this rule in the Dashboard XBFRString folder in business rules:

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.YearsList
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("Yearlist") Then
Dim CurrentTimeId As Integer = api.Pov.Time.MemberPk.MemberId
Dim CurrentYear As Integer = api.Time.GetYearFromId(CurrentTimeId)

If (CurrentYear < 2022) Then
Return CurrentYear
End If
End If

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

Then, I entered this formula in the frequency field: 

T#2022M9.AllPriorInYear, T#XFBR(Yearlist, CurrentYear).Base

But it doesn't work

[LeeB]

I'd be interest to know if the XFBR works in the Form Frequency Member Filter.  

It looks like you are calling a Business Rule named "YearList"..

T#2022M9.AllPriorInYear, T#XFBR(Yearlist, CurrentYear).Base

But the rule is actually called "YearsList"....

OneStream.BusinessRule.DashboardStringFunction.YearsList

Lee

[Sophie]

Hi Lee!

Thanks for the answer.

I've updated it, but still not working. 

I've tried to put it in a bound list dashboard. The whole frequency setting stopped working. 

Whereas when using the XFBR, the first part of the formula (T#2022M9.AllPriorInYear) still worked

[Sweez]

Your current issue I think is your trying to use an api in a XFBR rule.  XFBR rules can not use those.    I tested the follow and it works.

T#2022M9.AllPriorInYear, XFBR(YearsList,Yearlist, BaseYr=2022)

--------------------------

Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports System.Windows.Forms
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.YearsList
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("Yearlist") Then

Dim sBaseYr As String = args.NameValuePairs.XFGetValue("BaseYr")

Return "T#YearPrior1(" & sBaseYr & ").Months, T#YearPrior2(" & sBaseYr & ").Months, T#YearPrior3(" & sBaseYr & ").Months, T#YearPrior4(" & sBaseYr & ").Months, T#YearPrior5(" & sBaseYr & ").Months, T#YearPrior6(" & sBaseYr & ").Months, T#YearPrior7(" & sBaseYr & ").Months"

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

[Sophie]

It worked in the Frequency filter?

[Sweez]

What do you mean by frequency?  View dimension?

[Sweez]

Let's try this Sophie.  If you use this rule what exactly does not work for you as you expect?  What do you expect as the result vs what do you get.

[Sophie]

Hi Sweez, 

I'm expecting to be able to use a formula in the frequency filter in Form Templates section, (Application  > Data collection > Form Templates) as below:

Only the AllPriorInYear part of the formula works. The concerned cube view appears in the workflows from January to August 2022. But it does not appear in 2021. 

However, I've tester the formula in a cube views here: 

And It does show all the required periods in a table:

I've tried both formulas in Frequency filter: XBFR and BRString. 

None of it makes the Cube view appear prior to 2022. 

[Sweez]

What do you want to show prior to 2022? All possible periods?  Workflow period?  POV period? I think you might just need to add to to the last you already have.  So if you want the current POV as well then you would use: T#POV, T#2022M9.AllPriorInYear, XFBR(YearsList,Yearlist, BaseYr=2022)

[Sophie]

Hi, Yes, I need all periods until 2022M9. including 2021 but excluding 2023. POV and WF are not the right solution, because it includes 2023 and does not exclude it. 

[Sweez]

Unless I am missing another way I think you would just list the prior years out.  For example, if the first year of your calendar is 2015 you would do: 

T#2015.Months, T#2016.Months, T#2017.Months, T#2018.Months, T#2019.Months, T#2020.Months, T#2021.Months, T#2022M9.AllPriorInYear, XFBR(YearsList,Yearlist, BaseYr=2022)

[Sophie]

Yes, that's what we did. There is therefore no need to use the XBFR.

[Stephenie]

Hi everyone, to clarify and expand on the Form Frequency Member Filter question, is it correct to say that Substitution Variables can not be used when defining the frequency filter? 

For example, I attempt to display a Form using T#|WFTimeFirstInYear| so that it would only display in the first month of each year making the filter dynamic and maintenance free, however the forms will not display when using that member filter syntax. The only way I've been successful in getting the Member Filter to display only for M1 of each year is by explicity spelling out each year. (i.e. T#2020M1, T#2021M2,...etc.)