Remove Row from Table --> use Dimension as Page

Questions & Answers
1

I am trying to build a pie chart with our Test Executions, and I have to exclude one specific key.
When I view it as a table, I can remove the specific key without any problem. But as I want to have a nice pie chart, I need to put the Test Executions as a page, so I have only Measures and Statuses inside of my Pie Table.

Is there any way to remove one specific key from a page or do I have to select everything except this one key? or is there a formula for a calculated member I can use?

Best regards,
Anna

2

Hi Anna,

To remove a member from Page you can create a calculated member using Except(…) function. For example the following would remove test with ID XT-1 from Xray Test dimension

Aggregate(Except(
  [Xray Test].[Test].Members,
  [Xray Test].[Xray Tests].[XT-1]
))

Lauma / support@eazybi.com

3

Hi Lauma,

thank you for your reply. It is not quite working.
When I use the calculated member:

Aggregate(Except(

[Xray Test Execution].[Execution].Members,
[Xray Test Execution].[Execution].[JQM-7227]
))

I get this Error:

#ERR: mondrian.olap.fun.MondrianEvaluationException: Aggregation is not supported over a list with more than 1000 predicates (see property mondrian.rolap.maxConstraints)

4

I see. In case the dimension is this large, you can use more simple subtraction

[Xray Test Execution].DefaultMember - 
[Xray Test Execution].[Execution].[JQM-7227]

Lauma / support@eazybi.com

1 Like
5

Thank you very much, this is working perfectly :slight_smile:

2 Likes
6

Hi @lauma.cirule,
We tried to use the below condition, but we got the error as “Failed to execute query. Error message:
Aggregation is not supported over a list with more than 1000 predicates (see property mondrian.rolap.maxConstraints)” We use the below calculated member.

Aggregate(
{

Filter([Label].Members,
[Label].CurrentMember.Name NOT MATCHES ‘^PT_EXIT_EXCL.’
),
Filter([Label].Members,
[Label].CurrentMember.Name NOT MATCHES '^UAT_EXIT_EXCL.’
),
Filter([Label].Members,
[Label].CurrentMember.Name NOT MATCHES ‘^PROPOSED_UAT_EXIT_EXCL.’
),
Filter([Label].Members,
[Label].CurrentMember.Name NOT MATCHES '^PROPOSED_PT_EXIT_EXCL.’
)
}
)

Help us on the same to resolve this issue.

Regards,
Mohan S

7

Hi @Mohanbabu,

You seem to be trying to aggregate a list of many Labels. If the set of Labels describes some business logic (dimension), I suggest you consider a new calculated dimension instead of aggregating the members.
See a good presentation about this topic from my colleague: MDX vs. JavaScript: BehindComplexFilters.
Here is the documentation page with examples of creating a new calculated dimension: New calculated fields.

Lauma / support@eazybi.com

8

Hi Lauma.circule,

I too getting the error: |#ERR: mondrian.olap.fun.MondrianEvaluationException: Aggregation is not supported over a list with more than 1000 predicates (see property mondrian.rolap.maxConstraints)|

and my calculated member is defined something like this:

Aggregate(
Except (
[Reporter].[User].Members,
{
[Reporter].[A],
[Reporter].[B],
[Reporter].[C]
}
)
)

The idea here is that report the issue which are not created by my team members (A, B and C) and rather created by someone outside my team. Unfortunately on jira level there is no groups defined for team members and I do not have privileges to do that.

Is there any suggestion from your side. I am new to eazyBI and still exploring it.

Many thanks.

9

Hi @dineshsethiya!

At first glance, my suggestion would be the same - create a custom dimension within eazyBI, grouping users in the Team dimension during import. More about new calculated member creation during data import here: New calculated fields.

Still, I wonder how many users you have in the Reporter dimension to get this error. Could you send more details with screenshots to support@eazybi.com?

Lauma / support@eazybi.com

10

Thanks Lauma.circule. There are total 2180 Reporters. Here is the screenshot.

image
image1506×859 52.7 KB

11

Hi @dineshsethiya,

Yes, if there are so many users in the dimension, then creating a custom dimension or hierarchy in the Reporter dimension would be suggested.

Another option could be to create a measure that gives the Issues created by all but a few members, but this would not expand to see precisely which reporters reported the issues:

([Measures].[Issues created], [Reporter].DefaultMember) -
Sum(
  {
    [Reporter].[A],
    [Reporter].[B],
    [Reporter].[C]
  },
  [Measures].[Issues created]
)

Lauma / support@eazybi.com

12

Thanks Lauma. I think a custom dimension is the best option. However for now your egs Measure is good too.

Kind Regards,
Dinesh

1 Like