Create .rdlc Report in Lightswitch Html Client Using Visual Studio 2013

Introduction

This article shows how to create .rdlc reports in Lightswitch HTML Client using Visual Studio 2013. This article is inspired by Michael Washington article of Creating Reports in Lightswitch HTML Client. In his article, he generated the report data by WCF RIA services using Visual Studio 2012. However, creating reports for Lightswitch project in Visual Studio 2013 is easier using the ServerApplicationContext and some inbuilt methods that come with Visual Studio. Some of the ideas used in this article came from Michael Washington’s earlier mentioned article, so I give him credit for this.

What You Need

Visual Studio 2013 to run this code.

The final report will look like this:

A. Design the Tables

The report we will be creating in this article came from an assumed LOB data of Stocks taking. There are two tables involved. The first one is the Products table and contains ProductName field only. The second is the ProductStocks and contains the field as shown below:

B. Create the Report Folders in the Server Projects

Create the folders shown in the diagram below on the server Project.

1. Right click on the ReportSource folder. Click New Item and Select Dataset from the Data menu of the New Item and name it as ReportSource.xsd or whatever you like.

2. Drag a DataTable from the Toolbox unto the .xsd designer and give it a name. Then add the required fields as columns for the reports you want to create as shown below:

   

   Note: The .xsd file will generate classes in the ReportSource.Designer.vb or ReportSource.Designer.cs file and we will be using just ProductSummaryDataTable class, the ProductSummaryRow class and AddProductSummaryRow methods. Shown below is the code generated by the Dataset designer.

   Partial Public Class ProductSummaryDataTable
        Inherits Global.System.Data.TypedTableBase(Of ProductSummaryRow)
      .........
   
   Public Overloads Function AddProductSummaryRow(ByVal ProductName As String,
      ByVal TotalStock As Integer, ByVal TotalQuantitySold As Integer,
      ByVal TotalQuantityReturned As Integer, ByVal Balance as Integer,
      ByVal Status As String) As ProductSummaryRow
      .......
   

3. Add an .rdlc report into the Reports folder. Do not use the Report Wizard.

4. From the report Toolbox, add a table to the report designer, a Dataset Properties dialog will come on the screen. Give a name for the Dataset (1) and select the ReportSource.xsd dataset in the Data source box (2). Then select the ProductSummary table in the available datasets box (3). The columns of the table will be shown in the Fields: box (4) as shown below. Click the OK button.

   

5. Design your report, and add as many features as you want as shown below:

   

   1- Table report; 2- Bar Chart Report; 3- Pie Chart Report

C. Include the .rdlc File in the Build

1. Right click the Project file and select Edit Project file.

2. Move your cursor toward the end of the file that came up on the screen. You will see a <Build File Include=”…./> section. Copy and paste a section under another and add the location of your .rdlc file in the server to it as shown below:

   <_BuidFile Include="LSReportApp.Server\default.aspx">
     <SubFolder>
     </SubFolder>
     <PublishType>
     </PublishType>
   </_BuildFile>
   
   <_BuidFile Include="LSReportApp.Server\Reports\ProductSummaryReport.rdlc">
     <SubFolder>
     </SubFolder>
      <PublishType>
     </PublishType>
   </_BuildFile>
   

   Save the file and close it. Then right click the Project file again and select Reload Project File.

D. Create Report's .aspx Page & Write Code to Generate Report Records

1. Right click the ReportAspx folder and add a web form; name it ProductStockSummary.aspx.

2. Add a ReportViewer Control from the toolbox into the <div/> element and name it ProductStockReportViewer. Set the necessary properties you need.

3. Add a ScriptManager below the <div/> element.

Note: The .aspx.desiner.vb or .cs might ask you to include the file properties of the reportviewer file. Just open the prompt and click the files to add. This will add the necessary assemblies required by the reportviewer and the scriptmanager to work.

1. Right click on the aspx page and select View Code.

2. Follow the pattern of the code shown below:

Imports Microsoft.Reporting.WebForms
Imports ReportSource

'In the PageLoad Event
Public Sub Page_Load(ByVal Sender As Object, ByVal e As EventArgs) Handles Me.Load
 If Not IsPostBack() Then
  ShowReport()
 End If
End Sub

'In the ShowReport Method, Load the .rdlc report and Load the report data
Private Sub ShowReport()
'Load the .rdlc report as a local report
 Me.ProductStockReportViewer.ProcessingMode = ProcessingMode.Local
 Me.ProductStockReportViewer.LocalReport.ReportPath = _
                     Server.MapPath("~/ProductSummaryReport.rdlc")

'Load the report data
 Me.ProductStockReportViewer.LocalReport.DataSources.Add( _
    New ReportDataSource("ProductSummaryDataSet", LoadDataList()))

'The LoadDataList Method is where you use ServerApplicationContext to access your
'product stock table and process your summary for the report. Then add the summary data
'to the ProductSummaryRow using the AddProductSummaryRow() method
'of the ProductSummaryDataTable.

Private Function LoadDataList() As List(Of ProductSummaryRow)
 Using ctx As ServerApplicationContext = ServerApplicationContext.CreateContext

'Get the Product Stock Query as IDataServiceQueryable.
'This makes fetching of record very fast
Dim pStock As IDataServiceQueryable(Of ProductStock) = _
                        ctx.DataWorkspace.ApplicationData.ProductStocks

 If pStock IsNot Nothing AndAlso pStock.Count > 0 Then

'Get All the product names as a list of String
 Dim ProductNameList As List(Of String) = _
ctx.DataWorkspace.ApplicationData.Products.Select_
        (Function(a) a.ProductName).Execute().ToList()
 If ProductNameList IsNot Nothing AndAlso ProductNameList.Count > 0 Then
  Dim ProductSummaryList As New List(Of ProductSummaryRow)

  'Iterate through the ProductName List
    For Each ProductName In ProductNameList

   'Instantiate the summary variables
    Dim Product As String = Nothing
    Dim TotalStock As Integer = 0
    Dim TotalQuantitySold As Integer = 0
    Dim TotalQuantityRtd As Integer = 0
    Dim StockBalance As Integer = 0
    Dim StockStatus As String = Nothing

    'Set the variables
    Product = productName
    TotalStock = pStock.Sum(Function(a) a.Product.ProductName.Trim = productName.Trim _
                  And a.QuantityInStock)
    TotalQuantitySold = pStock.Sum(Function(a) a.ProductName.Trim = productName.Trim _
                  And a.QuantitySold)
    TotalQuantityRtd = pStock.Sum(Function(a) a.ProductName.Trim = productName.Trim _
                  And a.QuantityReturned)
    StockBalance = TotalStock - TotalQuantitySold + TotalQuantityRtd
    If StockBalance >= 2000 Then
     StockStatus = "Active"
    ElseIf StockBalance > 1000 And StockBalance  < 2000 Then
     StockStatus = "Reorder Stock"
    Else
     StockStatus = "Critical Stock"
    End If

    'Add the summary variables to the data-row of the table in the dataset
    Dim SummaryRow As ProductSummaryRow = _
           New ProductSummaryDataTable().AddProductSummaryRow( _
     Product, TotalStock, TotalQuantitySold, TotalQuantityRtd, StockBalance, StockStatus)
    If Not SummaryRow.HasErrors Then
     ProductSummaryList.Add(SummaryRow)
    End If
  Next
     If ProductSummaryList.Count>0
    Return ProductSummaryList
 End If
End If
   End If
 End Using
 Return Nothing
 End Function

Note: The code for generating the report summary could be:

1. A separate class that generates a summary record into a List
2. A separate Controller class that generates a summary record into a List
3. A WCF RIA Service that generates a summary record

Therefore, you can create a separate class with method like GetProductSummary and call this method within the LoadDataList function and then assign the summary variables to the AddProductSummaryRow method of the DataTable.

E. View Your Report

1. Create screens for the Products table and add some records to the Products table.
2. Create screens for the ProductStock table and add some records to the table.
3. On the browse screen of the ProductStock, create a new tab row Layout under the Product Stock Row Layout and add custom control to the row layout as shown below:

   

4. Click the Edit Render Code on the Custom Control Properties and write the code shown below into the render execute function:

   myapp.BrowseProductStocks.ScreenContent_render = function (element, contentItem){
     //Show Loading Message
     var HtmlContent = $("<div></div>").html("<object width='800px' height='800px'	
   			data='../reportsAspx/ProductStockSummary.aspx' />");
     HtmlContent.appendTo($(element));
   };

Run your app and browse to the report page. You can export report as PDF, Word or Excel as shown below:

Acknowledgement

I want to acknowledge the articles of Micheal Worshinton for inspiring me to write this article, Beth Massy for her various articles on 'Sharing the goodness" and the Lightswitch community and forum for the various answers to questions asked on the forum.

History

• 3^rd August, 2014: Initial version