SQL Server Reporting Services – Opening an RDL in Older Version of Visual Studio Opens as XML

In order to fix this issue, you need to remove a few lines of code from the XML.

    1. Replace xmlns with:
      <Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
    2. Remove only the Report Section header and footer tags (leave the child elements)
      <ReportSections>
      <ReportSection>
      
      ..................................
      
      </ReportSection>
      </ReportSections>
    3. Remove the entire ReportParametersLayout section
      <ReportParametersLayout>
      
      <GridLayoutDefinition>
      
      .....................................
      
      </GridLayoutDefinition>
      
      </ReportParametersLayout>

SQL Server Reporting Services (SSRS) – Order by different values from the same database table column

Let’s say you want to order your data in your tablix by last name, but you want certain names to be sorted first.

1) Double click the first grouping under Row Groups.
2) Select ‘Sorting’.
3) Click the ‘Add’.
4) Click the Expression button.
5) Add the following expression.

=switch(

Fields!last_name.Value = "SMITH", 1,

Fields!last_name.Value = "JONES", 2,

Fields!last_name.Value = "WILLIAMS", 3)

Now, all persons will be sorted by SMITH, then JONES, then WILLIAMS.

But, what about the rest of the data. How can you get the rest of last name to be sorted ascending like such:

SMITH
JONES
WILLIAMS
ANDERSON
DAVIS
EDWARDS
etc.

You can use a CASE statement in the SQL instead like this:

SELECT * FROM NAME_TABLE
ORDER BY
(CASE WHEN NAME_LAST= 'SMITH' THEN 1
           WHEN NAME_LAST = 'JONES' THEN 2
           WHEN NAME_LAST = 'WILLIAMS' THEN 3
          ELSE 4 END), NAME_LAST

SQL Server Reporting Services (SSRS) – How to Stop Creation of rdl.data Files

An rdl.data file will be created each time when the report is viewed with different input parameters. The data file caches the data so running the report again in the IDE with no changes to the parameter data will render quickly.

To stop the creation the data files, do the following:

For example: Visual Studio 2015
C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\PrivateAssemblies

Change the CacheDataForPreview key in the RSReportDesigner.config file from true to false

Here is an update to date (as of today) chart of Visual Studio versions:

Hide duplicate row SSRS 2008

Let's say you have a SQL table with 2 columns. The first column is a name and the second column is a street address. For every address a name has, the name column is repeated for every row as such:

name          address
John Doe    100 West Main Street
John Doe    200 Apple Road
John Doe    300 Orange Circle
Todd Toe     123 Maple Drive
Todd Toe     543 Ridge Road

Now you wish to build a report to display this data, but you don't feel like grouping the name column in your SQL nor do you want to group the name column in your report.

So, in the tablix of your report, you create two rows. The first row displays the name column and the second row display an associated address. The problem is that you don't want to repeat the name row for each address like this:

John Doe                                                                                          
100 West Main Street
John Doe                                                                                          
200 Apple Road
John Doe                                                                                          
300 Orange Circle
Todd Toe                                                                                          
123 Maple Drive
Todd Toe                                                                                          
543 Ridge Road

You want it to look this:

John Doe                                                                                          
100 West Main Street
200 Apple Road
300 Orange Circle
Todd Toe                                                                                          
123 Maple Drive
543 Ridge Road

You can do this (without any grouping) by simply using the Previous expression of SSRS.

1) Right Click on your name row and choose row visibility.
2) Click the 'Show or hide based on an expression' radio button and click the expression button.
3) Enter the expression:
    =iif(Fields!name.Value = Previous(Fields!name.Value), True, False)

That's it!

 

SQL Server Reporting Services (SSRS) – Alternating row color with visible rows

Use the Alternating Row Color code function and call it from the BackgroundColor property of the tablix row column.

Here is the Function that needs to be placed in the Code tab of the Report Properties window:

Function AlternateColor(ByVal OddColor As String, _
         ByVal EvenColor As String, ByVal Toggle As Boolean) As String
    If Toggle Then bOddRow = Not bOddRow
    If bOddRow Then
        Return OddColor
    Else
        Return EvenColor
    End If
End Function

Here is the expression for the BackgroundColor property for column 1

=Code.AlternateColor("LightGrey", "White", True)

Here is the expression for the BackgroundColor property for column 2 and on

=Code.AlternateColor("LightGrey", "White", False)

SQL Server Reporting Services (SSRS) – Add a Group Row Header to an Existing Group Row

If you want to add a group row header to an existing group row in order for that group row header to repeat on each group break, do the following.

  1. In the Row Groups Panel below the report designer in the Design tab, right mouse click the group where you want a group row header to appear above it and select, ‘Add Total’ then choose ‘Before’.
  2. Add an expression to the column(s) of the group row header that you just created that you want displayed to the client based on the group break.

For example, let’s say you have two groupings, the first being TruckModel, the second being V8Engine with a value of Y or N; Y being that it is a V8. If you created a row group that breaks on V8Engine, the group row header will appear only when the V8Engine changes for the specific  TruckModel. In the expression, you can check for the value of Y, and if true, display V8 else V6 like so:

=Fields!TruckModel.Value & " " & iif(Fields!V8Engine.Value = "Y", "V8", "V6")

SQL Server – Multiple data rows into one generated column, XML Path char(13), and removing that last carriage control in SSRS Expression

Let’s say you have a table called ‘car_make’ and another table called ‘car_model’. There is a one to many relationship between make and model.

You want to bring back a result set of all makes, 1 row per make, but you want only 1 generated column for model that has all the models concatenate into that 1 column.

You also want to add carriage controls so each model will appear on a separate line in your row on your report.

Let’s say you have 4 models for your 1 make of Chevy. Here is the output of what you are striving for:

Make     Model
--------------
Chevy    Astro
         Beretta
         Blazer
         Caprice

The following SQL will give you this result:

SELECT make,
left(ModelsConcat.Models,LEN(ModelsConcat.Models)-PATINDEX('%[^'+CHAR(13)+CHAR(10)+']%',REVERSE(ModelsConcat.Models))+1) As CarModels
OUTER APPLY (SELECT DISTINCT SUBSTRING((SELECT DISTINCT (m1.model + ', ' + + char(13))
 FROM car_make AS m1
 WHERE a.make = m1.make
 ORDER BY (m1.model + ', ' + char(13)) For Xml Path(''), type).value('.', 'nvarchar(max)') ,1,2000) AS Models 
 FROM car_model) AS ModelsConcat 
 FROM car_make a

In your expression, you simply reference the field like this:

=Fields!CarModels.Value

 

 

SQL Server Reporting Services (SSRS) – How to use an image as the layout of the report and then place conrols on top to display the data

1) On the body of the report, drag a tablix. Make the first row the height and width of your page. Call the Tablix ‘TheTablix’.

2) Drag a rectangle into the first row of the tablix. Call the Tablix ‘TheRectangleRow1’.

3) Left mouse click the rectangle. Click the top left square. Drag the top corner toward the bottom right corner about 3 inches.

4) Left mouse click the body. Right mouse click the body and insert image. Choose Embedded and click import. Click OK. Set the location of the image to 0,0. Change the Sizing to ‘FitProportional’. Call the image, ‘TheImage;.

5) Right mouse click the image, choose layout, and the send backwards.

6) Left mouse click the rectangle. Click the top left square. Drag the top corner back to the top left corner of the body. Copy the size of ‘TheRectangleRow1’

7) In the Properties window, select your image so it is selected. Paste the size of the rectangle size you copied to the clipboard into the size property of the image.

8) If everything was done correctly, when you click within the borders of the page, the image should be selected in the properties window. From here, you can select a control from the toolbox and draw it over the image. The end result, is the control actually becomes a child of the body and that is what you want.

The zOrder shoudld be as follows:

1) The parent of the controls is ‘TheRectangleRow1’.
2) The parent of ‘TheRectangleRow1’ is ‘TheTablix’
3) The parent of ‘TheImage’ is ‘TheRectangleRow1’ but 1 z-order behind the controls.
4) The parent of ‘TheTablix’ is the BODY.

SQL Server Reporting Services (SSRS) – Creating Full Width Group Headers in SSRS

Creating Full Width Group Headers in SSRS

Introduction

Dealing with group headers in banded reporting tools like Crystal Reports feels easy and natural. The group header section is clearly visible on the design surface, and it is simple to add text or format group header fields. Dealing with SQL Server Reporting Services (SSRS) group headers is a little less intuitive, as it is not a banded reporting tool, and uses the concept of a “Tablix” for displaying grouped data in a table structure.

In this article, I’ll take you through the steps necessary to create a “full width” group header on an SSRS report, using Report Builder 3.0.

The Easy Way – Adding a New Table

The fastest way to get the basic Tablix on the report with the grouping we want is to use the Table Wizard, which is available on the Insert tab under the “Table” option. For this example, you’ll create a table based on a query that has last_name, date_worked, and hours_worked fields. You’ll group on the last_name column:

Adding the table with the Table Wizard

After clicking “Next”, you are presented with the default layout options. The “Blocked” options give you that “Excel” look that you don’t want – choose the “Stepped, subtotal above” option. Un-check “Expand/Collapse groups”, and click “Next”. Then you’ll choose the “Generic” style and click “Finish”. When you run the report you should end up with a table that looks something like this:

A Basic Table

You’re close now! The next thing you need to do is select your group header row in the table and click the “Merge” button:

Merging the Header Cells

This gives you the desired full-width header cell, which you can then style to taste. It does, however, remove the summary value. To add a summary row as a “footer” for each group, you simply right-click on the detail row and insert a row below the group:

Adding the Footer

You can then merge the footer cells as you did the header, and add the Hours Worked field to the appropriate cell:

Adding the Summary Field

You can right-justify the cell, and you now have a data table with full-width group headers and footers – just style as desired!

The Harder Way – Modifying an Existing Table

It can be a little more difficult to modify a previously created table that has been set up as “Blocked”, but the following steps should suffice:

    • Delete the Group column

Highlight the group column, and delete it. The table will still be grouped by that field, only the column will be removed.

    • Insert a new Header Row

Right-click on the detail row, then select “Insert Row”, choosing the “Outside Group – Above” option. This will create a new row above each group instance. You can merge these cells to create a full-width header, and add the Group field to the merged cell.

    • Insert a new Footer Row

Repeat the step above, but this time choose “Outside Group – Below”. This will create a new row below each Group instance, and any summaries can be placed here.

That’s all there is to it! Dealing with Group Headers and Footers in SSRS can be confusing at first, especially if you are coming from a banded reporting tool like Crystal Reports. It doesn’t take long to get used to working with the Tablix control, though, and I hope the steps above helped clear the way a little.