Dynamic Report Generation Using SSRS Local Reports

Recently, I’ve been working on re-architecting part of an invoicing application that has a very rigid invoice formatting design into an application that will eventually allow for almost infinitely customizable formatting in a very efficient manner.  This should lead to better customer relationships and fewer software customizations and therefore improved ROI.

PROBLEM

Think about your water bill.  Does it have all the information on it that you want?  Let’s assume that it doesn’t.  Suppose your water bill only shows the activity for the current period on it and how much money you need to pay the water company.  This is probably enough information for most people.  But, let’s suppose you’ve recently invested in some new appliances in an effort to conserve water (and money).  Now you’re probably interested in having a little more information on your invoice.  For example, maybe you would like to also see your activity for the previous period and for the same period a year ago.  This information would allow you to easily see how well your investment in new appliances is paying off (your ROI).  Now suppose that you called up your water company and asked them to add this information to your monthly invoice.  I can hear you laughing.  I can hear the water company laughing too.  Well, this is exactly the type of requests that the company I’m working for gets on a weekly basis. 

Their solution up to this point has been to have available in their custom billing software a handful of predefined hard-coded formats from which customers can pick.  However, almost monthly we get a request that doesn’t fit the mold of one of these predefined formats.  When this happens, we have to weigh up the cost of implementing a new format against the amount of money we believe we’ll make from this customer in an effort to determine if its going to pay off to give this particular customer exactly what they want or if we have to tell them that they’ll have to make due with what’s available.  Obviously, only one of these answers leads to exceptional customer satisfaction (and hopefully retention).  Of course, we want to give every customer exactly what they want but this is the real world and code changes still mean considerable investment. 

Oh, and did I mention that all of the current invoices are built using a PDF API?  This makes it very difficult for a programmer new to the app to make modifications if he’s never used the API before.
So, there are several problems here that need to be solved.

1. Give every customer the same level of exceptional support regardless of contract size.
2. Allow for more dynamic invoice styling while keeping a uniform appearance and format across all invoices.
3. Eliminate use of the PDF API to make future application modifications faster (and cheaper) to implement.
4. Eliminate the need to measure ROI of customer satisfaction.

PROPOSED SOLUTION

The solution to this problem that I proposed is to first eliminate the use of the PDF API and replace it with a SQL Server Reporting Services (SSRS) solution.  For this application I will use SSRS in local reporting mode.  This eliminates the need for a new dedicated reporting server.  We may later add an SSRS server but at this time the added cost and administration was unwanted.  Also, using SSRS in local mode gives us more options for customization of report definitions.

The basic format of all invoices is the same.  There’s a header, a footer, and a body which contains all of the “real” information that customers are interested in (and in customizing).  So, my plan is to build a main SSRS report that would have the header & footer information and a placeholder for the body of the invoice.  The body would be a separate subreport.  This subreport is the part we’ll be interested in for the rest of this article.  The basic idea is replace this subreport with customizations per customer.

I decided to approach this in two different phases.  First, I will get the basic workings of this new approach in place by building a framework that allows the app to create SSRS reports to be exported as PDF files and put the tools in place to allow the subreport to be easily replaced with a customized version per customer.  In this first phase, I decided to create several static subreports (.rdlc files stored as embedded resources) using the Visual Studio reporting designer that would just replace the formats that were currently available via the PDF API code.  This would allow me to focus on the SSRS code without having to worry about any new UI pieces.  Depending on the customer’s current invoice style selection, I will dynamically replace the subreport placeholder with the appropriate subreport.  Completion of this phase should also help me determine if there are any limitations in SSRS local reporting that would prevent this proposal from working as planned.  This is where I am today and what I’ll be detailing in the rest of this article.

After the first phase is completed and working as planned.  The second phase of the project will be to replace the embedded resource subreports with user designed subreport layouts.  This phase should be rather easy since SSRS (.rdlc) files are really just XML files.  The most difficult part of this phase will be getting the UI designer right.  It’ll be more tedious than difficult and I’ll document it at some later date when its complete.

IMPLEMENTATION

This should be very easy, right?  It all sounds very simple.  Create a report definition in the designer and drop in a subreport and then at runtime figure out which subreport should be there and modify some subreport name property and there you go.  Right?  Wrong.  Actually, its not much more difficult than that once you finish this article and benefit from my week of research and experimentation and a call to Microsoft tech support (which eventually led to them telling me it wasn’t possible…).

So, I started by creating my report definitions and getting them all tied into the subreport selection logic and data sources.  This all compiled and executed properly and it seemed that I was done.  Until I noticed that the subreports weren’t actually being replaced and all I was actually getting was the subreport that compiled into the main report definition.  Well, this wasn’t going to work.  I was basically doing something like the following.

public virtual void RenderReport(SsrsReportTypes reportType)
{
    LocalReport report = new LocalReport();
    report.ReportEmbeddedResource = "DynamicSubreporting.Reports.DefaultInvoice.rdlc";
    report.LoadSubreportDefinition("InvoiceBody2", GetSubreportStream());
    report.Render(...);
}

It seemed that no matter what I did I could not get SSRS to recognize the new subreport definition provided it by LoadSubreportDefinition().  Originally, I thought the problem must have to do with the way I was binding the subreport or the way that the subreport was identified by the LocalReport object.  I tried everything I could think of to make this work.  I desperately needed it to work for this project to be successful.

An alternate solution to this problem that I experimented with and got working was to dynamically write out the main report definition and replace the subreport name in the XML prior to writing out the file.  This solution had several limitations that I didn’t want to have to compromise on.  Mainly, it was going to be much messier to utilize this in phase 2 of the project and would probably lead to some multithreading and performance issues.

I read every article on the net that I could find regarding the LocalReport object and it appeared that no one had ever tried doing exactly what I was doing before and documented it or perhaps they just didn’t have problems…  Regardless, this seemed like a good reason to document it and here we are…

So, after reading everthing I could find and trying everything I could think of I finally resorted to creating a Microsoft support ticket and eventually got in contact with a Sr. SQL Server support engineer.  I described the problem and even created a stand-alone project that could be run without the rest of the app & database that he could use to debug my solution (you can download it here).  After a couple days of sending emails back & forth and him talking to his colleagues, they finally came to the conclusion that what I wanted to do just wasn’t possible without using some sort of work-around hack and he sent me a snippet of text from the LocalReport object documentation

The ReportViewer control requires the definitions for all subreports before it can process a report. If the local report was loaded from the file system by specifying the ReportPath property, the ReportViewer control automatically loads the subreports from the file system. In cases where the local report was not loaded from the file system, these methods may be used to load subreport definitions.

This statement turned on a light bulb.  Up until this point, I’d always thought the problem had something to do with LoadSubreportDefinition().  Now I was thinking perhaps the problem was actually in the ReportEmbeddedResource property.  Although this property wasn’t mentioned in this quote of the documentation I thought maybe since I was pulling the embedded resource version of the report that it was also automatically pulling the subreport definitions at that time and I had lost my opportunity to override the subreport.  That’s when I remembered seeing a function called LoadReportDefinition().  Suddenly, it made sense that maybe this function and LoadSubreportDefinition had to be used together.  Sure enough, by replacing ReportEmbeddedResource with a call to LoadReportDefinition() all of my problems were solved.

public virtual void RenderReport(SsrsReportTypes reportType)
{
    LocalReport report = new LocalReport();
    report.LoadReportDefinition(GetReportStream("DynamicSubreporting.Reports.DefaultInvoice.rdlc"));
    report.LoadSubreportDefinition("InvoiceBody2", GetSubreportStream());
    ...
    report.Render(...);
}

In hindsight, this seems like such a simple problem but it ended up taking a lot of time to get working and since there seems to be little information on solutions using the LocalReport object on the web, I thought that this article seemed worthwhile.  Hopefully, someone will gain something from it. 

The complete demo project can be downloaded here.  There’s a lot more to making this work that I didn’t discuss that you can easily decipher from the code.  This project was created by simply stripping out the bare minimums needed to demonstrate this one piece of functionality.  Nothing else about it should be considered an example of model coding practices.

CONCLUSION

The SSRS LocalReporting object can be used to create a truly dynamic reporting framework that requires a minimal amount of coding yet provides a great amount of power and functionality.  If you find that you are regularly making code “enhancements” in order to provide custom-specific reporting, this approach should be examined to see if it can help you make a real enhancement that provides real ROI.