January 14, 2011 at 3:25 am
SQL Server 2008 has changed how it handles exporting Matrix Reports to CSV from how it was handled in SQL Server 2005.
SQL Server 2005 used to export exactly what was in the Matrix whereas SQL Server 2008 outputs the underlying dataset.
This has caused major problems for some statement reports that we used for a customer in 2005 who have now migrated to 2008.
Basically our Statement worked as follows (The following is a basic example). We take the dataset
Name Trans Date Trans Value
Joe bloggs 01/01/2011 £10.00
Joe bloggs 02/01/2011 £20.00
Joe bloggs 03/01/2011 £30.00
Joe bloggs 04/01/2011 £40.00
We put this in a matrix on a report and it displays as follows
Name Trans Date Trans Value Trans Date Trans Value Trans Date Trans Value Trans Date Trans Value
Joe Bloggs 01/01/2011 £10.00 02/01/2011 £20.00 03/01/2011 £30.00 04/01/2011 £40.00
When we exported to CSV in 2005 the file would look like this (which is what we want)
Joe Bloggs,01/01/2011,£10.00,02/01/2011,£20.00,03/01/2011,£30.00,04/01/2011,£40.00
In 2008 the file looks like this
Joe bloggs,01/01/2011,£10.00
Joe bloggs,02/01/2011,£20.00
Joe bloggs,03/01/2011,£30.00
Joe bloggs,04/01/2011,£40.00
Microsoft suggested solution is exporting to excel and then saving as csv. This is not a option for me as the files are too big to export to excel.
The only way i can see of doing this is by putting my dataset in a cursor and building a String. i am concerned about this method as firstly it will take a very long time to Process and secondly the String might not take all data that is needed.
This also needs to be genereated on the fly as the customer wants to be able to filter the transactions that appear on the statement\csv file
We also have no control over how the file is formatted (i dont need to be told that this should be in xml i know)
Basically i am looking for any suggestion for the most efficient way to replicate how it worked in 2005.
Any help greatly appreciated
January 14, 2011 at 8:39 am
So instead of doing this in a matrix, why not just do it in TSQL then write it to a temporary table in a varchar(20) and Varchar(Max) column. Then all you need to do is output the data in a regular table. Check out Jeff's article on Dynamic Cross Tabs and Pivots...
http://qa.sqlservercentral.com/articles/Crosstab/65048/
I think it will get it done in TSQL then you just output it to the screen in a two column table and when you output it to a csv it should just work.
Alternatively if you have very large recordsets, I'd suggest using SSIS or BCP to accomplish something similar.
-Luke.
January 14, 2011 at 8:46 am
i dont think this is possible as the number of transaction i have per line will vary depending on the number of transactions an individual will have in the period specified
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply