Alternating drill links

In this recipe, we will learn about a limitation of drill links and how to overcome it using Render Variable.

Let's say there is a crosstab report which shows sales quantity by month and order method. We need to provide a drill-through facility from the intersection. However, the drill-through target needs to be different depending on the order method.

If the order method is e-mail, the drill-through from the intersection should go to a report called Alternating Drill Link—Drill Report 2. For all other order methods, it should go to Alternating Drill Link—Drill Report 1.

Getting ready

Create a crosstab report to serve as the main report. Drag Month key (ship date) on rows, Order method type on columns and Quantity on the intersection.

Create two list reports to serve as drill reports. In the sample provided with this book, we have used two list reports for this. One accepts the Order method and Month. The other accepts only Month and is designed to work for the order method E-mail.

How to do it...

In this recipe, we will create two different drill-through links in the crosstab intersection based on the order method. To do this we will start by performing the following steps:

  1. As already learnt in Chapter 2, Advanced Report Authoring, create a drill-through to first drill the report from the crosstab intersection as shown in the following screenshot:
  2. Now make sure that the report objects are unlocked. Select the intersection text item (which now looks like a hyperlink as there is already a drill-through defined). Hold the Ctrl key and drag the text to the right within a cell.
  3. This should create a copy of the text item within that cell and it will look like the following screenshot:
  4. Now select this copy of the text item. Hit the drill-through button to open definitions. Delete the existing drill-through to the first report. Create a new drill to a second report. So, now we have two text items in the cell, each going to different drill reports.
  5. Create a string type of Conditional Variable. Define it as follows:
    if ([Query1].[Order method] = 'E-mail') then ('E-mail')
    else ('Other')

    Call it OrderMethod and define the two values to be E-mail and Other.

  6. Now go back to the report page. Select the first text item from the intersection. Open its Render Variable property. Choose the OrderMethod variable and select to render for Other as shown in the following screenshot:
  7. Similarly, define the Render Variable for the second text item, but choose to render for E-mail.
  8. Run the report to test it. You will see that clicking on the intersection numbers opens the first drill report for any order method other than E-mail, whereas for the numbers under E-mail, the second drill report opens.

How it works...

First, let me explain the limitation here. CRS allows us to define multiple drill targets for an item. However, there is no facility to define a conditional target. So, if we define two targets, Cognos will ask users to select one at runtime.

In our scenario, we want Cognos to go straight to one of the two targets depending on the order method condition. For that, we are using the Render Variable property.

Render Variable

This property allows us to attach a conditional variable to the report object and define the conditions for which the object will be rendered.

This works best with String variables. For a Boolean type of conditional variable, rendering is possible only for 'Yes'.

Instead of defining two targets on the same text item, we are creating two text items and controlling their rendering.

There's more...

Even with a String type of conditional variable, rendering cannot be defined for the default (Other) condition. Hence, we had to define our own Other condition.