SSRS subreport

My Learning: Log in Learning Report [Log in]


Remove Ads or Log in
Remove Ads or Log in
+0  |  -0

In this SSRS tutorial I will show you how to create subreport in SSRS. I will use SSRS 2008 R2 but this tutorial should also work in SSRS 2008 and SSRS 2012.

Just to clarify a subreport is a report which we want to display (embed) in main report.

First picture shows a report with sparkline which takes one parameter @date. In this tutorial I will use this report and emded it into main report.

 

Make sure that you have Dataset, Data Source and Parameter setup already.

In our example we will create a dataset called TrendWeeks which actually will contain only 1 row! We will use it to force a table to display a one cell we will add our subreport.

NOTE: Normally you would use real life dataset and display a subreport once or multiple times (many rows) with different parameters.

Our subreport takes one parameter "date" and in our main report we will create the same parameter "date" and pass it to the subreport.

NOTE: Very often you will pass either parameters to a subreport which is presented in this tutorial or you will pass values from a field which we will try to cover in a separate SSRS tutorial.

If you would like to know more about parameters please read our article "how to create parameter in ssrs" (link coming soon)

 

 

To add table please open Toolbox and drag and drop table into design area.

Using your dataset insert the columns you need in the table. In my case I will only add header.

In "Data" cell I will add subreport. You can either use right click option (see below) or use toolbox.

 

Once sub report is added go to Sub report Properties.

 

In Subreport Properties we need to type Name and select sub report from drop down box.

NOTE: In drop down box you should only see reports that are part of your solution.

 

In the next step I will set up parameters.

To do this I click "Parameters" on the right side and click Add button.

A row will appear where I can select subreport parameters and option to pass value.

NOTE: SSRS in 2008 R2 has a bug. If you select subreport and go to parameters you will see in drop down box all parameters from subreports BUT if you close the properties box and go again without selecting subreport you will get EMPTY drop down box. To "fix" it you need to go to General section select subreport AGAIN which actually does something, go to Parameters section and try the drop down box. This time you should see parameters from subreport in the drop down box.

Now that we select parameter name from subreport from Name drop down box I need to pass value and in my case I want to pass value from my main report parameter called DATE which allows users to select a date using a calendar. To do that I click the expression button (fx)... (Ignore the OK button on the screenshot below it shouldn't be there)

 

 

In Set expression for: Value box I can use my parameters by typing =Parameters!Date.Value

alternatively in category section I can select Parameters and double click my data parameter.

Click ok

 

Now when my parameter is setup I click ok button

NOTE: A shortcut for parameter name in value box is [@ParameterName] see below our [@Date]

 

 

We can now preview our report and see a subreport that uses a date from the parameter.

 

I hope this tutorial will help you to create an SSRS subreport and pass parameters.

 

Take care

Katie


Remove Ads or Log in

We need you!

Time of free websites that are supported by ads only is over, due to increasing use of ad blocking.

Our website would die probably within a year; as there is no point investing free time and later on own money in something that is doomed.

Our only hope to survive (without any ads) is to ask our visitors like yourself to support it by becoming a member.

We know it's only going to be a tiny % of visitors who will do that but we hope that will be enough to keep our website for free and within a year switch off all ads. (54% page ads are switched off already for visitors from low income countries and 15% video ads are switched off for everyone).

Become a proud member

Visitor Voting

Do you find our website helpful?
Visitor Voting: Yes +71  |  No -1
Add comment (no email required).



Add Comment
Name: sd.rabbani
Date Posted: 2/26/2012 10:43:45 AM
Comment:

thanks  a lot 

now iam eassy to understand ssrs

 




Name: bashka
Date Posted: 5/19/2012 10:01:00 AM
Comment:

Thanks this is great




Name: Jan
Date Posted: 8/9/2012 7:41:09 AM
Comment:

Thanks!

The first blog that I encountered that says something about the parameter glitch!




Name: Mary
Date Posted: 8/15/2012 12:52:13 PM
Comment:

Thank you for explaining the parameter glitch!




Name: ali
Date Posted: 8/28/2012 5:38:37 AM
Comment:

thank you, this tutorial helps me to create subreports with parameter, very easy uau




Name: Sam
Date Posted: 9/20/2012 8:51:50 PM
Comment:

Thanks you so much!  Now my subreports actually work correctly!




Name: Jim
Date Posted: 10/3/2012 12:19:22 PM
Comment:

Nice artical, thanks Emil!




Name: Daniela
Date Posted: 5/8/2013 7:18:17 PM
Comment:

Great article! I've been strugle so much just because I didn't see the subreport parameter and I didn't know I have to reselect the subreport again.




Name: ely&mary
Date Posted: 5/10/2013 12:57:43 PM
Comment:

thank you

we have a presention and we need your data base , can you give us please??




Name: Tim Whalen
Date Posted: 5/15/2013 10:42:39 PM
Comment:

THANK YOU THANK YOU for putting in the 2008R2 glitch about selecting the subreport AGAIN to see the parameters!






Are you a member?

If yes than please log in

Learning Map is for members with Bronze, Silver and Gold Membership.
For more information visit membership options.


Close this window , log in or learn more about membership benefits