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.