Business Intelligence Using Microsoft BI Tools

Lesson 5: Working with sub report in SSRS (Tutorial)

Add a new report “MainReport.rdl”

Add a dataset, name “Title”, select checkbox “use a dataset embedded in my report” and insert the SQL query in the query. Click Ok.

Add parameter name “MovieTitle”, prompt “Title:”

Navigate to available values tab and select checkbox “Get values from a query” and select dataset “Title” created earlier. Select value field and label field as Title. Then Click OK.

Add another dataset, name “MovieList1”, select checkbox “use a dataset embedded in my report” and insert the SQL query in the query. Click Ok.

Drag and drop a table into the report designer. Then add the columns from dataset MovieTitle1 into the table.

Drag and drop a Sub report from toolbox into the report designer.

Right click on the sub report and select Subreport Properties.

You will be prompted with Subreport Properties window. Enter name and select “SampleReport.rdl” corresponding to Use this report as a subreport dropbox.

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.

Then navigate to parameters tab, click on Add button. Enter Name “MovieTitle” and value title1.