Forum Discussion

ChrisR1ch's avatar
ChrisR1ch
New Contributor III
16 days ago

Member Filter on Scenario properties

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?

Application Properties
Metadata
Time
  • ChrisR1ch's avatar
    ChrisR1ch
    16 days ago

    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

     

  • chul's avatar
    chul
    Contributor III
    16 days ago

    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?

  • ChrisR1ch's avatar
    ChrisR1ch
    New Contributor III
    16 days ago

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

  • chul's avatar
    chul
    Contributor III
    16 days ago

    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.

    • ChrisR1ch's avatar
      ChrisR1ch
      New Contributor III
      16 days ago

      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