Drill-down on Studio Reports Crosstabs

crosstab are typically used to have summary on single sheet of paper / screen-view. However, when a report will be viewed by a decision maker, it is quite possible that he / she wants to have a look at details almost at that time.

Using drill-down feature, decision maker will be able to get to the detail of a summary at a single mouse-click.

What is a drill-down

This is a feature using which from within a report designed in Intellicus user can open any other Intellicus report or a document.

For example:

Opening and viewing Department-wise patient list from a crosstab having department-wise patient count.
Opening and viewing expenditure-information of a branch from a crosstab having expenditure-summary of all the branches.

How does it work

While setting up summary field, we need to make it hyperlink and associate a report with it. So that, while viewing the report on-screen, when user clicks the hyperlink the report associated with the hyperlink is opened.

If the hyperlink is associated with a web site, it will open up. If it is associated with an online document, respective document will open, provided you have the associated program (such as MS Word for a .doc file) installed on the workstation.

Prerequisites

While working on the crosstab having a hyperlink for drill-down effect, make sure that the report being opened on clicking the hyperlink is already exists and it is uploaded in the right folder.

To open the report with relevant data such as, showing detailed expenditure about the right branch, the report being associated will need parameters that will be sent from the crosstab report itself.

Setting up hyperlink

Before setting up the hyperlink in crosstab, make sure you have created the “hyperlinked report” and uploaded on the server.

Figure 32: Hyperlinking a crosstab cell

Example

Here is an example where a crosstab has hyperlink setup that will open up a detail report. The example of crosstab report depicted here is a sales summary report. When the user clicks one of the summary fields, report with sales details opens up.

Figure 33: Detail Report

Here is the crosstab:

A click on any of the sales figures should open a report:

Figure 34: Detail of Product

To get such an arrangement, you need to have two reports designed:

A detail report. This report will open when any of the cells in the crosstab report is clicked.

The crosstab report.

Reports will need following as part of record set:

ProductCode
RegionCode
SalesAmt
CityCode
RetailerCode

After designing the detailed report with ProductCode and RegionCode set as parameters (the report that will be called from crosstab) and uploading it to the server, general steps to design the crosstab report are:

Set the appropriate connection, create an SQL and refresh fields.
Place crosstab component preferably on Report Header.
Select RegionCode as row header.
Select ProductCode as column header.
Select SalesAmt as summary field.
Go to Format tab, select the cell of summary field and click Hyperlink button.
On Hyperlink dialog box, set the report, specify ProductCode and RegionCode as parameters.
Set formatting details.