Excel Reports From Anywhere With Microsoft Reporting Services

On a recent project, I had to generate some reports in Excel format. They needed to be automatically generated at regular intervals by a console application, stored on the server, and accessible for download at a later time. They'd also need to be generated on the fly by users of the web application.

Generating Excel documents from a web app is a common requirement. I usually use some combination of HTML tables, CSS, and setting a couple HTTP headers (content-disposition and content-type). This works well, and Excel is good at formatting the document consistently with the HTML/CSS provided. Sometimes the requirement is a list of tabular data, so I generate a CSV, set the HTTP headers, and write the CSV contents directly to the OutputStream of the HttpResponse. Simple. Easy. But it only works on the web.

I needed a different solution that works outside the context of the web, and I found a few different possible options. I could try the Microsoft Excel Interop library, but that would require a LOT of ugly code for a single report. Who wants to run Excel on the server?

There are open source and 3rd party components, but these suffer the same problem as the Excel API – lots of code needed for a single file. I wanted a visual report designer and an API to generate the report any time from my applications.

What I discovered was Microsoft Reporting Services.

I could say a lot about Reporting Services, but I'll focus on the parts I need to meet my immediate requirements. I noticed three major benefits in this context:

  • A visual designer integrated into Visual Studio 2010 (and a wizard that helps you select a data source).
  • An API to generate these reports from my application.
  • The data source of the report can be .NET CLR objects. You can have multiple, named data sources in a single report.

I can use my service layer to generate strongly-typed data for the report, pass it on to the reporting sub-system, and get back a Stream. Then I can then either write to a file, or send to the HTTP response. Awesome!

Now for some code. This is an example of how I could use the Reporting Services libraries to generate a report in runtime. 

Code Snippet
  1. public void GenerateReport(List<DataSource> data, Stream writableStream)
  2. {
  3. var report = new LocalReport();
  4. report.LoadReportDefinition(Assembly.GetExecutingAssembly()
  5. report.DataSources.Add(new ReportDataSource("DataSource1", data));
  6. report.SetParameters(new ReportParameter("GeneratedAt", DateTime.UtcNow.ToString()));
  7. Warning[] warnings;
  8. report.Render("Excel", null, (CreateStreamCallback)delegate { return writableStream; }, out warnings);
  9. }


Not much code, huh?

At the center of the API is the Microsoft.Reporting.WebForms.LocalReport object, which does most of the heavy lifting. Since the LoadReportDefinition method accepts a Stream, the report could also come from a file, database, or any other storage mechanism. I chose to have the reports compiled into the assembly as an embedded resource, so it’s easy to load them and they travel along with the assembly wherever it goes.

You then feed in the data sources and report parameters and call Render. Here, I am telling Reporting Services to generate the report in Excel format. The LocalReport object can also generate reports in PDF, Word, or Image format! That is doubly awesome, because creating reports in those formats is also a pretty common requirement, and now I have a single tool I can use to create them all!

Comments from CQL Readers

Kroshed 02/21/2013

<p>Hi,<br /> My requirement is the same as mentioned above..<br /> I am new to BI and I might be asking a very stupid question ..<br /> Is this a code that has to be implemented in SSIS in a script task?<br /> From where and how do I invoke this code ?</p>


Adam Clarke 02/22/2013

<p>Kroshed, This code was originally intended to be used in a web, desktop, or console application. I have not tried it, but I guess it would be executable from a SSIS package. Just remember to import the Microsoft.ReportViewer.WebForm assembly. You might also consider loading your report definition from a different source, such as a database or the file system. Hope that helps.</p> <p>Joel</p>


Leave a Comment

Subscribe to Our Blog

Continue Exploring




Our Work Services Commerce Cloud Approach Culture Blog Careers Contact

3344 Grand Ridge Drive NE
Grand Rapids, MI 49525
616 365 1000