Overview

Applications are creating and consuming more data than ever before. With so much data, how can your users quickly spot trends and get an overview of what’s important? Just as your car presents a dashboard indicating the top priority items, so can your application.

Matt introduced the ServerApplicationContext in his blog post. This article will build on it to show how to build rich dashboard and reporting apps using LINQ aggregate operators, returning data via WebAPI in a JSON format.

Noticing the length of this post, it’s fair to say this isn’t the normal point & click RAD productivity you’ve likely become used to with LightSwitch. This is more of an advanced scenario, showing the extension points we’ve enabled to extend the out of the box features.

There will be several steps, so let’s recap what we’ll be covering

• Create a Restaurant Point Of Sale (POS) database for reporting
• Creating an initial Ligthswitch App, adding a few screens to provide navigation to our graphs
• Add a LightSwitch custom control to display a Kendo UI DataViz Bar Graph
• Add WebAPIs for reporting
• Test the WebAPIs with Fiddler
• Connect the HTML Client Graph to the WebAPI report

Let’s get started

Create the Amazing Pie Restaurant Point of Sale (POS) database

Download the AmazingPie POS Sample database

Creating the LightSwitch Project

We’ll need a basic app to put the reports within. The app will be fairly straight forward with a few screens using the externally linked Amazing Pie database.

1. First, create a new Lightswtich HTML Application (C# or Visual Basic) named AmazingPieManager

    

2. Select “Attach to external data source”, connecting to the AmazingPie database we’ve just created

3. Select “Database”

4. Enter the connection information for your AmazingPie database

5. Select all the tables

6. Verify the Data Source is named AmazingPieData, and click [Finish]

Build some viewing screens

Before we create the graphs, we’ll need some screens to navigate around the application .

Locations Browse Screen

1. Right-click on the HTML Client node in the Solution Explorer and select “Add Screen”

2. Select the “Browse Data Screen” template, using the “AmazingPieData.Locations” entity as a Data Source

• Personally, I prefer all my screens grouped together by name, then type, so I’ll rename the screen Locations_Browse
  

3. We’ll change the default display a bit:

• Change the Display type from List to Tile List
  
• Reduce the properties to be displayed
  

Locations View Screen

1. Right-click on the HTML Client node in the Solution Explorer and select “Add Screen”

2. Select the “View Details Screen” template, using the “AmazingPieData.Location” entity as a Data Source

• We’ll name this Location_View
• Add additional data including Location Tables and click [OK]
  

3. We’ll change the default display a bit:

• Create a Columns Layout group
• Move City, State, Zip below this group
• Change the Tables to a Tile List
• Reduce the properties to Table Id, Table Type and Table Size
• I’ve also shortened the list of properties to Type and Size and moved Size last
• When done, you’re layout should look like this:
  

Link the screens through Item Tap

We need to navigate from one screen to another, and we do that with Item Tap

1. Open the Location_Browse screen

2. Select the Tile List for Locations

3. In the property grid, click Item Tap where it says none.

4. Click the dropdown for Choose an existing method

5. Select viewSelected
 

6. Verify Navigate To: is set to Location View and click [OK]

Add Kendo UI Controls

For the purposes of this post, I’m using Kendo UI DataViz controls. There are many great controls and partners we work with, and the meat of this post will be the same regardless of which controls you use.

1. Download Kendo UI. You can use their 30 day free trial.

2. Copy the Kendo UI .zip file to a location and for the ease of dragging into Visual Studio, extract the files to another directory

3. Copy the Kendo UI css and javascript files

• In Visual Studio, toggle from Logical to File View. (Note: the button is a toggle button so you don’t have to actually drop down and select File View)
  
• With the folder you extracted the Kendo UI files, open the styles folder
• Drag kendo.dataviz.min.css to your AmazingPieManager.HTMLClient\Content folder
  
• Open the Kendo UI js folder
• Drag kendo.dataviz.min.js to your AmazingPieManager.HTMLCLient\Scripts folder

4. Add the style and js file references
Since LightSwitch works as a single page application model, we’ll just need to add the references to the default.htm page  

• Open default.htm
• From the Content\ drag the kendo.dataviz.min.css file just above the </head> close tag
• From the \Script directory, drag the kendo.dataviz.min.js below the last js reference
• You can also take a moment to add spaces to the page heading of AmazingPieManager
• Your project should now look like:
  

Add Reports Tab

With the Kendo UI Control references added, let’s create a place for our graphs

1. Switch back to the Location_View screen

2. At the top of the screen designer, Right-Click on Tabs and select Add Tab

3. In the properties sheet:

• Rename the tab to ReportsTab
• Change the Display Name: to Reports
  

4. Right-Click on the Reports Tab and select Add Custom Control

• In the dialog, just select OK for the data path
• In the property grid, rename the custom control SeatedGuests

5. Now, add the SeatedGuests render code

• With the SeatedGuests Custom Control selected, select SeatedGuests_render from the Write Code tab at the top right of the screen designer
    

Add a Bar Graph

Before we worry about end to end service calls, we can leverage the simplicity of JSON and create some sample data to display

1. Add the following code to the SeatedGuests_render function

myapp.Location_View.SeatedGuests_render = function (element, contentItem) {
    var sampleData = [
        { "TableType_Id": "Bar", "Seats": 21 },
        { "TableType_Id": "Lounge", "Seats": 23 },
        { "TableType_Id": "Dining", "Seats": 48 }]

2. We’ll now add the <div> to contain the chart, and use jquery to append it to the element passed into the render function

var locationSeatingChartContainer = $('<div/>');
locationSeatingChartContainer.appendTo($(element));

3. Add the code for the Kendo UI DataViz Chart

locationSeatingChartContainer.kendoChart({
    // set the theme
    theme: $(document).data("kendoSkin") || "default",
    // set the datasource to use our sample data for now
    dataSource: {
        data: sampleData
    },
    title: {
        text: "Seats Per Location"
    },
    legend: {
        position: "bottom"
    },
    // Set the chart to a column (vertical) report type
    seriesDefaults: {
        type: "column",
        labels: {
            visible: true,
            // the 0:n0 means parameterize the label with the value - first 0
            // and then :n0 means use numeric with 0 decimals
            format: "{0:n0}"
        }
    },
    // map the vertical series to the number of seats, 
    // notice the field: "Seats" matches the name in the json payload returned from our WebAPI
    series: [{
        field: "Seats",
        name: "",
        colorField: "userColor"
    }],
    valueAxis: {
        labels: {
            format: "{0:n0}"
        }
    },
    // And the category from our WebAPI json payload
    categoryAxis: {
        field: "TableType_Id"
    }
});

4. Now run the app to see how our graph looks

Create the WebAPI to host the Reporting Service

Now that we’ve got the UI working, and a place to display our results, let’s create the WebAPI feed.

If you’re not familiar with WebAPI, you might want to check out the Getting Started series on the ASP.NET blog for a primer on the technology.

Joe Binder introduced WebAPI to LightSwitch projects. I’ll provide the details here for completeness, but I’d suggest reading his post as well.

The basic premise is we’ll create an MVC like convention for calling URLs, routing them to classes that represent services. Depending on the quantity and names of the parameters, the request will be routed to the appropriate method within the class and return our result using the JSON serializer.

A note about Project References

Using WebAPI requires a few additional project references:

• Newtonsoft.Json
• System.Net.Http
• System.Net.Http.Formatting
• System.Net.Http.WebRequest
• System.Web
• System.Web.Http
• System.Web.Http.WebHost

To eliminate the need to manually add the project references, we’ll take advantage of the Visual Studio Templates that add these automatically. We’ll add the Web API Controller Class first, as it adds most of the references we need, specifically those needed to configure the Web API routing.

Deployment Note: If you want to publish your app to an IIS server, note that you’ll need one extra component. Using the NuGet Package manager on your Server project, add the NuGet package “Microsoft.Web.Infrastructure”. This is installed already on some IIS machines, but not all of them.

Nested Folders

Joe covered the basics of using WebAPI. As we generate more reports, or use WebAPI for other scenarios, our root folder can get quite filled. To clean things up, we’ll use nested folders and a slightly different configuration for the route.

1. Toggle the View in Solution Explorer to File View

2. We’ll group all our reports under one folder:

• Right click on AmazingPieManager.Server and add a new folder named Reports

3. Add the WebAPI Reports Controller

4. Select the AmazingPieManager.Server\Reports folder, and select [add] – [new item]

5. Select Web API Controller Class from the Web Templates and name it SeatingController
 

6. Before we complete the SeatingController, we’ll add the Route configuration

• In the root of the project, select Add New Item
• From the Web Templates, add the Global Application Class
  

7. Configure the route

• Using the naming convention of [name]Controller.cs(.vb), we’ll add a route so we can create URIs similar to: http://localhost:22700/reports/Seating/BelSquarelkdsj

Note: For more info on Routes see ASP.net Routing

• Add the following using statements to the Global.asax file

VB
Imports System.Web.Routing
Imports System.Web.Http
C#
using System.Web.Routing;
using System.Web.Http;

8. Add the following route to the Application_Start method

---

VB
Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs)
    RouteTable.Routes.MapHttpRoute(
        "ReportsAPI",
        "reports/{controller}/{id}",
        New With {Key .id = System.Web.Http.RouteParameter.Optional})
End Sub

C#
protected void Application_Start(object sender, EventArgs e) {
    RouteTable.Routes.MapHttpRoute(
            name: "ReportsApi",
            routeTemplate: "reports/{controller}/{id}",
            defaults: new {
                id = System.Web.Http.RouteParameter.Optional
            }
    );
}

VB Note: The MapHttpRoute method is missing from VB intellisense, however it is in the System.Web.Http.dll, so simply type or paste the above text and as long as you’ve followed the steps above for adding the global.asax (adds the System.Web.Http.dll) , and verify you’ve added the imports statements your project should compile.

9. If everything is added properly, your project should now compile.

Testing WebAPI Calls & Results

Although WebPAI calls can be made directly in the browser, many browsers fail to display JSON results by default. It’s also helpful to see the HTTP Results code to debug what’s happening under the covers. For the purposes of this post, I’ll use Fiddler to test and monitor the WebAPI requests rather than fiddle with the browser settings.

As we’re building out our Reports Controller, and testing with Fiddler to initiate the request, we’ll need the base URL configured.

Setting up Fiddler

1. Hit F5 to launch our app and get the root URL. In my case, it’s http://localhost:22700    

2.  With your app still running Launch Fiddler  

3. Select the Composer tab, and enter [your base URL]/reports/Seating/1, then hit [Enter] or the [Execute] button
Remembering our route configuration, we’ve configured all requests that start with reports will use the convention of [name]Controller to route our URL to the SeatingController class. The .NET runtime looks for all types that derive from the ApiController type.
Since our request is a Get, it’s routed to the Get method. Because we passed a parameter, it’s passed to the Get(int id) overload    
4. If you double click the Web Session, click on the Raw tab for result formatting, you’ll see “value” returned from our basic WebAPI controller.    

Testing a JSON result

With the basics running, let’s crate a simple collection and return it to see how this looks in Fiddler, and ultimately how our HTML client will receive it.

1. Open the SeatingController from our AmazingPieManager.Server project and delete all the generated methods.

2. Since we’re returning an anonymous type, we need to change the return type from string to object

3. Add the following code to construct a collection and return it:

VB
Public Function [Get](id As Integer) As Object
    Dim myList As Dictionary(Of String, Integer) = New Dictionary(Of String, Integer)
    myList.Add("Bar", 21)
    myList.Add("Lounge", 23)
    myList.Add("Dining", 48)

    Dim result = From r In myList
                 Select New With {.TableType_Id = r.Key, .Seats = r.Value}

    Return result
End Function

C#
public object Get(int id) {
    Dictionary<string, int> myList = new Dictionary<string, int>();
    myList.Add("Bar", 21);
    myList.Add("Lounge", 23);
    myList.Add("Dining", 48);
 
    var result = (from r in myList
                    select new { TableType_Id = r.Key, Seats = r.Value });
 
    return result;
}

What we’ve done above is create a simple dictionary of name/value pairs
Then, using LINQ, construct a new type, with named properties, correlating to the key and values of our dictionary.
Because the project defaults to the JSON serializer, we’ll see the results below.

1. Build the project. Note: It’s not actually necessary to hit F5, although you can and set a breakpoint in the Get method.

2. From Fiddler, hit execute to make the HTTP Get Request.    

3. This time, Fiddler notices the JSON payload and defaulted the formatting to JSON. You can also switch to TextView, WebView or Raw to see it formatted in a typical JSON format that you may remember seeing earlier when we prototyped our Kendo UI Graph.

Debugging Tip: If you have your service running, you can copy/paste the result into your javascript to test your UI without having to make the service calls

Getting Specific Reports

With the basic WebAPI infrastructure in place, let’s start creating some reports. Reports tend to come in groups. For instance, the manager of Amazing Pie wants to evaluate the effectiveness of seating at various locations. Based on the number of seats at the Bar, Lounge, Dining Room, how often are they filled? Should they possibly expand one area, and reduce another? The manager may look at one or many locations. He may look for trends by month or day of the week. Another report may evaluate the menu and which items are selling. To group the various reports, we’ll create one WebAPI APIController class for each type of report.

Modify the Seating Report Controller

This is where we’re going to capture the total number of seats for each location. Using LINQ, we’re going to:

• Query the Tables entity
• Filter by the Location passed in
• Group by the TableType
• Get a Sum of the TableSize for each TableType at each Location

1. Open the SeatingController from the Server\Reports folder

2. Add the Extension Methods for query operators

Note: this is an important step. Without these extension methods, the LINQ query operators will not be available on the DataWorkspace

VB
Option Infer On
Imports System.Net
Imports System.Web.Http
Imports Microsoft.LightSwitch
Imports LightSwitchApplication

C#
using Microsoft.LightSwitch;

3. Remove the additional overloads for Get, Put, Post and Delete methods as we’re only using this ApiController for Getting reports.

4. Change the parameter from int to string in the method we edited earlier
The Location_Id from the Table entity in our AmazingPie datamodel is of type string

5. Add the following LINQ query

VB
Public Function [Get](id As String) As Object
    Using context As ServerApplicationContext = ServerApplicationContext.CreateContext()

        Dim reportResult =
            From t In context.DataWorkspace.AmazingPieData.Tables
            Where t.Location.Location_Id = id
            Group By t.TableType.TableType_Id, t.TableType.DisplayOrder Into Group
            Order By DisplayOrder
            Select TableType_Id, Seats = Group.Sum(Function(t) t.TableSize)

        Return reportResult.Execute()
    End Using
End Function

C#
public object Get(string id) {
    using (ServerApplicationContext context = ServerApplicationContext.CreateContext()) {
        var reportResult = context.DataWorkspace.AmazingPieData.Tables
            .Where(t => t.Location.Location_Id == id)
            .GroupBy(g => new {
                g.TableType.TableType_Id
            })
            .Select(g => new {
                TableType_ID = g.Key.TableType_Id.Trim(),
                Seats = g.Sum(t => t.TableSize)
            });
        return reportResult.Execute();
    }
}

Evaluating the LINQ query

To understand how the LINQ query is working here:

• We instance the context inside a using block to assure the ServerApplicationContext is disposed.
• Using the typed context, we can access the DataWorkspace, and drill into the AmazingPieData entity and the Tables collection.      

Remember, Tables is the name of our SQL Table that contains the list of tables in the restaurant. It’s not the collection of SQL Tables.

• We pass the id parameter to filter Restaurant Tables by the Location_Id
• In order to get a grouping of the subset of properties, we need to create an anonymous type, and create the collection into g. The LINQ query will pass the query to the database, where the group by will be performed inside the database, avoiding all the data streaming to the middle tier for aggregation.
• By adding the DisplayOrder to the group by, we can use the TableType.DisplayOrder property to sort our TableTypes by a logical order

Order By Tip: I wanted to sort the graph by Bar, Lounge, Dining. Sorting by TableType would have created Bar, Lounge, Dining. The Lookup table for TableType contains the DisplayOrder for just this purpose. This is a common practice to assure your lists are displayed in a logical sequence such as (OrdrePlaced, OnHold, Picking, Shipped, Returned)

• With the GroupBy, we reduce the quantity of rows, with the anonymous return type, we reduce the quantity of columns
• Since LINQ queries defer their execution until they’re requested, which is when the WebAPI stack kicks in, outside of the Get(string id) method, we must call execute here to use the ServerApplicationContext before it’s disposed.

Reducing the resultset

With the above query, we’re returning just 3 rows, and two properties. Dramatically smaller than returning all the rows and attempting to summarize this on the client.

[{ "TableType_Id": "Bar", "Seats": 21 },
 { "TableType_Id": "Lounge", "Seats": 23 },
 { "TableType_Id": "Dining", "Seats": 48 }]

In the case of AmazingPie, the largest quantity of tables is 'BelSquare' which has 32 tables. However, when we start aggregating the number of guests for a given restaurant, we may be processing hundreds of thousands of records. Yet, we only need to display a dozen values. Using LINQ, the new LightSwitch Aggregates such as GroupBy, WebAPI and JSON we can leverage the processing power of each tier of the application, minimize the data sent across the internet and still provide rich visuals to our end users.

Testing our Report WebAPI

With the code in place, we’ll first evaluate the results with Fiddler. Unit testing makes it easy to dissect the data before we start connecting the HTML client with javascript.

1. Build your app, or launch it with F5  

2. In Fiddler, remembering your base URL, compose the request.
Here’s a visual mapping of what we’ve done so far:

Connecting our HTML Client Graph to the WebAPI Report

With our WebAPI written and tested, we can now replace our SampleData with the WebAPI call

• We’ll first need a variable to contain our URL we’ll utilize for our WebAPI Reports call
• We’ll then update the KendoChart to use WebAPI/JSON to retrieve the data

1. Rather than switch back to logical view, we can stay in the File view to open our Location_View.js which is located under the UserCode folder.    

2. Create the reportsAPI variable. It should look similar to this when complete:
http://localhost:22700/reports/Seating/SeaBallard
We’ll construct the url using the relative path, passing in the reports API path, report class name and the parameter based on the contentItem parameter passed into the SeatedGuests_render function.

3. Below the point where we append the locationSeatingChartContainer, add the following code.    

Note, the existing code is greyed for clarity 

// Create a <div> to contain our chart
var locationSeatingChartContainer = $('<div id="chartContainer" class="chart-wrapper"  style="width:600px"></div>';
locationSeatingChartContainer.appendTo($(element));

// http://localhost:22700/reports/Seating/SeaBallard
var reportsAPI = "../reports/Seating/" + contentItem.screen.Location.Location_Id;  

4. We can now comment out the data: attribute used for our sample data, and provide the transport:attribute which will fetch the data from the WebAP, using the reportsAPI URL.  

// Create a <div> to contain our chart
var locationSeatingChartContainer = $('<div id="chartContainer" class="chart-wrapper"  style="width:600px"></div>');
locationSeatingChartContainer.appendTo($(element));
 
// http://localhost:22700/reports/Seating/SeaBallard
var reportsAPI = "../reports/Seating/" + contentItem.screen.Location.Location_Id;
 
locationSeatingChartContainer.kendoChart({
    // set the theme
    theme: $(document).data("kendoSkin") || "black",
    // set the datasource to use our WebAPI reports controller
    dataSource: {
        //data: sampleData,
        transport: {
            read: {
                url: reportsAPI,
                dataType: "json"
            }
        }
    },
    title: {
        text: "Seats Per Location"

5. Run the app and see the results
The Details Page for BelSquare    

And the Graph of the tables for BelSquare

Wrapping it up

Whew. That was a lot. I did take some extra time to explain some of the nuances and troubleshooting techniques, which hopefully you’ll find valuable as these were the things I tripped over and will hopefully spare you some pain.

Using LightSwitch, WebAPI and the new ServerApplicaitonContext we were able to:

• Expose a WebAPI JSON Payload
• Write LINQ queries to aggregate results, processed on the data tier, returning just the data we need to display
• Provide fast, read-only data useful for graphs

Thanks,

Steve Lasker
Microsoft Program Manager
Visual Studio LightSwitch

http://blogs.msdn.com/SteveLasker