For many years the mantra for implementing business logic in your line of business application has been: “don’t put it in the database, don’t put it in the user interface”. In other words, apply the layers design pattern if at all possible, together with implementing the Model-View-ViewModel (MVVM) or Model-View-Controller (MVC) pattern. Technologies like Entity Framework help us convert data in the database to .NET objects and add logic. Life is good.
Then it is time to create a report. Traditionally reports are run against the database and any self respecting reporting technology will to this day still offer you the option of building a report by querying directly against the database. Out the door goes the reuse of your .NET based business logic, right? No need to fear, Visual Studio offers a solution. Starting with Visual Studio 2005 Microsoft started shipping the ReportViewerControl with Visual Studio. Where SQL Server Reporting Services is full fledged reporting solution, with it’s own server, scheduling engine, user interface, the ReportViewerControl is only a small part of the food chain. The ReportViewerControl will render a report defined by an RDLC file against the data you feed into it. The data can still come from a database, but also from a WCF Service, any .NET object or SharePoint.
Let’s look at a sample. The sample will work on the AdventureWorks2008R2 database which can be downloaded from CodePlex. I’ve then created two views: CustomerView and OrderView. These views limit the data to Massachusetts and join a couple of table to make for more demo-friendly data.
The following script will add the two views that we’ll be using:
USE [AdventureWorks2008R2]
GO
/****** Object: View [dbo].[CustomerView] Script Date: 01/21/2011 05:53:18 ******/
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[CustomerView]'))
DROP VIEW [dbo].[CustomerView]
GO
/****** Object: View [dbo].[CustomerView] Script Date: 01/21/2011 05:53:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[CustomerView]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[CustomerView]
AS
SELECT DISTINCT Sales.Customer.AccountNumber, Person.Person.LastName, Person.Person.FirstName, Sales.Store.Name AS StoreName, Sales.Customer.CustomerID
FROM Sales.Customer INNER JOIN
Person.Person ON Sales.Customer.PersonID = Person.Person.BusinessEntityID INNER JOIN
Sales.Store ON Sales.Customer.StoreID = Sales.Store.BusinessEntityID INNER JOIN
dbo.OrderView ON Sales.Customer.CustomerID = dbo.OrderView.CustomerID
'
GO
/****** Object: View [dbo].[OrderView] Script Date: 01/21/2011 05:54:51 ******/
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[OrderView]'))
DROP VIEW [dbo].[OrderView]
GO
/****** Object: View [dbo].[OrderView] Script Date: 01/21/2011 05:54:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[OrderView]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[OrderView]
AS
SELECT Sales.SalesOrderHeader.SalesOrderID, Sales.SalesOrderHeader.CustomerID, Production.Product.Name AS ProductName, Sales.SalesOrderDetail.OrderQty,
Sales.SalesOrderDetail.UnitPrice, Sales.SalesOrderDetail.UnitPriceDiscount, Sales.SalesOrderDetail.LineTotal, Person.Address.AddressLine1,
Person.Address.AddressLine2, Person.Address.City, Person.Address.PostalCode, Person.StateProvince.StateProvinceCode, Person.Address.SpatialLocation
FROM Person.StateProvince INNER JOIN
Person.Address ON Person.StateProvince.StateProvinceID = Person.Address.StateProvinceID AND
Person.StateProvince.StateProvinceID = Person.Address.StateProvinceID AND Person.StateProvince.StateProvinceID = Person.Address.StateProvinceID INNER JOIN
Sales.SalesOrderDetail INNER JOIN
Sales.SalesOrderHeader ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID INNER JOIN
Production.Product ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID ON
Person.Address.AddressID = Sales.SalesOrderHeader.ShipToAddressID
WHERE (Person.StateProvince.StateProvinceCode = N''MA'')
'
GO
Next step is to create a WCF service application, add an Entity Framework model and drag the two views onto the model:
Next we’ll implement two methods to use the Entity Framework model to select the data and return a list of CustomerView or OrderView objects.
Note: Normally you would not select all the contents in a view, but since we know that the number of rows in our views are already limited in numbers there is no problem here.
1: using System;
2: using System.Collections.Generic;
3: using System.Linq;
4: using System.Runtime.Serialization;
5: using System.ServiceModel;
6: using System.ServiceModel.Web;
7: using System.Text;
8:
9: namespace AdventureServices
10: {
11: public class AdventureService : IAdventureService
12: {
13:
14: #region IAdventureService Members
15:
16: public List <CustomerView > GetReportCustomerData()
17: {
18: using ( AdventureEntities db = new AdventureEntities () )
19: {
20: var query = from customer in db.CustomerViews select customer;
21: return query.ToList();
22: }
23: }
24:
25: public List <OrderView > GetReportOrderData()
26: {
27: using ( AdventureEntities db = new AdventureEntities () )
28: {
29: var query = from customer in db.OrderViews select customer;
30: return query.ToList();
31: }
32: }
33:
34: #endregion
35: }
36: }
37:
38:
Next step is to create a report client. We can use any Windows or ASP.NET application and add start using the ReportViewerControl, but Visual Studio also offers a report application template. Very useful for quick demos:
Create the project (skip the wizard), then delete the Report1.rdlc. Add service reference to you AdventureServices and then add a new report using the Report Wizard (on my machine I’ve had poor luck adding the service reference as part of the wizard steps.
On the first screen of the wizard give the dataset a name (CustomerDataSet), select the service reference as a datasource and pick CustomerView as the available dataset.
On the next screen drag the fields we want to display to the ‘Value’ grid. More complex grouping per row and column is also possible.
Since we’re doing a very basic report the next screen offers no selectable options, although we’re starting to see part of our report.
Next we pick a style. There are a couple to choose from.
We click finish to close the wizard. Our report looks like this:
Use the designer and the tool box to enhance the report just a little:
Now the next step is to make sure our form will display this report. Go to the Form1 designer, select the ReportViewerControl and look for the smart tag in the top right hand corner of the control.
Activate the smart tag and you’ll see that you have to option to select a report. Select the report you’ve just created:
Notice how at the bottom of the forms designer there now is a design time control:
The design time binding source allows us to feed data into the report. So far the ‘links’ that we created to the service have only been used to pull in the schema of the data to be used. The actual data needs to be fed into the report when the form is run. For this we implement a call to our AdventureService:
1: using System;
2: using System.Collections.Generic;
3: using System.ComponentModel;
4: using System.Data;
5: using System.Drawing;
6: using System.Text;
7: using System.Windows.Forms;
8:
9: namespace AdventureReports
10: {
11: public partial class Form1 : Form
12: {
13: public Form1()
14: {
15: InitializeComponent();
16: }
17:
18: private void Form1_Load(object sender, EventArgs e)
19: {
20: using ( AdventureServiceReference.AdventureServiceClient client = new AdventureServiceReference.AdventureServiceClient () )
21: {
22: this .CustomerViewBindingSource.DataSource = client.GetReportCustomerData();
23: }
24: this .reportViewer1.RefreshReport();
25: }
26: }
27: }
28:
Note: Even though our service does not take any parameters to filter the data I hope you can see that it would only take a small amount of coding to add a couple of fields to the form and pass any kind of selection to the service. I leave the actual implementation of that up to you, when you’re building your ‘real’ report.