SSRS csv export with comma in the column header names

Question

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.

Solution 1

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.

Solution 2

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)

Solution 3

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.