Integrate Reporting Services with Silverlight and RIA Services

• Download source - 801 KB

Introduction

One of the most wanted future features in Silverlight is the possibility to create business reports using Reporting Services. In this article, I will show how to use the existing Reporting Services technology with Silverlight and RIA Services combination.

Background

I assume that the reader has some basic to intermediate level knowledge in Silverlight and RIA Services and C#, also some knowledge about WCF and the Entity Framework.

Using the Code

The first thing to do is create a Silverlight Application in Visual Studio 2010. I used the Silverlight Business Application template.

In the ASP.NET project, I added an Entity Framework Model.

In this demo, I'm using the AdventureWorksLT database.

These are the entities we will use in this demo:

Build the project and add a Domain Service Class:

Here are all the entities selected to be added to the domain service:

Now we can add a new Report. In this case, we are using Reporting Services in Local mode.

In the report, when a Tablix component is added, a dataset is requested. In this case, the Choose the Dataset Wizard sees the domain context that we added before.

For this demo, I created a new class that I will use to bind to the report. There are cases in which that is preferable to do because we may need to do some extra processing before the data can be bound to the report:

using System;
using System.Collections.Generic;

using System.Linq;
using System.Web;

using System.ComponentModel.DataAnnotations;


namespace ReportingServicesDemo.Web
{
    public class Orders
    {
        public Orders()
        { }

        [Key]
        public int OrderDetailsID
        {
            get;
            set;
        }

        public int OrderID
        {
            get;
            set;
        }

        public string Product
        {
            get;
            set;
        }

        public int Quantity
        {
            get;
            set;
        }

        public decimal UnitPrice
        {
            get;
            set;
        }

        public decimal UnitPriceDiscount
        {
            get;
            set;
        }

        public decimal LineTotal
        {
            get;
            set;
        }
    }
}

In this class, I made a reference to the System.ComponentModel.DataAnnotations namespace that allows me to add a Key attributte to the OrderDetailsID property - that is a requirement for the class so the domain service can recognize it as an entity.

If we dive into the domain service code, we find this:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.Data;
using System.Linq;
using System.ServiceModel.DomainServices.EntityFramework;
using System.ServiceModel.DomainServices.Hosting;
using System.ServiceModel.DomainServices.Server;


//// Implements application logic using the AdventureWorksLT2008Entities context.
//// TODO: Add your application logic to these methods or in additional methods.
//// TODO: Wire up authentication (Windows/ASP.NET Forms)
///        and uncomment the following to disable anonymous access
//// Also consider adding roles to restrict access as appropriate.
// [RequiresAuthentication]
[EnableClientAccess()]
public class ADWLTDomainService : 
       LinqToEntitiesDomainService<AdventureWorksLT2008Entities>
{

Now I create a domain operation that exposes the Orders entity I just created in my custom class:

public IQueryable<Orders> GetOrderDetails(int OrderID)
{
    AdventureWorksLT2008Entities adw = new AdventureWorksLT2008Entities();

    var query = from c in adw.SalesOrderDetail.Include("Product")
                where 
                c.SalesOrderID == OrderID
                select new Orders { OrderID = OrderID, 
                OrderDetailsID = c.SalesOrderDetailID, Product = c.Product.Name, 
                Quantity = c.OrderQty, UnitPrice = 
                c.UnitPrice, UnitPriceDiscount = c.UnitPriceDiscount, 
                LineTotal = c.LineTotal };
   return query;
}

Here are some remarks. In this method, I'm using the Entity Framework context instead of the object context that the domain service declares, and exposing like the other select methods as IQueryable.

If we build the project again, the "Choose the DataSet Wizard" sees our custom method and all the fields exposed by the entity ready for our report.

Here is the report design together with the dataset available:

Now we add a WebForm to the web project in order to place the report using the ReportViewer control:

When we add the ReportViewer control, it creates an ObjectDataSource control.

Looking at the ObjectDataSource's properties, we can see the Select property of our custom method.

Looking at the SelectParameters collection, we see our custom method parameter.

Now in the Silverlight project, we add a DataGrid bound to the Sales Orders entity.

Each field in the Sales Order ID column is represented as a button just for this demo in order to pass the content value as a parameter on the click event. The idea is to call a popup window using the HtmlPage object, with HtmlPopup options from the System.Windows.Browser namespace.

HtmlPopupWindowOptions options = new HtmlPopupWindowOptions();
options.Left = 0;
options.Top = 0;
options.Width = 930;
options.Height = 800;
options.Menubar = false;
options.Toolbar = false;
options.Directories = false;
options.Status = false;

Button btn = sender as Button;

int OrderID =int.Parse(btn.Content.ToString());

string address = Application.Current.Host.Source.AbsoluteUri;
int i = address.IndexOf("/ClientBin/", 1);
string url = address.Substring(0, i);
url = url + "/RpDemoWebForm.aspx?OrderID=" + OrderID;

if (true == HtmlPage.IsPopupWindowAllowed)
    HtmlPage.PopupWindow(new Uri(url), "new", options);

We can obtain the current web address were the Silverlight XAP application is running, using the Application.Current.Host.Source.AbsoluteUri property. Later, we can create the URI associated with the ReportViewer's Web Form.

Finally, we can see our Order Details report.

Points of Interest

I hope this article can be helpful while we wait for the next Silverlight releases, and also can explore more options in business reporting.

History

• 07-26-2010: Submitted to CodeProject.