Ratio query with parameter filter

[Adrianilloo]

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":

Code Select Expand

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:

Code Select Expand

PCA-ERR-0021 Unable to evaluate a value expression on a set with more than one member.

How to fix the formula? Thank you.

[MFGF]

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":

Code Select Expand

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:

Code Select Expand

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.

[Adrianilloo]

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.

I'm unsure about the two first basic questions, as I was not involved in the import process. It seems like it's a dimensional model, though. But I can answer the rest.

[Z] is indeed a level. The related ?p_z? parameter (prompted on the request page) is a multiselectable list of all the third dimension values (as [X] and [Y] are parent ones, between them as well).
The two current basic detail filters for [X] and [Y] are

Code Select Expand

[X] = ?p_x? and

Code Select Expand

[Y] IN ?p_y?.

Finally, if I employ the syntax

Code Select Expand

set([Your level or set of members] -> ?Your Parameter?) I get an error, the full query being:

Code Select Expand

total([Measure] within set ([Z] -> ?p_z?)) / [Measure]

The error isn't too descriptive, it's just a QE-DEF-0260 one saying the syntax failed after the -> token.

[MFGF]

I'm unsure about the two first basic questions, as I was not involved in the import process. It seems like it's a dimensional model, though. But I can answer the rest.

[Z] is indeed a level. The related ?p_z? parameter (prompted on the request page) is a multiselectable list of all the third dimension values (as [X] and [Y] are parent ones, between them as well).
The two current basic detail filters for [X] and [Y] are

Code Select Expand

[X] = ?p_x? and

Code Select Expand

[Y] IN ?p_y?.

Finally, if I employ the syntax

Code Select Expand

set([Your level or set of members] -> ?Your Parameter?) I get an error, the full query being:

Code Select Expand

total([Measure] within set ([Z] -> ?p_z?)) / [Measure]

The error isn't too descriptive, it's just a QE-DEF-0260 one saying the syntax failed after the -> token.

Hi,

You can refer to the FAQs post below to find out how to identify a relational package vs a dimensional package:
https://www.cognoise.com/index.php/topic,27563.0.html
The UI has changed a little since the post was added, but the basic concepts still apply.

Let's focus first on [X] and [Y]. You have detail filters for these, and we really need to get rid of them.

Are you displaying [X] and [Y] as items in your report? If so, do the following:

Remove the detail filter for [X], then replace the [X] item being displayed in your report with a query calculation that has the expression [X] -> ?p_x?
Remove the detail filter for [Y], then replace the [Y] item being displayed in your report with a query calculation that has the expression set([Y] -> ?p_y?)

Now on to your expression

To get a prompted set of members for [Z], you use the expression set([Z] -> ?p_z?)
You now want to total your measure across the members of this prompted set, so you use syntax that says total([Measure] within set <your set of members>)

So the full expression for this would be total([Measure] within set set([Z] -> ?p_z?))
Notice the word 'set' occurs twice within the expression - once for 'within set' and once as a set() function.

You should then be able to divide that by your [Measure], giving you an expression

total([Measure] within set set([Z] -> ?p_z?)) / [Measure]

Does this work for you?

Cheers!

MF.