Skip to main content
C
ChrisR1ch
December 5, 2024
Solved

Member Filter on Scenario properties

  • December 5, 2024
  • 3 replies
  • 0 views

I would like to apply a member filter on a scenario, which will return a list of a scenarios based on it's properties like Name, Description, WorkflowTime, WorkflowStartTime, and WorkflowEndTime...i.e.  WorkflowTime = |someYear| or WorkflowStartTime = |someYear| or WorkflowEndTime = |someYear|

I can do this S#Scenario.where(Name contains paramScenario) 
I can do this S#Scenario.where(Description contains paramDescription)

I cannot do this S#Scenario.where(WorkflowTime = 2025)
I cannot do this S#Scenario.where(WorkflowStartTime = 2025)
I cannot do this S#Scenario.where(WorkflowEndTime = 2025)

What am I missing?

Best answer by ChrisR1ch

Just thought I would add what I did to resolve this issue.

I created a parameter, of type Member List
I set the Display Items and Value Items (comma delimited) as a call to an XBFR rule: XFBR(XFBR_Member_List, SP_Source_Scenario_List,PoVTime=|WFTime|)
I set my Combo Box, Bound Parameter to the previously created parameter

In my XFBR rule I return the list like this...

Dim POVWorkflowTime As String = args.NameValuePairs.XFGetValue("PoVTime")
Dim memberFilter As String = "S#SpendPlanControl.Base.Where(Name DoesNotContain Working)"
Dim ScenarioLists As List (Of MemberInfo) = BRApi.Finance.Metadata.GetMembersUsingFilter(si, "S_BudEx", memberFilter, True)
Dim memberString As String = String.Empty

For Each scenarioMbr In ScenarioLists	
	Dim ScenarioMember As MemberInfo = BRApi.Finance.Metadata.GetMember(si, DimType.Scenario.Id, scenarioMbr.Member.Name, True)
	Dim ScenarioMemberID As Integer = ScenarioMember.Member.MemberId
	
	'Get Scenario member property WorkflowTime for comparison
	Dim scenarioListWorkflowTime As String = ScenarioMember.GetScenarioProperties.WorkflowTime.GetStoredValue.ToString.Substring(0, 4)
	Dim scenarioListWorkflowStartTime As String = ScenarioMember.GetScenarioProperties.WorkflowStartTime.GetStoredValue.ToString.Substring(0, 4)
	Dim scenarioListWorkflowEndTime As String = ScenarioMember.GetScenarioProperties.WorkflowEndTime.GetStoredValue.ToString.Substring(0, 4)

	'Compare Scenario List Workflow time to POV Workflow Time
	If  scenarioListWorkflowTime = POVWorkflowTime Then
		memberString = memberString & scenarioMbr.Member.Name & ","
	End If				
Next 

Return memberString

 

3 replies

C
chul
December 5, 2024

The Where Clause Properties box in the Member Filter Builder list all properties that can be filtered on. Unfortunately, the ones you listed above aren't included in that list. It may be a good IdeaStream enhancement?

C
ChrisR1chAuthor
December 5, 2024

Thanks for the reply......would you happen to know a workaround for this limitation?

C
chul
December 5, 2024

You can use the text fields on the scenario. Granted, it's redundant but it will allow you to use the where clauses. If you didn't want an admin to have to remember to add the text fields when new scenarios are created, you could write a BR to add them and schedule it so the admin wouldn't need to remember to run it.

C
ChrisR1chAuthorAnswer
December 6, 2024

Just thought I would add what I did to resolve this issue.

I created a parameter, of type Member List
I set the Display Items and Value Items (comma delimited) as a call to an XBFR rule: XFBR(XFBR_Member_List, SP_Source_Scenario_List,PoVTime=|WFTime|)
I set my Combo Box, Bound Parameter to the previously created parameter

In my XFBR rule I return the list like this...

Dim POVWorkflowTime As String = args.NameValuePairs.XFGetValue("PoVTime")
Dim memberFilter As String = "S#SpendPlanControl.Base.Where(Name DoesNotContain Working)"
Dim ScenarioLists As List (Of MemberInfo) = BRApi.Finance.Metadata.GetMembersUsingFilter(si, "S_BudEx", memberFilter, True)
Dim memberString As String = String.Empty

For Each scenarioMbr In ScenarioLists	
	Dim ScenarioMember As MemberInfo = BRApi.Finance.Metadata.GetMember(si, DimType.Scenario.Id, scenarioMbr.Member.Name, True)
	Dim ScenarioMemberID As Integer = ScenarioMember.Member.MemberId
	
	'Get Scenario member property WorkflowTime for comparison
	Dim scenarioListWorkflowTime As String = ScenarioMember.GetScenarioProperties.WorkflowTime.GetStoredValue.ToString.Substring(0, 4)
	Dim scenarioListWorkflowStartTime As String = ScenarioMember.GetScenarioProperties.WorkflowStartTime.GetStoredValue.ToString.Substring(0, 4)
	Dim scenarioListWorkflowEndTime As String = ScenarioMember.GetScenarioProperties.WorkflowEndTime.GetStoredValue.ToString.Substring(0, 4)

	'Compare Scenario List Workflow time to POV Workflow Time
	If  scenarioListWorkflowTime = POVWorkflowTime Then
		memberString = memberString & scenarioMbr.Member.Name & ","
	End If				
Next 

Return memberString

 

Terms & ConditionsAccessibility statement