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 8 and 5 and type the answer here:
  • Post
  • I have added the project (both VB and C#) to the MSDN code gallery:

    code.msdn.microsoft.com/Display-a-chart-built-on-eb631504

    Enjoy!

    Eric

  • Eric,

    Very good article, works great when summarizing to a single row.  What if my summary is a grouping of more rows, I am only gettting the first row in my Screen when trying to summarize a group of orders by there order status.

    <Query(IsDefault:=True)>

    Public Function GetRollupByGroupId() As IQueryable(Of RollupStatus)

           Return From od In Me.Context.Orders

                   Join st In Me.Context.OrderStatus On st.OrderID Equals od.OrderID

                   Group od By od.GroupID, st.StatusType.StatusDescription

                   Into Count()

           Select New RollupStatus With {.GroupId = GroupID.Value,

                                         .StatusDescription = StatusDescription,

                                         .iStatusCount = Count}

    End Function

    End Class

    Public Class RollupStatus

       <Key()>

       Public Property GroupId As Guid

       Public Property StatusDescription As String

       Public Property iStatusCount As Integer

    End Class

    Thanks again,

  • @Todd - As we discussed offline, the issue is that the aggregate query is returning multiple rows with the same "GroupId".  Thus, only marking GroupId as the Key for the RollupStatus class isn't enough.  LightSwitch sees multiple rows with the same Key values, and discards what it thinks are duplicate records.

    To fix this, you need to put <Key()> attributes on the subset of properties that will uniquely identify a row.  In this case, you need to add <Key()> to the StatusDescription property in the RollupStatus class.

  • Thanks Eric.

    Here is the new class definition that fixed this implicit filtering.

    Public Class RollupStatus

      <Key()>

      Public Property GroupId As Guid

      <Key()>

      Public Property StatusDescription As String

      Public Property iStatusCount As Integer

    End Class

    As Eric suggested these dialogs are probably best suited for the discussion group so I am putting a plug-in here.

    social.msdn.microsoft.com/.../threads

  • Great walkthrough thanks, learned a lot.  

    Had to put the bar chart control in a separate project.  With VS 2012 RC, I could not add a reference to it in the 'New Custom Control' dialogue when I put it in the Client project.

  • To get this working with the _InstrinsicData database I had to add "Enlist=false;" to the connection string in the Web.config of the Lightswitch Server project.  Not sure why...

  • Hi Eric,

    Thanks for the article.

    I am scratching my head for last few weeks in order to find a way to deploy WCF RIA Service separately as a web end-point i.e. on a separate server than the LS application server.

    I have been search for any resource that explains multi-tier scenario but couldn't find it?

    Is there any way to do it? We want to write a service to search a third party Document management System. The limitation is that the service has to be deployed locally on the Document Management System server.

    Your help is really appreciated.

    Thanks

    Raj

  • I ha two classes

    [DataContract]

       public class Employee

       {

           [DataMember]

           public int EmpNo { get; set; }

           [DataMember]

           public string EmpName { get; set; }

           [DataMember]

           public List<Department> AllDepart { get; set; }

           //[DataMember]

           //public List<Sales> AllSales { get; set; }

           [DataMember]

           public string DeptName { get; set; }

       }

       [DataContract]

       public class Department

       {

           [DataMember]

           public int DeptNo { get; set; }

           [DataMember]

           public string Dname { get; set; }

           [DataMember]

           public string Location { get; set; }

           //public List<Employee> Allempl { get; set; }

           [DataMember]

           public List<Sales> AllSales { get; set; }

           public Employee Employee { get; set; }

       }

    Method like this

    [WebGet(UriTemplate = "Employee", ResponseFormat = WebMessageFormat.Xml)]

           [OperationContract]

           List<Employee> GetAllEmployeeDetails();

    Getting data from two classes output like below

    How can i get that type of output?

    I want to output like this

    <Employee>

    <DeptName>DEV</DeptName>

    <EmpName>Anil</EmpName>

    <EmpNo>101</EmpNo>

    </Employee>

      <Department>

       <DeptNo>1</DeptNo>

       <Dname>Sales</Dname>

        <Location>Vij</Location>

       </Department>  

    </Employee>

    please help me

Page 2 of 2 (23 items) 12