Apr 28

Written by: Michael Washington
4/28/2011 6:51 PM  RssIcon

image

In many LightSwitch applications, you will desire the ability to display aggregated data and percentages. While the article at this link explains a method that will work 100% of the time, with the best performance, it requires the creation of an additional project. The method described here, is easier to use, yet, it has limitations (for example it will not allow you to use GroupBy).

The Call Log Application

image

First, we create a LightSwitch application with a single table called PhoneCall.

image

We click on CallType in the table, and in the Properties for the field, select Choice List.

image

We enter options for the field. This will automatically cause a dropdown list to appear for the field when we create a screen for this table.

image

In the Solution Explorer, we right-click on the Screen folder and select Add Screen.

image

We select a List and Details Screen.

image

This creates a screen for us.

image

We hit F5 to run the application.

image

We are able to enter data into our simple Call Log application.

Display Aggregate Data And Percentages

We now desire to display the number of calls by call type, and their percentage of the total calls.

When you use the LightSwitch screen designer, it may seem confusing at first. It is actually very simple, the “stuff” (the data and the properties) is listed on the left side of the screen designer, the right-hand side of the screen is used to display the “stuff”. The right-hand side of the screen displays the data and elements in a object tree.

When we design a LightSwitch screen, we first make sure we have the “stuff” we need in the column on the left-hand side. We then indicate where in the object tree we want to display the “stuff”.

To display the count of, for example, just the Sales, and the percentage of calls that Sales represents, we need to add two properties to the left-hand side of the screen hold the values. We then calculate the values for those properties in the “_InitializeDataWorkspace” method for the screen. 

Lastly, we will place labels to display those properties in the object tree designer on the right-hand side of the screen.

image

On the screen designer, we click the Add Data Item button.

image

We add a property for CountOfSales, and a property for PercentOfSalesCalls.

image

The properties will show on the left-hand side of the screen.

image

The existing collection of PhoneCalls has paging turned on so it cannot be used for calculations because the calculations would only be for the current page and we want the calculations to cover all records.

Add an additional instance of the PhoneCalls collection and call it PhoneCallsAll.

image

In the Properties for the PhoneCallsAll collection, uncheck Support paging.

image

We select Write Code, then the “_InitializeDataWorkspace” method.

We insert code into the method:

 

    partial void PhoneCallsListDetail_InitializeDataWorkspace(List<IDataService> saveChangesTo)
    {
        // Write your code here.
        int TotalCalls = PhoneCallsAll.Count();
        double SalesCalls = (from calls in PhoneCallsAll
                                where calls.CallType == "Sales"
                                select calls).Count();
        PercentOfSalesCalls = (SalesCalls / TotalCalls).ToString("p");
        CountOfSales = SalesCalls.ToString();
    }

 

We switch back to the screen designer (note, if you were to try to run the application while a code window has the focus in Visual Studio, it will not run).

image

We right-click on the Phone Calls List Detail section and select Add Group.

image

Click on the newly added group, and drag it under the the List Column group.

image

Change it to Columns layout.

image

In the Properties for the group, change it to Left and Top.

image

Add Count Of Sales.

image

Change it to a Label.

image

Add Percent Of Sales Calls, and also change it to a Label.

image

Hit F5 to run the application. You will see the count of sales calls and their percentage of total calls.

You must hit the Refresh button to refresh the values after adding new calls.

image

Add the additional properties shown above.

Alter the “_InitializeDataWorkspace” method to the following:

 

        partial void PhoneCallsListDetail_InitializeDataWorkspace(List<IDataService> saveChangesTo)
        {
            // Write your code here.
            int TotalCalls = PhoneCalls.Count();
            double SalesCalls = (from calls in PhoneCallsAll
                                 where calls.CallType == "Sales"
                                 select calls).Count();
            double ServiceCalls = (from calls in PhoneCallsAll
                                   where calls.CallType == "Service"
                                   select calls).Count();
            double OtherCalls = (from calls in PhoneCallsAll
                                 where calls.CallType == "Other"
                                 select calls).Count();
            PercentOfSalesCalls = (SalesCalls / TotalCalls).ToString("p");
            PercentOfServiceCalls = (ServiceCalls / TotalCalls).ToString("p");
            PercentOfOtherCalls = (OtherCalls / TotalCalls).ToString("p");
            CountOfSales = SalesCalls.ToString();
            CountOfService = ServiceCalls.ToString();
            CountOfOther = OtherCalls.ToString();
        }

 

image

Add two new groups to display the additional properties.

Set the properties for each group to match the first group (left and top).

image

Hit F5 to run the application.

The Application is complete.

Download

You can download the code on the Downloads page:

http://lightswitchhelpwebsite.com/Downloads.aspx

6 comment(s) so far...


Gravatar

Re: It Is Easy To Display Counts And Percentages In LightSwitch

I very like your article especially for buttons of previous and next record. The code help me a lot. Is it possible to add code for "CurrentRecord of TotalRecords" in between to show more detail on the screen?
Thanks.

By Wanda Zeng on   5/8/2013 4:04 PM
Gravatar

Re: It Is Easy To Display Counts And Percentages In LightSwitch

@Wanda Zeng - Yes it is possible but I have no examples, sorry.

By Michael Washington on   5/8/2013 5:21 PM
Gravatar

Re: It Is Easy To Display Counts And Percentages In LightSwitch

Is there a way to have one count of and have it linked to the PhoneCall table using a drop down list for call type? This way you could drop down the combo box and choose sales, service, or other? This would allow us to expand the call type in the table later on and not have to add more code.

I only ask because I have a similar problem I'm working on where I want to calculate the results from a query that have the same drop down type options and I think this solution might help me.

Thanks!

Russ

By Russ Landry on   6/25/2013 8:52 PM
Gravatar

Re: It Is Easy To Display Counts And Percentages In LightSwitch

@Russ Landry - Please direct any technical questions to the official lightSwitch forums: http://social.msdn.microsoft.com/Forums/vstudio/en-US/home?forum=lightswitch

By Michael Washington on   6/25/2013 8:53 PM
Gravatar

Re: It Is Easy To Display Counts And Percentages In LightSwitch

I tried that http://social.msdn.microsoft.com/Forums/en-US/b93e00a8-c697-48e4-b864-f6c733a76820/lightswitch-vb-question-about-calculations-on-query-results they weren't much help.

Thanks for the quick response, I'll keep researching.

Thanks again,

Russ

By Russ Landry on   6/27/2013 4:15 AM
Gravatar

Re: It Is Easy To Display Counts And Percentages In LightSwitch

@CT - Yes there is a performance impact. With a large record set I would use WECF RIA Services.

By Michael Washington on   7/14/2014 4:07 AM

Your name:
Gravatar Preview
Your email:
(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:
Security Code
CAPTCHA image
Enter the code shown above in the box below
Add Comment   Cancel 
Microsoft Visual Studio is a registered trademark of Microsoft Corporation / LightSwitch is a registered trademark of Microsoft Corporation