Filter Time Members by Project Name

[nubz]

Hi,

Thanks for any help in advance. I’m pretty new to all of this, and haven’t done any programming since 2003. Any help would be appreciated.

Objective:

When a project is selected from the common page dropdown, I want to show data for the projects Program Increment in all reports in the dashboard.

Notes:

• There are custom members created in the time dimension for each PI for both projects using DateBetween
• I would like to add something to the dashboard definition but if it has to be in every applicable report, it is what it is
• I cannot use aggregate because each PI is its own stacked bar
  
  

The current solution path I am following is outlined below,

Using filter to isolate custom members in the Time dimension using MATCH and RegEx

Ex:
custom time dimension members
“Black Time Period 1”
“Black Time Period 2”
“Black Time Period 3”
“Black Time Period 4”
“White Time Period 1”
“White Time Period 2”
“White Time Period 3”
“White Time Period 4”

The filter I am using is in user defined measures:
Aggregate(Filter([Time].Members,
[Project].CurrentMember MATCHES (‘^Black$’)))

Thanks!
nubz

[nubz]

Also, in order to avoid the “cannot be a set” error when creating the measure, I had to aggregate. I do want all time periods for black to show individually.

Lastly, I have tried with all of the above time periods selected in the time dimension

[gerda.grantina]

Hi @nubz,
From the information you provided, it is a bit hard to understand what is your current setup and what you want to achieve. In this case, the best would be to contact eazyBI support directly and share the report definition and more information about your setup in Jira and how you want the data to be represented in the eayzBI report.

best,
Gerda // support@eazyBI.com

[nubz]

@gerda.grantina

Thanks, Gerda.
Let me try to clarify with screenshots

This is the current chart display

Notice it is showing the data for the number of features in status for iQies and EQRS

There is a common page selector for project by category, which has two values, iQies and EQRS

Time is in rows

I have custom members in the time dimension

The desired result is, that when selecting a project from the common page selector for project, the report only shows members that match the project name. For example, if iQies is selected in the common page, I would see this

and if EQRS was chosen in the common page selector, I would see this:

Here is the current report defintion with measures from both project categories selected in the time dimension

{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “3 - Features in Status by PI Start”,
“folder_name”: “program test reports”,
“result_view”: “bar_chart”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:[“[Measures].[Open issues until today]”,“[Measures].[Issues history]”],“members”:[]},{“name”:“Transition Status”,“selected_set”:[“[Transition Status].[Funnel]”,“[Transition Status].[Analyzing (SAFe)]”,“[Transition Status].[Refining]”,“[Transition Status].[Ready for PI]”,“[Transition Status].[Feature Definition]”,“[Transition Status].[Feature Refinement]”,“[Transition Status].[Feature Deep Dive]”,“[Transition Status].[PI Ready]”],“members”:[],“bookmarked_members”:[{“depth”:1,“name”:“Implementing”,“full_name”:“[Transition Status].[Implementing]”},{“depth”:1,“name”:“Funnel”,“full_name”:“[Transition Status].[Funnel]”},{“depth”:1,“name”:“Analyzing (SAFe)”,“full_name”:“[Transition Status].[Analyzing (SAFe)]”},{“depth”:1,“name”:“Analyzing”,“full_name”:“[Transition Status].[Analyzing]”},{“depth”:1,“name”:“Refining”,“full_name”:“[Transition Status].[Refining]”},{“depth”:1,“name”:“Ready for PI”,“full_name”:“[Transition Status].[Ready for PI]”},{“depth”:1,“name”:“Ready for Validation”,“full_name”:“[Transition Status].[Ready for Validation]”},{“depth”:1,“name”:“Ready for Release”,“full_name”:“[Transition Status].[Ready for Release]”},{“depth”:1,“name”:“Resolved”,“full_name”:“[Transition Status].[Resolved]”},{“depth”:1,“name”:“PI Ready”,“full_name”:“[Transition Status].[PI Ready]”},{“depth”:1,“name”:“Feature Definition”,“full_name”:“[Transition Status].[Feature Definition]”},{“depth”:1,“name”:“Feature Refinement”,“full_name”:“[Transition Status].[Feature Refinement]”},{“depth”:1,“name”:“Feature Deep Dive”,“full_name”:“[Transition Status].[Feature Deep Dive]”}]}]},“rows”:{“dimensions”:[{“name”:“Time”,“selected_set”:[“[Time].[iQies PI 2 Start Date]”,“[Time].[iQies PI 3 Start Date]”,“[Time].[iQies PI 4 Start Date]”,“[Time].[iQies PI 5 Start Date]”,“[Time].[iQies PI 6 Start Date]”,“[Time].[PI 14 - EQRS]”,“[Time].[PI 15 - EQRS]”,“[Time].[PI 16 - EQRS]”,“[Time].[PI 17 - EQRS]”,“[Time].[PI 18 - EQRS]”],“members”:[],“bookmarked_members”:[]}],“nonempty_crossjoin”:true},“pages”:{“dimensions”:[{“name”:“Project”,“selected_set”:[“[Project.Category].[All Projects by category]”],“members”:[{“depth”:0,“name”:“All Projects by category”,“full_name”:“[Project.Category].[All Projects by category]”,“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“iQIES”,“full_name”:“[Project.Category].[iQIES]”,“drillable”:true,“parent_full_name”:“[Project.Category].[All Projects by category]”}],“bookmarked_members”:[{“depth”:1,“name”:" End-Stage Renal Disease Quality Reporting System",“full_name”:“[Project].[ End-Stage Renal Disease Quality Reporting System]”,“drillable”:true,“key”:“EQRS”},{“depth”:1,“name”:“iQIES Program Board “,“full_name”:”[Project].[iQIES Program Board ]”,“drillable”:true,“key”:“IQIESPB”},{“depth”:1,“name”:“iQIES Program Project-NEW”,“full_name”:“[Project].[iQIES Program Project-NEW]”,“drillable”:true,“key”:“IQIESPP”}],“current_page_members”:[“[Project.Category].[iQIES]”]},{“name”:“Issue Type”,“selected_set”:[“[Issue Type].[Epic]”],“members”:[{“depth”:1,“name”:“Epic”,“full_name”:“[Issue Type].[Epic]”}],“bookmarked_members”:[{“depth”:1,“name”:“Epic”,“full_name”:“[Issue Type].[Epic]”}],“current_page_members”:[“[Issue Type].[Epic]”]}]},“options”:{“nonempty”:“columns”},“view”:{“current”:“bar_chart”,“maximized”:false,“bar_chart”:{“stacked”:true,“vertical”:true,“swap_axes”:false,“data_labels”:“values”,“series_options”:{“To Do”:{“color”:“#344563”},“In Progress”:{“color”:“#2F579C”},“Done”:{“color”:“#00875A”},“Funnel”:{“color”:“#274380”},“Refining”:{“color”:“#FFBC29”},“Ready for PI”:{“color”:“#0093B5”},“Implementing”:{“color”:“#34AD70”}}},“table”:{}},“calculated_members”:[],“description”:“\u003cH4\u003e\u003cB\u003eFeatures in Status on PI Start Dates\u003c/H4\u003e\u003c/B\u003e\nThe number of Features in a status on the first day of each PI.”}
} ],
“calculated_members”: [{“dimension”:“Measures”,“name”:“Open issues”,“format_string”:“#,##0”,“formula”:“CASE WHEN [Issue].CurrentMember.Level.Name \u003c\u003e ‘Issue’ THEN\n Cache(\n NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),\n Cache([Measures].[Issues created]\n - [Measures].[Issues resolved])\n ))\n + [Measures].[Issues created]\n - [Measures].[Issues resolved]\n )\nWHEN [Time].CurrentHierarchyMember IS [Time].CurrentHierarchy.DefaultMember\nTHEN NonZero([Measures].[Issues due])\nELSE\n – optimized formula for drill through Issue\n NonZero(IIF(\n DateBeforePeriodEnd(\n [Issue].CurrentMember.get(‘Created at’),\n [Time].CurrentHierarchyMember) AND\n NOT DateBeforePeriodEnd(\n [Issue].CurrentMember.get(‘Resolved at’),\n [Time].CurrentHierarchyMember),\n ([Time].CurrentHierarchy.DefaultMember,\n [Measures].[Issues created]),\n 0\n ))\nEND”},{“dimension”:“Measures”,“name”:“Issues history”,“format_string”:“#,##0”,“formula”:“Cache(\n NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),\n Cache([Measures].[Transitions to]\n - [Measures].[Transitions from])\n ))\n + [Measures].[Transitions to]\n - [Measures].[Transitions from]\n)”},{“name”:“PI 14 - EQRS”,“dimension”:“Time”,“formula”:“[Time].[Day].DateMember(‘Apr 4 2021’)”,“format_string”:“mmm dd yyyy”},{“name”:“PI 15 - EQRS”,“dimension”:“Time”,“formula”:“[Time].[Day].DateMember(‘Jul 20 2021’)”,“format_string”:“mmm dd yyyy”},{“name”:“Open issues until today”,“dimension”:“Measures”,“formula”:“CASE WHEN\n DateCompare(\n [Time].CurrentHierarchyMember.StartDate,\n now()\n ) \u003c 0\nTHEN [Measures].[Open issues]\nEND”,“format_string”:“”},{“name”:“PI 16 - EQRS”,“dimension”:“Time”,“formula”:“[Time].[Day].DateMember(‘Oct 13 2021’)\n”,“format_string”:“”},{“name”:“PI 17 - EQRS”,“dimension”:“Time”,“formula”:“[Time].[Day].DateMember(‘Jan 19 2022’)”,“format_string”:“”},{“name”:“PI 18 - EQRS”,“dimension”:“Time”,“formula”:“[Time].[Day].DateMember(‘Apr 13 2022’)”,“format_string”:“”},{“name”:“iQies PI 2 Start Date”,“dimension”:“Time”,“formula”:“[Time].[Day].DateMember(‘Apr 14 2021’)”,“format_string”:“”},{“name”:“iQies PI 3 Start Date”,“dimension”:“Time”,“formula”:“[Time].[Day].DateMember(‘Jul 7 2021’)”,“format_string”:“”},{“name”:“iQies PI 4 Start Date”,“dimension”:“Time”,“formula”:“[Time].[Day].DateMember(‘Oct 13 2021’)”,“format_string”:“”},{“name”:“iQies PI 5 Start Date”,“dimension”:“Time”,“formula”:“[Time].[Day].DateMember(‘Jan 19 2022’)”,“format_string”:“”},{“name”:“iQies PI 6 Start Date”,“dimension”:“Time”,“formula”:“[Time].[Day].DateMember(‘Apr 20 2022’)”,“format_string”:“”}]
}

The approach I have been taking is probably not the best, so any solution approaches are appreciated

[gerda.grantina]

Hi @nubz ,
Thanks for the detailed information you provided!

In that case, you can create a new measure in Measure dimension by which you can filter your rows (>0). The measure will return 1 when the conditions are true - if the selected “Category” is “EQRS” and the Time member name contains “EQRS” or “Category” Is “iQies” and the Time member name contains “iQies”.
If you want to check the project name, then you need to replace “Category” in the formula with “Project”.

CASE WHEN
  Generate(
    DescendantsSet([Project].CurrentHierarchyMember, [Project].CurrentHierarchy.Levels("Category")),
    [Project].CurrentHierarchyMember.Name,
    ","
  ) MATCHES ".*(^|,)EQRS($|,).*"
  AND
  Cast([Time].CurrentHierarchyMember.Name as string) MATCHES ".*EQRS"
THEN
  1
WHEN
  Generate(
    DescendantsSet([Project].CurrentHierarchyMember, [Project].CurrentHierarchy.Levels("Category")),
    [Project].CurrentHierarchyMember.Name,
    ","
  ) MATCHES ".*(^|,)iQies($|,).*"
  AND
  Cast([Time].CurrentHierarchyMember.Name as string) MATCHES "iQies.*"
THEN
  1
END

After you have filtered your rows, you can remove the measure, and the filter will remain active.

best,
Gerda

[nubz]

@gerda.grantina

Thanks SO much. I have a lot to learn! I have one more question. Is there a way to apply the filter in the measure script, or create another measure that doesn’t appear to the user? People tend to fiddle and we want to limit it.

Thanks again.

nubz

[gerda.grantina]

Hi @nubz ,
I would suggest adding this report to the dashboard for users to use. In the dashboard, this type of filter is not visible, and users won’t be able to fiddle with it.

best,
Gerda