XtraReports for LightSwitch does not group on Date fields – convert date to string makes it work

This week I had to create a complex report with 3 group levels and further sorting on 4 detail fields. The problem was that the 3^rd group field was a date field, and the report never grouped on the date field, but created a new group with the same data field with each detail line. To exclude an obvious mistake: The dates do not contain any time data, they are purely dates.

E.g In the Northwind database you have a Categories, Products, Sales. The report would be grouped on Category name, Product name, Salesdate. The detail would be sorted on 3 other numerical fields.

The project is written in VS 2010 with LightSwitch 2011 and DevExpress XtraReports 11.1.

This is the result I would like to see

• Beverages
• Chai
• 2010-01-12
• 12,00 13,20 10,00
• 12,00 15,01 12,00
• 13,00 13,25 12,12
• 2010-01-13
• …

The problem was that the report never came out like that, instead

This is what I saw in the XtraReports print preview

• Beverages
• Chai
• 2010-01-12
• 12,00 13,20 10,00
• 2010-01-12
• 12,00 15,01 12,00
• 2010-01-12
• 13,00 13,25 12,12
• 2010-01-13
• …

Of course, my subtotals for the day (not shown here) were completely wrong too.

Solution: add computed property to convert date into string, the group on computed property

After checking the dates do not contain any hour information (and they did not). After having tried setting all kinds of combinations in the group and sorting properties, I decided to take another approach. In the Lightswitch datasource that served as the LightSwitchDataSource for the report, I added a computed field to convert the date field into a string like yyyyMMdd.

Here is an example of the Method for the computed property groupSalesdate:

partial void groupSalesdate_Compute(ref string result)
{
result = this.Salesdate.ToString("yyyyMMdd");
}

In my XtraReport designer, I replaced the group field for the 3^rd level by the computed field. The field that is displayed can remain as it is , so that the date field can be formatted properly.

The final result is what I needed:

• Beverages





• Chai





• 2010-01-12





• 12,00 13,20 10,00



• 12,00 15,01 12,00



• 13,00 13,25 12,12





• 2010-01-13





• …

Remark

I have not tried this yet with VS2012 and DeveXpress release for 2012.

LightSwitch 2011 out of browser app shows completely blank screen after installation

Problem

After a migration of a LightSwitch application with XtraReports to a new web server with Windows 2008 and IIS 7, and after installation of the out of browser application on the clients, the app starts up with a blank screen. No menus or status bar is shown at all.

Solution

Some kind of error must have happened, but no indication at all of what happened. I have read about the blank screen in fora on the internet: it involves setting the basic authentication to false. So I tried to follow the advice, but in the IIS manager for the LightSwitch website there was not even Basic authentication under the authentication options.

• Basic authentication is not installed on the web server:
• On the 2008 server, run Server Manager
• Select Roles / Web Server (IIS), in the Role services pane, add the Basic Authentication role, (along with Windows Authentication)
• 
• Restart IIS Manager, select the LightSwitch web site
• select Authentication, verify that the Basic Authentication is disabled
• 

After setting Basic Authentication and stopping and starting the web server, I started the client app again, and this time I was shown a Silverlight dialog box with an error message: ‘Could not load file or assembly DevExpress.Xpf.V11.1.LightSwitch.Common.dll…’

To solve the real problem I had to manually copy this file from my development machine to the server under …Inetpub\wwwroot\Myapp\bin

How to update query parameters on Xtrareport

Question

When an Xtrareport report in a Lightswitch project is based upon a query with parameters and new parameters have been added to the query after the report was designed, how do you update the report parameters?

Explanation

In Visual studio in logical view, modify the query to add new parameters.

Save the query and rebuild the project.

Switch to file view

go to Server folder

open the report in design modus

Select the Lightswitch datasource

In the properties panel, Click on the link Update QueryParameters

The parameters are now added to the Query Parameter Collection. You can verify this by clicking on the Ellipsis button next to the (Collection) text to open the dialog box with the parameters from the query.

Print alternating background colour in detail section of XtraReports for Lightswitch

Problem

The readability of a printed report with text and numbers can be improved by printing the details with and alternating colour, e.g. lines in white background alternate with lines in light grey background.

Solution

In a LightSwitch application with an XtraReports report, we can make the colours alternate by creating an EvenStyle and OddStyle in the Styles collection in the properties of the detail section. One of these styles will be given a different background colour from the other.

Prerequisites

The example is based on a view vEmployeeDepartment from the AdventureWorks database. The view has been added to the Datasource of the Lightswitch project. To create a report with grouping see my article about grouping with XtraReports 

How to set alternating background colours

• In the XtraReports design, select the detail section. Click on the grey Details band.
• In the properties window, expand the Styles Collection property
  
• The EvenStyle, OddStyle properties are displayed. Click on (none) near the EvenStyle.
  
• Click on the dropdown arrow, in the dropdown list, select (New)
  
• A new style is created with the name xrControlStyle1. Repeat the above step for OddStyle.
  
• A 2nd style is created , called xrControlStyle2.
  
• Now we have to set the background colour of these styles to a different colour. By default the background of both styles will be transparent. We will only modify the background of the OddStyle to WhiteSmoke. To modify click on (Collection) of the Styles collection, so that a button ‘…’ appears.
  
• Click on the button ‘…’. IN the Styles editor dialog window select ‘xrControlStyle2’.
  
• In the right-hand panel, click (Not set) near the BackColor property to open a dropdown with colours. Select the WhiteSmoke colour.
  
• Click Close.
• Now the background will be printed in alternating white and WhiteSmoke colour as shown in the start of this article.

Remark

In XtraReports, you have to fit your labels together horizontally, so that there is no white space. Because the details background only shows where there are controls. It is not quite as one would expect. If your labels don’t fit together the alternate colour will not show up.

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.

Provide a combobox with parameter values in XtraReports for LightSwitch

Problem

When using XtraReports for LightSwitch based on a filtered query, the default behavior for the XtraReports preview screen is to show a textbox in which you can type the value of the parameter. With Date values the XtraReports has a calendar dropdown, but for numeric or string data like identifiers or codes that refer to primary keys in lookup tables, we have to provide some code to build a combobox list to facilitate life for the user.

Prerequisites

This article is a follow-up on my article on how to create a 3 column report.

In this example a report preview screen LabelProductsByCategory was created based on a query qryProductsByCategory with a filter with one integer parameter (CategoryId)

The report preview screen uses a numeric up-down control to enter a category Id. Here the user would appreciate an combobox with a list of the names and not the id’s.

Solution

The solution I provide is based on the XtraReports tutorial Provide Custom Editors for Report Parameters. Some lines of coding are needed to replace the standard up-down box by a DevExpress ComboBoxEdit control. This control is installed together with the XtraReports controls.

Advantages of the ComboBoxEdit control

This control has properties that can be set improve the user experience, in particular:

• ItemsSource – can be set to a List of objects
• ValueMember – will contain the value of the column that contains the value we need for the parameter (in most cases it is an Id column)
• DisplayMember – will contain the column value that show a human readable text (in most cases the name column)

The code in the example under step 3. will illustrate this.

Example

We need to add code in the preview screen to generate a local list object with values from the categories table that can be used by the ComboBoxEdit control.

1. In the solution explorer right click on the report preview screen and select View Screen Code.
   
2. In the report preview screen code above the namespace,
1. Add a reference to the DevExpress.Xpf libraries
   

   using DevExpress.Xpf.Editors;

   using DevExpress.Xpf.Printing;



3. In the class LabelProductsbyCategory




1. define a List object in the report preview screen class
   
2. define a customize parameters event handler, in here the ComboBoxEdit will be created.
   
3. add the address of the event handler to the report preview model
   
4. In the Activated event populate the list with the categories entities.
   
   
   
   

   public partial class LabelProductsByCategory

   {

       // 1. define a list object

       List<object> categories;

       // 2. define a customize parameter event handler

       void model_CustomizeParameterEditors(object sender, CustomizeParameterEditorsEventArgs e)

       {

           if (e.Parameter.Name == "ProductCategoryID")

           {

               var editor = new ComboBoxEdit();

               editor.ItemsSource = categories;

               editor.ValueMember = "ProductCategoryID";   // this is the column name with the value that will be used 

               editor.DisplayMember = "Name";  // this is the column name with the text that will be displayed

               e.Editor = editor;

               e.BoundDataMember = "EditValue";

           }

       }

       public void CustomizeReportPreviewModel(DevExpress.Xpf.Printing.ReportPreviewModel model)

       {

            // 3. add the customize event handler to the model

           model.CustomizeParameterEditors += model_CustomizeParameterEditors;

       }

       // 

       partial void LabelProductsByCategory_Activated()

       {

           this.ReportTypeName = "LightSwitchApplication.LabelProductByCategory";

           // populate the List object

           categories = new List<object>();

           foreach (ProductCategory category in new DataWorkspace().AdventureWorksData.ProductCategories)

           {

               categories.Add(category);

           }

       }

   }



4. Save and debug.
   
   
   
   1. When we open the preview screen, click on the ComboBoxEdit control the list with 4 Categories opens.
      
      
   2. Select an item, e.g. ‘Clothing’ and click ‘Submit’
      The report is shown with products that belong to the category ‘clothes’
      

Conclusion

The XtraReports preview screen can be based on a filtered query, the parameters of which are presented automatically in a text box. With a little coding and a ComboBoxEdit control we can then make the selection of the parameter values user-friendlier.

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.