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

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

Rate This
  • Comments 23

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:

image

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

image

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
        Get
            If _context Is Nothing Then
              Dim builder = New EntityConnectionStringBuilder
              builder.Metadata =
                  "res://*/NorthwindData.csdl|res://*/NorthwindData.ssdl|res://*/NorthwindData.msl"
              builder.Provider = "System.Data.SqlClient"
              builder.ProviderConnectionString =
                  WebConfigurationManager.ConnectionStrings("NorthwindData").ConnectionString

              _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

C#:

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
        {
            get
            {
              if (this.context == null)
              {
                EntityConnectionStringBuilder builder = new EntityConnectionStringBuilder();
                builder.Metadata = 
                  "res://*/NorthwindData.csdl|res://*/NorthwindData.ssdl|res://*/NorthwindData.msl";
                builder.Provider = "System.Data.SqlClient";
                builder.ProviderConnectionString = 
                  WebConfigurationManager.ConnectionStrings["NorthwindData"].ConnectionString;

                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
    {
        [Key]
        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
        {
            set
            {
                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.

image

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.

image

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

image

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.

image

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

image

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:

image

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:

image

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:

<UserControl
  x:Class="NorthwindTraders.Client.ProductSalesBarChart"
  xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
  xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
  xmlns:chartingToolkit="clr-namespace:System.Windows.Controls.DataVisualization.Charting;assembly=S
yste
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()
        {
            InitializeComponent();
        }
    }
}

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

image

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

image

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

image

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.

image

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.

image

Also remove the “Screen Content” default label.

image

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

image

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 <
        Me.Details.Properties.ProductSales.PageCount
End Sub

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

C#

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

partial void PreviousPage_Execute()
{
    this.Details.Properties.ProductSales.PageNumber--;
}

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

partial void NextPage_Execute()
{
    this.Details.Properties.ProductSales.PageNumber++;
}

Conclusion

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.

Leave a Comment
  • Please add 1 and 7 and type the answer here:
  • Post
  • for real nice tip buts im asking when ands where i can download a book about microsoft visual studio lightswitch

  • Hi Mcdavid El-Haj,

    Typically books are released as the product releases and I know of a few that are being written including this one: www.amazon.com/.../ref=sr_1_2

    For now, you can find a TON of training on the Developer Center. Check out the "Learn" section: msdn.microsoft.com/.../ff938857

    Cheers!

  • Very useful. How can I from "server" code access a query defined in the ApplicationDataService class where I am using an application data source rather than an external database. I get a null reference error when executing a query having created a local instance of ApplicationDataService.

  • Martin,

    When using the ApplicationData source instead of an external data source, a slight modification is needed to get the correct connection string.  Instead of specifying WebConfigurationManager.ConnectionStrings["ApplicationData"], use "_IntrinsicData" in place of "ApplicationData".  LightSwitch treats the Application data source as a special data source, and as such it requires a special connection string.  See social.msdn.microsoft.com/.../06f603f7-244b-409d-a0bb-b9dbfb2d0757 for more information.

    Eric

  • How does this solution behave when it comes time to deploy?  Will LightSwitch deploy the RIA Service, or will that be an additional manual step?  Does the RIA Service have to be deployed on the same IIS server if you are doing a 3-tier deployment?

  • Andrew,

    The assembly containing the WCF RIA Service will be deployed to the LightSwitch web service "bin" directory.  The WCF RIA Service is not deployed separately as a web end-point.  At runtime, the LightSwitch service will load the Ria DomainService class in memory and call queries/updates on the DomainService instance.

    To answer your other questions: LightSwitch will deploy the RIA Service automatically, no manual step is needed.  Yes, the RIA Service will be deployed on the same IIS server if you are doing a 3-tier deployment.

    Eric

  • Did you tried deploying this application into IIS?

    If so,  are you able to see data when you browse from IIS??

    Please let me know if you can see data while browsing from IIS.

    I did a LS application similar to your application,but my LS app is not pulling data after publishing onto IIS

    Thanksm

    LV

  • LV,

    Yes I have deployed this application to IIS and it works correctly.

    One possibility for your problem is that you connected to the "Northwind" database using 'Integrated Security=true'.  When you deploy the application to IIS, the service tier is no longer running as your windows account.  Instead, it is running under the MACHINE_NAME\ASP.Net user account (unless you changed your app pool to something else).  This user probably doesn't have access to connect to your Northwind database using Integrated Security.  You can change your connection string to use a SQL account to get around this problem.

    If this isn't your problem, one way of debugging the problem is to enable Diagnostics on the server.  See this article: www.code-magazine.com/article.aspx Look for the section titled "Diagnostics in the Save Pipeline".

    Eric

  • When i'm trying to create the wcf ria service, j have this message :

    The type NorthwindTraders.Reporting.NorthwindTradersReportData could not be loaded. The assembly containing the class may be missing or may have additional references which are missing.

    Can you help me ?

    Thanks

  • Christian,

    Can you ask a Forum question for this issue?  social.msdn.microsoft.com/.../threads

    That may be a better place to work on the solution.

    Eric

  • THanks Eric Erhardt, it helped alot

  • For a C# version see:

    social.msdn.microsoft.com/.../6485f314-d9aa-4293-8725-0295304d8e3f

  • I created a Blog that coveres this material with more screen shots for those of us who need a bit more help :)

    lightswitchhelpwebsite.com/.../WCF-RIA-Service-Combining-Two-Tables.aspx

  • Hello!

     I have a trouble when I try to make a project according to method in the paper.The problem is as problem:

      when I enter F5 to run the project and can succefully enter into the system,at this time,the problem appears in the search screen,the information is "TypeName Format of Custom Control is not correct".So I want to ask you that what's wrong with the project and how to solve the problem.

      thanks!

  • Added:

      can you send the project of the paper to me?? I would like to learn it.My mailbox is zxliang999@hotmail.com

Page 1 of 2 (23 items) 12