It’s only a matter of time before a full-featured reporting extension is available exclusively for LightSwitch. When it’s available, you can bet that I will be one of the first to get it – even if I have to pay for it. Until the, I need reporting functionality now, not later. This article is a walk through of how I implemented a reporting solution in LightSwitch using Telerik reporting tools.

I am a fan of Telerik products. Their tools have provided me a considerable return on time and money. That being said, I wanted to try and leverage my knowledge of Telerik reporting tools to create a solution that would work for me in LightSwitch.

With a little trial and error, and a lot of Hail Mary deployments, I managed to craft a solution that works perfect for my requirements. The solution involves the following ingredients;

• 1 LightSwitch application (of course),
• 1 custom Silverlight control library project containing a User Control with a Telerik Report Viewer control on it,
• 1 custom Silverlight Class Library project with a Telerik report class,
• 1 WCF Service project,
• A Windows 2008 server with IIS, And
• Some patience and a willingness to laugh and find humour in the mistakes that you will make!

Here is how that looks…

For development I have a  Windows Server 2008 box sitting in a room that we now use as a “server” room in our house, which doubles as a craft room (I am the only male in our house, so please, cut me some slack). I configured the server with the appropriate roles so that I can deploy web applications and WCF services.

On the same server I also installed SQL Server 2008 R2 Express as an instance named SQLExpress. Express was initially installed because I thought I would use the SQLExpress instance for the deployed application database. However I later changed my mind and ended up installing the full blown version, along with all the fun other stuff, like SSRS.

I am not going to get into how to set up and configure the server. That is something for you to figure out

For the LightSwitch application itself, I created a simple little invoicing solution that my wife can use for her photography business…

Functionality includes providing customers with quotes (note the Print Quote button at the top of the screen)…

By selecting a quote from the Quotes list, and clicking the Print Quote button, a quote report is generated and viewed in a Telerik Report viewer within LightSwitch…

First, an overview of the approach taken…

Pretty simple really (in retrospect anyway). I created a baseline LightSwitch application complete with data and screens. I then deployed the baseline to a Windows 2008 server with IIS and SQL 2008 on it. I then used the deployed application to create some data for the database.

Next I created a Silverlight library project where I added a Telerik report. The process used to create the report used the data from the deployed database on the server. By the way, the report datasource is a stored procedure that the report passes a parameter to.

With the report created, I then created another Silverlight control library. A Silverlight User Control was then created and a Telerik Report Viewer control was added to it. Additionally, a textbox control was added. The textbox control was defined with binding attributes that would bind the text value of the control to a parameter value from one of the LightSwitch screens. Code was crafted in the user control to then use the textbox text value as a parameter for the Telerik report.

Keeping in the spirit of using WCF with Silverlight, a WCF Service project was added to the solution. A service (.svc) was added to the projected and crafted specifically for Telerik reporting. Back in the custom Silverlight user control, the Telerik Report viewer has defined to use the WCF service, along with the definition that told the viewer what report (class) to use.

Finally, the custom Silverlight control was added to a screen in LightSwitch. When running, the user is presented with a list of customer quotes. When a Print Quote button is clicked, a quote id is passed as a parameter to the screen containing the custom control. The quote id screen parameter is bound to the textbox on the custom control. The id is then applied to the report parameter; which is called via the WCF service.

Starting Point

I started out by creating a baseline LightSwitch application with some data and screens. I knew what I needed for my quote report, so I made sure that I had the necessary data entities in the LightSwitch application. Doing so would allow me to run the application and add some data for use during  the report development.

Because of the data centric approach to development with LightSwitch, I abstracted some entities into very context specific tables. For example, there are three types of quote details that can be added to a quote. Instead of trying to build one data entity table for quote details, I created three distinct tables.

The following is an example of the table used for Materials for a quote. Note the other tables shown in the solution explorer…

Using my baseline model of data and screens, I published the application (as a Desktop Application, with IIS for the services) to my web server. I then ran the application and added some test data for purpose of using during the development of a quote report.

The Report

The report required is one that should display all the details of a quote. Considering that there are three distinct quote detail tables, something needs to be done to consolidate all quote details into a single source of data for the report to use. To do this, a stored procedure was created on the SQL server database used for the (published) application.

Here is the stored procedure created for the purpose of the report…

Note the @quote_id parameter in the stored procedure.

Next, a blank Silverlight Class Library project is created and a Telerik Report is added to the project. I called this project MyCompany.Reports. Using the report designer, a SQL Data Source was used and connected to the stored procedure above. A report parameter is created, and the data source is updated to map the report parameter to the data source stored procedure parameter.

The idea of the parameters is this… when the report is used by the application, the application will pass a quote id as a report parameter. The report parameter will then pass the quote id as a data source (the stored procedure) parameter.

Here is a screen shot of the report designer.

…and this shows the report parameters dialog…

…and this is the dialog showing the parameter in the data source…

WCF Service

The next step was to create a WCF service for use by the Telerik Report Viewer control that will be created later. This WCF service is very straight forward. I copied what Telerik recommended for creating this service.

A WCF Service Library project was added to the solution. The default service files were deleted and new one named ReportService.svc was added. Here is the code for that svc file…

<%@ServiceHost Service="Telerik.Reporting.Service.ReportService, Telerik.Reporting.Service, Version=5.0.11.316, Culture=neutral, PublicKeyToken=A9D7983DFCC261BE" %>

Next, in the Web.config file, copy the connection string created and used in report project and paste it into this Web.config file.

Here is the web.config file, updated with the necessary Telerik information, and the connection string.

<?xml version="1.0"?>
<configuration>
  <connectionStrings>
    <add name="MyCompany.Reports.My.MySettings.MyCompanyDb" connectionString="Data Source=TESERVER01\SQLEXPRESS;Initial Catalog=MyCompanyDb;Persist Security Info=True;User ID=MyCompanyDbUser;Password=MyCompanyDbUser" providerName="System.Data.SqlClient"/>
  </connectionStrings>
  <system.web>
    <compilation debug="true" strict="false" explicit="true" targetFramework="4.0" />
  </system.web>
  <system.serviceModel>
    <serviceHostingEnvironment aspNetCompatibilityEnabled="true" />
    <services>
      <service name="Telerik.Reporting.Service.ReportService"
               behaviorConfiguration="ReportServiceBehavior">

        <endpoint address=""
                  binding="basicHttpBinding"
                  contract="Telerik.Reporting.Service.IReportService">
          <identity>
            <dns value="localhost" />
          </identity>
        </endpoint>

        <endpoint address="resources"
                  binding="webHttpBinding"
                  behaviorConfiguration="WebBehavior"
                  contract="Telerik.Reporting.Service.IResourceService"/>

        <endpoint address="mex"
                  binding="mexHttpBinding"
                  contract="IMetadataExchange" />
      </service>
    </services>

    <behaviors>
      <serviceBehaviors>
        <behavior name="ReportServiceBehavior">
          <serviceMetadata httpGetEnabled="true" />
          <serviceDebug includeExceptionDetailInFaults="false" />
        </behavior>
      </serviceBehaviors>

      <endpointBehaviors>
        <behavior name="WebBehavior">
          <webHttp />
        </behavior>
      </endpointBehaviors>

    </behaviors>
  </system.serviceModel>
  <system.webServer>
    <modules runAllManagedModulesForAllRequests="true"/>
  </system.webServer>

</configuration>

Finally, a reference to the MyCompany.Reports project was added to the WCF Service project.

I also added the Telerik.Reporting, Telerik.Reporting.Service, and Telerik.Xaml.Rendering assemblies to the project.

Finally, the WCF service is published to the server. Here is the profile I created for publishing the service.

I used port 8000 so that I could install it as it’s own application on the server. Once deployed, logged into IIS administration and created a new application pool, using .Net 4, and applied to the new WCF application.

I then tested it to make sure it worked.

Yup, it works!

The Report Viewer Control

The next task was to create a Silverlight control that I could use in the LightSwitch application. This control will be responsible for displaying the report via a report viewer control.

A blank Silverlight Control Library project was created. I give the project a name of MyCompany.Controls.

A new User Control with a name of QuoteViewer.xaml was added to the project. Here is the XAML for this control…

<UserControl x:Class="MyCompany.Controls.QuoteViewer"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
    xmlns:TelerikReportViewer="clr-namespace:Telerik.ReportViewer.Silverlight;assembly=Telerik.ReportViewer.Silverlight"
    mc:Ignorable="d"
    Background="Transparent">
    <Grid x:Name="LayoutRoot">
        <Border Margin="1 2 1 1" />
        <StackPanel>
            <TextBox x:Name="MyQuoteID" Text="{Binding Screen.QuoteParameter}" Visibility="Collapsed" />
            <TelerikReportViewer:ReportViewer
                x:Name="MyQuoteViewer"
                ReportServiceUri="http://teserver01:8000/ReportService.svc"
                Report="MyCompany.Reports.Quote001, MyCompany.Reports"
                 />
        </StackPanel>
    </Grid>
</UserControl>

In the above, the ReportServiceUri is the WCF Service that the report viewer is going to call, and the Report value is the namespace and class of the report to use (which is a referenced assembly in the WCF service project).

Also note the MyQuoteID textbox and how the Text attribute value is set to bind to the QuoteParameter parameter on the screen that uses the control.

Here is the code behind for the XAML control…

Imports Telerik.ReportViewer.Silverlight

Partial Public Class QuoteViewer
    Inherits UserControl

    Public Sub New()
        InitializeComponent()
        AddHandler MyQuoteViewer.RenderBegin, AddressOf MyQuoteView_RenderBegin
    End Sub

    Private Sub MyQuoteView_RenderBegin(ByVal sender As Object, args As RenderBeginEventArgs)
        args.ParameterValues("quoteid") = CInt(MyQuoteID.Text)
    End Sub
End Class

The RenderBegin handler of the report viewer control is where the quote id from the MyQuoteID textbox will be assigned to the quoteid parameter of the report.

Adding the Control to LightSwitch

Back in the screen where you want to use the report viewer control, add the control as a Custom Control to the screen…

Next, a data item was added to the screen and defined as a parameter. This data item was named QuoteParameter.  If you recall in the creation of the report viewer control, the QuoteParameter data item is what is being bound to the textbox in the XAML.

In my application the screen above is named QuotePrint. I also have a screen that shows a list of quotes. I added a button to the screen command bar of the quote list screen, and updated the code for that button to open the QuotePrint screen, using the selected quote id as the QuoteParameter parameter for the screen.

E.g…

Namespace LightSwitchApplication

    Public Class QuoteHeadersListDetail

        Private Sub PrintQuote_Execute()
            Application.ShowQuotePrint(QuoteHeaders.SelectedItem.Id)
        End Sub

        Private Sub PrintQuote_CanExecute(ByRef result As Boolean)
            result = QuoteHeaders.SelectedItem IsNot Nothing
        End Sub
    End Class

End Namespace

And that was all that was needed… sort of.

Most of my challenges came from trying to make sure all the necessary assemblies were added in the right places. For that, I will leave for you to figure out. I don’t have the will power to write a post long enough to list all the possible scenarios that might cause you problems. Besides, how else would you learn

Cheers!