Wednesday, February 3, 2010

Interactive measure reports

Last year one of my customers would like to have a Cognos BI report having an in-report prompt on the measure to be shown in a combination chart in HTML-format. Something like this:
The chart currently shows the number of Worked Hours, a measure that is selected in the radio button list on the left hand of the chart. When the user clicks another measure, the chart directly shows that measure, without further prompting.

In this post I will show how this has been developed. 

Some notes first:
  • This report is created in Cognos 8v4 BI, using Report Studio.
  • The data source is a PowerCube, so some dimensional authoring is done in the report (using MUN's et cetera). This not relevant though, because I think the technique should also work with relational authoring.

Create a value prompt

First, create a Value Prompt object from the toolbox, and set it to:
  • Required: "Yes"
  • Multi-Select: "No"
  • UI: "Radio button group"
  • Auto-Submit: "Yes" (important, otherwise the report will not automatically be rerun):
In the Static Choices property of the Value Prompt object, enter the values to use (i.e. the measure MUN's) and to display in the radio button group:

In the Default Selections property of the Value Prompt object, enter the value to use when the report is run for the first time, so no prompt page appears:
So now the prompt object is finished.

Create a query item to refer to the prompt value

Next, the query used by the Chart object needs to contain an item that dynamically refers to the selected prompt value. Let's call this query item Q_Measure:

This item is defined in the query as a Data Item object, having these properties:
The Expression property contains the # prompt() # macro. This will read the value of parameter P_Measure (as defined in the Value Prompt object), and applies this value an item (the so-called 'token'). In this case, the specified MUN value is used to display the according cube measure in the chart.

In general, this is a very powerful function you could use in many cases to create powerful reports. You are however limited to strings in the expression provided entirely by the macro function, because the macro function cannot be combined with other non-macro functions. See the Report Studio Professional Authoring User Guide for more information on macros and expressions.


Oh, and did you wonder what the link "Go to Analysis Report" was about in the report example above? Check out one of my next posts to come, about Contextual Analysis...

7 comments:

Tricia said...

I am so glad to find some examples of reporting with Cognos. I'm not interested in the data - but just the ideas used. It helps me understand what can be done with the tool.

Any other tips or screen shots you have available of completed reports would be great. (Maybe you can smudge the actual/identifying data?)


Thanks! Thanks! Thanks!

Marc said...

@Sunny: thanks for your comment.

I will continue doing my best to share my most interesting Cognos experiences with you!

Anonymous said...

I like your blog and the ideas that you are presenting about Cognos. By the way, Are you still using Cognos? I haven't seen an new posting in the last 8 months.

Keep it up.

Sam

Marc said...

@Anonymous:

Thanks for your positive feedback! Yes I'm still using Cognos (of course;-) but there's not much interesting to write about lately... Besides report development I'm also doing consultancy, design and support activities so development is just one side of my profession.

But speaking of this, I remember I might have a simple but useful technique to share with you all in a short while, so stay tuned!

Thanuja Samarawickrama said...

HI Marc,

Thanks for sharing your brilliant ideas...!

Raj said...

Hi Marc,

Great blog. I've managed to produce an interactive measure report! The next challenge is apply this in a dashboard but I've had no luck so far. Will the same principles apply?

When I try, I get a request for prompted for a different prompt (e.g. week) that should be controlled via another parameter via a dashboard portlet. Error messages:
QE-DEF-0260 Parsing error before or near position: 9 of: "2011 W28" accompanied by SOAPFaultException
accompanied by
"QE-DEF-0260 Parsing error before or near position: 9 of: "2011 W28"

Any ideas

PF-COM-6203

Marc said...

Hi Raj,

Thanks for your compliment.

Regaring your question: maybe it's the parameter type that is not correctly set? E.g. data item value, parameter value...