There are instances when you will need to use a subreport in SSRS. Here I will build a report with a subreport, using Report Builder to demonstrate. The example is there is a table holding sales header data and a table holding sales line data. We will use the header on the report, and the lines on the subreport. To build the report, do the following.
First, open Report Builder and create a blank report. Add a new dataset. Here I am referencing Sales Transactions. This is the header table of a sales transaction.
Next, add a table onto the report:
From the data set, drag fields onto the table. Here I am dragging across the Sales Type (SOP Type), Sales Number (SOP Number), the Customer Number and Customer Name. The SOP Type and SOP Number fields will be used in the subreport.
Run the report to confirm it is working so far.
We will now add a subreport.
Select the table and Insert Row->Inside Group – Below:
Highlight the cells and click “Merge Cells”:
Select Insert->Subreport:
Now create the actual subreport. Open a new Report Builder and create a new report.
Now add parameters to the report. Add SOP Type and SOP Number:
Create a new table and drag across the fields SOP Type, SOP Number, Item Number and Item Description. The SOP Number and SOP Type will match with the Sales header table.
Update the dataset query so it filters based on the parameters:
Save the report. I have called mine Test Sales Lines. Run the report to confirm it works:
Now in the original report, go to the Subreport properties and select the report:
Add Parameters:
For simplicity, I’ve added a parameter on the main report to filter by Sales Number.
Now run the main report. You will see the subreport showing under the main report details. Here we see the sales order lines associated with the sales invoice selected:
I AM SPENDING MORE TIME THESE DAYS CREATING YOUTUBE VIDEOS TO HELP PEOPLE LEARN THE MICROSOFT POWER PLATFORM.
IF YOU WOULD LIKE TO SEE HOW I BUILD APPS, OR FIND SOMETHING USEFUL READING MY BLOG, I WOULD REALLY APPRECIATE YOU SUBSCRIBING TO MY YOUTUBE CHANNEL.
THANK YOU, AND LET'S KEEP LEARNING TOGETHER.
CARL
