Sponsored By Aspose - File Format APIs for .NET

Aspose are the market leader of .NET APIs for file business formats – natively work with DOCX, XLSX, PPT, PDF, MSG, MPP, images formats and many more!

Reporting against a domain model

I have a love it /hate it relationship with reports. In many an application the reports are the main output. They are the prints which get filed or the invoices which have to ensure the ROI of the application. A good looking output rich with information does increase customer satisfaction. The downside is that fine tuning a report is tedious and requires quite different skills than coding. Building a system with a lot of reports can be pretty boring. So the tools better be good. Switching from Crystal Reports to sql server  reporting services (RS) was quite a relief.

But  RS still has two drawbacks. First is that it accesses data using plain sql. My application is built around a domain model using nHibernate to hide all db details. The domain model includes calculated properties which are to be included in the report. The calculations are done in the domain object. It would be repetitive and prone to error to repeat them in sql or reporting expressions. It would be quite a relief to report straight from the domain model itself.

The other drawback is that RS requires an ms sql server instance with installed reporting services. The actual data is just a sqlexpress instance and I don’t want to set up a full blown reporting services server.

New in Visual Studio 2005 were client side reports (rdlc’s). They are very much like RS reports (rdl’s) but can work with any data source and don’t require a sql reporting server instance. To include these reports in an app two client side report viewers are include in the .NET framework, one for WinForms and one for asp.net. This looked like the best of both worlds so I decided to give the winforms version a try.

For a good overview on the reports check the got reportviewer site. In name it supports VS 2008, but (some of ?) the samples are based on VS 2005. Moving to 2008 there has been a change in the namespace for the classes, after fixing the reference the samples run in VS 2008. How to work with a non sql datasource is covered but not that clear. Here I will describe my minimalistic approach to work with a domain model fed by nHibernate.

The domain model

The intent of the application is to print invoices. This class diagram describes the model

Rep1

 

An invoice is for a contact, has a number of lines and an enumerated property. That is a number of values which contain a tax rate and have a clear name describing this value. Which gives the model additional value, something which would get lost when viewing the data as raw db data. An invoice has a reference to a contact and many Invoice lines. The (fluent) nHibernate mappings express this.

public class InvoiceMap : ClassMap<Invoice>

{

    public InvoiceMap()

    {

        WithTable(“Factuur”);

        Id(x => x.Id);

        Map(x => x.Number).ColumnName(“Nummer”);

        Map(x => x.OnDate).ColumnName(“Dedato”);

        Map(x => x.BtwPercentage).CustomTypeIs<BtwPercentage>();

        Map(x => x.Description).ColumnName(“Inleiding”);

        HasMany(x => x.Lines).KeyColumnNames.Add(“idFactuur”).Cascade.All();

        References(x => x.ForContact).ColumnName(“idRelatie”);

        Map(x => x.PrintIt).ColumnName(“Printen”);

    }

}

So we have a customtype, a hasmany and a references. Let’s see how these are used in the report.

The report

There are several ways to include a report in an application. The default when adding  a new report (Add –> New Item –> Reporting –> report) is embedded. Which is clear and easy in deployment. When you need more flexibility you can switch to rdlc file or (back to) server side report.

The report needs a datasource. It will accept any datasource including an object datasource.

Add a new object datasource to the project and pick the Invoice domain object.

Rep2

Just pick your POCO domain class. There is no need to set up any methods to get data, it is enough to have a model of the data.

This object data source is the datasource for your report. Having done so the domain invoice is available in the report designer. (To be precise the datasource has to be attached to a List, see the reportviewer docs for more information on that.)

 

Rep3

 

A report is built from VB-like expressions. A simple one looks  like this:

=Fields!Description.Value

Which displays the plain content of a property. But expressions can be more complicated. The report designer has a good expression builder which even includes intellisense. The next expression uses both the meaningful name and the value of my tax enumeration property in a string

=“BTW “ & Fields!BtwPercentage.Value.ToString & ” “  & Fields!BtwPercentage.Value & “%”

The result is a string like “BTW Hoog 19%”.

Things get really interesting when displaying the contact information. In the domain model this expressed as

invoice.ForContact.AddressLine1

In the report the expression is quite similar

=Fields!ForContact.Value.AddressLine1

The subreport

The lines in the invoice are a one to many relation. The invoice datasource has a lines property but this property is not usable in the report. To display the lines I need a subreport. The subreport is based on invoice lines. After adding the InvoiceLine class as a datasource the subreport is drawn. It is a simple table displaying the lines.

A subreport is linked to its parent report using parameters. In my case an invoice and its lines are linked through a parameter named FactuurNummer.

Rep4

All of this works just as in “traditional” reports. In the main invoice report this parameter is assigned the value of the invoice number

Rep5

Check the gotreportviewer site for further information.

 

 

Binding the data to the report

Now the reports are ready. The next step is to get some real life data into them.

It takes code to bind real data to a report and display the result in the viewer. Also the subreport has to be fed with data. To wrap things up I have built a helper class which takes a reportviewer component and a list of invoices and wires them up.

internal class InvoiceReporter

{

    internal InvoiceReporter(ReportViewer viewer, IList<Invoice> reportData)

    {

        _reportData = reportData;

        _viewer = viewer;

    }

 

    private readonly IList<Invoice> _reportData;

    private readonly ReportViewer _viewer;

 

    internal void ShowReport()

    {

        _viewer.LocalReport.ReportEmbeddedResource = “Gekko.Administratie.FactuurPrinten.Invoice.rdlc”;

        _viewer.LocalReport.DataSources.Add(new ReportDataSource(“Gekko_Administratie_DomainModel_Invoice”, _reportData));

        _viewer.LocalReport.SubreportProcessing += SubreportProcessing;

        _viewer.RefreshReport();

    }

 

    private void SubreportProcessing(object sender, SubreportProcessingEventArgs e)

    {

        var invoiceNumber = e.Parameters[“FactuurNummer”].Values[0];

        var invoice = _reportData.First(fakt => fakt.Number == invoiceNumber);

        e.DataSources.Add(new ReportDataSource(“Gekko_Administratie_DomainModel_InvoiceLine”, invoice.Lines));

    }

}

The code requires some explanation. The constructor should be clear, it receives viewer and data.

The ShowReport method does the real work. The first line loads the report in the viewer. The second line loads the report data. This line is tricky. The name of the ReportDataSource has to match exactly the name of the datasource class used as model. The Invoice class name is Gekko.Administratie.DomainModel.Invoice which gets formatted as Gekko_Administratie_DomainModel_Invoice. This required magic string is not clear from any documentation, it took me quite some time to find out how essential it was.

All these datasource properties in the report definition are confusing. The model of the data is saved in the report definition. Changing the datasource  to another “dataset” will also lead to errors at run time. The report will complain about the former datasources. To get rid of the error messages you have to check the report defintion by hand. The report definition file (.rdlc) is plain xml. The last part contains dataset definitions. What helps is just deleting the unused nodes by hand.

Upon rendering a subreport an event is fired. In the third line of ShowReport a handler is attached. This SubReportProcessing method feeds the subreport with data. From the parameters the number of the invoice is read. Which is used to find the corresponding invoice. The datasource for the subreport is set just like the datasource of the main report. Again spelling the name of the reportdatasource right is essential. The data is the Lines property. Which is an IList of InvoiceLines.

Putting it all together

With all pieces in place it’s time to get some results. I have a windows form with a reportviewer on it.

var repo = new InvoiceRepository();

var reporter = new InvoiceReporter(reportViewer1, repo.ListPrintableInvoices());

reporter.ShowReport();

This reads the invoices from the repository and sends them with the reportviewer to the helper class.

There is one more thing to watch. The repository is an nHibernate repository. By default nHibernate lazy loads related objects, like the Contact and the Lines of the Invoice. The contact and the invoicelines will not be read from the database until they are actually used. This default setting has some serious drawbacks.

In the first place the reportviewer‘s datareader does not operate very well (at all ?) with nHibernate’s proxies. In the report all the contact fields will contain error messages. The subreport on the  lines will work. But still the lines are not read from the database until rendering the sub-report. So for this lazy loading to work the report has to be run against a dataset with an open dataconnection. That’s bad, I try do do as little as possible, and most likely not presenting a report, with an open connection. A far better scenario would be to fetch all data in one go, close the connection to the database and render the report after that. This can be done by eagerly loading the Contact and Lines properties and disposing of the session before returning the data.

In the nHibernate API the fetch mode can be set on a property level. The code in the repository takes care of fetching all data in one big snapshot.

public IList<Invoice> ListPrintableInvoices()

{

    using (var session = SessionFactory.GetFactory.OpenSession())

    {

        var query = session.CreateCriteria(typeof(Invoice));

        query.AddOrder(Order.Asc(“Number”));

        query.Add(Restrictions.Eq(“PrintIt”, true));

        query.SetFetchMode(“Lines”, FetchMode.Eager);

        query.SetFetchMode(“ForContact”, FetchMode.Eager);

        return query.List<Invoice>();

    }

}

 

The repository reads all data from the database and closes the connection. After that the collected data are input to the reportviewer. Which now works completely as hoped for.

 

Rep6

 

I have my report complete with printing and export functionality and am still working with my beloved domain objects.

This way reporting can almost be pure fun.

This entry was posted in Data. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • http://codebetter.com/members/pvanooijen/default.aspx pvanooijen

    OK, I think see your pain.

    As you describe it you have your domain model and a view model. In which the latter is just a view on the domain. Imho “exposing” the view model to the repository is/should be the same as including the view in your domain model.

    Or is that an oversimplification ?

  • Hendry Luk

    @pvanooijen
    The model is complete and able to support the UI. It’s just not necessarily efficient to do the calculation on the domain model when we deal with hundreds of data, plus deep lazy-load.
    E.g. the example that I gave.
    One of the very first screen in my CRM application is to search a customer, where one of the collumn in the grid result is “number-of-*active*-phone-subscription the customer has”.
    (FYI, the relation is customer->many accounts->many subscriptions. And the word ‘active’ has some kind of logic too.).

    Even worse if the user decides to order the result by that collumn.

    Early in development, it was easy. Just grab the customer from repository, then we ask for its active subscriptions and populate the count to ViewModel.
    Of course, total disaster.
    Now to avoid this situation, I am thinking to expose this ViewModel to reach out to repository, so the repository would be able to query these information in the “correct” way. Of course, this involves rewriting some of the code into SQL/HQL (e.g. to determine the definition of *active* account and *active* subscription, as well as how to calculate other collumns that are normally calculable by domains).

  • http://codebetter.com/members/pvanooijen/default.aspx pvanooijen

    @Sergio,
    coming up !
    @Hendry
    We agree there is a mismatch, but imho the mismatch should disappear. A domain model not describing main parts of the users view is incomplete and should be dealt with.

    When it comes to calculations SQL is good in things like SUM and AVG, you can steer nHiernate into that direction. When it comes to other kinds of calculations SQL is completely outperformed by compiled C#. But there is usually not much to be won there, that’s micro optimization.

    My main goal for reporting against the domain model and not the database model is to avoid duplications.

  • Hendry Luk

    @pvanooijen
    I totally agree, the model doesn’t fit the screen (it’s very clear that it will be an unacceptable performance-harm to do all those calculations on code for each row. They should take place in SQL).
    I’m already in the stage to deal with performance. My ViewModel (or ReportModel) should be projected straight from HQL (or SQL).. where does this fit in DDD patterns? Sould I be exposing this ViewModel to the whole layers and reach out to repository/data-access?
    How to minimize duplications? (i.e. rewriting the logic in HQL/SQL)

  • Sérgio Sarabando

    Great! I’ll be waiting for that post. There’s one more thing that I have to test before being able to make an informed decision: the ability to “create” the contents of an empty report programmatically. If I can pull that off, I think I’ll be able to swing management into dumping CR.

  • http://codebetter.com/members/pvanooijen/default.aspx pvanooijen

    @Sergio Great ! It took me a little time to find the custom code.
    What I would go for is loading your own assembly (instead of report script) in the report. There is a small example on the reportviewer site how to do that. The nice thing is that you can use real C# (od vb) code inside your report. I will delve into that in a new post. Might take some time, holdiays are coming up.

    @Hendry. Imho the domain model should de a model of the domain in a language spoken by the developper and the user of the application. In case your domain model doesn’t fit a report or a screen your model is incomplete/ does not represent the users view. It should be visible to all layers, it IS the core of your application.
    Performance is an issue which should and can be solved after that.

  • Hendry Luk

    Domain entity doesnt suit well in most of my reports. Things like “100 top orders of this months” with one of the columns being “total price”, sorted by that column (hence “total price” would also be the definition of top 100).
    Using conventional domain-entity would make it horrible in term of performance. And those scenario happens not only in reporting in normal sense, but also in usual order-search screen, or customer-search.
    I believe NH projections would make much better sense. This means that reporting or screen-specific model will reach (and live) straight into data-access (repository), since repositories know how to load them “correctly” (performance-wise).
    I don’t feel comfortable having ViewModel (or reporting-model) exposed to all core layers (domain, repositories, application layers).
    What’s your view about this?
    Ta

  • Sérgio Sarabando

    I’ve arrived at a way of doing it. First, if I put a Sum of ReportItems in the page header or footer, it’ll only sum the ReportItems of that page. Knowing that, I’ve created two functions in the Custom Code area of the report, one to store the sum per page in a Collection (index is the page number) and another to get a running total from that Collection. Then I invoke those functions in the header and/or footer and I get my “transport” field. Not as clean as in CR, but doable.

  • Sérgio Sarabando

    Thanks. We’ll dig in some more.

  • http://Http://PetersGekko.Codebetter.com Peter van OOijen

    @Sergio : There is a lot you can do with the expressions. It takes some experimenting to see where that stops. It is also possible to load your own assemblies into a report. Which makes the sky the limit. I havn’t tried that myself yet (no need yet). Of all the rporting tools I’ve seen these rdlc’s are amnongst the most powerfull.
    The problem with most docs is that they are pretty incomplete.

    @Mike Yes for an invoice it would make sense to save a reporting copy in a reporting DB. But a reporting DB is not allways an option. In one of my projects people update data and print a report to check if they did everything right. If no they correct things asap. SO those reports really have to be against the live production data.

  • Mike

    I would have thought that once the Invoice is finalised the domain could be captured to save the data off for reporting separately.

  • Sérgio Sarabando

    We are currently evaluating if we make or not the change to the client side reports that you mention (we use CR), but we’ve stumbled into a problem: in the same invoice scenario that you showed, we have to show a “transport” field that shows the running total at the bottom of each page and show it again in the top of the next one when there are more lines that can fit in a printed page.
    We’ve searched all over the docs and the ‘net but are unable to find a solution.
    Have you had the same problem?

  • http://codebetter.com/members/pvanooijen/default.aspx pvanooijen

    There is a reportviewer for asp.net. Havn’t tried that yet. It should accept the same report.

  • CWS

    Great post!

    Is this approach applicable on a Asp.Net app?

    Regards

  • Steve Sheldon

    SSRS can also use a Web Service as a source for data. I haven’t looked at 2008 yet to see if this capability has been further expanded, but it was there in 2005.

  • http://codebetter.com/members/pvanooijen/default.aspx pvanooijen

    @Jimmy, there are magic strings everywhere.

    The nice thing with these reports is that a lot is checked at compile time. Behind the scenes code is generated and the expression
    =Fields!OnDates.Value
    will result in a compile error, because the field OnDates does not exist. (It’s OnDate)

    Also the expression
    = “Faktuur ” + Fields!Number.Value +,+
    will result in compile error, because it has an invalid syntax.

    The other magic strings are in the nHibernate code. Building a query has a lot of magic. That won’t be checked till runtime.

  • http://jimmybogard.lostechies.com Jimmy Bogard

    One thing that always bothered me with reporting is the magic strings for binding to properties. I was able to create strongly-typed reports (i.e., expression-based) for Telerik:

    http://www.lostechies.com/blogs/jimmy_bogard/archive/2009/04/11/strongly-typed-telerik-reports.aspx

    Not sure if this is possible with Reporting Services reports, as Telerik uses a code-beside model for their report definitions.

  • http://blog.fossmo.net Fossmo

    Thanks for a great post. I’m soon going to do this in my project, so this was just perfect.