In this recipe, we will learn about a limitation of drill link and overcoming it using Render Variable.
There is a crosstab report which shows sales quantity by month and order method. We need to provide drill-through facility from the intersection. However, the drill-through target needs to be different, depending on the order method.
If order method is e-mail, the drill-through from 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'.
Create a crosstab report to serve as the main report. Drag Month (shipment) on rows, Order method on columns and Sales 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'.
So, now we have two text items in the cell, each going to different drill reports.
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.
First, let me explain to you 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 the run-time.
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.
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 same text item, we are creating two text items and controlling their rendering.