Hi Team
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
Hi @Mohanbabu,
The problem with the expression is that it aggregates several sets of labels. Each sub-set created by the Filter function contains labels not compliant with the specific keyphrase. Since each of these conditions is applied to the whole set of available labels independently - the labels not matching any keyphrase are returned four times. The labels matching any keyphrase are only excluded once but still returned three other times.
If you want to exclude labels with specific keyphrases, you might join all these conditions under one Filter function. You might join the conditions with AND to ensure that all of them are applied.
However, in most cases, calculated members with aggregation are not preferred on multi-value fields.
The issues having multiple labels are reported once for each compliant label resulting in the same issue being reported multiple times.
Such calculated members with an excessive number of included primary members might also cause slowness of reports as they call for significant background requests from the database.
A JavaScript-calculated custom field dimension might be a better option to split the issues based on their labels.
You might define a new account-specific custom field if you have a Data admin or a higher account role.
Please read more about account roles here - Account users.
Please read about creating new account-specific JS-calculated custom fields here - New calculated fields.
The code for the expression might be as follows.
if(issue.fields.labels && issue.fields.labels.length>0){
var exclusions = null;
for (var i = 0; i < issue.fields.labels.length; i++) {
if(issue.fields.labels[i].match("^PT_EXIT_EXCL.") ||
issue.fields.labels[i].match("^UAT_EXIT_EXCL.") ||
issue.fields.labels[i].match("^PROPOSED_UAT_EXIT_EXCL.") ||
issue.fields.labels[i].match("^PROPOSED_PT_EXIT_EXCL.")){
exclusions = "Exit exclusions";
}
}
}
if(exclusions) {
return exclusions;}
else {
return "Remain";}
This feld would create a new dimension that allows excluding the issues with specific labels.
Regards,
Oskars / support@eazyBI.com
