Cognos Analytics

In this blog I hope to show you how to make a typical microchart report more efficient and easier to write. Microcharts are a great way to visualise data and add visual flair to a report.

Here is a very simple list, that shows the sales for each Product Type.

You could add make this report look so much better and allow easier comparison of Revenue.

The above is the same list with some minimal formatting and a microchart that visualises the Revenue.

A beginner report author would use the same query to populate both the list and the microchart, with a master detail relationship restricting the microchart to the appropriate Product Type.

If the report is authored in this manner then when run the list must query the data source. Each instance of the microchart will query the same data source again, but with the appropriate filter. In this example that means the data source is queried 22 times (1 list + 21 charts).

Whilst this works it is inefficient. You end up querying the same data source multiple times. Is there a better way?

Well, lets examine that list again.

The list already has the Revenue number we want to chart, it does seem crazy to have to query the database again to get the same number. Can that list Revenue number be reused?

Through the clever use of a parameter indeed it can!

I have created a new query called Microchart Query. This query has a single data item called Measure 1. This data item is populated via a parameter.

I have used the prompt macro syntax to prompt for the p_Measure1 parameter. (See the Final Tip section for why I use the prompt macro rather than ?parameter? syntax).

As I want to use this data item as a measure in the chart I need to set the Aggregation properties to something that indicates a measure, I have used Calculated:

The microchart is now changed to use this new query and data item.

Crucially, the master detail relationship is changed. Rather than filtering the detail query, the list query Revenue measure is now passed into the Measure1 parameter:

We are no longer using the Master detail relationship to filter the chart query, but to populate the parameter.

The entire report can now be executed with just a single query against the underlying data source.

This sample report has been written against the very small Great Outdoors dataset, making it difficult to see the performance improvements.

Switching on the ‘Include performance details’ from the report run options allows you to see the performance improvements.

Looking at the performance details for the classic filter master detail relationship report.

The overall report execution time is 2.39 seconds. The query for the watches microchart takes 252ms.

Compare to the parameterised version of the report.

The overall report execution time is quicker at 2.099 seconds. But the most interesting part is that the query for the Watches microchart is now only 19ms, compared to the previous 252ms.

In a real life scenario, the query to produce one of the typical microcharts is likely to be much longer than 252ms. Regardless of the complexity of the database, the parameterised master detail query will always take microseconds – its just answering a parameter and never hitting the data source.

Simplifying Queries

This technique can be also used to also simplify more complicated microcharts.

The requirements are expanded to allow an easy comparison of the Product Type Revenue to the Maximum Product Type Revenue.

This is easy to achieve in a list. But how could this be shown if the microchart were implemented in the traditional master detail filter?

Such a relationship would filter the microchart query to a single Product Type. If the query is filtered to a single Product Type, how can it work out the maximum Revenue for all Product Types? If the query is filtered to a single Product Type it isn’t possible to write such a query.

With the parameterised technique you don’t have to solve this problem. Instead of passing one value into the microchart query, you pass in a second value using the same technique.

A second data item is added to the microchart query:

This can then be used on the chart.

The maximum Revenue measure that was calculated on the list can be passed into this new parameter.

In this chart a marker has been used for Measure 2:

The report consumer can now easily see the gap between an individual Product Type’s Revenue and the best seller.

Passing Through Text Attributes

In the examples seen so far, we have only passed through numeric values. We can use the same techniques to pass through any data type examples, for example text.

An example of this could be to pass through the Product Line to set the colour of the bar.

In the above example, the colour of the bar changes depending on the Product Line.

To achieve this I have added another data item to Microchart Query to capture the attribute I want to use on the chart.

Notice that this time the parameter is a string and the default is a quoted string of ‘NotAnswered’. As I want to use this as an attribute on the chart I have changed the aggregation properties of this data item to None.

I can now use this attribute on the chart.

Here I have placed the attribute in the y axis. The microchart automatically hides this y axis text on the output so that whilst it is in the chart it won’t be displayed. The same effect can be achieved on any chart type just by hiding the y axis.

With the attribute on the chart you can now use it for formatting, such as a conditional palette.

Finally, the master detail relationship is updated to pass the Product Line into this new attribute.

This technique can be expanded to pass through any additional tokens you want to use on the chart. Maybe you would like to pass through more data in order to add tooltips and the like onto the chart.

Final Tip

Keep the chart query generic, for example a generic query name and generic measure names. In this example I named the data item Measure 1 and not Revenue.

In a more complicated report the same query can be used for multiple microcharts. For example, there is no need to have a query for a bar microchart, and another query for a pie microchart. The same query can be used. This can reduce the number of queries within a report.

If I have a single microchart query, what would happen if on one chart I wanted to use one measure, and another chart I wanted to use two measures and some text? Don’t I need one query with just one data item and another query with multiple data items? This is why I use the prompt macro syntax for a parameter rather than the usual, simpler ?parameter? syntax. With the prompt macro I can give each data item a default value. Meaning that if a parameter item isn’t used in the masterdetail it will just default and not prompt for the parameter.

An example report showing these techniques can be found in the Cognos LIbrary here: Example Report 
Be sure to check out the pages within the report as the different pages show the different techniques.