Cognos reports and local file system

One of the issues which we faced during my new assignment was to store the report output on local computer’s file system. Till date we were just storing the output to only content store. So for us it was not the major task to maintain the report output. But later on as requirement changed we have had to come up with the solution.

After doing lots of analysis and research, I came up with following solution and that works guys!!!

1) open Cognos configuration on the server.
2) Edit global parameters
3) Go to general tab and give appropriate path to file system
4) Save the settings and then restart the services
5) Then go to Cognos connection and open dispatcher and services.



6) Click on define file system option

7) specify the folders and subfolders if required

8) select a report to store the output to local file system and provide appropriate parameters

9) And run the report in background.

10) Checkout the output in respective file folder.


this is just a draft version... i m yet to prepare complete document.....

comments are accepted.

Cheers!!!!
C1PH3R

Cognos sql and native sql….

Cognos sql and native sql….

Within my company I joined new account as a report developer. Its all together a new assignment I got from my company. This new assignment’s main motto is to optimize the query performance so that report generation will take less time to generate the reports.

Here, these guys are using DB2 mainframes for backend. And they are using Cognos to generate the reports. So whenever there is any call to generate the report from Cognos, a query is executed in the backend. Here just to inform u all that, a cost is associated to each query being executed on DB2 mainframe systems. So if report generation query takes 5 minutes to execute the report then some cost will be charged on that. So in order to minimize the cost I started using native sql feature of Cognos. This is truly amazing. I mean rather than using complex joins of the Cognos sql, we can tune the query performance in better way using native sql.

In my development of reports, I got the chance to use the native sql in few of the report. And that did work well. And it also improved the report execution time. So it was really a win win situation for all of us…

For me, I learned the new way of optimization of my queries and for my team, they got the logic to cut down the cost. It was great…

Cognos Report studio and CSV file format….

Hi all,
Few days back I was working on one assignment, in that we have had to export the report output in .CSV file format. Initially I thought that it would be easy to create the report and then run it in .csv file format. So I thought, “ohhh… thank god!!!!! This time I will not have to strive hard for report developement.” But friends, it was not the case. Later on I got more requirement from the client. Let me just tell the requirements.

Requirements:
They were looking for few fields in list report format which were already in the DB2 database. But apart from that they wanted the total record count. That is the number of records retrieved from report studio. They were also looking for the report run date or current month date.

My approach:

I started figuring out, whether I have all the required fields in my DB2 or not? So I looked in to the database. In my DB2 database, I could see, all the fields except the row number. I mean date fields was already there in the database so there was no need to worry. So for me to get the row number was an easy task. I went to my report studio and bought row_number calculated field. That did work great. (actually I ran the report in html format. LOL :) ) but I wanted to have the output in .CSV file. So I changed the format and I ran the report with .CSV file. And guys, what to tell u? Cognos opened one excel sheet, where I could not see my header and footer and not even my row_number() calculated field. Rest of the fields were as it is. So it was really tough for me to get in to the root cause, then later on after lots of googling. I came across the root cause. I came to know that, .CSV file format does not support header, footer and the calculated fields. So for me the picture was very clear. And I have had to come up with some other alternative solution.
Then I wrote rank function() in my list report query and I created new data item. That did work well, and it gave me the serial number kind of effect on my report when I ran the report in .CSV file format. So that was good. So finally I could get my development in sync with client's requirements.

So that was a small challenge in CSV file format reports in report studio….


Hope u liked it.

Note: rank() function does not work properly if you apply rank function on the measure which has many repetitive values. Order of the data is disturbed if you apply the rank function. So it is recommended that don’t use any order by or group by on list report.

Stepping stone: First Blog

Hi all,

After lots of thinking and researching I came to this decision that I will start posting the challenges which I face during the Cognos report development.

I think this will help other developers to get rid of some common problems and issues. This blog may become a channel of communication where other developers can post their comments and their ideas. I think this way, pool of strong resources can be created.

Hope the journey will move stably at later stage…

Comments and suggestions are accepted…


Cheers!!!!
C1PH3R.