Create group summary report with XtraReports for Lightswitch

Problem

For a selected period I need to show/print a report with a summary per category and with a grand total. The dates to select from are stored in the detail record, but no detail should be shown.

Solution

Quick and dirty: create a report with a detail section that will be hidden, create a group header with the totals, add a report footer with the grand totals.
The report uses a LS Query based on the entity: 2 parameters from date and to date. To facilitate gathering data from several tables, I will create a view that gathers de detailed data.
In this solution I will not use WCF RIA services, which could be a better solution, because the data could be filtered on the SQL server side. My quick and dirty solution will extract all details and group these details.

Prerequisites

This example was made with the SQL Server 2008 AdventureWorks database. A LightSwitch project called AdventureWorksLS. The Datasource AdventureWorksData has been added to the project.

SQL server view

1. On the SQL server database I will create view that contains the necessary detailed information to create a detail of each product sold. The orderdetails table contains the base information, other tables are joined to obtain product category data
1. OrderDate to select the period in the entity query
2. ProductCategory Id and Name to  select on a category in the entity query

SELECT ISNULL(a.SalesOrderDetailID, -1) AS SalesOrderDetailID






    , CAST(b.OrderDate AS DATE) AS OrderDate






    , CAST( e.ProductCategoryID AS Int) AS ProductCategoryID






    , CAST(e.Name AS nvarchar(50)) AS ProductCategoryName






    , CAST(LineTotal AS Decimal(12,2)) AS LineTotal






FROM Sales.SalesOrderDetail AS a






INNER JOIN Sales.SalesOrderHeader AS b ON b.SalesOrderID = a.SalesOrderID






INNER JOIN Production.Product AS c ON a.ProductID = c.ProductID






INNER JOIN Production.ProductSubCategory AS d ON c.ProductSubcategoryID = d.ProductSubcategoryID






INNER JOIN Production.ProductCategory AS e ON e.ProductCategoryID = d.ProductCategoryID











2. This view will be created as Sales.uvSalesOrderDetail



3. The ISNULL function and CAST statements are added to prevent LightSwitch from interpreting every non nullable field as part of the primary key, My primary key is SalesOrderDetailId, that is way I used the ISNULL function. It is not beautiful but it works.

The entity and the filter query

1. To add the view, right click on the Data Source AdventureWorksData, select Update Datasource
   
   



2. Select the view uvSalesOrderDetail (Sales)
   
   



3. The inferred key warning comes up, Select continue, because the view was constructed with ISNULL and Cast expressions to set this straight.
   
   



4. The entity is created
   
   



5. Add a new query to the entity, right click on the entity uvSalesOrderDetails
   
   



6. Rename ‘Query1’ to ‘qrySalesOrderDetails’



7. In the query design window, click Add Filter (single)



8. Create the filter ‘Where’ ‘OrderDate’ ‘Is Between’. 2 parameters have to be created now.
   
   



9. Click on the parameter type dropdown, select @ Parameter
   
   



10. Click the textbox for the parameter value, select Add New…
    
    



11. In the parameter name textbox replace the name OrderDate by FromDate
    
    



12. Now add the new parameter type in the ‘Is between … And ‘ parameter type dropdown, select @ parameter. In the textbox for the parameter value, select Add New… In the parameter name textbox replace the name by ToDate.
    
    

Create the report

1. In the Solution explorer change the view from Logical to File view.
   
   



2. In the solution explorer, right click on the Server project, select Add / New Item… 
   



3. Select the XtraReports V11.1, give the report object a name: RptCategoryProductSales
   
   



4. The report design window is opened
   
   



5. Click on the smart tag on the upper left corner, open the DataSource dropdown, select the Query uvSalesOrderDetails and set the ReportUnit to TenthsOfAMillimeter.
   
   



6. In the Group and Sort panel, click on Add a Group, select ProductCategoryName
   
   
   



7. The group is added with a group header by default. I will add the total sales per category in this header, As you can see, to add a footer, the Show Footer checkbox can be checked (not in this example)
   
   



8. From the Field list drag the ProductCategoryName and the LineTotal to the GroupHeader1 on the design surface space
   
   
   



9. The LineTotal needs to be a sum, not a single value. Click on the smart tag of the LineTotal label.
   
   
   



10. Click on the Summary ellipsis button … The summary editor windows opens.



11. In the Summary Running select Group
    
    
    



12. Click on the Format string ellipsis button … In the Category select Number. Click on the Custom tab
    
    
    



13. Click OK twice to close both dialogs.



14. To hide the Detail section, there are 2 possibilities, either change the height of the detail section to 0 or set the Visibility property to False.
    
    



15. Click on Save All. Build the solution.

Create a report preview screen

1. In Solution Explorer switch back to logical view



2. To use XtraReports in the LightSwitch project we need to add the XtraReports extension to the project. And add a DataSource to the WCF RIA services XtraReports.





1. Double click on the Project Properties. In the Extensions tab, check the DevExpress XtraReports extension.
   
   



2. right click on Data sources, select Add Data Source…
   
   



3. In the Attach Data Source Wizard, select WCF RIA Services, click Next.
   
   



4. On the next page, select the XtraReportService, click Next.
   
   



5. On the next page, Check Entities, click Finish.
   
   





3. In the project right click on Screens, select Add Screen…
   
   



4. In the Add screen dialog, under Select a screen template, select Report Preview Screen. In the Screen Name, type ReportCategoryProductSales. Click OK.
   
   



5. In the designer panel, click on Write Code, Select ReportProducCategorySales_Activated.
   
   



6. In the code window, overwrite the report name with the name of the Server report class: RptCategoryProductSales
   
   
   
   
   
   

   
   

   partial void ReportCategoryProductSales_Activated()

   
   
   
   

   {

   
   
   
   

       // Assign the name of the report, which you want to preview in this screen.

   
   
   
   

       this.ReportTypeName = "LightSwitchApplication.RptCategoryProductSales";

   
   
   
   

   }

   
   

   
   
   
   



7. Save all, Debug the project.