Thursday, 17 September 2009

Set up drill through from a crosstab to a detail list report

The following blog describes the process of setting up drill through from a source crosstab report to a target detailed list report by clicking on the measure values within the crosstab.

Create a target detail list drill through report.

1. Create a new report list report. Ensure that you include on this report (either on the report itself or in the underlying query) the same query items that you intend to use as the axis of the crosstab. If your crosstab shows product name by order year, include these in your query.

2. Test your report works

3. Add two filters to your report. These act as the prompts to the source report to supply the filtering values. These must be the same query items as the cross tab query items.
For example:
product_name =?product?

4. Save the target drill report

Create a source crosstab drill through report

Create a new crosstab report using the same (filtered) query items for your axis. The measure can be anything you like.

Select the measure cell, right click and select ‘Drill Throughs’

Click the new button.
Click the ellipsis (…) for the report and select the target report you just created.
Set the Action to ‘Run the report’
Click the ‘pencil’ button at the bottom of the window. This will load the required parameters from the target report. (The filters you set up earlier).
Set the method for both to ‘Pass data item value’. Then set the appropriate data item from your crosstab query to be the data item that you pass through to the target report filter.
Click OK a couple of times and save the report.

You’re done.

Run the source report and click on the measure value to drill through. This will open the target report and pass the axis data items for the measure intersection you selected through to the target report and filter it.



At 7 April 2010 23:32 , Blogger eejimkos said...

hi,tnks in advance.....
i have a problem.
Is it possible if we have a cognos cube as a package to select from 2 calendars the period of time we want from report studio...?

tnks one more time.

At 8 April 2010 09:53 , Blogger Phil Thompson said...

Hi eejimkos,
I'm not sure what you are trying to achieve. I assume you have a financial calendar based on accounting weeks and periods and a 'normal' calendar based on days weeks and months. You can certainly use both calendars at the same time in Report Studio, but I'm not sure why you would want to do it. Perhaps you could supply some more details of what you are trying to achieve. Thanks Phil

At 8 April 2010 11:36 , Blogger eejimkos said...

tnks for your quick response.
My scenario is like this....

i want to choose a period of time
from 2 calendars prompts in order to customize my report.

Let's say,i want from 1/1/2010 to 15/1/2010.So i choose from the one calendar 1/1/2010 and from the other 15/1/2010.

Here is the problem.if i use a dimensional package(From FrameWorkManager) everything is ok.
BUT , if i choose as a package , my cognos Cube (From Transformer) = OLAP , it does not work.Have you tried it?

Tnks onemore time

At 8 April 2010 11:45 , Blogger Phil Thompson said...

Hi eejimkos,

by the sound of it you should use the same dimension from the source cube and create a start prompt and end prompt from the same dimension. Check out this knowledge base doc:

It discusses this method in more detail and gives example.

Let me know if it helps.


At 8 April 2010 14:28 , Blogger eejimkos said...

hi....guess/ again...
First of all,many thanks for your time.
Tnks for your tip....but still in the ibm's Doc, there is nothing about Calendar(just prompts->edit...) and nothinh about dates only years and months...

tnks one more......

Any other thoughts??

At 8 April 2010 16:20 , Blogger Phil Thompson said...

Hi eejimkos, an interesting problem this. I have just tested out this solution:

It uses an except statement with two embedded periodstodate functions.
The first periodstodate is controlled by the value of your enddate prompt result member. The second periodstodate is controlled by the result of your start date prompt result member. By comparing the two a set is returned that only includes dates between those you selected in the prompts.

This should satisfy what you need.


At 8 April 2010 16:24 , Blogger Phil Thompson said...

p.s. If you don't want the date values on the report you should set this up as a dynamic slicer instead.


At 9 April 2010 09:19 , Blogger eejimkos said...

tnks one more time....
i will try again your suggestion...but yesterday i did not accoplish it...


At 9 April 2010 09:32 , Blogger Phil Thompson said...

Hi eejimkos,

I tried embedding an 8.4 report spec but it didn't like it. (too long). Send me your email address and I'll send you the example report.


At 9 April 2010 13:22 , Blogger eejimkos said...

my e-mail is


Post a Comment

<< Home