Note, before getting started with this lesson there are some prerequisites you should know about. Please read my post Getting Started with SQL Server 2008 to ensure you have everything setup correctly, otherwise you will be missing objects required to get the code here to work correctly.
Subreports are an incredibly useful concept within Reporting Services. They allow you to compartmentalize complex logic. They also allow you to create reports that can be used in many different parent reports.
In this lab, we’ll look at how to create a subreport and use it as a region within a parent report. For this example, we’ll create a base report, then a subreport that will function as an executive summary which we can place at the top of the report body. These types of summaries are commonplace in the reporting world.
Let’s get started by creating our base report. This will be identical to the base report used in other labs.
Step 1. Add the main report
As with our other reports, right click on the Reports branch in Solution Explorer, pick Add New Report, and (if you haven’t already disabled it) click next to move past the welcome screen.
Step 2. Set the data source.
Pick the Contoso shared data source, or setup a new source to Contoso, and click Next.
Step 3. Setup the query.
In the query builder, we’ll be using one of our views. Enter this SQL statement:
SELECT [FiscalYear]
,[ProductCategoryName]
,[ProductSubcategory]
,[Region]
,[TotalAmount]
FROM [ContosoRetailDW].[Report].[V_SubcategoryRegionTotalsByYear]
and click next.
Step 4. Set the format.
For the report type we’ll use the simple Tabular format, so just click Next.
Step 5. Determine field placement in the report.
To keep this simple we’ll not use any groups on this report, so just put all report fields into the Details section. You can do it in one easy step by clicking on the top most item (FiscalYear), holding down the shift key, and clicking the bottom item (TotalAmount). This will select all of the fields, just click the Details button to move them. Then click Next.
Step 6. Select the formatting Style
Once again we’ll go with Corporate for the style and click Next.
Step 7. Name the report.
Finally we’ll give the report a name of “Regional Sales by Subcategory Subreports as Report Areas” and click Finish.
Step 8. Format report columns
To make the report a little easier to read, expand the width of the columns and format the Total Amount as Currency. (See the previous labs if you don’t recall how to accomplish this.)
Step 9. Add the subreport
It’s now time to create the subreport. Just like with a regular report, right click on the Reports branch in Solution Explorer, pick Add New Report, and (if you haven’t already disabled it) click next to move past the welcome screen.
Step 10. Set the data source.
Pick the Contoso shared data source, or setup a new source to Contoso, and click Next.
Step 11. Setup the query.
In the query builder, we’ll be using one of our views. Enter this SQL statement:
SELECT [ProductCategoryName]
,[CategoryTotal]
FROM [ContosoRetailDW].[Report].[V_ProdcutCategoryExecutiveSummary]
ORDER BY [ProductCategoryName]
and click next.
Step 12. Set the format.
For the report type we’ll use the simple Tabular format, so just click Next.
Step 13. Determine field placement in the report.
To keep this simple we’ll not use any groups on this report, so just put all report fields into the Details section.
Step 14. Select the formatting Style
Unlike other reports, we will pick Generic for the style and click Next.
Step 15. Name the report.
Finally we’ll give the report a name of “Subreport – Executive Summary” and click Finish. Note that is common to start the names of subreports with the name “Subreport” to make them easier to find.
Step 16. Format subreport columns and body
To make the report a little easier to read, expand the width of the columns and format the Total Amount as Currency. (See the previous labs if you don’t recall how to accomplish this.)
In addition, we don’t need the body to be any wider that what is needed. Click on the text box that has the body title “Subreport – Executive Summary” and shrink it to match the width of the table. Then hover the mouse over the right side of the report and drag it over to bump against the right side of the table.
Gotcha: If you try and shink the body first, it will not go. The right edge of the body can never be less than the right edge of the widest object (or the object whose right edge is farthest to the right).
Step 17. Setup the detail header
Start by changing the titles of the detail grid to “Product Category” and “Total”. Now highlight the entire row by clicking the gray row selector square to the left of the row.
We can change the fore and background color of this row to match those of the main report. You can pick from standard colors, or enter your own color value. As an example of the first, go to the Color property, and from the drop down pick the color white. You will see the property name change to “White”. You could also have chose to just type in the word White.
You can also enter a hexadecimal value for the color. Click on the Background Color property and enter “#1c3a70”. (No quotes, but make sure to include the # so the entered value will be understood as hex and not a standard color, such as “White”.
Note that you can also change the values of each textbox independently, using the same technique. Most commonly though you will want to set the entire row.
Step 18. The “Green Bar” effect
Once upon a time, in a computer room in the distant past, all reports were printed on paper that had alternating blocks of green and white background. This was known as “Green Bar” paper. The color made it easy to follow long lines of text across the page.
It’s possible to setup the same effect within our report today. Highlight the detail row, then in the Background Color of the properties window, click the drop down, then instead of a color pick the Expression option. For the expression, enter:
=iif((RowNumber(NOTHING) MOD 2) = 0, "LightBlue", "White")
Using the MOD function we determine if it’s an odd or even row, and set the background color accordingly. For the colors any color constant or hexadecimal value would work.
Step 19. Add a value for the body header.
When a report is used as a subreport, any headers or footers are ignored. It can be useful to have a nice title though, so in this step we’ll create one.
19.1 Hover over the bottom of the body, and drag it down to expand the body height.
19.2 Now click on the grid. When the grid row/column bars appear, click on the one in the very upper left corner. When you do, the row/column bars hide themselves, and the grid sizing handles appear. In the upper left is an icon that points up/down/left/right. Click on it and drag the grid down, leaving space at the top for a textbox. Also leave a little space at the bottom that can serve as a gap between it and other items that might appear on the main report we place this subreport on.
19.3 Next drag a textbox from the toolbox onto the top of the page. Expand the textbox to take up the width of the body. Increase the font size to 12, make the font bold, and center it.
19.4 We have a place now to put our title, lets grab some data to put there. Add a new dataset by right clicking on the Contoso data connection in the Report Data window.
19.5 Name it “CurrentFiscalYear”, for the query text enter:
SELECT MAX(FiscalYear) AS CurrentYear
FROM [Report].[V_ProdcutCategoryExecutiveSummary]
Click OK to save this new dataset.
19.6 Returning to the textbox, right click and pick Expression. For the expression text, enter:
="Executive Summary for " & Sum(Fields!CurrentYear.Value, "CurrentFiscalYear")
To build the center part of the string, click on the Datasets option under category. Then click on the CurrentFiscalYear dataset. In the Values area, one item appears, Sum(CurrentYear). Click on it to add the text to the current expression.
There is an oddity with getting fields from other datasets then the main one that supplies data to the body, they must be an aggregate expression such as Sum. However, since we are SUMing one value, the subreport will look like.
Design Mode Preview Mode
Step 20. Add subreport to main report.
Adding the subreport is quite simple. First, expand the body to make room above the grid similar to what was done in the above step. Then, drag the subreport from the Solution Explorer onto a blank area of the body.
Positioning it can be a bit of a pain, there’s no nice “put in the center” button. But with a little math you can accomplish it.
Return to the subreport a moment, and click on the grid which should take up the entire width of the body. In the properties window, expand the Size property to see the width. In this case it’s 2.3 inches.
Back in the main report, repeating the procedure with the main report’s grid, we see the width is 6.58 inches. Now it’s easy, (6.58 – 2.3) / 2 yields 2.14 inches. Use this for the left property of the subreport. The width isn’t that important, just set it wide in this case.
Step 21. Preview the report.
As you see, you now have an attractive subreport that you can reuse in multiple reports.
Arcane Code 