14 comments
Business Intelligence 
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: & "¶m_City=" & Join(Parameters!param_City.Value, "¶m_City=")
6: & "¶m_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.
14 comments
How do you handle for “Multivalue” parameter. I have a multivalue parameter, when I click “Select All” :: I am passing ALL as a value as my SP understands “ALL” value but there is no such value when the values are displayed on the Multivalue parameter.
For selection of few values it works, for “Select All” the URL imposes length as 2048 char…. so how should I handle this scenario??
How to handle multivalue :: coz right now it is throwing error “value is not valid for the parameter <>”
Anil
Hello Anil, thank you for replying. I shall try to address your issues as accurate as possible, but please correct me if I’m heading in the wrong direction.
You need to know that the ‘ALL’ parameter value in SSRS differs from ‘regular’ parameter values in this way that the ‘ALL’ parameter isn’t really a value you can refer to. Even though it shows up in the dropdownlist as a selectable value, it isn’t passed on as a value itself. All it does, is selecting all values in the dropdownlist and this ‘list’ is being passed on.
The second problem you’ll bump into is that you can’t assign multiple values to one parameter in the URL string. What needs to be done, is that all values are split up and being passed in the URL string as individual values, i.e.:
& “¶m_City=” & Join(Parameters!param_City.Value, “¶m_City=”)
In my example this would result into
¶m_City=Bellevue¶m_City=Cambridge
This is SSRS’s way of passing multivalue parameters in the URL string.
If I understand your question correctly, you have this technique (almost) working but you’re running into the limits of the maximum URL length? Are you sure that there is no other cause for your error?
marks
Is there a way to modify the behaviour of exporting to excel? Specifically, formatting and alignments?
Alex
GUD GREAT Artcile 🙂
manishKumar1980
Great article – used it to get round the 2008R2 export to csv extra line issue. Thanks!
paul
Hi, How do you make the Button? I added a Text Box and copied/editted the values per the Jump to URL and once deployed and previewed, the Export to CSV was not a button?
Thanks!
Elisabet
Hi Elisabet, could you clarify what you mean with ‘Export to CSV was not a button’?
Technically speaking the ‘button’ is not a real button like the ones you usually see on websites.
It’s actually a textbox with a sort of ‘onclick’ event that jumps to another URL, this way the textbox acts like a button (although it doesn’t have a fancy pressdown animation like regular buttons have).
To come back to your question, what exactly isn’t working?
Mark Streutker
[rsRuntimeErrorInExpression] The Hyperlink expression for the textbox ‘textbox20.ActionInfo.Action’ contains an error: Operator ‘&’ is not defined for string “/Pages/Folder.aspx?/_Review&acco” and type ‘Object()’.
Preview complete — 0 errors, 1 warnings
adding to Elisabet question it throws this error ,and does not show text box as hyper link.
Mark Can you tell what is that i am missing
help much appreciated
andy
Hello Andy,
May I ask what you’re trying to accomplish with your expression? I see that you are referring to Folder.aspx instead of ReportViewer.aspx. Could you post your entire expression, so I can take a look at it?
Mark Streutker
=Globals!ReportServerUrl
&”/Pages/Folder.aspx?”
& “/Finance/Financial Planning and Analysis/_Reports/”
& “_review”
& “&accountno=” & Parameters!accountno.value
& “&rs:Command=Render&rs:Format=CSV&rc:NoHeader=true&rc:FieldDelimiter=,”
andy
this is the url…i do not know what is wrong with it ??
please help !!!
andy
Hi Andy,
thank you for your expression. At first glance the expression seems fine, so I decided to create a little dummy-report using your expression to create a .CSV file.
Having tried this, I have come to the conclusion that your expression is a valid one. So at this moment I’m not quite sure why you get an error..
Could you try if my dummy-report works in your environment? Download here:
https://blogs.infosupport.com/wp-content/uploads/2010/04/review.rdl_.zip
You should deploy the report in the “/Finance/Financial Planning and Analysis/_Reports/” folder in order to get the export to work.
Oh and about the ‘button’ itself, by default a textbox will not display itself as a clickable URL. To mimic this behaviour, the button’s text is made blue and underlined.
Mark Streutker
Hello Mark.
Thanks for that help but i also found another way of dealing with it and it works completely fine ,i made a couple of changes which are :
Under Report-> Report Properties -> Code, Paste the following
Public Shared Function BuildParams(ByVal param As String, ByVal paramname As String) As String
Dim params() As String = Split(param, “,”)
Dim url As New System.Text.StringBuilder
For Each val As String In params
url.Append(“&” + paramname + “=” + val)
Next
Return url.ToString()
End Function
All i’m doing in the above code is just getting the parameter collection and building the url dynamically.
Now in the textbox properties, Action -> go to URL, you need to do the following,
=Globals.ReportServerURL & “?” & Uri.EscapeDataString(Globals.ReportFolder & “/” & “_review”) & Code.BuildParams(Join(Parameters!accountno.Value,”,”),”accountno”)
Where Code.BuildParams(Join(Parameters!empid.Value,”,”),”empid”) is calling the above written function with the list of values from the parameter and then the actual name of the target report parameter(s). The final built URL will look some thing like,
http://machinename/ReportServer?%2FFinance%2Financial palnning and analysis&accountno=2&empid=5
Thanks
andy
This helped me a lot thanks.
http://www.aboutsql.in/
http://letslearnssis.blogspot.com/
Kutub