The report server cannot decrypt the symmetric key

On a newly installed sql server standard 2008 R2 with reporting services, I received the following error message, when I opened the http:\\<myserver>\Reports site

The report server cannot decrypt the symmetric key that is used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. (rsReportServerDisabled)

In the Microsoft connect site I found this problem described.

The answer by VenkatKokulla fixed my problem:

“Do you happen to have a backup of your encryption key? If not, follow these steps:
Open Reporting Services Configuration Manager
Connect to your RS server
Select 'Encryption Keys' from the left pane
Press the 'Delete' button to get rid of your encrypted content
After doing this, you will need to re-set the connection strings and stored credentials on your reports.
Also, I'd be sure to make a backup of the encryption key afterwards.
If you do have a backup, press the 'Restore' button instead of the 'Delete' button.”

Using the T-SQL RANK() function to dynamically number detail rows in master-detail view

The RANK() function will generate a sequence of numbers for detail rows, partitioned by each master row.

The following example was written with SQL 2008 R2 AdventureWorks database.

SELECT a.Name AS SubCategoryName, b.Name As ProductName

, RANK() OVER (PARTITION BY b.ProductSubcategoryID ORDER BY b.ProductID) AS ranknbr

FROM Production.ProductSubcategory AS a

INNER JOIN Production.Product AS b

    ON a.ProductSubcategoryID = b.ProductSubcategoryID

The result set is like this, where the last number is the row number for the detail rows, renumbered each time another master record starts.

Mountain Bikes    Mountain-100 Silver, 38    1
Mountain Bikes    Mountain-100 Silver, 42    2
Mountain Bikes    Mountain-100 Silver, 44    3
Mountain Bikes    Mountain-100 Silver, 48    4
Mountain Bikes    Mountain-100 Black, 38    5
...
Road Bikes    Road-150 Red, 62    1
Road Bikes    Road-150 Red, 44    2
Road Bikes    Road-150 Red, 48    3
Road Bikes    Road-150 Red, 52    4
Road Bikes    Road-150 Red, 56    5
Road Bikes    Road-450 Red, 58    6
...
Touring Bikes    Touring-2000 Blue, 60    1
Touring Bikes    Touring-1000 Yellow, 46    2
Touring Bikes    Touring-1000 Yellow, 50    3
Touring Bikes    Touring-1000 Yellow, 54    4

LightSwitch hangs while publishing

From the LightSwitch forum, as a reminder for myself, because I have had to fix a similar problem.

“We have identified an issue when packaging an IIS site with "Update existing database" selected.  This reproduces when using a connection string which does not have db admin privileges to generate the script.  Please try again with this permission and let me know if it continues.”

Erik - LightSwitch QA

“I tried that, and it worked for me.  Publish is now successful.

Being new at this, let me tell others like me exactly what I did:

Open SQL Server Management Studio, and connect to the local server, where I have the existing database from having published my package the first time, when publishing the first version of my app.

Go to Security \ Logins

Right click on the user that has admin rights to your database in your live environment.

Click "Properties"

In the Login Properties Page, go to "Server Roles"

Make sure "sysadmin" is selected.

Click OK

Now publish your package using this user and this user's password in your connection string.”

Delete large amount of records without interrupting other processes and without creating huge log

Business case

A customer wants to regularly delete older data from a table. There is a date on which to select data, large amounts (more than a million) of records will be deleted at once, the deletion should not interrupt other processes on the same database, the transaction log should not grow too long.

Solution

Truncate table is not an option, because only records older than a certain date need to be deleted.

Split the delete process in chunks with a while loop, smaller sets of records are deleted in every iteration of the loop, at the end of each iteration there is a wait to allow other processes to continue.

SQL Script

DECLARE @DeleteChunk int;

SET @DeleteChunk = 5000;    -- the number of records to delete in one chunk

DECLARE @DisposableDate smalldatetime;

SET @DisposableDate ='2012-04-15';  -- the date to compare against for older data

-- WHILE EXISTS will return true as soon as a single record is found, so not too slow

WHILE EXISTS(SELECT * FROM dbo.MyTable WHERE DisposableDate >= @DisposableDate) 

    BEGIN

        DELETE 

        FROM dbo.MyTable

        WHERE MyTableID IN 

            (SELECT TOP(@DeleteChunk) MyTableID        -- select TOP 5000 records at a time

             FROM dbo.MyTable WITH (NOLOCK)

             WHERE DisposableDate >= @DisposableDate);

        WAITFOR DELAY '00:00:00:25';    -- give other processes time to act

    END

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.

Using the summary property of a table to make records more readable

Summary properties are displayed anytime you use the Summary control on a screen or when LightSwitch generates a layout for you that needs to display a description of the entity.

To show the user a better string representation of the records in some tables, we can use a computed column as the summary property of a table. By default summary properties are set to the first string column of an entity. For example whith the BillOfMaterials entity from the AdventureWorks database, the Summary Property will be set to the column UnitMeasureCode.

A nicer representation could be the Assembly product name, the assembly product is linked by a foreing key productassemblyId to the BillOf²Materials table. In the entity it is somewhat awkwardly represented as Product1

First we will add a computed property that displays the name of the product and then we set this computed field as the Summary Property of the entity.

1. To add a computed property:
1. In the BillOfMaterials design, click on the computed property button
   
2. Rename property1 to ProductComponent and then click on the Edit method link in the property panel.
   
3. Add the line result = Product.name in the Compute method
   

   partial void ProductComponent_Compute(ref string result)

   {

       // Set result to the desired field value

       if (Product != null)

           result = Product.Name;

   }



2. To set the computed property as the summary property




1. Select ‘BillOfMaterial’ in the properties panel choice list (or click on the title BillOfMaterial) in the design window.


2. Change the summary property to ProductComponent.
   


3. To prove everything works as expected, create a List and Details screen




1. In solution Explorer, Right click on the Screens folder, Add screen


2. In the Add new screen dialog, select screen template ‘List and details screen’, Screen Data: AdventureWorks…BillOfMaterials, additional data to include is checked. Click OK.
   


4. Build and debug the app. The List panel will display the name of the product instead of the Unit Measure code.
   

Remark

The use of computed property columns as summary column has some disadvantage: The computed column can take some time time to calculate and display, You cannot sort on the computed column either. Fancier solutions can be made with WCF RIA data services on the server side.

Update WCF RIA services Domain service when entity model has changed

Problem

When you need to add an extra table from the database in the entity model, you need it to be reflected in the RIA services. This does not happen automatically.

Solution

I had more or less guessed that generating another domain service would help create the code that could then be pasted in the original domain service. I found the confirmation in David Yack's blog article

Add a new table to a RIA services model and Domain service

The example a WCF RIA services library project in a LightSwitch solution.

The model AdventureWorksModel.edmx is based on 4 tables of the AdventureWorks database (Product, ProductModel, ProductCategory, ProductSubCategory).

The DomainService class has been created on the basis of this model and is called AdventurWorksDomainService.cs.

The underlying database is the AdventureWorks database. My RIA services project already contains 4 tables and now I want to add a 5th table BillOfMaterials, by rightclicking on the design surface of the model, choosing ‘Updating Model from the Database’, after selection of the 5th table, it was added to the model.

Now first save and build the project.

Now I add a new DomainService class to the project, called DomainServiceTemp, click Add.

From the entities, select only the BillOfMaterials, with enable Editing, Uncheck ‘enable client access. click OK.

Once the class and accompanying metadata class are generated:

• copy the code from the BillOfmaterials class in the DomainServiceTemp.cs to the AdventureWorksDomainService.cs class
• copy the code for the BillOfmaterials from the metadata temp file to the AdventureWorks metadata.
• Delete the 2 temporary domainservice files.

Set attribute [Query(IsDefault = true)]

Before we can use the BillOfMeterials in the LightSwitch project, one of the Queries in the DomainServiceClass has to be declared as default query, otherwise LS will not accept it. I had before by me for the 4 other tables, now one line has to be added before the GetBillOfMaterials() Iqueryable method.

[Query(IsDefault = true)]

public IQueryable<BillOfMaterials> GetBillOfMaterials()

To use the new table in LS we build the WCF RIA Service project.

Remark

when a new column is added to the table, the only thing you need to do is copy the columns from the metadata file.

Update the data source in the LightSwitch project

Once the WCF RIA services project is rebuilt, we can update the data source in the LightSwitch project. The LS project already uses 4 of the 5 tables, now I can add the 5th. In the Select Data Source Objects the 5th table has just been selected.

The entity is add to the LS project

Conclusion

Once you start using WCF RIA services it is always wise to plan well ahead so that all necessary tables , views, stored procedures are included in the DomainService. But in a real world there are many different causes why some table data has to be added or updated afterwards. It can be added with a little manual intervention from the developer.

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.