Exporting report content in CSV format

Hi all,
I am planing to use Iserver Express to deploy to my customers, I am trying to figure out how to export the report content in a CSV format. at the moment the CSV option only allows for importing report data.
within the table I am trying to import, there are some label that i would like to appear in the csv file. also the date always gets converted rather than displayed like what appears on the screen. e.i.
report viewer display

MPAN Number Metername readingdate reading
MPAN E-34597818 01 V R 20090103000000 7216.8 T
MPAN E-34597818 01 V R 20090103000000 7217.8 T
MPAN E-34597818 01 V R 20090101000000 7213.1 T
MPAN E-34597818 01 V R 20100106000000 7351.6 T
MPAN E-34597818 01 V R 20090101000000 7214.0 T
MPAN E-34597818 01 V R 20090102000000 7214.9 T
MPAN E-34597818 01 V R 20100104000000 7218.8 T
MPAN E-34597818 01 V R 20100104000000 7219.7 T
MPAN E-34597818 01 V R 20100105000000 7267.4 T
9999999999999 9

but when I try to get the CSV data i get

Aggregation,metername,reading,readingdate
13,E-34597818,7214,01/01/2010 23:22:00
13,E-34597818,7214.9,02/01/2010 11:04:00
13,E-34597818,7351.6,06/01/2010 08:49:00
13,E-34597818,7430,07/01/2010 08:12:00
13,E-34597818,7467.5,07/01/2010 19:54:00
13,E-34597818,7218.8,04/01/2010 09:55:00
13,E-34597818,7219.7,04/01/2010 21:37:00
13,E-34597818,7267.4,05/01/2010 09:22:00
13,E-34597818,7216.8,03/01/2010 10:32:00
13,E-34597818,7217.8,03/01/2010 22:13:00
13,E-34597818,7509.7,08/01/2010 07:35:00
13,E-34597818,7549.3,08/01/2010 19:17:00
13,E-34597818,7213.1,01/01/2010 11:41:00

all the static values are simply ignored and the the aggregation data is repeated on all the rows.

What i would like to achieve is to export the the exact report content as a CSV including the empty column and for the date not to be converted back.
Thanks in advance

Find more posts tagged with

Comments

magic_bern

Just to add some clarification on the table:
MPAN Number | Meter Name | 01 | V | R | reading date | reading | | | T |

10 columns in total where 01, V, R, T are static and 2 empty column
the end of file is also always
9999999999999, No of records.

averma

Hi magic_bern,
I am not sure I understand your comment about csv option being supported only for importing report data. You can export report data to cvs format using export data option from the drop down menu in the viewer.
One way to get your required date format in CSV format is to create a calculated column (as string)and use that while doing a data export.

Ashwini

magic_bern

Thank Averma for the reply,
only data from the dataset seems to be available from the CSV data export. (I am unable to bring the static value that are constant in each rows i.e. 01,v,r)Also I have a couple of empty columns that i would like showing on the CSV file.
I have attached the file I would like to produce.
Also, when i choose a data and set it as a string, i am unable to format it the way i require (YYYYMMdd000000)

averma

When you create a calculated column it becomes available in the dataset. Can you attach a sample report (preferably using classic models sample database) and the desired sample csv output so that I can have a clear understanding?

Ashwini

magic_bern

Hi Averma,
please find attached a sample of the report i am trying to produce as a CSV as well as a sample csv file.
i am using the payments table from classic models to create the report.
in the csv, the first "0000000000000" are always static, it is followed by the date when the report is produced and then by the number of time the report was produced. (i wrote 1 in the sample but the number will keep incrementing every time the reports is ran).

at the end of the file, the "9999999999999" is also a fix number followed by the number of rows in the record but this number is preceded by leading zeros to make it a 13 digit number.
Thanks in advanced

averma

Hi magic_bern,
Thanks for sharing the design file, I think now I have an understanding of the issue you are running in. Here is one workaround:
1. Run the report as you do.
2. Enable interactivity from the menu
3. Click on any table column
4. On top of the column a menu apprears
5. Click on the drop down button in this menu
6. Select Export Content
7. Click OK on the dialog

This will present you the table dump in Excel. For dates to appear in the desired format you should create calculated column in your dataset (of type string) and use it in the date table column. Hope this helps
Ashwini

magic_bern

Hi Averma, Thank you for the reply,
I have been using the method you indicated to try to have the file i am required. but i have to do it for few different site and it is a report i have to email every day so i was hoping i might be able to schedule it.
I have just noticed that CSV is not available as an option for scheduling.

About the date column, adding a computed column in the dataset as a string does not seem to do the trick as it does not let me change the date format to what i want but instead write the date in a format i definitely do not want.
I have noticed however that the date when exported into excel come with a ' in front (like when you what a cell to appear as a string in excel).

At the moment i am considering adding few static field in the database table so i can bring 01,V,R,T in the dataset but im not even sure whether i will be able to schedule it.

averma

Hi magic_bern,
You can schedule the report to Excel output format and then use Excel to save it into CSV format. As for the date format, see the attached sample report to see how it can be done.
Since BIRT framework is extensible you could also write your own custom CSV emitter and schedule your report output directly to the desired format.
Ashwini

magic_bern

Hi Averma,

Thanks for the dateAsString example, it is doing what i am looking for. i will be importing them for the time being as ask the users to save them as csv.
I shall look into the emitter at one point later hopefully.

Is it possible to add the a computed column that is incremented every time the report is scheduled and emailed?

Thank you

averma

One simple way would be to set up a counter in your database and access it using stored procedure in your BIRT report. In your stored procedure you could be incrementing this counter.

Ashwini