In the software development industry, the bottom line is always, “Can this technology actually help me help my clients in a technically solid, yet time-efficient way?” As illustrated below, the answer is a definite yes. Reports can be built relatively quickly, formatted nicely, and delivered in many styles by any type of application. Sounds like a win for everybody.
On a recent project I had to generate some reports in Excel format. These documents needed to be automatically generated at regular intervals by a console application, stored on the server and accessible to users for download at a later time. They would also need to be generated on the fly by users of the web application.
Generating Excel documents from a web app is a fairly common requirement, and 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 simply a list of tabular data, in which case I would generate a CSV, set the HTTP headers, and write the CSV contents directly to the OutputStream of the HttpResponse. Simple. Easy. But only works on the web.
So I needed to find a different solution that works outside the context of the web and found a few different options that I could use. There is the Microsoft Excel Interop library, but that would require a LOT of ugly code, just to generate a single report, and who really wants to run Excel on the server? There are several open source and 3rd party components that could be used, but these generally suffer the same problem as the Excel API – lots of code needed for a single file. What I really wanted was a visual report designer, along with an API to generate the report any time from my applications. What I found was Microsoft Reporting Services.
There is a lot that could be written about Reporting Services, but I will focus on just the parts that I need to meet my immediate requirements. (Note that the capabilities of Reporting Services extend far beyond what I am doing here.) First of all, there is a visual designer that is integrated into Visual Studio 2010 (and a wizard that helps you select a data source). Second, there is an API that I can use to generate these reports from my application. Third, and this one surprised me (pleasantly), the data source of the report can be .NET CLR objects. (You can have multiple, named data sources in a single report). So, now 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, than 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.
- public void GenerateReport(List<DataSource> data, Stream writableStream)
- {
- var report = new LocalReport();
- report.LoadReportDefinition(Assembly.GetExecutingAssembly()
.GetManifestResourceStream(“Reports.Report1.rdlc”)); - report.DataSources.Add(new ReportDataSource(“DataSource1″, data));
- report.SetParameters(new ReportParameter(“GeneratedAt”, DateTime.UtcNow.ToString()));
- Warning[] warnings;
- report.Render(“Excel”, null, (CreateStreamCallback)delegate { return writableStream; }, out warnings);
- }
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, a 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 optionally, 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!