How Do I: Display a chart built on aggregated data (Eric Erhardt)

In a business application there is often a need to roll-up data and display it in a concise format.  This allows decision makers the ability to analyze the state of the business and make a decision quickly and correctly.  This roll-up may retrieve data from many different sources, slice it, dice it, transform it, and then display the information in many different ways to the user.  One common way to display this information is in a chart.  Charts display a lot of information in a small amount of space.

Visual Studio LightSwitch v1.0 doesn’t provide a report designer out of the box.  However, that doesn’t mean that the hooks aren’t there for you to implement something yourself.  It may take a little more work than building a search screen, or data entry screen, but being able to report on your business data is going to be a requirement from almost any business application user.

I am going to explain, from beginning to end, an approach you can use to aggregate data from your data store and then add a bar chart in your LightSwitch application to display the aggregate data.

The approach I am going to take is to use a Custom WCF RIA DomainService to return the aggregated data.  It is possible to bring aggregate data into LightSwitch using a database View.  However, that approach may not always be possible.  If you don’t have the rights to modify the database schema, or if you are using the intrinsic “ApplicationData” data source, you won’t be able to use a database View.  The following approach will work in all database scenarios.

In order to follow along with this example, you will need:

The business scenario

Let’s say you have a sales management database, we’ll use the Northwind database for the example, that allows you to track the products you have for sale, your customers and the orders for these products.  The sales manager would like to have a screen that allows him/her to view how much revenue each product has produced.

Creating your LightSwitch application

First, let’s start by launching Visual Studio LightSwitch and creating a new project.  Name it “NorthwindTraders”.  Click on the “Attach to external Data Source” link in the start screen, select Database and enter the connection information to your Northwind database.  Add all tables in the Northwind database.  Build your project and make sure there are no errors.  Your Solution Explorer should look like the following:


Creating a Custom WCF RIA Service

LightSwitch version 1.0 doesn’t support aggregate queries (like GROUP BY, SUM, etc.).  In order to get aggregate data into a LightSwitch application, we will need to create a Custom WCF RIA Service.  In order to create a Custom WCF RIA Service, we need to create a new “Class Library” project.  Visual Studio LightSwitch by itself can only create LightSwitch applications.  You will need to either use Visual Studio or Visual C#/Basic Express to create the new Class Library project.  Name this Class Library “NorthwindTraders.Reporting”.  Build the project to make sure everything was created successfully.

Once you have created the Class Library project, you can add it into your LightSwitch solution.  First, make sure the solution is being shown by opening Tools –> Options.  Under “Projects and Solutions” ensure that “Always show solution” is checked.  Then, in the Solution Explorer, right-click on the solution and say “Add –> Existing Project”.  Navigate to the Class Library you created above.  (The Open File Dialog may filter only *.lsproj files.  Either typing the full path into the dialog or navigating to the folder, typing * and pressing “Enter” into the File name text box will allow you to select the NorthwindTraders.Reporting.cs/vbproj project.)

Now you will need to add a few references to the NorthwindTraders.Reporting project.  Add the following:

  • System.ComponentModel.DataAnnotations
  • System.Configuration
  • System.Data.Entity
  • System.Runtime.Serialization
  • System.ServiceModel.DomainServices.Server (Look in %ProgramFiles(x86)%\Microsoft SDKs\RIA Services\v1.0\Libraries\Server if it isn’t under the .Net tab)
  • System.Web

You can rename the default “Class1” to “NorthwindTradersReportData”.  Also make sure it inherits from the System.ServiceModel.DomainServices.Server.DomainService base class.  You should now have a Custom WCF RIA Service that can be consumed in LightSwitch.  For more information on using Custom WCF Ria Services in LightSwitch, refer to the following blog articles:

Returning aggregate data from the Custom WCF RIA Service

Now comes the real work of connecting to the Northwind database and writing a query that will aggregate our product sales information.  First, we need a way to query our Northwind data.  One option is re-create an ADO.Net Entity Framework model that connects to the Northwind database, or use LINQ To SQL.  However, that would force us to keep two database models in sync, which may take more maintenance.  It would be really nice if we could re-use the Entity Framework model that our LightSwitch application uses.  Fortunately we can re-use this model by Adding an Existing Item to our NorthwindTraders.Reporting project and selecting “Add as Link”.  This will allow both our LightSwitch application and NorthwindTraders.Reporting projects to consume the same files.  The LightSwitch application will keep the files up-to-date and the Reporting project will just consume them.

To do this, in the Solution Explorer right-click on the “NorthwindTraders.Reporting” project “Add –> Existing Item”.  Navigate to the folder containing your LightSwitch application’s .lsproj file.  Navigate “ServerGenerated” \ “GeneratedArtifacts”. Select “NorthwindData.vb/cs”.  WAIT!  Don’t click the “Add” button just yet.  See that little drop down button inside the Add button?  Click the drop down and select “Add As Link”.

UPDATE:  If you are using LightSwitch in Visual Studio 11, there isn’t a “ServerGenerated” folder.  The Server and ServerGenerated projects have now been combined into one “Server” project.  So instead of navigating to “ServerGenerated” \ “GeneratedArtifacts”, navigate to “Server” \ “GeneratedArtifacts”.


The NorthwindData.vb/cs file is now shared between your LightSwitch application and the NorthwindTraders.Reporting project.  As LightSwitch makes modifications to this file, both projects will get the changes and both projects will stay in sync.

Now that we can re-use our Entity Framework model, all we need to do is create an ObjectContext and query Northwind using our aggregate query.  To do this, add the following code to your NorthwindTradersReportData class you added above.

Visual Basic:

Imports System.ComponentModel.DataAnnotations
Imports System.Data.EntityClient
Imports System.ServiceModel.DomainServices.Server
Imports System.Web.Configuration

Public Class NorthwindTradersReportData
    Inherits DomainService

    Private _context As NorthwindData.Implementation.NorthwindDataObjectContext
    Public ReadOnly Property Context As NorthwindData.Implementation.NorthwindDataObjectContext
            If _context Is Nothing Then
              Dim builder = New EntityConnectionStringBuilder
              builder.Metadata =
              builder.Provider = "System.Data.SqlClient"
              builder.ProviderConnectionString =

              _context =
                  New NorthwindData.Implementation.NorthwindDataObjectContext(
builder.ConnectionString) End If Return _context End Get End Property ''' <summary> ''' Override the Count method in order for paging to work correctly ''' </summary> Protected Overrides Function Count(Of T)(query As IQueryable(Of T)) As Integer Return query.Count() End Function <Query(IsDefault:=True)> Public Function GetSalesTotalsByProduct() As IQueryable(Of ProductSales) Return From od In Me.Context.Order_Details Group By Product = od.Product Into g = Group Select New ProductSales With {.ProductId = Product.ProductID, .ProductName = Product.ProductName, .SalesTotalSingle = g.Sum(Function(od) _ (od.UnitPrice * od.Quantity) * (1 - od.Discount))} End Function End Class Public Class ProductSales <Key()> Public Property ProductId As Integer Public Property ProductName As String Public Property SalesTotal As Decimal ' This is needed because the cast isn't allowed in LINQ to Entity queries Friend WriteOnly Property SalesTotalSingle As Single Set(value As Single) Me.SalesTotal = New Decimal(value) End Set End Property End Class


using System.ComponentModel.DataAnnotations;
using System.Data.EntityClient;
using System.Linq;
using System.ServiceModel.DomainServices.Server;
using System.Web.Configuration;
using NorthwindData.Implementation;

namespace NorthwindTraders.Reporting
    public class NorthwindTradersReportData : DomainService
        private NorthwindDataObjectContext context;
        public NorthwindDataObjectContext Context
              if (this.context == null)
                EntityConnectionStringBuilder builder = new EntityConnectionStringBuilder();
                builder.Metadata = 
                builder.Provider = "System.Data.SqlClient";
                builder.ProviderConnectionString = 

                this.context = new NorthwindDataObjectContext(builder.ConnectionString);
              return this.context;

        /// <summary>
        /// Override the Count method in order for paging to work correctly
        /// </summary>
        protected override int Count<T>(IQueryable<T> query)
            return query.Count();

        [Query(IsDefault = true)]
        public IQueryable<ProductSales> GetSalesTotalsByProduct()
            return this.Context.Order_Details
                .GroupBy(od => od.Product)
                .Select(g =>
                    new ProductSales()
                        ProductId = g.Key.ProductID,
                        ProductName = g.Key.ProductName,
                        SalesTotalFloat = g.Sum(od => 
                            ((float)(od.UnitPrice * od.Quantity)) * (1 - od.Discount))

    public class ProductSales
        public int ProductId { get; set; }
        public string ProductName { get; set; }
        public decimal SalesTotal { get; set; }

        // This is needed because the cast isn't allowed in LINQ to Entity queries
        internal float SalesTotalFloat
                this.SalesTotal = new decimal(value);

NOTE:  This example uses an attached database named “Northwind”, which means the data source is named “NorthwindData”.  That is why the hard coded strings to set the builder.Metadata and builder.ProviderConnectionString properties use “NorthwindData” – it matches the name of the data source.  There is one exception to this rule.  When using the intrinsic “ApplicationData” data source, you still use “ApplicationData” in the builder.Metadata property.  However, for the ProviderConnectionString use “_IntrinsicData” instead of “ApplicationData”.

Import the Custom WCF RIA Service into LightSwitch

Now that we have a WCF RIA Service that will return our aggregated data, it is time to bring that data into our LightSwitch application.  In the Solution Explorer, right-click the “Data Sources” folder under your NorthwindTraders project.  Select “Add Data Source…”.  In the Attach Data Source Wizard, select “WCF RIA Service”.  Click Next.


You shouldn’t have any available WCF RIA Service classes yet.  LightSwitch needs you to add a reference to your NorthwindTraders.Reporting project in order for the service to be picked up.  Click the Add Reference button.


In the Add Reference dialog, select the “Projects” tab at the top and select your “NorthwindTraders.Reporting” project.


After you add the reference, your NorthwindTradersReportData WCF RIA DomainService class will show up in the WCF RIA Service classes list.  Select it and click Next.


Check the “Entities” node in the tree to select all the data source objects that are exposed by the NorthwindTradersReportData service.  Then click Finish.


You should get a new Data Source node in your LightSwitch application project.  This new data source will call into the aggregate query and return total sales numbers for each Product in the Northwind database.  You should build a “Search Data Screen” based on the ProductSales data in order to test that everything is working in your Custom WCF RIA Service.  If everything is working, you should get a screen that looks like the following:


Adding a Bar Chart using a Custom Control

Now that we can bring aggregate data into our LightSwitch application, we want to display the data it in a bar chart that will allow our end user to visually see the total sales information for each product.

First, we’ll need to create the bar chart Silverlight control.  To do this, add a new .xaml file to the “Client” project.  First, in the Solution Explorer, select the “NorthwindTraders” project node, and switch to “File View” by clicking the view switching button in the Solution Explorer toolbar:


Next, right-click on the “Client” project and select “Add –> New Item…”.  Selecting a “Text File” in the dialog will be fine, but be sure to name it with a “.xaml” extension.  I named mine “ProductSalesBarChart.xaml”.

Add the following xaml code to the file:

m.Windows.Controls.DataVisualization.Toolkit"> <chartingToolkit:Chart x:Name="ProductSalesChart"> <chartingToolkit:Chart.Series> <chartingToolkit:BarSeries Title="Sales Totals" ItemsSource="{Binding Screen.ProductSales}" IndependentValueBinding="{Binding ProductName}" DependentValueBinding="{Binding SalesTotal}"> </chartingToolkit:BarSeries> </chartingToolkit:Chart.Series> </chartingToolkit:Chart> </UserControl>

For Visual Basic users, this is the only file that is required.  However, C# users will also have to add a code file that calls InitializeComponent from the constructor of the control:

namespace NorthwindTraders.Client
    public partial class ProductSalesBarChart
        public ProductSalesBarChart()

Since we use the Silverlight 4 Toolkit in the xaml, add a reference from the Client project to the "System.Windows.Controls.DataVisualization.Toolkit” assembly by right-clicking the Client project in the Solution Explorer and selecting “Add Reference…”.


Note: if you don’t see this assembly, make sure you have the Silverlight 4 Toolkit installed.

All that is left is to add this custom control to a LightSwitch screen.  To do this, switch back to the “Logical View” in the Solution Explorer using the view switching toolbar button.  Open the “SearchProductSales” screen that was previously created on the ProductSales data.  Delete the “Data Grid | Product Sales” content item in the screen designer.  This will leave you with a blank screen that has a single ScreenCollectionProperty named “ProductSales”.


Click on the “Add” button in the screen designer and select “New Custom Control…”


Next, find the ProductSalesBarChart custom control we added to the Client project.  It should be under the “NorthwindTraders.Client” assembly in the “NorthwindTraders.Client” namespace.  Select it and click OK.


One last step is to fix a little sizing issue.  By default, the Silverlight chart control will try to take up the minimal size possible, which isn’t readable in the screen.  To fix this, you need to set the control to be “Horizontal Alignment = Stretch” and “Vertical Alignment = Stretch” in the Properties sheet.


Also remove the “Screen Content” default label.


You can now F5 your project and see your chart control in LightSwitch:


Something you should keep in mind is that this data is paged to 45 records by default.  So you aren’t seeing all available products.  You can either turn off paging (which I don’t recommend) or you can add paging buttons on your screen.  The backing code for these paging buttons could look like:

Visual Basic

Private Sub PreviousPage_CanExecute(ByRef result As Boolean)
    ' PageNumber is 1-based
    result = Me.Details.Properties.ProductSales.PageNumber > 1
End Sub

Private Sub PreviousPage_Execute()
    Me.Details.Properties.ProductSales.PageNumber -= 1
End Sub

Private Sub NextPage_CanExecute(ByRef result As Boolean)
    result = Me.Details.Properties.ProductSales.PageNumber <
End Sub

Private Sub NextPage_Execute()
    Me.Details.Properties.ProductSales.PageNumber += 1
End Sub


partial void PreviousPage_CanExecute(ref bool result)
    // PageNumber is 1-based.
    result = this.Details.Properties.ProductSales.PageNumber > 1;

partial void PreviousPage_Execute()

partial void NextPage_CanExecute(ref bool result)
    result = this.Details.Properties.ProductSales.PageNumber <

partial void NextPage_Execute()


Hopefully by now you can see that the extensibility story in Visual Studio LightSwitch is pretty powerful.  Even though aggregate data and chart controls are not built into Visual Studio LightSwitch out of the box, there are hooks available that allow you to add these features into your application and meet your end users’ needs.