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
Anonymous
March 07, 2007
The comment has been removedAnonymous
March 11, 2007
I must say Thank you!! Quite a catch on this.Anonymous
December 05, 2007
The comment has been removedAnonymous
December 28, 2007
yours help me to fix column when I use "hide"However, Exporting csv do not display correctly the Header on the table ( in case Iuse field value (for example "=Fields!customHeader1.Value") for Header onthe table) yet.any suggestion for this??Anonymous
January 09, 2008
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.aspxis 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.Anonymous
January 09, 2008
The comment has been removedAnonymous
March 12, 2008
My problem is with images. I don't want to display them when I export to PDF or Excel. How do I accomplish that?Anonymous
May 08, 2008
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,HenriqueAnonymous
May 12, 2008
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.Anonymous
May 15, 2008
The comment has been removedAnonymous
August 07, 2008
The comment has been removedAnonymous
August 25, 2008
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...Anonymous
September 10, 2008
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?Anonymous
November 09, 2008
The comment has been removedAnonymous
December 04, 2008
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 youAnonymous
December 04, 2008
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 youAnonymous
January 22, 2009
The comment has been removedAnonymous
March 05, 2009
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!!!Anonymous
June 24, 2009
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"Anonymous
August 09, 2009
Thanks Much... this really helped and solved the problem. Great explanation.Anonymous
August 21, 2009
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.Anonymous
January 14, 2010
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?Anonymous
January 24, 2010
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 thanksAnonymous
March 28, 2010
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!!Anonymous
June 10, 2012
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
Anonymous
June 14, 2013
Thanks for sharing useful information :)Anonymous
August 22, 2013
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, PrasannaAnonymous
February 06, 2014
Great post - very useful. Thanks!Anonymous
August 05, 2014
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? ThanksAnonymous
October 29, 2014
@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.Anonymous
April 10, 2015
if u want explanation with brief example. check the below link satishmsbiworld.blogspot.in/.../how-to-hide-column-in-export-data-from.htmlAnonymous
June 17, 2015
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.Anonymous
October 13, 2015
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.