Wednesday, March 30, 2011

Creating Drill-Through Style Reports using Parametrized URLs

Here's an example of how you can create Drill Through Style Reports using Parametrized URLs against saved report outputs.

Requirement: Create a parent report displaying various Product Lines. Create a child report that displays individual Product Line details. Both the reports need to be scheduled and saved. Users should be able to navigate from each parent Product Line record in the parent report to the corresponding product line child report output.

The requirement to have the reports scheduled and saved makes sense when you are dealing with either huge amount of data being pulled into these reports or being rendered in the reports causing them to run for a long time when run on-demand. Hence it makes perfect sense to have these reports scheduled to be run once a day, burst and saved.

Solution:

Step 1: Create the parent report by dragging in the Prod Line data item.

Step 2: Create the child report by dragging in Prod Line and the other required data items. Set the bursting options on this report to produce burst outputs by Prod Line.

Step 3: Now to create the navigational links, look up the search path for the child report.

Step 4: In the Parent report, create the HTML items as shown below:



HTML Item 1: <a href="../cgi-bin/cognos.cgi?b_action=cognosViewer&ui.action=view&ui.object=/content/folder[@name='Reports']/report[@name='Dim - Parm URL 2']/reportVersion[last()]/output[contains(@burstKey,'

HTML Item 2: Set the source type to Data Item Value and choose the Prod Line data item.



HTML Item 3: ')]&cv.header=false&cv.toolbar=false">

Use the cv.header and cv.toolbar if you need to hide the Cognos Viewer header and toolbar.

HTML Item 4: </a>

Step 5: In the child report, create the link to the parent report as shown below:



HTML Item 1: <a href="../cgi-bin/cognos.cgi?b_action=cognosViewer&ui.action=view&ui.object=/content/folder[@name='Reports']/report[@name='Dim - Parm URL 1']/reportVersion[last()]/output&cv.header=false&cv.toolbar=false">

HTML Item 2:</a>

Step 6: Run and save the output for the parent report. Run and burst the outputs for the child report.







Clicking on Camping Equipment in the above saved output displays the below saved Camping Equipment output in the same window.



And clicking on the Camping Equipment link in the child output takes you back to the parent report displayed in the same window.

You may notice that as you navigate back and forth between the 2 reports, the left side margin space increases pushing your report to the left and scroll bars appear in your reports now. To avoid this use a target="_parent" tag in your HTML items. If folks are interested then I will blog about why this seems to be happening.

Related Articles:
 

6 comments:

ArulsTEG said...

Zephyr,

Thanx for sharing.

-Arul

aditya said...

Hi Zephyr,

PLease share as in why the left side margin space increases pushing the report to the left and scroll bars appear in reports , this is happening in my report when I click on "Top","Page up","Page down" , "Bottom" , "Keep this version" or any link in my report output in HTML (Cognos 10). Do you have a solution for this?.
Any help would be appreciated as this is an urgentt issue.

Thanks & Regards ,
ADDY

Zephyr said...

The left margin space issue occurs only in saved HTML outputs. But from your question this seems to be happening in on-demand reports. You could open a PMR with IBM on this. Hope this isn't a bug in Cognos 10.

It would be great if you can keep us posted on this for the benefit of those planning to move to Cognos 10.

Balamurugan Loganathan said...

Zephyr,
If the report contains more than one version and if I need to refer third version, how should I build the URL?

reportVersion[last()] refers to the latest version. Could you please let me know whether you have any idea of referring different output versions?

Thanks.

Parimal Patil said...

I have one scenario in which report has say Regions Column which holds North, South ,East West,All valus.
i want to navigate report for all values from report except 'ALL'
I don't want to keep HTML link on ALL value.

How to do it?

Anonymous said...

Great write up!!! This helped me immensely.

For Balamurugan Loganathan - if you wish do specify an alternate version you will need the run time for the save. example
/reportVersion[@name='2014-10-28T20:24:07.618Z']
if this is a version that runs on a daily basis i would specify something like this
/reportVersion[starts-with(@name,'2014-10-28')]