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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 13:22 , Blogger eejimkos said...

my e-mail is

At 31 May 2010 at 17:34 , Blogger jennifer said...

Whenever i see the post like your's i feel that there are still helpful people who share information for the help of others, it must be helpful for other's. thanx and good job.

Finance Dissertation Proposal

At 31 July 2012 at 07:38 , Blogger ashishmaihu said...

I amazed with the analysis you made to create this actual submit incredible. I feel strongly about it and adore learning more on this topic. I hope you write again incredibly soon! Linen shirts for men

At 5 November 2012 at 09:53 , Blogger SEO said...

Very nice post. I just stumbled upon your blog and wanted to say that I have really enjoyed browsing your blog posts.
nehru jacket

At 4 December 2012 at 23:47 , Blogger learningcognos said...

Thank you so much for this information. It was very helpful for the report I'm creating, but I would like to use the drill through feature without prompts. Scenario: I have a summary crosstab report that tells me the region of the world where employees are located based on their company and gender (how many females are working for company "X" in Germany?) I want to click on the value in the summary report and see the corresponding names of the employees without being prompted for additional information. (example: The summary report says there are 5 females working for company "X" in Germany. I want to click on the hyperlinked 5 and see that Joni, Judi, Jodi, Julie, and Jamie are in Germany). Does anyone know how to make this happen?

At 7 February 2013 at 12:50 , Blogger dissertationconsultant said...

Your blog post Set up drill through from a crosstab to a detail list report is superb in fact, i have seen your post and That was very learning and very enjoyable for me. Gratitude for blog posting Really Such Things. I should suggest your site to my friends.

labour dissertation help

At 20 February 2014 at 17:01 , Anonymous Anonymous said...

This is an excellent article with well-written, interesting content and loaded with valid points and thoughts. I think most of your information is similar to what I would write if I could write like you.
Yepi 8


Post a Comment

Subscribe to Post Comments [Atom]

<< Home