Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SSRS csv export with comma in the column header names

By default, csv takes the text box name as the csv header name for the columns. The text box does not allow a space.

How do I make a different header than the text box for the csv?
Is there a way to display comma in the header when exported to csv?

Note: The solution has to be only for one report; not global.

like image 627
Lijo Avatar asked Apr 13 '10 17:04

Lijo


3 Answers

The column name comes from the DataElementName property, or if that is blank, the Name property. Unfortunatly, neither allow commas or quoting.

There is a blog post here:

http://www.behindthecode.net/Blog/post/2008/01/29/How-to-modify-Reporting-Services-CSV-export-Behavior.aspx

on how to change settings in the config file for csv exports. These are global changes. The list of properties available is here:

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

None of these allow the spaces you need, or the quoting of column names that I am currently trying to achieve. Sorry.

like image 72
Keith Avatar answered Sep 19 '22 16:09

Keith


In short, No, it's not possible.

From SSRS 2008 Column issue when exporting to a CSV file:

For csv export, the header of a column is determined by the DataElementName property of the textbox which actually contains the data. If the DataElementName property is not explicitly set by the user, DataElementName automatically gets defaulted to the value of the textbox.Name property.

That being said, I believe you're getting this issue because RDL has a restriction that both a reportItem.Name and reportItem.DataElementName property must be CLS-compliant identifiers. The headings like "Phone #", "Other Name(s)", etc... are not CLS-Compliant.

You can verify this by opening up the report in BIDS or RB and trying to set the DataElementName/Name property of the textbox containing the data to those names.

You will get an error which states the following:

Property Value is not valid. Specify a valid name. The name cannot contain spaces, and it must begin with a letter followed by letters, numbers, or the underscore character (_).

There is a Microsoft Connect Issue about this, but unfortunately it looks like it won't be acted on.
See: Use of expressions for DataElementName property (for CSV Export)

like image 21
KyleMit Avatar answered Sep 21 '22 16:09

KyleMit


This solution works: https://stackoverflow.com/a/32110072/674237

In summary:

  1. Modify your query to have the first row in the data set be your desired column headers. E.g., select header names and union them to the data set.
  2. Add a new CSV rendering extension to the report server that excludes headers.

This workaround will let you to put whatever values you'd like in the headers.

like image 41
Brandon Avatar answered Sep 21 '22 16:09

Brandon