Modify Reporting Services Export to CSV behavior

Aside from the default HTML rendering, SQL Server Reporting Services (SSRS) supports rendering extensions as well.
You can write your own extension if necessary, or you can use one of the rendering extensions that are included in SSRS.
These default extensions allow you export your report to the required format, i.e. PDF, EXCEL, CSV, etc.
Sometimes you come across a situation where the default behavior of an extension does not meet your requirements.

By default, the CSV renderer will always include header information in the first line of the file. The default delimiter of a CSV file is a comma.
But what if your requirements say that the first line must not contain any header information and that the desired delimiter is not a comma, but a semicolon?
In this blog I’m going to elaborate on an alternative way to render CSV files, by making use of the CSV rendering extension and URL access.

To demonstrate the default behavior I have created a simple testreport that lists employee-information.
This report has two parameters, a multivalue parameter to select one or more cities and a single-value gender parameter.
Here’s a selection of all female employees that live in Bellevue or Cambridge:

To export this report to CSV, we select the desired exportformat and press the ‘Export’ button.

If we take a closer look at the CSV file we will find something like this:

Notice how the first line contains the header information and that the data is delimited by a comma.
One way to omit the header information of a CSV file, is by setting the DeviceInfo parameter “NoHeader” to true.
To do this, you need to locate the RSReportServer.config on the reportserver and change the CSV rendering extension settings. On a similar way you can alter the delimiter as well, a list of available settings can be found here.

These settings, however, are serverwide and have impact on every report that is deployed on the reportserver.
Since we only want to omit the header information and change the delimiter in this specific report, changing the CSV device information settings isn’t an acceptable solution.
An alternative way to influence the format of a CSV file is by passing the device information settings as URL parameters.

Now let’s get back to our original report; we’re going to extend its functionality by adding a Textbox to it.
This textbox will serve as an “Export-Button”, so we’re going to label it as “Export to CSV”.

What we want this button to do is to render the report once more, but this time as a CSV file without header information and with a semicolon as delimiter.
Because we want to add the settings as URL parameters, our button needs to make use of the “Jump To URL” feature.

A great advantage of the “Jump to URL” navigation compared to the “Jump to Report” navigation is that it allows you to build an expression.
Here’s the complete expression:

   1: =Globals!ReportServerUrl    

   2:         & "/Pages/ReportViewer.aspx?" 

   3:         & Globals!ReportFolder 

   4:         & "/" & Globals!ReportName 

   5:         & "&param_City=" & Join(Parameters!param_City.Value, "&param_City=") 

   6:         & "&param_Gender=" & Parameters!param_Gender.value 

   7:         & "&rs:Command=Render&rs:Format=CSV&rc:NoHeader=true&rc:FieldDelimiter=;"

This expression includes the passing of the variables (both single- and multivalue) and the device information settings like “NoHeader” to omit the header information and “FieldDelimiter” to set the delimiter to a semicolon.
If we run the report once more and press the “Export to CSV” button our new CSV will look like this:

As you can see, we no longer have header information and the file uses a semicolon as delimiter.
By making use of URL access we have created an alternative export for a specific report, without having to alter any config files.
Using this technique you could set other settings as well, like changing the encoding of a CSV-file in case UTF-8 won’t do the job.

One last word, keep in mind that URL access counts toward the maximum length of the URL. Although the specification of the HTTP-protocol does not specify any maximum length, limits may be imposed by your webbrowser.