Hi,
I'm new to Cognos BI. I basically need to create a query expression for a report, concretely a percentage for a measure where the numerator's total is filtered by an input parameter (from a request page), and the denominator is tied to all unfiltered items in respect to the former. Both parts are also filtered by two parent dimensions coming from the request page, too.
In short, the scheme is the following: X, Y are the two parent dimensions (these are directly nested themselves, as well). Z is the filtered dimension for the numerator. Also ?p_z? is the parameter related to Z I let the user provide values into, from the request page (it allows multiselection).
I am using a normal detail filter for X and Y (for the report), since they are common to both ratio parts. But, since Z filter is only to be applied to the numerator, I can't add it as a detail filter since otherwise the ratio result would naturally be 100%.
I have tried the following in a query calc, and I hope it makes sense with what I explained. I'm just calling the target measure "Measure":
total([Measure] within set filter([Measure];[Z] IN ?p_z?)) / [Measure]
The expected result is a value in [0.0, 1.0] range that I'll format later.
When I add the query calc in a report list, I get the following error:
PCA-ERR-0021 Unable to evaluate a value expression on a set with more than one member.
How to fix the formula? Thank you.
Hi,
First, a sanity check and a few questions.
Your package is a dimensional model, not a relational, correct?
What is the source - is it a real OLAP cube (eg Powercube, Dynamic cube, TM1/PA cube etc) or is it Dimensionally Modelled Relational (DMR)?
What exactly are you filtering here - ie what is [Z]? Is it a level? A defined set of members? A hierarchy? Something else?
What is your ?p_z? parameter prompt returning? Is it a member? A set? Something else?
How are you filtering the X and Y dimensions? You mention a detail filter - can you specify exactly what this is doing?
Now those pesky questions are out of the way, let's take a broader view.
Firstly, if this really is a dimensional package, detail filters are probably not the right choice to use. They can give inconsistent and sometimes incorrect results when dealing with sets of members from a cube. The preferred option is to use dimensional expressions to derive the appropriate set of members from each dimension. For example, rather than bringing in an entire level then using a detail filter to focus in on the members of the level you require, instead create a set() expression in a query calculation to bring in just the members you require, and use this instead of the level. There are many more examples - for example if you want a prompted set of members, instead of bringing in the level and using a detail filter with a parameter, use a dimensional expression with a parameter in a query calculation - eg set([Your level or set of members] -> ?Your Parameter?)
Secondly, the filter() function you are using here is designed to filter a set of members - based on a measure value or a descriptive attribute. It isn't something you can use to filter a measure.
A couple of examples:
filter( [Your set of Region members], [Measure] > 100) - this will return just the Region members whose aggregated measure value is greater than 100
filter( [Your Product Line level], caption([Your Product Line level]) contains 'Equipment') - this will return the Product Line members which contain the word Equipment in their caption
In this case, I would first replace the detail filters with dimensional expressions to isolate the required members for those dimensions, then I would change the expression for Z to use a dimensional expression with the parameter embedded (see example above) so that it returns a prompted set of members.
Cheers!
MF.