Drill through to file

[mbk]

Good day!

I need to save the filters/parameters the user selected to be able to run a report at a later time.

I'm using data modules, and therefore cannot use FM to call SP to write back to db. I'd rather not write back to a db.

I created a dashboard that drilled to a report, that has all the parameters I want to generate a detailed report, but I am not allowed to display the detailed report. It must be created and store on external file system.

could I add a button/extension that called the report but created it externally? Or is there a way to save the user's selections/filters to a file / csv for later executing.
Cognos has so many features: bursting, drill thru's, etc

Any thoughts, idea would be extremely appreciated.

[dougp]

If I could avoid it, I wouldn't use a report for that.  If this was a report (not a dashboard), I would create a Custom Control that makes and AJAX call to a generic handler (ashx or php file) with the parameter values in the URL.  The handler would store the data.  It could also have both GET and SET capability so the same handler could be used to retrieve the data later.

But I don't know that you can put a Custom Control on a dashboard.  Is there a way you can "drill" to a URL and pass the parameters?  ...without navigating?  The problem here is that by clicking on the link on the dashboard, the user may navigate away from the dashboard.  I assume that would be bad.

[cognostechie]

Good day!

I need to save the filters/parameters the user selected to be able to run a report at a later time.

I'm using data modules, and therefore cannot use FM to call SP to write back to db. I'd rather not write back to a db.

I created a dashboard that drilled to a report, that has all the parameters I want to generate a detailed report, but I am not allowed to display the detailed report. It must be created and store on external file system.

could I add a button/extension that called the report but created it externally? Or is there a way to save the user's selections/filters to a file / csv for later executing.
Cognos has so many features: bursting, drill thru's, etc

Any thoughts, idea would be extremely appreciated.

Just curious - why do you need to run the report at a later time? Is it because the report is too slow?

[mbk]

Thanks dougp !

I'm so glad there's a way for me to achieve this. I will try adding a 'custom control' as you suggested (ajax/php etc) on my 'Report'.
I haven't used custom controls yet but it looks like there's some good articles/example for me to learn.

[mbk]

Hi cognostechie,

No, it's not related to performance. It's a business requirement to have the detailed report generated to file, not on screen.

[cognostechie]

Hi cognostechie,

No, it's not related to performance. It's a business requirement to have the detailed report generated to file, not on screen.

In that case, you can specify the output to be Excel or PDF instead of HTML. The reason it shows up on screen is because the output is set to HTML (default for Cognos). You can also try bursting with a static value so it will show all the data and set the file system location in Cognos configuration. Cognos allows burst reports to be saved to a folder on the hard drive so burst it using a dummy value so that Cognos think it is a burst report but in reality it will be a report will all data.

[mbk]

Thank you Cognostechie, this is great feedback.

If possible, are you able to elaborate more on the 'burst it using a dummy value'?
Dashboard drills to report with selected parameters, then I can drill to report set to write to external file in folder. I'm not following the burst part...

I'm waiting for my admin to allow me to write to our external file, so I can't test it yet.

Again, I appreciate the expertise provided.

[cognostechie]

You are welcome mbk !

For the sake of explaining it, let's forget about the Dashboard for a moment.

Let's say you have 100 Salesreps in your organization and you need to send them a report every month so that they would know how much they sold that month. You created a burst report which has a query to show Sales amount. You used 'Salesrep ID' as the burst key so that it will create one report for each Salesrep. In Cognos configuration, you can specify a folder for burst reports so every time the burst report is run, it looks at the configuration and accordingly saves the burst output to that folder.

https://www.ibm.com/support/pages/how-save-report-outputs-file-location-report-basis

Now imagine that you drill-thru from the Dashboard and it activates the detailed report. If the detailed report is set to burst then it will send the output to the folder. Now about the dummy value. Create a query calculation in the detailed report query and type 'A' so that the value of that query calculation is always 'A'. Use that query calculation as the Burst Key so that it creates only one report with the data because the value 'A' will apply to each row of the data.

Hope this helps. Good Luck

[mbk]

Hi cognostechie,

This definitely helps clear up my understanding of your proposed solution. Your steps were very detailed, exactly what I needed, now just waiting for the admin to set the ability for external files. ... and then I'm on my way

Many thanks!

[mbk]

Hi cognostechie,


Finally go access to write to an external file system.

I can only burst the report to the file system if I select 'Run as' and enable both the 'burst' and 'Save report as an external file'.
I can't get this report to Burst to the external directory when called / drilled from another report or a dashboard. The drill thru creates the file on my local file system, not the external file system.

Is there anything that I should be verifying to get it to burst/write to external file when drilled to? I hesitated asking but I'm definitely stuck here.

[cognostechie]

What is the 'local file system' ? Is it the C drive of your laptop? Is the External file system is a network folder?

[dougp]

I'm a little late to the game on this one...

I think https://www.ibm.com/docs/en/SSEP7J_11.0.0/com.ibm.swg.ba.cognos.ug_cra.doc/ug_cra.pdf, starting on page 79, explains how to export all report outputs to the filesystem (when you use the CM.OUTPUTLOCATION setting).  I tested this and it didn't fit my need.  I think this would meet my need if I develop some batch file defined by CM.OUTPUTSCRIPT (or powershell routine called by a batch file) that runs every time a report drops in order to process it in a way that would meet my need.  It seemed like more work.

Regarding the instructions provided by cognostechie...
On step Part B(ii).9, notice "(This folder will show up first time you select RunWithOptions and save report to new Location)".  I don't recommend that.  Outputting these reports to a location on the Cognos server creates more work for you and is, generally, unhelpful to users.  What I do is create Windows junctions (I assume you're using Windows, but you didn't say) to locations on my network where the people needing these reports are already doing work.  The only catch is that the account running Cognos must have write permission to that share.

So, I create a folder (C:\ReportOutput) to hold all of the "folders" (actually, Windows junctions), create the junctions, then use Cognos Administration to configure the locations and secure them.

Create a junction:

Code Select Expand

mklink /D NameInCognosAdministration "\\server\path\to\user\folder\"

So, nobody needs read access to the Cognos server and everybody gets their reports.

[mbk]

Hi  cognostechie,

First, thanks for your help. I know I 'm missing a part of the puzzle, so hopefully I'm close.

What is the 'local file system' ? Yes - it is the C drive of your laptop?

Correct, the External file system is a network folder where my reports are created when I run them using 'Run in Background and selecting the save to external file.

Your earlier comment suggested I could Burst to external folder, 'Now imagine that you drill-thru from the Dashboard and it activates the detailed report. If the detailed report is set to burst then it will send the output to the folder. '

However, when I do the above, I get the file on my laptop, not the network folder.

[mbk]

Hi dougp - I really appreciate your knowledge on this subject.

I have a different business requirement. I will provide a dashboard to a user that is choosing filters to determine who to contact. I can't provide them the list of contacts/report on screen, security reasons. So they will drill to a report that has their selected filters listed, and hopefully it will burst to a file stored on the network. Then I can use this file to run the Contact report with their selected parameters.

So that means the user's won't need to access this report stored on the network directory.
You previously suggested that I could try 'Custom Control that makes and AJAX call to a generic handler (ashx or php file) with the parameter values', which is an option I may try soon.

I thought that the possibility of a Dashboard drill to a report (with the parameters) and burst this report (storing parameters) to network drive would also be an option without coding. But... when I do it, the burst report goes to my laptop. Meaning the users will have the same thing, instead of it writing to the network drive. I can 'Run as', and get it to write to the drive, but that's not how I need it, I need it thu the drill/burst.

I set the report's burst option in the 'Report' page to 'Directory Entries', and thought that would send it to the network drive.
The admin won't set CM.OUTPUTLOCATION for me as it will affect more than just my project.

Again, I appreciate the time on this. I've been searching and reading anything I can

[cognostechie]

So they will drill to a report that has their selected filters listed, and hopefully it will burst to a file stored on the network. Then I can use this file to run the Contact report with their selected parameters.

Now this is very different than what you mentioned earlier. I can't understand what you objective is and why.  I thought all you wanted is to send the report output to a network folder and the user will get it from there. You can make the drill thru report independent of the Dashboard by adding a parameter page to it and then scheduling it to run. That way, the output would go to the network folder, no need to burst it. However, I don't know what you mean by what is shown in bold above.

[mbk]

Hi cognostechie,

I am very sorry if I was unclear on my need, I thought in my first post that I had described it, but it definitely had holes in it:
" I created a dashboard that drilled to a report, that has all the parameters I want to generate a detailed report, but I am not allowed to display the detailed report. It must be created and store on external file system. I need to save the filters/parameters the user selected to be able to run a report at a later time ".

Objective -> Users need to access a dashboard with filters and visualizations to decide 'who they want to contact', based on their dashboard choices. Example, user may decide to select females, those who are in the age range 30 -50, live in west coast etc. Visuals only have summary info. Once they have narrowed this criteria on the dashboard, I need to provide them a detailed report of the people that meet this criteria they have selected. Normally the report would be generated based on the parameters in a drill thru report. But I cannot display this detailed report on screen as the User's data is confidential, and this is the difficult part for me.

Solution -> I thought if I could save the users choices and later create the report, it would meet the requirement, but I wasn't sure how to do this.
   I read there's stored procs to write back to DB  but this is supported in FM only and I am using data modules.
   dougp mentioned a 'Custom Control that makes and AJAX call' , and I'm sure this is true except I didn't know AJAX etc at the time.

   And I was able to save the parameters to a file when I tried the (interactive) dashboard to drill report capability, but not a network file. Probably where I got side-tracked and hoping I could do it without coding. I do appreciate your help, and knowledge


   

[dougp]

The admin won't set CM.OUTPUTLOCATION for me as it will affect more than just my project.

That's why I made the recommendation I did.

[cognostechie]

Objective -> Users need to access a dashboard with filters and visualizations to decide 'who they want to contact', based on their dashboard choices. Example, user may decide to select females, those who are in the age range 30 -50, live in west coast etc. Visuals only have summary info. Once they have narrowed this criteria on the dashboard, I need to provide them a detailed report of the people that meet this criteria they have selected. Normally the report would be generated based on the parameters in a drill thru report. But I cannot display this detailed report on screen as the User's data is confidential, and this is the difficult part for me.

If this is the only requirement then this is the same as what I did for a client and they just love it. This is what I did:

Keep the Dashboard only to show them the summary and don't use it for details. Create a copy of the drill-thru report which already has the filters. Add a parameter page to it and put the prompts there to work with the filters. Run this report independently do that when they run it they will specify the criteria in the parameters and then the report will show them the details. Since this will not be a drill-thru report and not a burst report, you can schedule it to run with their criteria and specify the network folder during scheduling. This works and I know that. It will only send the output to the local C drive when run manually but running it from the scheduler will always send the output to the network folder which is specified in Cognos Configuration. You can ask them all possible combinations for parameters and create a separate schedule for each one of them.