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.
   
2. In the Query design screen, rename the Query1 to qryProductsBySellStartDate
3. Add Single filter, Where SellStartDate Is Between @, Add New…
   
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
   
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…
   
3. In the Add new item dialog window, Select XtraReport Class, change the name to ReportProductBySellStartDate.cs, click Add
   
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
   
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.
   
8. Right click on the design surface (but not on the productid or name label), select Insert Band / Page Header
   
9. A new header band appears above the Detail section.
   
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
    
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
    
14. Double click in the text box next to the BeforePrint event, this generates code for this event
    
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..
   


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


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
   

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.