Serving Dynamic SSRS Excel Formatted Documents Via a MemoryStream

On an old Web Forms app we had the request to allow users to download the data from a formatted SSRS report as an Excel spreadsheet.  They currently view the formatted report using the ReportViewerControl, however, when you use the export feature it exports it to Excel with all the formatting of the report, including groups.  This is fairly unusable.  What you really need is a simple spreadsheet with a cell for every value without any grouping.

So, I created a report that had no grouping or formatting and a column for every field of data. 

I could have forced the user to view this ugly report in the ReportViewer and then export it as an Excel spreadsheet but I wanted them to be able to click on a link and get the report directly.  That’s easy enough because you can get an SSRS report formatted as Excel by appending “rs:Format=Excel” to the end of the report url.  This doesn’t work for us, however, for two reasons:

1) The website is accessible to users outside our network and the SSRS server is not.

2) The report retrieves sensitive data filtered by a parameter.  It would be fairly easy for a user to change the parameters in the url to obtain data they shouldn’t be viewing.

In the ReportViewerControl we change the parameters on the server side so the user simply views the generated report with no option to change the parameters.  Now, I needed a way to let them download the Excel version with the same restrictions.

Below is the solution I used with the help of several different other sites on the web.

I created a hanlder that would create the url with the proper parameters and formatting, retrieve the report and then send it out to the user as an Excel document. This way when the user clicked the link for Excel version of the report their browser would open a dialogue to Open or Save the Excel file.  Works like a charm.

Here is the code:

The first few lines simply clear any headers or content that may be initially set.  This is mandatory when sending files because we are going to be setting the headers later.

Line 7 is the url of our report.  Notice that “rs:Format=Excel” is in the url letting SSRS know we want this report as an Excel doc.  Also notice that I set the DistrictName parameter by getting the logged in user’s district name via a utility method.

Line 8 sets up an HttpWebRequest object so that we can make a call to the SSRS server just as you would with a browser. 

Since the SSRS server is on our domain and user restrictions are in place Line 9 sets the credentials we need. Our webapp impersonates a user with the correct access so that we can have the proper security restrictions in place yet allow non-domain users access to the data.  This impersonation is set up in the web.config and DefaultNetworkCredentials looks their first to get the credential information.

Lines 12 & 13 simply grab the web response and sets up a Stream object to read it with.

Lines 15 – 20 sets up a temporary MemoryStream object which holds the bytes read from the response (remember SSRS is sending us an Excel file, which is a binary file, not plain text).

Lines 22 – 28 finally sets up the headers and content type, sends the bytes and closes the stream.

I probably didn’t need a MemoryStream object.  In the loop I probably could have written the bytes directly to the context.Response object.  But this seems a little easier to read and potentially debug if necessary.

In the end I’d rather use MVC.  In fact I created the exact same result (except as a CSV file) using MVC and LINQ.  It was a snap and I had it done in 5 minutes.  Unfortunately the current site is on a Windows 2000 server and makes heavy use of Web Forms and the SSRS viewer controls.  I know I could have simply had the link request the result from an MVC app on another server but I wanted to keep this all fairly consistent and coherent.

Look for me to change this all up next year when I’ve moved our department webpage entirely over to MVC.