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