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
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.
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.)
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.
All of this works just as in “traditional” reports. In the main invoice report this parameter is assigned the value of the invoice number
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.
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.