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!

By becoming a proud member we can:
  • Switch off annoying ads
  • Create more articles and videos
  • Develop more useful features supporting your learning
  • Provide FREE webinars (for members)
  • Feel our hard work since 2011 is appreciated
Become a proud member and get exclusive features.

If 2% of our 64,943 visitors (Last 30 days) become a proud member than we can quit our jobs and work full time on this website. We started membership in May 2014 and so far 0.07% of our visitors are members and we would like to say THANK YOU to those who become a proud member and support our efforts.

Thanks to members:
  • Education is for FREE and we create more FREE articles and videos
  • We provide new features (members) that can speed up learning.
  • 53% of our page views (82,000 each month) do not have page ads.
  • We switched video ads from 14% of our videos (50 out of 345).
We have invested 4 years of our time in this website and we estimate it will take about 5 years before we can quit our jobs and dedicate all our time to develop considerably our website which should help to truly achieve our vision of free education for everyone; all thanks to support of our members only. Become a proud member

Visitor Voting

Visitor comment (we like):
"I like you both, you read Dale Carnagie, you want what most of us want and you are trying to do something useful for all! You are already successful! Blessings!"
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
  Become a Proud Member