Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


SSRS Using a Subreport

SQL Server Reporting Services Subreports

This article covers how to create a report in SSRS with a subreport. I am using SQL Server Data Tools 2012 to create the report. The master report source will be a View created in the AdventureWorks database of Customers. The detail subreport will use a datasource of a view of Customer Orders.

Create Views for the Source Data

Open SQL Server Management Studio 2012 and open the Server where the AdventureWorks database resides. Open up a Query Window and type the code for the 1st View, dbo.vw_Customers.

CREATE VIEW [dbo].[vw_Customers]
AS
SELECT Sales.CustomerAddress.CustomerID, Person.Contact.FirstName + CASE WHEN len(Person.Contact.MiddleName) > 0 THEN ' ' + Person.Contact.MiddleName ELSE '' END + ' ' + Person.Contact.LastName AS CustomerName,
Person.Address.AddressLine1 + CASE WHEN len(Person.Address.AddressLine2) > 0 THEN ' ' + Person.Address.AddressLine2 ELSE '' END AS AddressLn1,
Person.Address.City + ', ' + Person.StateProvince.Name + ' ' + Person.Address.PostalCode AS AddressLn2
FROM Sales.Customer
INNER JOIN Sales.CustomerAddress ON Sales.Customer.CustomerID = Sales.CustomerAddress.CustomerID
INNER JOIN Person.Address ON Sales.CustomerAddress.AddressID = Person.Address.AddressID AND Sales.CustomerAddress.AddressID = Person.Address.AddressID
INNER JOIN Sales.Individual ON Sales.Customer.CustomerID = Sales.Individual.CustomerID
INNER JOIN Person.Contact ON Sales.Individual.ContactID = Person.Contact.ContactID AND Sales.Individual.ContactID = Person.Contact.ContactID AND Sales.Individual.ContactID = Person.Contact.ContactID
INNER JOIN Person.StateProvince ON Person.Address.StateProvinceID = Person.StateProvince.StateProvinceID

And create code for the 2nd View, dbo.vw_CustomerOrders

CREATE VIEW [dbo].[vw_CustomersOrders]
AS
SELECT Sales.Customer.CustomerID, Sales.SalesOrderHeader.SalesOrderNumber,
Sales.SalesOrderHeader.TotalDue, Sales.SalesOrderHeader.SubTotal,
Sales.SalesOrderHeader.TaxAmt, Sales.SalesOrderHeader.Freight, Sales.SalesOrderHeader.OrderDate
FROM Sales.Customer
INNER JOIN Sales.SalesOrderHeader ON Sales.Customer.CustomerID = Sales.SalesOrderHeader.CustomerID

Create SSRS Report Project in SQL Server Data Tools

Open SQL Server Data Tools which will launch Visual Studio 2012 and select a Report Server Project.

Sql Server Data Tools

Adding a Shared Data Source

Right click the Shared Data Sources folder and select select Add New Data Source.

Shared Data Source

Click on Edit and select the SQL Server and Database to create the connection string.

Data Source Properties

Adding a Shared Dataset

Right click in Solution Explorer on the Shared Datasets and select Add new dataset. Select the Shared Datasouce from the drop down and click on the Query Designer button to select the View created earlier.

Data Source

Click on the button that has a grid with a Plus Sign on it to select the View vw_Customers from the View tab and click Add and then Close. In the Designer, click each of the columns to add and click OK.

Query Designer

Adding the Main Report

Right click Reports and click Add New Item. Selecting Add New Report will start the Wizard and take you through steps. Using Add New Item will allow you to have more manual control. Select Report from the list and click Add.

Add New Item - Report

Right click on Datasets in the Report Data window and click on Add Dataset. Click on the Shared data set and make sure that the option Use a Shared Dataset is checked.

Dataset Properties Dialog

Drag a List item from the Toolbox on to the report.

Report Design Mode

Drag and drop fields from the Dataset into the List item. Add a Textbox with the word Customer: in it to clarify what data is being presented.

Add Dataset fields

Click on the Preview tab to ensure that the data is appearing in the report as it is supposed to do.

Report Preview Mode

Adding the SSRS Subreport

Create a new report by right clicking Reports in the Solution Explorer - Click Add New Item and click on Report. Right click on Datasets in the Report Data window and select Add Dataset. Click on the option to Use a dataset embedded in my report. In the Query Designer, select the View vw_CustomerOrders and select the columns. In the Filter box next to CustomerID, add the text "= @CustomerID" and click OK. The final resulting query should appear as it does in the image below (with a WHERE clause that has a @CustomerID parameter in it).

Parameterized Query

Drag and drop a Table object on to the report from the Toolbox. Drag one of the Dataset fields into one of the Data Cells (2nd Row) in the table. You have now set the dataset for the table and can click in each cell remaining to select the fields or drag and drop each dataset field into the cells in the 2nd Row. To add additional columns, right click in any column and select Insert Columns. You can then choose Left or Right as the location to add a new column to the Table object.

Table in SSRS Report

Add a parameter to the report by Right Clicking Parameters and selecting Add Parameter.

Add New Parameter

Click in the Name Box and name it CustomerID. Enter a Prompt name and select Text as the Data Type. I am using Text instead of Int as I received an error when I first built the report about a conversion issue between String and Integer data types.

Report Parameter Properties

Format the cells by right clicking on each one. Select Placeholder Properties and go to Number. From there you can set the Formatting options to Date or Currency to make the report appear more professional.

Data Formatting

Run the Orders report by clicking on Preview and entering a CustomerID in the Parameter box to test it out.

Preview SubReport

In the Master\Main report, select the orders report from the Reports listed in Solution Explorer into the List Item below the Textboxes. Format and size it to fit as shown.

Adding the Subreport

Right click on the Subreport and select Subreport Properties to link the 2 reports

Subreport Properties

Click on the Parameters and click on the Add button. Select the CustomerID value and select or type the name CustomerID in the Name box. Click OK.

Subreport Properties Dialog

Click on Preview to run the report

SSRS Report Preview

SSRS Subreport Performance Trade Offs

Although subreports are a nice way to pull in other reports and link them to one another for Parent\Child Data, the performance in retrieving the data is significantly poorer when using a Subreport as opposed to using an SSRS Composite report. SSRS Grouping of one multi-row dataset would be a better choice performance wise with these datasets of Customers and Orders (> 30000+ rows). However, there is a time savings of not having to recreate a report, especially if you are talking about a small amount of data to process.