Using the ReportViewer with ADO.NET Data Services and Entity Framework

Using the ReportViewer with ADO.NET Data Services and Entity Framework

  • Comments 17

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”.

 image

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:

image

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.

image

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!

image

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.

image

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

Now hit F5 to run this baby and you’ll see the report pull the data from the service and display in the form:

image

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:

Enjoy!

Leave a Comment
  • Please add 7 and 2 and type the answer here:
  • Post
  • Excellent Post!

    My biggest problem though is that Linq doesn't support right/left outer joins well at all (PITA to build the queryies to the point if siilliness) and I use this all of the time in my sql for reports.

    Microsoft: Please make this a feature of .NET 4 so that we can do leftjoin and rightjoin instead of just join.

  • Hi James,

    You can actually achieve very similar results in LINQ for outer/left/right joins using the Group Join syntax.

    Take a look at this post for info:

    http://blogs.msdn.com/vbteam/archive/2008/01/31/converting-sql-to-linq-part-8-left-right-outer-join-bill-horst.aspx

    The only thing you may have to watch out for is using it with ADO.NET Data Services because the RESTful nature of that framewwork puts some limitations on the queries you can write. So sometimes you just have to break up the statements and pull the objects down onto the client and work from there. See this MSDN topic for details on the exact syntax that's supported:

    http://msdn.microsoft.com/en-us/library/cc907912.aspx

    I'll try and write a post addressing this in the future. Thanks for the feedback!

    HTH,

    -B

  • This was an excellent post.  This example was my first time ever leveraging ADO.NET Data Services and the ReportViewer control and I was definitely surprised at how quick it was to get up and running.  If I had only tested this out a few months ago!  Will this work similarly with WPF and WCF?

  • Hi Ramone,

    ADO.NET Data Services are built upon WCF REST services but if you want to manually create WCF services to return objects (or datasets) then you can use those as data sources on the report as well.

    The ReportViewer is a Winforms control but you can use it in your WPF applications. You can create a Winform and call it from your WPF forms. Otherwise you can host it on a WPF form. Just add a Winforms UserControl to your WPF app and put the ReportViewer on the Winforms UserControl. Then on your WPF Window use the WindowsformsHost control and then set the Child property to the UserControl. Then you can put the code to load the data and refresh the report like above in the WPF Window Loaded event handler. Take a look here for more info on the WindowsformsHost:

    http://msdn.microsoft.com/en-us/library/ms751761.aspx

    I'll try to write up a post on this one too. Thanks for the feedback!

    HTH,

    -B

  • Can this approach also work with Linq to SQL?

  • Hi John,

    Like I mentioned, the ReportViewer will work against any objects as long as you don't have a many-to-many relationship defined. So LINQ to SQL objects would work the same.

    However, since LINQ to SQL is just a simple 1:1 mapper to your database tables you would most likely create database views for your more complex reports and then map to those. Although if you're connecting directly at that point it's probably easier to just use datasets.

    You can use LINQ to SQL with ADO.NET Data Services. Take a look at this post:

    http://blogs.msdn.com/codefx/archive/2009/08/31/the-step-by-step-guide-of-making-ado-net-data-services-for-ado-net-entity-data-model-linq-to-sql-classes-and-non-relational-data.aspx

    HTH,

    -B

  • Thanks for the quick response.  It would be a problem with the 1:1 as any of the complicated queries on do on Sql Server, so all my reports see are simple views.  The reason i'm doing this is because I ran into a brick wall creating my reports with the traditional way using table adapters etc.  I all ready am using Linq to SQL for my ASP.Net MVC CRUD app (written in VB.Net which is cool) so why not do the same for the reports?

    thanks again

  • Beth,

    Are we able to use this same model with ASP.net Report Viewer?  I would like to have an asp.net page that references a dataservice.  That data service should feed the ASP.net report Viewer.  

    Is this possible?  I am just having trouble setting the object as my datasource via the asp.net report viewer.

    Thank you,

    Michael Buller

  • Hi Beth,

    I am working on a project which uses WCF services, entiy framework. Here while desgining a report using Repot Viewer Tool inorder to add Data sources to the report i clicked on Report --> Data sources which throws an error saying

    (Undefined Complex Type 'http://schemas.datacontract.org/2004/07/system.data.objects.dataclasses:entityobject' is used as a basse for complex type extention)

    no clue of what does it really mean??

    my question is, can't we use report viewer while we are working on WCF services? if so, then is there any other way for generating reports?

    i am stuck here. unable to proceed further with this error. please suggest an alternative for this as soon as possible. Thanks and Regards

  • Hi Chinni,

    I assume you have written your own WCF services that are returning entities and are not using ADO.NET Data Services? Have you already added the service reference and generated the proxy types? It also sounds like you need to add a reference to the System.Data.Entity.

    After you generate the proxy objects you should be able to select those to bind to in the report wizard.

    You may also want to look into using ADO.NET Data Services or returning simple data transfer objects instead of returning EF entities through your service, especially for view/read-only reporting.

    HTH,

    -B

  • HAI BETH,

    I HAVE 2 QUESTIONS.

    1. HOW  TO DISPLAY IMAGE FROM DATABASE USING REPORTVIEWER IN WINDOWS FORM

    2. HOW TO LOAD DATA IN REPORTVIEWER BY CODE (UNTYPED DATASET) NOT IN WIZARD

    THANKS

  • You are very coooooooooooooooooooooooool

    I love you

  • I'm building a local report directly on my EDM object, which has 2 different 1-to-many relationships. In my report, I used 2 separate tables to display the related data from these relationships. . . however, both of the related datasets are named the same thing by the data source designer and the compiler gakks on it. Where do I go to change the names of those datasets?

  • Madam,

         I have seen your 'how do i video' showing how to make auto complete in a combo box.It was excellent,but how to do the same thing in a combo box inside a datagrid view.Another doubt is,i have a datagrid associated with a data table.i have changed the text boxes in side the datagrid into combo boxes.Then i selected the datasource property and data memnber property of the combo boxes to another table(multiple data binding).the thing is that when i select values in one combo box,i want the corresponding datas to appear in the other combo boxes also.i have done the same thing in 'normal combo boxes'.but i am not able to do the thing in combo boxes inside datagrid view.please hel iam working on a 'stock sales billing ' software.

  • i would like to know how to be able to be conected from different locations to

    the same project in one computer using visual basic and sql 2005.

    What i mean is i have a program in main computer and i want my other stores able to perform

    transactions from other computers.

    Thanks

Page 1 of 2 (17 items) 12