ThoughtWorks projects are built using best practices that include layered architectures, at the heart of which is often a domain model. The model contains concepts and business logic to support the application. The model is often persisted using an object relational mapping scheme with either custom code or a mapping tool such as Hibernate. Our domain model is used to drive screens and processes within the application. But when it comes time to writing those pesky reports that the business desperately needs, we tend to revert back to plain old SQL stored procedures. This article shows how we can leverage a .NET C# domain model to create traditional Reporting Services reports.

The Bad Old Days

Microsoft Reporting Services is a standard part of the .NET stack. When faced with a reporting requirement on a .NET project, we’d need to do a lot of explaining to choose a different reporting tool, and we’d be taking some chances. For this article we’ll assume the political or technical environment doesn’t allow us to choose another tool or to avoid reports entirely. It is usually still worth asking the business whether an interactive screen will serve their needs instead of a report—on a current project in Calgary the business champion understands that a screen is faster to develop and easier to test than a Reporting Services report. He will often choose a screen instead of a report where functionality such as PDF export or printability is not required.

The Reporting Services authoring tool is designed around SQL or stored procedure development. The default data source is an SQL data source, and the tool works well alongside a SQL editing environment where we can develop our queries. Reporting Services uses SQL based data sources for report parameters too. This leads many of us to conclude that the right way to use Reporting Services is to develop custom SQL for report parameters and data sets. SQL cannot leverage our domain model, so we end up repeating many business rules and concepts within our reporting SQL. Since SQL or even T-SQL is less expressive than C# code, and since most programmers are less adept at database programming, our reporting SQL gets unwieldy, complicated, and bug ridden. Maybe we add some views or functions to attempt to clarify things. Then we tune our SQL so the reports perform well. The result is often a really nasty section of our application that no-one wants to work on, and that can often contain bugs. Reports are usually important for running the business, so the numbers must be right.

Web Services to the Rescue!

A little-advertised feature within Reporting Services is its ability to consume Web Services as a data source. One reason this feature is poorly advertised might be Microsoft’s lack of expertise with applications that have a real domain model—most of their examples get as far as Data Access Objects but no further. Another reason is that the tool support for using web services isn’t as good as the support for SQL and stored procedures. It’s still quite usable, however.

The basic idea is to expose our existing domain model using web services, with methods specifically tailored to our reports. The SOAP response from a web service call is an XML document and is “flattened” into a dataset by reporting services. We can tune the way the flattening works to avoid extraneous elements (for example, we’re probably not interested in the SOAP envelope). Once we have the dataset we can write a report against it in the usual way.

A Web Services Driven Report

When creating a report you first need to define an XML data source. For the connection string enter the path to your web service.

Now, add a new data set for the data source. The command type should be “text” and the query string an XML fragment including the method you wish to invoke on the web service. You should include parameters in your XML query for each parameter the web service method expects. In this example we have four parameters. We mapped the query parameters to report parameters using the “parameters” tab.

Beware that whilst you’re designing your report and setting up your data source, Reporting Services will often call your web service method using null for each parameter. Code your web service so that it checks for null and returns an empty response (empty list, zero response, etc) for one of these invalid calls, otherwise you’ll find the designer loses your query/parameter settings. This can be really annoying.

In our example we’re using ElementPath to collapse the web service XML response into a data set. The syntax is fairly arcane but you can try starting with a * as your element path which does a default conversion. You can read more about the syntax at Microsoft TechNet. We seem to need the Response {} and Result {} at the start of the path, even though those elements don’t actually occur in the web service SOAP response. In this example, we’re also forcing various fields to Reporting Services data types such as Date, Boolean and Decimal, rather than treating them as simple strings.

You should now be able to exercise your data source and see results come back. Now you can build the rest of your report in the designer as usual.

Performance Considerations

One common reason for using SQL or stored procedures for reporting is a worry about performance. Traditionally, a domain object approach is slower than using “raw” SQL. In fact this is always likely to be true—getting the database to do all the work will always be faster than loading objects into memory and processing them. But how fast do we really need a report to perform? And can we truly write database procedures that duplicate our business logic and get them to perform well?

Our experience in Calgary has shown that a report that took over two weeks to develop using stored procedures could be built in an afternoon using our domain model. Tuning the original report took considerable time, but we got to the point where we could generate an 850 page report in under 20 seconds. Initially the domain model version of the report took minutes to run, but with a small amount of tuning (only a few hours) we had it running in just ten seconds—twice as fast as the SQL based report. As an added benefit, the performance tuning we did on the domain model helps the entire application run faster. Tuning a domain model isn’t that hard; simply avoid loading too many objects into memory. Instead of loading 30,000 transaction entries and then processing them, we get the database to aggregate them by transaction type and date, something that we’d need to do in the domain model before displaying the results anyhow.

Reporting Services 2005 doesn’t allow you to selectively disable export formats for your reports. You can disable export formats for the whole server, but this is a bit useless in a shared environment. For my current client, we wanted to disable XML, Tiff and “web archive” export on all of our reports, but not affect any other reporting applications deployed on the server.

Our reports consist of custom parameter pages followed by a report view page onto which we’ve dropped a ReportViewer control. When the page loads, we programatically set the ReportViewer’s server URL, report path, and parameters. We’re doing this to ensure users are requesting reports for data that they’re allowed to see and not tricking us into revealing other users’ data.

Whilst there’s no official way to disable export formats on an individual report, a little bit of exploration using Reflector reveals that the ReportViewer contains a ServerReport that acts as a proxy between the control and the Reporting Services web service. The ServerReport allows you to list rendering extensions—these guys do the hard work of actually exporting in each format—but it doesn’t allow you to set the visibility of an extension. Reflection to the rescue!

The following code sample turns off XML, Tiff, and “web archive” export for a particular ReportViewer or ServerReport:

using System.Reflection;
using Microsoft.Reporting.WebForms;
using Microsoft.SqlServer.ReportingServices2005.Execution;

namespace MyProject
{
    public class ServerReportDecorator
    {
        private readonly ServerReport serverReport;

        public ServerReportDecorator(ReportViewer reportViewer)
        {
            this.serverReport = reportViewer.ServerReport;
        }

        public ServerReportDecorator(ServerReport serverReport)
        {
            this.serverReport = serverReport;
        }

        public void DisableUnwantedExportFormats()
        {
            foreach(RenderingExtension extension in serverReport.ListRenderingExtensions())
            {
                if(extension.Name == "XML" || extension.Name == "IMAGE"
		                           || extension.Name == "MHTML")
                    ReflectivelySetVisibilityFalse(extension);
            }
        }

        private void ReflectivelySetVisibilityFalse(RenderingExtension extension)
        {
            FieldInfo info = extension.GetType().GetField("m_serverExtension",
                                                          BindingFlags.NonPublic
							| BindingFlags.Instance);
            if (info != null)
            {
                Extension rsExtension = info.GetValue(extension) as Extension;
                if(rsExtension != null)
                {
                    rsExtension.Visible = false;
                }
            }
        }
    }
}

I really hope Microsoft includes this functionality in the next release of Reporting Services. It would be really quite simple to include it in the graphical report designer and avoid this nasty reflection hack entirely. At the very least, the sealed RenderingExtension class could have a mutable “Visible” property, rather than the read-only property it has now.

Most people are familiar with the Model, View, Controller pattern (MVC) for separating business logic and presentational logic within an application. MVC is implemented in a number of Java web frameworks, such as Struts and Spring. A more recent pattern–Model, View, Presenter–can be applied in contexts where there is no central “controller” for the application. One such framework is ASP.NET.

My team recently built an MVP-based application on ASP.NET 2.0 and had great success with highly testable presenters and a highly adaptable presentation layer. In fact, we chose to switch from creating custom web controls to using simple .aspx pages and didn’t have to change our presenters at all–it’s always nice to validate those previously abstrct design decisions!

The MVP pattern separates three elements. The model is one or more domain-specific objects representing the current state of the system, the information we’re trying to display, etc. The view is how we present that information to the user and handle input, usually a particular screen or web page. The presenter is the logic that ties together the model and the view, handles navigation, business logic requests, and model updates.

For MVP in ASP.NET 2.0, we use an .aspx page–or more precisely, the code behind partial class–as the view, custom domain objects as the model, and a Plain Old C# Object (can I steal the term POCO?) as the presenter. Let’s assume we’re creating a page to list customers in our application. Our ListCustomers.aspx page might look like this:

<asp:Content ContentPlaceHolderID="Main" Runat="Server">
    <h1>Customer List</h1>
    <asp:GridView ID="customerGridView" AutoGenerateColumns="false" runat="server" SkinID="CustomerGrid">
        <Columns>
            <asp:BoundField HeaderText="Customer" DataField="Name" />
        </Columns>
    </asp:GridView>
</asp:Content>

Nothing special here, we’ve just defined a GridView which will list the customers and display a title. What’s interesting is the code-behind:

public partial class ListCustomers : System.Web.UI.Page, IListCustomersView
{
    protected void Page_Load(object sender, EventArgs e)
    {
        ICustomerService customerService = ServiceRegistry.GetService();
        ListCustomersPresenter presenter = new ListCustomersPresenter(this, customerService);
        presenter.PageLoading();
    }

    public List<Customer> Customers
    {
        set
        {
            customerGridView.DataSource = value;
            customerGridView.DataBind();
        }
    }
}

The code-behind partial class implements IListCustomersView, which we’ll see in a moment. When the page is loaded we create a new ListCustomersPresenter, passing it the view (this) and anything else it requires (in this case, an ICustomerService). We then call the presenter’s PageLoading() method. What’s happening is that the code-behind is making no decisions about what to display on the page, it simply delegates to the presenter for any non-display-related business logic.

The IListCustomersView interface defines how the presenter can interact with the ASP page. There’s just one settable property, Customers:

public interface IListCustomersView
{
    List<Customer> Customers { set; }
}

Looking back up at the code-behind, you can see that the implementation of the Customers property sets the grid view’s datasource to the list of customers and then calls DataBind() to populate the grid.

Let’s take a look at the final piece of the puzzle, the presenter:

public class ListCustomersPresenter
{
    private readonly IListCustomersView view;
    private readonly ICustomerService customerService;

    public ListCustomersPresenter(IListCustomersView view, ICustomerService customerService)
    {
        this.view = view;
        this.customerService = customerService;
    }

    public void PageLoad()
    {
        List<Customer> customers = customerService.GetAllCustomers();
        view.Customers = customers;
    }
}

When our presenter is constructed the two things it depends on, the view and the customer service, are passed to it (in this case, by the code-behind). This is known as constructor dependency injection. In the PageLoad() method the presenter simply accesses the customer service to load customers and sets this information on the view. This simple example can be extended to include input (the view can have read-only properties that correspond to text boxes, etc) and action (add a button to your web page and in the code-behind for its click call an action method on the presenter, like AddCustomerClicked()).

So why is this useful? Why not just have the code-behind access the CustomerService to load the customers? One of the main benefits is that the difficult business logic is captured in the presenter and can be more easily tested. Because we’re using dependency injection we can instantiate the presenter in an NUnit test, mock-out the view and service, and check the presenter does the right thing. The “load customers” example isn’t very hard but you can imagine logic that needed to take a set of user input and perform something more complex, such as placing an order. A second benefit is it’s very easy to see how the presenter can interact with the view–it can only use methods and properties on the IListCustomersView interface, which means it’s much easier for us to see the logical interface between the UI and the business layer. Finally, it’s possible to test drive your presenters and views, which tends to lead to simpler, more modular design for the system.

I’ve simplified some of the other stuff you’ll need to do in a real application. Screen flow and input validation are good examples. We solved the flow issue by having presenters able to return a “presenter result” object, indicating whether the ASP.NET framework should redirect to another page, stay on the current page, go to a login screen, etc. With input validation and error conditions, we add properties on the view so that the presenter can instruct the view to show a particular error message. You can still use an ASP.NET validator for client-side validation, but the presenter needs to be able to toggle it visible too.

We started our application using MVP, implementing the view using custom controls coded in C#. We did this mostly because we envisaged our application as a series of reusable controls, some of which (for example a Wiki control) should be embeddable in other applications. Ultimately we went a bit too far with this and coded some simple screenflow, which wasn’t really reusable, with the same C# controls. We found that layout and other tweaks became fairly onerous and decided to switch to implementing the view using traditional .aspx pages and code-behind. We found that our presenters required no changes at all to be able to accomodate the new view–excellent validation of the MVP design pattern.