none
Reporting Service 2008 - CSV export

    Question

  • Greetings,

    I am using reporting services 2008 and many of my users export data from reports via csv export.

    In prior version (2005) csv would be formatted with my date column horizontal like this;

    date 1    date 2    date 3 etc...

    now all my date columns are vertical like this;

    Date 1
    Date 2
    Date 3

    Can anyone tell me how to alter the way the data is displayed via export csv? I didnt create the original so not sure what steps were taken but hope someone smarter then me can assist.

    thanks,



    Saturday, March 21, 2009 11:58 PM

Answers

  • Hi,

    What you are seeing can occur if your matrix 2005 report gets upgraded to a tablix-equivalent report in SSRS 2008 (since matrix, table, and list are now all merged into the tablix structure for SSRS 2008). Specifically, the combination of the way your matrix gets upgraded to tablix from SSRS 2005 to SSRS 2008 and the changes in CSV renderer from 2005 to 2008 will cause the behavior you are seeing. One way to work around this is to create your report as a table, instead of a matrix, and specify your date fields (date1, date2, date3) as details. Also, make sure that the textboxes containing your actual date1, date2, and date3 data have DataElementName set to "Date1", "Date2", Date3", respectively.

    Hope this helps.


    Ke Xu - MSFT Reporting Services
    Monday, March 23, 2009 6:02 PM
  • Hi,

     

    As you noticed, this is a by design behavior in exporting to CSV file. It means you cannot display the format like a matrix. For example, if the matrix looks like this:

     

    Name  1    2     3  total

    A       1.0  2.0  3.0  6

    B      4.0   5.0   6.0  17

    Total   5      7    9    23

     

    In Reporting Service 2008, after exporting to CSV file, the default format looks like:

     

    Name <column filed> <detail>TextboxName TextboxName TextboxName

    A         1             1.0          6     5       23

    A       2            2.0          6     7          23      

    A       3            3.0          6     9         23

    B       1            4.0          17    5         23

    B       2            5.0          17    7         23

    B       3            6.0          17    9         23

     

    In Reporting Service 2005, after exporting to CSV file, the default format looks like:

     

    1, 2,3 

    1.0,2.0,3.0 

    4.0 ,5.0 ,6.0 

     

    In addition to Ke Xu, one possible work-around I can think of is exporting to the Excel file. After that, change the file type to the CSV.

     

    Hope this helps,

    Raymond

    Tuesday, March 24, 2009 8:56 AM

All replies

  • Hi,

    What you are seeing can occur if your matrix 2005 report gets upgraded to a tablix-equivalent report in SSRS 2008 (since matrix, table, and list are now all merged into the tablix structure for SSRS 2008). Specifically, the combination of the way your matrix gets upgraded to tablix from SSRS 2005 to SSRS 2008 and the changes in CSV renderer from 2005 to 2008 will cause the behavior you are seeing. One way to work around this is to create your report as a table, instead of a matrix, and specify your date fields (date1, date2, date3) as details. Also, make sure that the textboxes containing your actual date1, date2, and date3 data have DataElementName set to "Date1", "Date2", Date3", respectively.

    Hope this helps.


    Ke Xu - MSFT Reporting Services
    Monday, March 23, 2009 6:02 PM
  • Hi,

     

    As you noticed, this is a by design behavior in exporting to CSV file. It means you cannot display the format like a matrix. For example, if the matrix looks like this:

     

    Name  1    2     3  total

    A       1.0  2.0  3.0  6

    B      4.0   5.0   6.0  17

    Total   5      7    9    23

     

    In Reporting Service 2008, after exporting to CSV file, the default format looks like:

     

    Name <column filed> <detail>TextboxName TextboxName TextboxName

    A         1             1.0          6     5       23

    A       2            2.0          6     7          23      

    A       3            3.0          6     9         23

    B       1            4.0          17    5         23

    B       2            5.0          17    7         23

    B       3            6.0          17    9         23

     

    In Reporting Service 2005, after exporting to CSV file, the default format looks like:

     

    1, 2,3 

    1.0,2.0,3.0 

    4.0 ,5.0 ,6.0 

     

    In addition to Ke Xu, one possible work-around I can think of is exporting to the Excel file. After that, change the file type to the CSV.

     

    Hope this helps,

    Raymond

    Tuesday, March 24, 2009 8:56 AM
  • Thank you both for your replies, One further clarification.

    The report viewable to users is a chart with the number of months they have selected shown graphically and the totaled numbers by an aggregate in this case a selling area. In the past version exporting to CSV would also provide the underlying metadata that is marked as hidden (District, address etc..) when I use the excel export I only get the graph and the totaled numbers not all the metadata.

    I will attempt Ke Xu's solution however I fear it will also alter what users see and thus not be a usable solution. I have created refreshable ODBC queries into a pivot table for the near term but would prefer to somehow code this to allow users to view it in the format they are used to seeing as they have solutions they have built off the older style.

    Thanks,

    Eric

    Sunday, March 29, 2009 6:55 PM
  • I am getting a different issue. I got
    
    in a single csv file when I export from SSRS 2008 to csv. Any ideas?
     
    Tuesday, May 26, 2009 6:58 PM
  • Hi,

    You might want to take a look at the following thread:

    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/67c22fa6-e4e6-43b1-ae5c-5c5cb163ffca

    Although the post above is for SSRS2005, you might want to make sure that the textboxes (as well as all other elements) in your report that you want to be output in csv have their DataElementOutput property set to "Output". Additionally, can you change the Encoding device info for csv renderer in rsreportserver.config

    http://msdn.microsoft.com/en-us/library/ms155397.aspx

    to see if that will help your issue? Try explicitly setting it something other than the default (which is UTF8), such as "ASCII", "Unicode", "ISO-8859-1". Below is a link listing possible encoding values for reference:

    http://msdn.microsoft.com/en-us/library/system.text.encoding.aspx

    HTH


    Ke Xu - MSFT Reporting Services
    Wednesday, May 27, 2009 6:19 PM
  • As I understand it the possible solutions are the following.  Any other suggestions?

    1) Convert Matrix report to a Table. 

    This won't work for me because the reason why the report is a Matrix is that the pivotted column headers and value are unknown until the report renders.

    2) Render the Matrix report to Excel and the change the file type.

    This won't work either because the reason why we don't render in Excel, in the first place, is that when the matrix groups the data, it loses the repeating values in the group.  For example:

    If the actual data looks like this

    Organization  Project PivotHeader PivotValue
    A                    1         ProjectMgr    John
    A                    2         ProjectMgr    Paul

    B                    3         ProjectMgr    Joe

    B                    4         ProjectMgr    Jay

     

    Excel Renders this:

    Organization  Project ProjectMgr
    A                     1        John
                           2        Paul

    B                     3         Joe

                            4        Jay

     

    I lose the repeated values for Organization and the user needs these values.

    How can I get around this issue?

    Thanks

    Monday, May 31, 2010 1:06 PM