Custom Member Frequency filter for all previous periods

Sophie
New Contributor

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:

Sophie_0-1664976046671.png

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

 

Thanks in advance!

 

16 REPLIES 16

Sweez
New Contributor III

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
New Contributor

@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
New Contributor III

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
New Contributor

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
Contributor II

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
New Contributor

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
New Contributor III

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
New Contributor

It worked in the Frequency filter?

Sweez
New Contributor III

What do you mean by frequency?  View dimension?

Sweez
New Contributor III

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
New Contributor

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:

Sophie_0-1665996799748.png

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: 

Sophie_2-1665997014155.png

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

Sophie_3-1665997051228.png

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

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

 

 

 

 

Sweez
New Contributor III

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
New Contributor

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
New Contributor III

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
New Contributor

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

Stephenie
New Contributor II

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.)