Bursting on Multiple Keys

We were recently asked to produce one report for a customer where the requirement was to Burst on two keys.

IBM Cognos Analytics only allows a report to be burst on one key.

For this example we are using IBM Cognos Analytics 11.0.12 and the data set is the pre-installed Go Samples Query sample package. This will also work in IBM Cognos Report Studio 10.2.2 and 10.2.1.

Of course, two reports could be setup but I wanted to explore the option anyway.

The trick to solving this is to make the Burst Key dynamic based on a Prompt value.

Queries

For this report you will need 4 queries. It can be done with less but I’ve separated the Report Query from the Section Query.

The first query is for the section header, the second is for the report.

The other two queries contain the prompt values.

One for Retailer Region and the other for Retailer Country.

Section Query

Create a query named Section Query, containing these items.

Section Variable

Section Variable is a Query calculation containing a Prompt Macro

#prompt('pSection', 'token', '[Retailer country]')#

Syntax:

#prompt(‘PARAMETER NAME’,’ DATA TYPE’, ‘DEFAULT ENTRY’)#

This means if nobody chooses a Region or Retailer country, the report will burst on the default (Retailer country).

From your data source, drag a data item containing your recipient’s email addresses. This will be used to send the relevant report to each recipient.

Create a List using the Section Header query

Create a simple List report using the Section Query

Create a List using the Report Query

Add a new List to the report and add some items from the package, including a copy of the Section Variable taken from the Section Query, name this query Report Query.

Attach the List to the Section Variable Header

Section Variable is in a List which points to the Section Query.

The List comes from the Report Query.

You will now need to create a Master-Detail relationship to ensure the contents of the List (Report Query) are filtered by the contents of the Section List (Section Query).

Create a Master Detail Link

From the Section Variable (Section Query) to the Section Variable (Report Query).

Add a Page Set

Click on Report pages

Add a Page Set and point this to the Section Query.

Group by the Section Variable.

A new page will now appear based on the Section Variable.

So either Region or Retailer country

Add a Prompt page

Add a Prompt page.

• Create a Value Prompt for Region, choose to filter the Report query by Region. Ensure this is multiselect.

• Create a second Value Prompt for Retailer country, choose to filter the Report query by Retailer country. Ensure this is multiselect.

• Create a third Value Prompt, this will contain the Bursting options.

Add these as Static choices

Point this Value Prompt to Parameter: pSection

Set up the Bursting options

Click Report

Open the Burst options property and define as follows:

The report is finished

Test and check the report is burst either by Region or Retailer country

The report is successfully burst and emailed by Region.

Run the report again, the same report is successfully burst and emailed by Retailer country.

Create report views of this report and schedule each view to run using different selections.

Thanks for taking the time to read this, we hope you found it useful.

If you would like us to send you the full report spec, please contact us This email address is being protected from spambots. You need JavaScript enabled to view it.

Please quote 'bursting blog' in the subject line and include your name and telephone number in the content.