Sunday, April 15, 2012

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
    3. 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

    image


  2. Select the view uvSalesOrderDetail (Sales)

    image


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

    image


  4. The entity is created

    image


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

    image


  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.

    image


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

    image


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

    image


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

    image


  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.

    image


























Create the report




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

    image


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


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

    image


  4. The report design window is opened

    image


  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.

    image


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


    image


  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)

    image


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


    image


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


    image


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


  11. In the Summary Running select Group


    image


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


    image


  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.

    image


  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.

      image


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

      image


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

      image


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

      image


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

      image



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

    image


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

    image


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

    image


  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.





5 comments:

Anonymous said...

I try to show group data to detail . not header. but i can't .

Anonymous said...

I try to group at group footer but i can't.

Unknown said...

louis vuitton outlet, sac longchamp, ralph lauren pas cher, replica watches, louboutin outlet, louboutin shoes, christian louboutin outlet, prada outlet, ugg boots, longchamp pas cher, tiffany and co, louis vuitton, louboutin, air jordan pas cher, tory burch outlet, cheap oakley sunglasses, nike outlet, polo ralph lauren outlet, ray ban sunglasses, polo ralph lauren outlet, louis vuitton outlet, nike air max, replica watches, louis vuitton, air max, longchamp outlet, michael kors, oakley sunglasses, chanel handbags, nike free, nike roshe run, oakley sunglasses, burberry, tiffany jewelry, ray ban sunglasses, kate spade outlet, prada handbags, oakley sunglasses, nike air max, louis vuitton, louboutin pas cher, uggs on sale, ray ban sunglasses, oakley sunglasses, longchamp, jordan shoes, gucci outlet, ugg boots, nike free, longchamp outlet

Unknown said...

lancel, celine handbags, jimmy choo shoes, bottega veneta, nike roshe, asics running shoes, gucci, giuseppe zanotti, nike huarache, mcm handbags, herve leger, hollister, hollister, new balance, ray ban, p90x workout, soccer shoes, babyliss, longchamp, mac cosmetics, nike trainers, chi flat iron, mont blanc, vans, vans shoes, ghd, iphone cases, nike air max, hollister, ferragamo shoes, ralph lauren, louboutin, nike air max, beats by dre, valentino shoes, converse outlet, lululemon, north face outlet, instyler, soccer jerseys, birkin bag, insanity workout, baseball bats, north face outlet, abercrombie and fitch, timberland boots, reebok shoes, nfl jerseys, oakley, wedding dresses

Unknown said...

hollister, louis vuitton, canada goose outlet, moncler, pandora jewelry, swarovski, louis vuitton, bottes ugg, coach outlet, moncler, supra shoes, montre pas cher, moncler, ugg,uggs,uggs canada, ugg,ugg australia,ugg italia, canada goose, links of london, pandora jewelry, karen millen, doudoune canada goose, juicy couture outlet, moncler, pandora charms, marc jacobs, swarovski crystal, moncler, ugg pas cher, thomas sabo, louis vuitton, moncler outlet, moncler, canada goose, canada goose uk, canada goose outlet, ugg boots uk, juicy couture outlet, wedding dresses, moncler, canada goose, toms shoes, louis vuitton, replica watches, sac louis vuitton pas cher, pandora charms, canada goose