We've created some tables from the Cognos 11 Audit package (c111_audit) and the content store (c111_content), but haven't cracked the nut on getting a report of all the people a report is scheduled to.  Anyone figured this out yet?  (No, I'm not going to buy a service or tool or engage anyone to figure it out.  Just trying to save a little time.)  Thanks.

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]

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]

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.

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 MF,

I'm using 11.2.1. and these are the steps taken:
1. Right click on the report, then select run as
2. Select Run in Background, ticked PDF and HTML
3. Click run button
4. Once the report finishes running, click on the report

The report when navigating using default page up and down button, URL doesn't change (this means that the report is not interactive correct?)

Best regards.

Yes MF,

I have tried using bookmark but it will only work in PDF output. For saved report output in HTML format, clicking on the button which uses bookmark to land to other page yield no response at all.

Best Regards

Hi,

Bookmarks should work for both HTML and PDF rendered reports. When you are testing, you need to make sure that you are running and saving the HTML output first, then viewing that saved output. If your testing is running the report interactively, only the first page gets rendered, so bookmarks do nothing.

Cheers!

MF.