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.