Monday, April 23, 2012

How to print the value of a LightSwitch query parameter on a report with XtraReports

Problem

Last week one of my customers wanted a report with the selected parameter values in the heading of the report, for instance a period with a start date and end date. Because not all data necessarily contains values for the exact same range, like the exact start or end date, it is not always obvious which range the user has selected for the report.

Solution

The query parameters collection is available to the report code on the server side. For each section of the report, like a page header, group header or detail section, there are events that can be triggered, most notably ‘Beforeprint’. In the event code we can read the parameter data and copy that into a label on the report section.

Step by step demonstration

Prerequisites

This example was made with the SQL Server 2008 AdventureWorks database. A LightSwitch project called AdventureWorksLS was created and the Datasource AdventureWorksData has been added to the project. We use the Products table which has a Date field called ‘SellStartDate’. Based on this field we create a report preview that shows products within a certain period of start dates.

Create a query with parameters
  1. Add a new query to the Products entity, right click on Products, Add Query.
    image
  2. In the Query design screen, rename the Query1 to qryProductsBySellStartDate
  3. Add Single filter, Where SellStartDate Is Between @, Add New…
    image
  4. Create Parameter StartDate of type Date (specifically Date and not DateTime)
  5. Continue the filter after ‘and’, @, Add New…
  6. Create parameter EndDate of type Date, the final query design looks like this
    image
  7. Build the project
Create a report based on the query
  1. In project explorer, switch to file view.
  2. right click on the Server project, select Add / New Item…
    image
  3. In the Add new item dialog window, Select XtraReport Class, change the name to ReportProductBySellStartDate.cs, click Add
    image
  4. In the report desing panel, click on the smart tag in the upper left corner, to open the Report Tasks menu, select datasource qryProductsBySellStartDate
    image
  5. The datasource is added and is called ‘lightSwitchDataSource1’ (this name will be used in the code).
  6. Optionally
    1. in the Report Tasks menu select ReportUnit: TenthsOfAMillimeter (can also be set in the reports properties)
    2. in the Properties panel, with the ReportProductBySellStartDate, set paperKind to A4.
  7. From the Field List, drag the ProductId and Name field to the Detail section of the report, reduce the height of the Detail section.
    image
  8. Right click on the design surface (but not on the productid or name label), select Insert Band / Page Header
    image
  9. A new header band appears above the Detail section.
    image
  10. From the Toolbox, DX.11.1: report controls, drag the XRlabel onto the PageHeader band. In the properties panel of the xrLabel, change the (Name) to lblStart
    image
  11. Drag another xrlabel onto the Pageheader and rename this to lblEnd.
  12. reduce the height of the Pageheader;
  13. Select the PageHeader band, in the properties pane, click the events button
    image
  14. Double click in the text box next to the BeforePrint event, this generates code for this event
    image
  15. In the code we can now set the text properties of the 2 labels to the value of the 1st and 2nd parameter of the lightSwitchDataSource1 object. The parameter values are of type Date, we cast the value to DateTime and then format this to a short date string, which is then put into the Text property of the label.
    private void PageHeader_BeforePrint(object sender, System.Drawing.Printing.PrintEventArgs e)
    {
        this.lblStart.Text = ((DateTime) lightSwitchDataSource1.QueryParameters["StartDate"].Value).ToShortDateString();
        this.lblEnd.Text = ((DateTime)lightSwitchDataSource1.QueryParameters["EndDate"].Value).ToShortDateString();
    }

  16. Build the solution


Create a report preview screen in the LS project.


  1. in the solution explorer switch to Logical view.

  2. right click on Screens, Add Screen..
    image

  3. In the Add New Screen dialog, Select a screen template Report Preview Screen, change the screen name to ReportProductsBySellStartDate, click OK
    image

  4. In the AdventureWorks Screen design panel, click on Write Code, select ReportProductsBySellStartDate_Activated, change the name of the Server report to ReportProductBySellStartDate (beware this is without an ‘s’ after Product, excuse me for the lousy names)

  5. Save all and build.

















Debug and test the report


  1. Debug

  2. In the menu select Report products by Sell Start Date

  3. In the Report Products tab, in the StartDate, select the data, click on the month name, this shows a list of months for the current year, click on the year, this shows the years, scroll back, select 1998, select januari, select day 1.

  4. In the EndDate select 31 december 2000. Click Submit. the report preview is shwon with the parameter values in the page header
    image





Conclusion


To give the user a better understanding of the reports he printed, we can easily add additional data on the report, such as the query parameter data.

3 comments:

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