Drill Down functionality in Relational Report

[rajurokz]

Hello Team,

This forum has been providing fantastic support and I'm glad that there is a specific forum for Cognos.

I have a requirement , in which i need to implement drill down functionality in my report. I already have 4 pages with different reports and queries attached to each of them. Now I want to implement  drill down, as in Year=>Month=>Week=>Day and similarly Office=>Manager=>Staff.

Please help me achieve this.

Thanks in Adv!
Rajesh Musturu

[MFGF]

Hello Team,

This forum has been providing fantastic support and I'm glad that there is a specific forum for Cognos.

I have a requirement , in which i need to implement drill down functionality in my report. I already have 4 pages with different reports and queries attached to each of them. Now I want to implement  drill down, as in Year=>Month=>Week=>Day and similarly Office=>Manager=>Staff.

Please help me achieve this.

Thanks in Adv!
Rajesh Musturu

Hi,

The way I see it you have two options here:

1. Change your FM model to include Dimensionally Modelled Relational (DMR) objects. These emulate the dimensions, hierarchies, members and measures you'd find in an OLAP cube, and give you drill down/up capabilities. You'd need to re-write your report to be a single report based on these objects, but you could then drill down/up within the report.

2. Create drill-through definitions using your existing reports (ie drill from the Year report to the Month report, passing the selected year). You wouldn't be remaining within the same report, though, and "drill up" would involve going back to the previous report.

Cheers!

MF.

[rajeurokz]

Thanks!

Please suggest me if something can be done to achieve this functionality within the same report rather drilling to a different report. As the DMR option is ruled out, we cannot go with the firs option. Hence throw some light on this.


Regards,
Rajesh Musturu

[MFGF]

Thanks!

Please suggest me if something can be done to achieve this functionality within the same report rather drilling to a different report. As the DMR option is ruled out, we cannot go with the firs option. Hence throw some light on this.


Regards,
Rajesh Musturu

There are techniques involving drilling through from a report to itself, but things can get very messy very quickly. Unless you're an experienced Report Studio author, I'm guessing you'd struggle to set this up, and it could be a real headache to support and maintain. Why is DMR ruled out? It's the simplest, easiest solution and it gives you exactly the functionality you need. I'm not sure throwing a light has anything to do with this, though? Wouldn't it smash? :)

MF.

[Lynn]

I'm not sure throwing a light has anything to do with this, though? Wouldn't it smash? :)

sooooo bad!!

[BigChris]

And also not necessarily true: https://www.tinc.uk.com/products/illuminated-bouncy-ball-pink/

[Lynn]

And also not necessarily true: https://www.tinc.uk.com/products/illuminated-bouncy-ball-pink/

ha ha!

[cognostechie]

Thanks!

Please suggest me if something can be done to achieve this functionality within the same report rather drilling to a different report. As the DMR option is ruled out, we cannot go with the firs option. Hence throw some light on this.


Regards,
Rajesh Musturu

Drill Up/Down is not something that has to be setup in the modelling tool before you make it work in Report Studio because the data has to be arranged accordingly. The arrangement of data in such a way is referred to as OLAP format whereas what you probably have right now is data in a Relational format.
Either you have to make a DMR in Framework Manager , which, as MFGF said, is the easiest way to create a virtual OLAP or you have to create cubes which
will require to learn a completely different tool.

I can relate to your situation though as I have seen the same in quite a few companies. Does your manager understand the technical side of BI or is he a musician appointed as the Director of BI because your management thought that BI is pretty straight and simple and does not require a qualified guy ? Does your management think that the only thing you do in BI is to write queries to pull the data out of the system and that BI is no different than Reporting and since this is 21st century, the term 'Reporting' is now called 'BI' ? If so, you might want to tell them that you can make 788 reports, one for each level of drill down and together , all of them will show the data for all drill downs. These reports should then be printed every day and the users should read those first thing in the morning spending 4-6 hours of their time. 

[raj_aries81]

Hello Team,

This forum has been providing fantastic support and I'm glad that there is a specific forum for Cognos.

I have a requirement , in which i need to implement drill down functionality in my report. I already have 4 pages with different reports and queries attached to each of them. Now I want to implement  drill down, as in Year=>Month=>Week=>Day and similarly Office=>Manager=>Staff.

Please help me achieve this.

Thanks in Adv!
Rajesh Musturu

Hi Rajesh,

You can use javascript to implement the Drill Up/Down fuctionality in the report.However, there are some caveats like - you need to amend the script based on the browser and cognos version, also you may not be able to leverage the analysis studio capabilities. I haven't implemented this script though, but you can give it a try if you want -

http://www-01.ibm.com/support/docview.wss?uid=swg21377785
http://www.cognosonsteroids.com/2012/02/create-report-with-collapsible.html

Thanks & Regards
Raj