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.

alt text

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

alt text

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.

Ali Adravi Having 10+ years of experience in Microsoft Technologies (C#, ASP.Net, MVC and SQL Server). Worked with Metaoption LLC, for more than 8 years and still with the same company. Always ready to learn new technologies and tricks.
  • ssrs
  • report
  • sql server
By Ali Adravi On 09 Sep, 13  Viewed: 7,310

Other blogs you may like

Sql reporting services first report

SQL Reporting services also known as SSRS is a great tool to create report from database. In this article we will learn how to create a basic Report. We will learn how to create a shared data source so we can use it for all the reports in our project, How to create our data set to bind columns in... By Ali Adravi   On 10 Sep 2013  Viewed: 609

Get precision and decimal value in SQL Server by using PARSENAME

In SQL server there are decimal and money data type to store precision and scale both together or say decimal values. Suppose we want to get precision and scale separately then how to get it. There are many ways to do this but there is a function in SQL named PARSENAME which can be used to get... By Myghty   On 26 Mar 2013  Viewed: 1,057

Available wildcards in sql server with examples

To write a good performing procedure or function we should be aware about available wildcards in SQL Server. I found most developer depends on ‘%’ wildcard only while we have others as well. In this article we will discuss all the available wildcards in detail with examples. First of all let’s see... By Dev D   On 25 Feb 2013  Viewed: 304

Concatenate multiple columns in SQL Server with NULL value

When we need to concatenate two columns simply we can use `+` sign and that is correct, but what if any of them is null, Will it return what we want, NO, it will return null. So let's discuss how we should concatenate two or more columns without creating extra space or extra comma problem. Let's... By Ali Adravi   On 13 Feb 2013  Viewed: 4,508

Bulk insert into SQL Server from CSV file by skipping first row

Bulk insert is used to Import a data file into a database table or view in a user-specified format. It's not daily using kind of code, so it's very common question on most of the site like. How to import CSV file into a table? How to insert records from a CSV file to a table, delimited by comma?... By Ali Adravi   On 08 Feb 2013  Viewed: 11,858