Reporting Services: Why aren’t all my report columns exporting to CSV and/or XML?


Don’t be surprised if reports exported to CSV or XML are missing several columns of data you otherwise see when viewing the report as HTML, PDF, etc.


PDF, HTML, and Excel export leverage “layout renderers” – a generally more expensive export process which attempts to mimic the “look” you created in Report Designer. CSV and XML are “data renderers”, and only care about data. The difference is reflected in what a layout renderer  typically exports (basically everything) and what data renderers save (not necessarily all of your columns).


Here is a semi-complete list of the factors that can impact whether or not your columns are exported when saving to CSV:     


·         Static Values – If a column in your report contains hard-coded static data that doesn’t change from row to row (like the value “hello”), a data render won’t bother exporting it.


·         Expressions on Visibility– If you conditionally flip the Hidden property of a textbox or column in your report, data renderers will automatically omit this data from export, even if the expression evaluates to False (False meaning “don’t hide”). This is a by-design behavior.


The happy ending is that you can ultimately control the export behavior on a textbox-by-textbox basis with the DataElementOutput property. By default, this sucker is set to “Auto”, which allows the data renderer to think. If you don’t want it to think, but just OBEY, you can set this value to Yes or No.


To get to the right dialog, right-click any textbox, choose Properties on the context menu, then select the Data Output tab


Tons of thanks to Denis Levin for much of this information!


Post-publishing edits:


removed bullet re: many NULL values in a column causing data not to be exported – misunderstanding on my part

Comments (33)

  1. Raheel says:

    thanks you save my cute butt I have full bloom reporting site powered by ssrs and I found when user export to csv the file is blank and nothing I could do about it. Now I know what I need to do get this sucker out of the way.

  2. Satya says:

    I must say Thank you!! Quite a catch on this.

  3. Bidyut says:

    This is a real butt saving solution,the best part is it doesn’t burn.

  4. Ken says:

    yours help me to fix column when I use "hide"

    However, Exporting csv do not display correctly the Header on the table ( in case I

    use field value (for example "=Fields!customHeader1.Value") for Header on

    the table) yet.

    any suggestion for this??

  5. Sidney says:

    As far as table to csv exports go, I’ve noticed that the name I use for the detail row’s cell’s textbox is the actual value used in the header. Use the View Document Outline to quickly rename all your textboxes to something you want displayed as a header. this page:

    http://technet.microsoft.com/en-us/library/ms155365.aspx

    is very valuable as well.

    I find it funny that I can change the extension to TXT and the delimter to %09 for tab or w/e. Essentially Using CSV settings to produce something hardly resembles a CSV.

  6. Sidney says:

    Follow up regarding column headers:

    It will accept the textbox name or an override using the DataElementName property.

    Bottom line is the CSV still ignores your header name on a table.

  7. Josh says:

    My problem is with images.  I don’t want to display them when I export to PDF or Excel.  How do I accomplish that?

  8. Henrique says:

    I’d like to know if you have a clue on how to solve my problem. I have a matrix report with a row group and a conditional second row group, based on parameters. The first row grouping is required for the user, but the second one is optional. When generating as HTML, everything works fine. The problem is when you generate it choosing just the first grouping and then export it to excel. The conditional grouping, instead of being hidden, appears with a white cell.

    Got it? Any ideas?

    I Appreciate it.

    Thanks,

    Henrique

  9. russch says:

    I’m guessing there isn’t much you can do about this and you’re dealing with an artifact of the Excel Rendering Extension.

    One thing that might work is if you set the height of the rows in the second group to a really, really small value and then allow them to expand as necessary. With luck, this might change the behavior.

  10. R. Jeremy says:

    Controlling the export is indeed a butt saving happy ending that doesn’t burn!

  11. rrosin says:

    Hi there,

    We’re seeing an issue where if a field is null, data does not export.  This behavior is expected, but it’s causing problems since columns are not lining up in CSV export.  

    ACCT_CODE     PO     FY

    10001-10-00   5004    08

    5005    08

    So where ACCT_CODE is blank, everything else gets shifted over in the row which is very confusing for people exporting and expecting to see any kind of linear result.  Is there a way around this?  Is there a way to generate some sort of placeholder even if a field is blank so the export is more meaningful?

    Thanks much!!

  12. Dave Green says:

    OK is THIS possible????? …

    I have a column on my report that is a hyperlink to a subreport (Jump to Url). When I export to PDF or Excel I don’t want this column included in the export, obviously, as its difficult to have a working hyperlink on paper 😉

    Can this be done? conditional hiding depending on whether its being viewed in the browser or Exported?

    Thanks for this…

  13. Ray says:

    I have a report where I am using some calculated fields for some columns. now when I run the report in report designer it shows the column values fine but when I export that in CSV it shows the old column values what I had before. As this is in report designer where do I mark this header to be rendered during CSV export?

  14. Trevor says:

    I am getting frustrated with this export to CSV "functionality" in RS. I have a matrix and none of the headers are being rendered in CSV. The header row is there but no values!! I have tried everything and read numerous articles on the web. All my matrix row and column data cells have a perfectly valid names and I have messed around with different combinations of the DataElementName, DataElementOutput and DataElementStyle. I have also tried embedding a table in the row above the row values (to align my row headers) and the closest I get is that the table headers are repeated for each data row of the matrix. I am sure this limitation will be resolved when we eventually go SQL2008 and the long awaited arrival of the Tablix!! In the mean time, I will have to tell my users to retype in the column headers once the data has been CSV’d to Excel. Pretty sucky

  15. Pradip Mallick says:

    I have a report with multiple charts plotted on top of another with the background color set to TRANSPARENT. When we are exporting the report to PDF format, it is showing only the topmost chart, but not the other charts behind it. Please help me with a fix so that all the charts can be made visible in PDF format…Thank you

  16. Pradip Mallick says:

    I have a report with multiple charts plotted on top of another with the background color set to TRANSPARENT. When we are exporting the report to PDF format, it is showing only the topmost chart, but not the other charts behind it. Please help me with a fix so that all the charts can be made visible in PDF format…Thank you

  17. N Virani says:

    I created a Table(Not Matrix)

    I have a group header below Table header line in Table.

    Group header line is Visibility -> Hidden -> True. Group header Textbox name is IPCode.

    I created a textbox name "TextIPCode" in Page Header with "=ReportItems!IPCode.Value".

    I can see "TextIPCode" in preview of report but I can NOT see in when I export in Excel or PDF.

    Please help me so Group header can be display as page header.

    Thanks,

  18. Shailesh says:

    Guys I have 3 different tables in SSRS Layout. I hide the output based on the parameter value.When I try to export the data in .csv I get the names of the other two hidden tables in the output.

    Can any1 help me out!!!

  19. Syed Ashrafi says:

    No column value will be exported to csv if column has a static value csv will only export columns that has expression values. Put = sign behind the value to make the value as expression. e.g = "My Header"

  20. Sheetal Chordia says:

    Thanks Much… this really helped and solved the problem. Great explanation.

  21. Guillathame Emanuel says:

    Interestingly, I am having problems with the DataElementOutput property. No matter what I set this to, the rows are being exported when I export to Excel. I am using SSRS 2005.

  22. SLS says:

    I have problem with exporting mulitple tables to CSV, Is there any way which can make the tables come one after the other in the CSV or make the tables in two different sheets in the csv?

  23. Jane Mann says:

    Do you know how to get 2008 Reporting Services to render the char(9) in the reports stored procedure?

    The report’s underlying SQL has a concatenated string, using + char(9) +.  How do I get Reporting Services 2008 to render this?

    Many thanks

  24. Srinivas Gorur says:

    I have 6-8 fields on my report and am exporting to excel. I see the report as intended when it is rendered. However, when I export the report to xml, i loose all formatting like currency symbol, decimal places etc.., that was done on the fields. Does anyone have an idea of what I might be missing.. Am using SSRS 2008

    Any help is really thanked!!

  25. Divya K says:

    Hi,

    I have some SSRS 2005 reports(rows 60000 and column 100)

    which if :

    – I am trying to export to excel, this gives runtime error.

    – I export to csv, this also fails if there are more no. of rows

    – I export with XML, it works but the Date and integer columns with NULL values donnot get exported. What should I do to make this work.

    (Note: report renders fine in the SSRS grid, problem only comes with export)

    Please help.

    Thanks,

    Divya

  26. Thanks for sharing useful information 🙂

  27. Prasanna says:

    Hi,

    I have 13 fields in my table. I want to give an option to user to choose what are the columns they need to display.I have made this through Column visibility property. While exporting to excel and PDF, It is working fine. But for CSV format, all 13 columns are getting exported instead of the columns that user has chosen. Please suggest me if you have solution to this.

    Thanks,

    Prasanna

  28. S Doubt says:

    Great post – very useful.

    Thanks!

  29. Deepak says:

    Can anybody answer Prasanna's question? I want to keep columns showing/hiding based on an expression rather than hard coding. This solution wont work if show/hide is based on expression. Any suggestion?

    Thanks

  30. Sunrise says:

    @Deepak, it is a bug in RS (I have 2008), columns not shown by setting the Hidden to True at design time don't show in CSV.

    However columns not shown by setting the Hidden by expression =True still show in CSV.

    This can be exhibited by choosing a column by clicking the tablix header then setting the Hidden to True with the drop down.  This will hide when exported.

    Then change the Hidden from True to =True by clicking expression and adding an equals sign before the word true and try again.

  31. HariSatish says:

    if u want explanation with brief example. check the below link

    satishmsbiworld.blogspot.in/…/how-to-hide-column-in-export-data-from.html

  32. Mohan says:

    I have to export (EXCEL template) data into CSV. But the data is not considering the formatting done in EXCEL. Can anyone help me in getting the data in CSV correctly. Thanks in advance.

  33. Shri says:

    Hi, I have 7 columns where I have made visibility of the column on some condition. when I preview the data its fine but when I am trying to export into CSV file even the hidden columns are exported with data. I need to export only the columns that I am previewing in SSRS report. When I try to export in other different formats(PDF, Excel, TIFF, word etc) its working good. I am able to export data that I want to see.