SQL Server Reporting Services (SSRS) - Tips & Tricks

This article explains how to create a recursive hierarchy group, which organizes data in a report to include multiple hierarchical levels. This is helpful when you want to display hierarchical data in a report. For example: employees in an organizational chart, or product subcategory in a product list. The image below is an example: 

 This article assumes you have the following:

• SQL Server 2008 and Reporting Services installed
• Business Intelligence Development Studio (BIDS) or Report Builder 2.0 is installed. Note: We are using Report Builder 2.0 for this example.
• AdventureWorks database
• Experience working with Reporting Services

Steps Taken:

1. Create a new report
2. Add a Data Source and Dataset
3. Add a Table to the report and include dataset field
4. Edit Group properties and reference a Recursive Parent 
5. Add custom format expressions
6. Run code 

Create a new Report

1) Open Report Builder 2.0 and create a new report.

Note: If you have not done this before, please view my previous article "Creating a Report" for more information.

2) Save the report to your desired Report Server location and name it "RecursiveHierarchyGroup".

Add Data Source and Dataset

Creating the Data Source

1) From the Report Data pane, click New and select Data Source

the data source dialog will open...

 Note: You may choose an existing shared data source, if you have previously created one. For this example, we will be using an embedded connection. 

2) Provide a name for your data source. We will use the default value provided in this example.

3) Select "Use a connection embedded in my report" option

4) Select your Connection Type from the drop down. For this example we are using Microsoft SQL Sever.

5) Click the Build button from the right of the "Connection string:" textbox. You will see the Connection Properties dialog below.

6) Enter the same values in the property fields, found in the image above.

1. Test the connection when you are done.
2. If test is successfull, click OK.
3. Click OK to close the Connection Properties window.
4. Click OK to close the Data Source Properties window.
5. You should now see your data source name on the left Report Data pane.

Note: the value in Server Name property is a period "."

Creating the Dataset 

1) From within the Report Data pane, select New >> Data Set

2) Make sure the Query item is selected in the left pane of the Dataset Properties window. Enter "Organization" in the Name field.

3) Select "AventureWorks" from the Data Source dropdown, or the name you gave your data source.

4) Copy and paste the following T-SQL script to the Query textbox:

select hr.EmployeeID, hr.ManagerID, c.FirstName + ' ' + c.LastName as 'Name', hr.Title

from HumanResources.Employee as hr inner join

Person.Contact c on hr.ContactID = c.ContactID

you should have something similar to below:

 5) Click OK to save changes and close the window.

Add Table to Report

1) In Design view, add a table, and drag the following dataset fields to display:

1. Name
2. Title

2) Insert a new column to the right of the Title field and name it "Level".

3) Click in the "Click to add Title" field and enter "Organizational Hierarchy"

Your design view should look similar to the image below:

Edit Group properties and reference a Recursive Parent

4) Right-click anywhere in the table to select it. The Grouping pane displays the details group for the selected table. Optionally, you can select View from menu tab and check Grouping. In the Row Groups pane, right-click Details, and select Group Properties. The Group Properties dialog box opens.

5) In Group expressions section, click Add. A new row appears in the grid.

6) In the Group on list, type or select the field to group - EmployeeID.

Your Group Properties General tab should look similar

7) Click the Advanced tab, from within the left pane of the Group Properties dialog.

8) In the Recursive Parent list, type or select the field to group on - ManagerID.

9) Click OK.

10) Run the report. You will notice the list of Employee names and titles. However, we need to do some formatting to indent the hierarchy accordingly.

Add custom format expressions

Indent Hierarchy

1) Right click on the [Name] field and select Text Box Properties...

2) From the left property pane, select the Alignment tab.

3) From under the Padding options section, select the Left expression button.

After clicking the expression button, you should see the following expression dialog.

4) Enter the following script in the expression textbox:

=CStr(2 + (Level()*10)) + "pt"

The Padding properties all require a string in the format nnyy, where nn is a number and yy is the unit of measure. The example expression builds a string that uses the Level function to increase the size of the padding based on recursion level. For example, a row with a level of 1 would result in a padding of (2 + (1*10))=12pt, and a row with a level of 3 would result in a padding of (2 + (3*10))=32pt.

Set Font Style

1) From the left property pane, select Font.

2) Under the Style section, click Bold expression button.

3) Enter the following script in expression textbox:

=IIF(Count(Fields!Name.Value, "Details", Recursive) > 1, "Bold", "Normal")

Essentially the Count function is searching the Fields!Name.Value, from within the "Details" group, recursively. If the recursive parent has children, then the parent is set to Bold, else Normal.

4) Click OK.

5) Right click on the Level field and click Expression...

6) Enter the following script:

=Level()

The Level() Function returns the current level (Integer) of depth in a recursive hierarchy.

7) Click OK.

5) Run the report. You should see similar results.

That's all there is to it.

Cheers!

For this post we will walk through the steps in creating a custom assembly and referencing it from Reporting Services 2008. After we reference the assembly we will can the call the static (shared) method from a texbox field, using a custom expression.

This article assumes you have the following:

• SQL Server 2008 and Reporting Services installed
• Business Intelligence Development Studio (BIDS) for report development
• Experience with C# and SQL Server Reporting Services

Create custom assembly

1) Open Visual Studio 2008 and create a new Project.

2) The New Project window will open; Select the Visual C# node; from the right pane select Class Library. Complete the following:

1. In the Name field enter "rsCustomAssembly"
2. In the Location field enter "C:\Visual Studio 2008\Projects"
3. In the Solution field enter "rsCustomAssembly"
4. Click OK when you are done

3) Delete the default class.cs file created. When prompted click OK.

4) Add a new class by right clicking on the solution name and selecting Add >> Class...

5) Name the new class Utilities.

6) Enter the code below into the class file and save it.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

namespace rsCustomAssembly

{

    public class Utilities

    {

        /// <summary>

        ///  Sample concatenate string method; not intended for production use.

        ///  Note: the string object already provides a concatenate method - string.concat(object o)

        /// </summary>

        /// <param name="string1"></param>

        /// <param name="string2"></param>

        /// <returns></returns>

        public static string Concat(string string1, string string2)

        {

            return string1 + " " + string2;

        }

        public static string HelloWorld()

        {

            return "HelloWorld";

        }

    }

}

Your screen should look similar

7) From the top menubar you will find Build; Click this and select rsCustomAssembly from the list. The bottom status bar should presenet Build succeeded. If not, check your code again to make sure it follows the description above.

Copy the assembly to the Visual Studio Private assemblies and Report Server bin

You must copy you assembly (.dll) to the report designer and report server folders before you can use the assembly in Reporting Services. You can find your assembly by locating the bin folder in your project. For example: C:\Visual Studio 2008\Projects\rsCustomAssembly\rsCustomAssembly\bin\Debug

Note: the location is dependent on your install

For Report Designer: C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies

For Report Server: C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin 

You have completed with this step.

Create a new Report

 This step we will perform the following:

• Create a new report
• Reference the custom assembly
• Add a new textbox to the report designer with a custom expression

1) Open BIDS and create a new project, like you did in 1) under "Create custom assembly".

2) Under the Project Type pane, select Business Intelligence Projects; from within the Templates pane, select Report Server Project and complete the following:

• For the Name field enter SampleReport
• For the Location field enter C:\Visual Studio 2008\Projects
• For the Solution field enter SampleReport
• Click OK

3) From the Solution Explorer, right click on the Reports folder and select Add >> New Item

The Add New Item window will open

4) From within the Templates pane, select Report and leave the default name of Report1.rdl

5) Click Add

Reference the Assembly

1) From the top menu bar select Report >> Report Properties

Note: if you don't see the Report menu item, try placing your cursor anywhere in the design view; you should then see the menu item.

2) The Report Properties window opens. From within the left pane, select References.

3) Click Add under "Add or remove assemblies".

4) Click the ellipsis right of the text box. The Add Reference window will open.

5) Select the Browse tab and navigate to the Report Server bin location, where you copied your .dll, as described earlier in this article under Copy the assembly to the Visual Studio Private assemblies and Report Server bin

you should see a similar value in the reference text box

6) Click OK to save and close the Report Properties window.

Add Expression and run report

1) Drag and drop a Text Box from the Toolbox pane on the left of your design pane. Strech the text box so you have some room for text.

2) Right click the text box you just added and select Expression

3) For the expression value enter :

=rsCustomAssembly.Utilities.Concat("Reporting", "Services")

4) Click OK to save and close the Expression window.

5) To the right of the Design tab, click Preview. You should see the following:

Save your work. You have completed referencing a custom assembly in SQL Server Reporting Services.

Cheers!