Questions about using Aggregate and Except with [project]

Questions & Answers
1

HI,
I am new to eazyBI and trying to create my first report.
What I am trying to do is in a set of 120 projects, I need to get the number of issues closed and the number of story points shipped in a year. I can get that fairly easily, and I can drill through issues.
What I am having an issue with is setting a toggle so I can show stories and points for the entire set of projects, or without the service desk projects.

The easiest way thing I can do is aggregate function where I exclude the project category for intake projects; however when I do that it displays fine but I cannot drill through.
this is my exclude for the category:

Aggregate(
  Except(
    [Project.Category].[Category].Members,
      {
      [Project.Category].[ESD Intake]
      }
  )
)

somehow this is expensive and when I try to ā€œdrill through issueā€ type it times out.

So I am trying to do an exclude at the project level (their are only six intake projects)
so I do this:

Aggregate(
  Except([Project].[Project].Members,
    {
      [Project].[Intake 1],
      [Project].[Intake 2],
      [Project].[Intake 3],
      [Project].[Intake 4]
    }
  )
)

but this never filters out out the projects.
I need the toggle to be in my filters and not selectable by the end user.

any help would be appreciated.
Thanks
Steve

1 Like
2

Hi @stanacker,

You might consider creating separate accounts for different project categories. For example, a separate account for Service Management projects - I believe the end-users might be interested in slightly different metrics. And a separate account for Sofware projects to follow the closed issues and story points.
In import options, you can specify the project category instead of listing projects one by one: Jira Issues Import - eazyBI for Jira

For more details on how to manage accounts and what benefits users will get from this, see this training video: Training videos on specific topics - eazyBI for Jira.

Best,
Zane / support@eazyBI.com

3

Hi,
I am having a similar problem in that I have many projects being imported and for one report I only need a subset of these projects. Iā€™m using aggregateā€¦except but it doesnā€™t appear to work, all projects are being included in the report.

Aggregate(
Except(
[Project].Members,
{ [Project].[Data],
[Project].[Infra],
[Project].[Main],
[Project].[Design]
}
)
)

I think this should work? I shouldnā€™t need to create a new project to filter these out from one report.

Any help you can give most appreciated.

Thanks,
Julian

*** I knew Iā€™d fix it as soon as I asked for help! :slight_smile:
Changing the expression to [Project].[Project].Members has resolved the issue.
Any chance you could explain why?

Thank you

1 Like
4

Hi @Julian_Bolton, well spotted =)

Function Members expects the information on which dimension and what level members it should look for:

[dimension].[hierarchy level].Members

Any item in a dimension (including top member ā€œAll Projectsā€) is a member of that dimension. And each dimension member could be described by its full name or location in the dimension hierarchy. Please see the documentation for the naming convention:
Calculated measures and members

The expression [Project].Members represents only the dimension ā€œProjectā€ without any level.
You may read this expression as getting all members in the Project dimension and that woudl include all dimension members from all hierarchy levels, like, ā€œAll Projectsā€, all individual projects, and all individual project categories.

On the other hand, the expression [Project].[Project].Members describes the dimension and the hierarchy level at which to list all members.
You may read this expression as getting Project dimension members at the Project level.

Best,
Zane / support@eazyBI.com

2 Likes
5

Fantastic Zane, thanks so much for the clear explanation.
I think that will help me for other problems Iā€™ve seen and not fully understood!

Julian

1 Like
6

Hi Zane, Iā€™m trying something similar but Iā€™m getting an error. Can you please help? I tried to remove the 2nd project before architecture but it keeps giving the error

Aggregate(
  Except(
  [Project].[Project].Members,
  [Project].[Project].[Architecture])
)

This is my error

Failed to parse query, try to make query simpler.
Or maybe saved report uses deleted calculated member.
Error message:
All arguments to function ā€˜{}ā€™ must have same hierarchy.

7

Hi @Kickbackjack,

If you want to group all projects except for project ā€œArchitectureā€, then the expression should be like this:

Aggregate(
  Except(
  --get all projects (members) from the dimension Project, level Project
  [Project].[Project].Members,
  --exclude a specific project Architecture from the Project dimension
  [Project].[Architecture])
)

Use the member and function library when writing the calcauted members; eazyBI will suggest valid options for functions and dimension member names (Calculated measures and members).

Kind regards