Calculated member for aggr projects except a few issues

Questions & Answers
1

Im a bit struggling with creating a calculated member for an “all except for X”.

I have a selection of 4 projects. But from 2 of these projects I need to exclude a few issues.

So initially I tried to make a calculated member in the projects dimension and trying to add an except in there for a few [Issue].xxx. But that did not work…
Then I tried to make a calculated member in the issue dimension, where I basically just did something like this:

[Issue].[CalculatedMemberWithExcept]:

Aggregate(
Except(
[Issue].[ProjectName],
{ [Issue].[ProjectName].[MPOI-20 Support Delivery],
[Issue].[ProjectName].[MPOI-19 Support Standby Weekend],
[Issue].[ProjectName].[MPOI-11 Holiday],
[Issue].[ProjectName].[MPOI-7 Support Standby Night],
[Issue].[ProjectName].[MPOI-4 Illness],
[Issue].[ProjectName].[MPOI-2 National Holiday]
}
)
)

But this does not work. And I’ve tried things like adding .CurrentMember, AllMembers etc. It does not seem to work. I dont really understand the whole members/children/parent structure that well yet.

Anyway, then I would make a 2nd calculated member to include the other projects + the above calculated member (CalculatedMemberWithExcept):

Aggregate(
{
[Issue].[ProjectOne],
[Issue].[ProjectTwo],
[Issue].[ProjectThree],
[Issue].[CalculatedMemberWithExcept]
}
)

What is the correct approach to this? Do it in Projects or Issues dimension? And how would the calculated member look, either way?

Thanks in advance.

Robin

2

Hi Robin,

Here I would recommend one of two approaches

  1. use JQL filter in import options to not import these 6 issues at all. Be aware that would remove all 6 of them from all eazyBI reports from the eazyBI account.
    Note that you can create as many eazyBI accounts as you need and perhaps split projects and issues per different reporting purposes

  2. use a Javascript in advanced settings to define a new calculated custom field (boolean Yes or No) f which you can import for each issue as separte dimension. Then you could add this dimension to report and filter the report by dimension member.

The approach you have started to use is rather a slow one, however, there are some ways to create expected calculation using MDX report builder

Martins / eazyBI team