The Winforms ReportViewer in Visual Studio Pro will allow you define client reports with a variety of data sources. If you’re not familiar with creating client-side reports using the ReportViewer, take a look at these videos:
(The ReportViewer can also display server-based reports a la SQL Reporting Services and has a whole boatload of features that I’m not going to talk about here. For more information on this freely redistributable control, please read ReportViewer Controls (Visual Studio) in the MSDN library.)
ADO.NET Data Services was released with Visual Studio 2008 SP1 and is basically a framework for exposing your data models via RESTful web services. So if you are building a remote CRUD data access layer then this is a technology that will make that super-simple, especially if you just want to expose a few data entities as read-only report sources. (I’ve written a lot about how to use ADO.NET Data Services in a variety of ways so check out these posts if you’re interested.) Entity Framework was released at the same time and you can use EF as the data model behind your ADO.NET Data Service and expose it easily. If you’ve never created one before, check out this quick start here.
What’s the Problem?
If you’ve ever tried to use the ReportViewer to design a client-side report with an ADO.NET Data Service data source (or use an Entity Data Model directly) you may be disappointed. If you try to design a new report, the wizard may crash Visual Studio when selecting an entity. Yikes!
Let me show you what I mean, but don’t try this at home. I have an ADO.NET Data Service based on the Northwind database like the one I created here and in the quick start. I then added a Windows Forms client project to the same solution (File –> Add –> New Project, then select Windows Forms App). Now open Form1 in the client and from the toolbox under the Reporting tab, drop the Microsoft ReportViewer control onto the form and from the smart tag select “Design a New Report”.
This will open the Report Wizard. The first thing I need to do is tell it what data source to use, so I’ll select Service and click Next. This will open the Add Service Reference dialog box where I can add the reference to my data service and Visual Studio will generate the proxy objects for me. In this example my service is in the same solution as the client so I can just click the Discover button and Visual Studio will see it. I named it NorthwindService. Click OK then click Finish.
At this point I’m brought back to the Report Wizard:
Hmmmm…. I’m a little lost at this point because — didn’t I just select the data source? Why don’t I see it?
Maybe I should have added the service reference first from the Solution Explorer instead of through the Report Wizard? You can do that and you’ll get to the same screen at this point.
So okay, I’ll click the Add Data Source button that’s calling to me. Granted, I like clicking buttons that have the ellipsis (…) on them because I’m always curious to see what’s under them, but this seems redundant.
This opens the same dialog as before but this time I’ll choose Object instead of Service. I’m choosing object this time because I’m going to try and bind to the client proxy types that were generated for me when I added the service reference. After selecting Object, click Next and expand the service namespace.
For this report I want to just display a list of Customers in the system so I’ll select Customers and then click Next.
Then… then…… are you ready?… you better sit down for this…. click Finish……. wait for it…. wait for it….. not responding…. uh ohhhh…..
Visual Studio has encountered a problem and needs to close. Darn!
Now that was fun.
How Do I Fix this?
Unfortunately the Report Wizard in Visual Studio 2008 doesn’t know how to work with many-to-many relationships and that’s exactly what we have defined in our EF Entity Data Model which is what is backing our ADO.NET Data Service. Customers have many CustomerDemographics and vice versa in Northwind. You can actually define a couple classes yourself manually and create a many-to-many association between them:
Public Class Class1 Private _class2 As List(Of Class2) Public ReadOnly Property Class2() As List(Of Class2) Get Return _class2 End Get End Property End Class Public Class Class2 Private _class1 As List(Of Class1) Public ReadOnly Property Class1() As List(Of Class1) Get Return _class1 End Get End Property End Class
Try to use either class above as the data source of the report and it will crash too. Woah! So when the report designer tries to read these associations I’m guessing it gets into an infinite loop walking the association references back and forth and then it crashes. So it’s not an error with the generated proxy objects per se, the root cause is our object model. If I would have chosen Categories instead, it would have worked.
So what do we do? We could create another layer of classes without the associations on both sides if we wanted and then use those types instead but then we’d have to write code that IMO is pretty useless, just to shape the data again how we want on the client.
My recommendation is to create a separate data model for your reports that doesn’t have many-to-many associations at all. You’re going to end up with a lot of different looking entities anyways for your reports because they will most likely pull from multiple database Views and/or stored procedures. You’ll always be displaying one side of the many-to-many relationship on paper anyways.
Creating a Reporting Data Model
So let’s go back to the data service project and add a new Entity Data Model to the web project called NorthwindReport. This time I’ll just select the Customers, Orders and Order Details and all the Views defined in the database.
Next I’ll add the ADO.NET Data Service and call it NorthwindReport and configure it so that we’re only allowing read-only access to our data by setting the entity access rule like so:
Imports System.Data.Services Imports System.Linq Imports System.ServiceModel.Web Public Class NorthwindReport Inherits DataService(Of NorthwindReportEntities) Public Shared Sub InitializeService(ByVal config As IDataServiceConfiguration) config.SetEntitySetAccessRule("*", EntitySetRights.AllRead) End Sub End Class
Now back on the client we can add the service reference to this service instead. After we add the service reference we can run the Report Wizard again by selecting “Design a New Report” smart tag on the ReportViewer control like I showed in the beginning. Select Object as the data source (or Service if you didn’t add the service reference first, then you’ll have to click “Add Data Sources…” again and then select Object) and you should see the list of entities exposed in the NorthwindReport data model. Now when we select Customer, we don’t have a problem.
Design the report how you like (see the documentation for more details) by clicking through the wizard and then go back to the smart tag on the ReportViewer and you should see the report you just created in the dropdown. Next go to the code-behind for the form and in the Load handler we just set the BindingSource.DataSource property to the results of the ADO.NET Data Service LINQ query:
Imports NorthwindClient.NorthwindReportService Public Class Form1 Private Sub Form1_Load() Handles MyBase.Load 'TODO: put in My.Settings Dim uri As New Uri("http://localhost:1933/NorthwindReport.svc/") 'Create the service reference Dim db As New NorthwindReportEntities(uri) 'Set the report's DataSource to the results of the query Me.BindingSource.DataSource = From c In db.Customers _ Where c.Country = "USA" _ Order By c.CompanyName Me.ReportViewer1.RefreshReport() End Sub End Class
Recap & Resources
ADO.NET Data Services are a great way to quickly and easily expose data on the web, especially if it’s just read-only data used for remote reporting clients. The key to using the ReportViewer with ADO.NET Data Services (or an Entity Data Model directly, or even your own object model) is to make sure there are no many-to-many associations on the entities. Here are the resources you need to get started with ReportViewer and ADO.NET Data Services:
- Data Service Quick Start (ADO.NET Data Services Framework)
- How to: Add, Update, or Remove an ADO.NET Service Reference
- Configuring ReportViewer for Local Processing
- Report Designer (Visual Studio)