SSRS subreport with drill down step by step in sql server 2008

sql reporting subreport with toggle To create a toggle sub report in sql reporting service is too easy but if you will not aware about it then it is really difficult for you. So in this article we will see how to create a report with having sub report, and sub report data will load only when we will toggle (0pen) the sub report and not on the load of main report.

To learn basic of reports see my previous article How to create basic report in Sql Server 2008

Here is the report output which we will create in this article.

Let’s create a new blank project to work (if you are familiar then omit this section)

• Click Start -> All Programs -> Microsoft Sql Server 2008 -> Sql Server Business Intelligence Development Studio
• File -> New -> Project..
• Business Intelligence -> Report Server Report
• Give a name to the project and click OK

It will create blank project with two folders

1. Shared Data Source
2. Reports

Right click on Shared Data Source and select Add New Data Source

Click Edit to Add connection String and set your Sql Server connection

Now we created a new Shared Data Source which we will use in our project for every report.

We will use Northwith database to create an Orders report with Order Details as sub report, so let’s create our main report Orders

• Right Click on Reports folder
• Select Add -> New Item...
• Select Report and give name to it “Orders.rdl”

It will open a blank surface to add our report columns, header Title etc.

Open Toolbox and drag a Table to the report surface

It will open Data Source Property Dialog

Click on Use Shared Data Source reference and select our created data source

Click Next and type your query or procedure name which you want to use for this report, in our case

 Select * from Orders

Execute it to check the records and click Finish

A table will be added to the report area, now time to drag columns one by one, if need more column click on header and add more columns to it.

Adjust header background color and text color and font.

Click on Preview button to see the report, good it is working fine till now, let’s format the date column.

Right click on Order Date column -> Expression and change it to

=Format(Fields!OrderDate.Value, "dd MMM, yyyy")

Now report will show Order date as 04 Jul 2014.

Now time to create Order Details report which we will use as sub report in our main report Orders As earlier we created our Orders report, create a new report with name “Order Detail” and here is the sql statement

Select P.ProductId, P.ProductName, od.UnitPrice, OD.Quantity, OD.Discount From [Order Details] OD
INNER JOIN Products P ON P.ProductId = OD.ProductID
Where OD.[OrderID] = @OrderID

As you can see we used @OrderID as a parameter so this report will take order Id to run it which we will pass on toggle of parent report.

Now we will add our order detail report to main report Orders.

Right click on the item row -> Insert Row -> Inside Group – Below

Select all the column to merge, right click and merge it.

Open Toolbox and drag a Subreport in our merged cells

Right click on it -> Subreport Properties

From Use this report as a sub report select Order Details

Select Parameters -> Click Add -> Select OrderID in both dropdown

Now if you will see the order report it will show all the order with detail without drill down so let’s add drill down

Again open sub report properties -> Visibility

1. Select Hide
2. Select Display can be toggled by this report item
3. From drop down select Order Id

Now we completed run and see the report.