Filter projects by name and category

[Sanat]

hi there,

I am trying to figure out how to list specific projects only from the drop down in my report.
We have a requirement to list only software development projects and unfortunately, they are all in same category, so we can’t use category to separate these projects out.

I am trying below query but to no avail-

Aggregate({

Filter([Project].Members,
[Project].CurrentHierarchyMember.Name MATCHES “Credit Card Channel” || “Mobile Team” || “Debit card team”)

})

First of all, I read somewhere in these forums that filter slows down query execution, so I do not prefer to use it (please suggest a better solution without using filter, if there is !).
Secondly, how do I add more project names to this list? I have tried using comma, OR operator, single | but nothing is working.
Lastly, I want to be able to see the data at project level by selecting projects from the drop down as well as, be able to roll up to the portfolio level and see overall stats. I guess, something like “all projects” at top?

many thanks,

[daina.tupule]

Yes, you are correct. You can create calculated members in the Project dimension using a formula. It might slow down reports, though. It might multiply calculations by the count of projects pulled in by the formula.

You are quite close to this formula. However, you would like to use one string value as a parameter for matches. I also would suggest explicitly setting the dimension and the level [Project].[Project] to address members.

Aggregate({
Filter([Project].[Project].Members, 
[Project].CurrentMember.Name MATCHES 'Credit Card Channel| Mobile Team|Debit card team')
})

I used CurrentMember instead of CurrentHierarchyMember. Calculations in the dimension should address members of one specific hierarchy explicitly. In this case, the member of the default hierarchy. However, in most cases, both approaches will work.

| works as or within a string expression for matches.
|| concatenates two strings.

As an alternative, if you have many projects and you would like to group them, I would suggest considering using custom hierarchies. You can import additional property for the project and then use this property to build a custom hierarchy. It will allow selecting one hierarchy member in reports and it will work as a single selection without multiplication of results.

Here is one step-by-step guide on how to use eazyBI to prepare a file for data import, set the mapping, and import data. It is based on users, but a similar approach could be applied to projects as well:

Import properties for Jira Assignees and build custom hierarchy

eazyBI allows importing additional properties for dimensions and creating custom hierarchies. In this example, I will walk you through how to define and import property Department for Assignee and build a custom hierarchy in the dimension Assignee. You can use the same steps to add more details for assignees or any other user dimension. Create a list of users for your source data. You can use eazyBI as a basis to create your data source. Create a new empty report in eazyBI. Use Transition Aut…

Daina / support@eazybi.com

[Sanat]

thank you Diana ! that worked except for one project that has a bracket in its name and for some reason, EazyBi can’t figure that out.

example:
Aggregate({
Filter([Project].[Project].Members,
[Project].CurrentMember.Name MATCHES ‘Team name - A|Team B|Team C (offshore)|Team Name D’)
})

Could you please suggest how to differentiate strings in concatenated syntax?

We have a lot of projects, so we did consider your suggestion to create a separate category and use the category to list projects. That is still WIP. The newly created category is currently not showing the projects in EazyBi.

thanks again, appreciate if you could drop a hint to segregate names in the above syntax.