IBM Cognos BI Blog

Drill through for individual data items in a crosstab is a really useful feature. However, it’s not always convenient to drill through to the same target report. Although it’s hidden away, it is possible to drill through to a different target report depending on the data item.

The example below shows a scenario where we want to drill through to two different reports based on the year. For the purposes of demonstration, let’s say that all sales in 2004 were postal orders but in subsequent years there are a variety of order methods. To keep things tidy we will drill through to a report that excludes the order method for 2004 and a different report that gives a breakdown of order method in all other years.

I’ve already created two simple list reports for the above scenarios and called them ‘Drill A’ and ‘Drill B’. The latter report includes order method information but the former doesn’t.

Now I want to setup a crosstab that drills through to these reports. In the example below I’ve created a high level summary report showing the revenue by year split out by product line:

I want to drill through from the crosstab intersection. Therefore I’m going to click on the unlock button in the toolbar:

Next I’ll select the text item in the crosstab intersection:

Then click on the drill –through icon in the toolbar:

When the drill – through definition window opens we just need to setup the drill through definition to point at one of the target drill reports (in this case ‘Drill A’):

Next hold ctrl and drag the text item to the left of itself. This makes an exact copy of the existing text item including the drill through:

Select the newly created text item and edit the drill definition to point at ‘Drill B’ instead of ‘Drill A’:

Now we have two text items that drill through to two different target reports. We need to somehow hide the ‘Drill B’ text item for 2004 and hide the ‘Drill A’ text item for all other years. This is achieved using a Boolean variable (it is also possible to use a string variable).

I’ll create a new Boolean variable where the year is equal to 2004. Being a Boolean variable this gives two possible outcomes. Either ‘Yes’ the year is 2004 or ‘No’ the year is not 2004:

By associating both text items with the new variable we can chose to show or hide each text item based on the value of this variable.

Add the Boolean variable as a style variable in the properties of each text item (a render variable would be used for a string variable):

Now use the conditional explorer to tell report studio the desired behaviour for each of the results. First select ‘Yes’ (i.e. the year is 2004) and then select the text item associated with ‘Drill B’:

Select the text item and then in the properties pane change the box type to ‘none’. This means that when the year is 2004 this text item will not display.

Change the conditional explorer to reference ‘No’ (i.e. the year is not 2004). Select the other text item and change the Box Type properties to ‘None’.

Now when we run the report only one text item is shown at each intersection. What isn’t entirely obvious is that the text items for 2004 are different to all others:

If we click on any of the 2004 items we drill through to ‘Drill A’ which doesn’t include order method information:

If we click on any other cross tab intersection then we drill through to ‘Drill B’ which does include order method information:

There you go. Maybe not as straightforward as it ought to be, but definitely a workable solution