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.
Adding a Shared Data Source
Right click the Shared Data Sources folder and select select Add New Data Source.
Click on Edit and select the SQL Server and Database to create the connection string.
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.
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.
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.
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.
Drag a List item from the Toolbox on to the report.
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.
Click on the Preview tab to ensure that the data is appearing in the report as it is supposed to do.
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).
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.
Add a parameter to the report by Right Clicking Parameters and selecting Add 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.
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.
Run the Orders report by clicking on Preview and entering a CustomerID in the Parameter box to test it out.
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.
Right click on the Subreport and select Subreport Properties to link the 2 reports
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.
Click on Preview to run the report
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.