Designing Your Report

The most important phase of any project, big or small, is the design. You must know what you are trying to accomplish before you start building the solution. A SQL Server Reporting Services (SSRS) report by itself is a relatively small project, but you will save time and frustration by understanding the requirements and coming up with a design before launching SQL Server Data Tools (SSDT).

Depending on the requirements for documentation at your company or department, you may end up just using a white board or paper to sketch out your design. For reports that require multiple grouping levels, calculations, or any advanced features, spend some time figuring out how the report should look. If you are lucky, the person requesting the report will provide a design layout along with the requirements.

Your company may have a very formal procedure to follow for new report requests with explicit requirements. Or, maybe a report request will made with just a phone call. In this chapter, you’ll go through the process of building a report based on a simple request.

The Report Layout

Now that you have more details, you can begin to mock up the report layout on a white board or paper. You come up with the design shown in Figure 5-1.

Figure 5-1. The report layout

The sales manager approves the layout, and you are ready to build the actual report.

Building a Report with Grouping Levels

This report will have three levels, but it will still be a relatively simple report. The skills you learn in this chapter will be easily applied to more complex reports.

Follow these steps to get started:

1. Using SSDT, create a new report project with the name Grouping Level Reports in a solution called Beginning SSRS Chapter 5.

2. Create a shared data source pointing to AdventureWorks2016 and also named AdventureWorks2016.

3. Add a new report to the project named Sales by Territory.

4. Add a data source named AdventureWorks to the report. The data source should point to the AdventureWorks2016 shared data source.

5. Add an embedded dataset to the report named SalesByTerritory that uses the AdventureWorks data source. Use this query:

   SELECT YEAR(OrderDate) AS OrderYear, C.CustomerID, SUM(TotalDue) AS Sales,
       T.TerritoryID, T.Name AS Territory, s.Name AS Store
   FROM sales.SalesOrderHeader AS SOH
   JOIN Sales.SalesTerritory AS T ON SOH.TerritoryID = T.TerritoryID
   JOIN Sales.Customer AS C ON SOH.CustomerID = C.CustomerID
   JOIN Sales.Store AS S ON S.BusinessEntityID = C.StoreID
   GROUP BY C.CustomerID, T.TerritoryID, T.Name,
       YEAR(OrderDate), S.Name;

6. Add a table to the report design canvas.

7. Add the CustomerID, Store, and Sales fields to the Data row.

The report design should look like Figure 5-2.

Figure 5-2. The report design

Add a Grouping Level to a Table Row

So far, the detail row is in place. The detail row will be nested inside a grouping level which is based on TerritoryID. There are two ways to add grouping levels, and you will learn both methods. Follow these steps to add a grouping level to a table:

1. Right-click any cell in the data row.

2. Select Add Group ➤ Row Group ➤ Parent Group as shown in Figure 5-3.

   

   Figure 5-3. Add a parent group

3. This brings up the Tablix group dialog box. Select TerritoryID in the Group by dropdown box.

4. Select Add group header.

5. The dialog should look like Figure 5-4.

   

   Figure 5-4. The Tablix group properties

6. Click OK. Figure 5-5 shows the table with the first grouping level.

   

   Figure 5-5. The first group added to table

In this figure, the Sales data cell is selected. Notice that an orange bracket on the left of the CustomerID cell is visible. This indicates the grouping level of the selected cell. In this case, it is the detail level. Click one of the empty cells. Now the bracket can be found on the TerritoryID cell as shown in Figure 5-6.

Figure 5-6. The bracket is located on TerritoryID

This means that the cells in the middle row belong to the TerritoryID group. Add the Sales field to the empty cell on the right. Because this is a grouping level outside the details, the field is automatically summed as shown in Figure 5-7.

Figure 5-7. Sales is automatically summed

In the CustomerID column, add the Territory field to the empty cell. The placeholder doesn’t show it, but it is the first value found in the group. Since there is one Territory for each TerritoryID, this expression will work. Preview the report; it should look like Figure 5-8 at this point.

Figure 5-8. The report preview

Add a Grouping Level to the Grouping Window

The grouping window at the bottom of the report provides another way to add and configure grouping levels. Column Groups, on the right, are used for matrix reports. You’ll learn about matrix reports in the section “Building a Matrix Report” later in this chapter.

At this point, you should see the TerritoryID row group in the window after switching back to design view. To add the next grouping level, follow these steps:

1. If the Grouping window is not visible below the report canvas, right-click the report and select View ➤ Grouping.

2. In the Row Groups window, click the down arrow next to TerritoryID.

3. Select Add Group ➤ Parent Group as shown in Figure 5-9.

   

   Figure 5-9. Add aParent Group

4. Select OrderYear for the Group by property in the Tablix Group dialog.

5. Check Add group header.

6. Click OK. The Row Groups section will now look like Figure 5-10.

   

   Figure 5-10. The row groups

7. In the empty cell in the Sales column, add Sales. This will be the total for the year.

8. In the empty cell in the Store column and second row, create an expression with the following formula: =Sum(Fields!Sales.Value)/CountDistinct(Fields!TerritoryID.Value). This will give the average sales over the territories, in other words, the average of the sum.

The report design should look like Figure 5-11.

Figure 5-11. Thereport design

When you preview the report, you will see that there is some cleanup to do. The report looks like Figure 5-12at this point.

Figure 5-12. The report inpreview mode

Formatting the Report

Follow these steps to begin formatting the report:

1. Switch to design view.

2. Format the four cells referring to the Sales field to currency with a thousands separator and no decimal points. Remember that once you format one cell, you can select the other three and paste in the format at one time.

3. Decrease the width of the Order Year and Territory ID columns.

4. Increase the width of the Store column.

The report is in the default group layout that you get when adding grouping levels to the report, but you can always rearrange it. You will now add two new rows to modify the layout. Follow these steps:

1. Right-click the TerritoryID cell and select Insert Row ➤ Inside Group ➤ Above. This adds a new row to the TerritoryID group.

2. Right-click the TerritoryID cell again and select Insert Row ➤ Outside Group ➤ Above. This adds a new row to the OrderYear group. The table layout should look like Figure 5-13.

   

   Figure 5-13. The report layout with two new rows

3. In column 2, move the Territory ID heading to the cell above TerritoryID.

4. In column 3, move the Territory data field up one row.

5. Move the Customer ID heading above the CustomerID data cell.

6. Type the word Name above the Territory data cell. The layout should look like Figure 5-14 at this point.

   

   Figure 5-14. The report preview after rearranging cells

7. In column 4, move the Store heading to the cell above the Store data cell.

8. In the top row, above the average sales expression, type in Average Sales. The layout should look like Figure 5-15.

   

   Figure 5-15. The layout after rearranging column 4

9. In column 5, change the heading in row 1 to Total Sales.

10. In row 3, the territory header row, type in Total Sales.

11. In row 4, add the Sales field which will automatically sum.

12. Format the cell as currency with no decimals and with a thousands separator.

13. In row 5, the detail header row, change from the Sales field to the words Total Sales. The layout should look like Figure 5-16.

    

    Figure 5-16. The layout after rearranging column 5

14. Select the Average Sales column. Set the alignment to Align Right.

15. Select the Total Sales column. Set the alignment to Align Right.

16. Preview the report. It should look like Figure 5-17.

    

    Figure 5-17. The formatted report in preview mode

You can use color and other formatting aspects to highlight the grouping levels. Follow these steps to format the groups:

1. Switch back to design view.

2. Select the top row and view the Properties window. Change the FontSize to 12 pt, the BackgroundColor to CornflowerBlue, and the Color property found in the Font section to White.

3. Repeat the formatting to the second row.

4. Select the third row. Change the BackgroundColor to LightBlue and FontWeight to Bold.

5. Repeat the formatting to the fourth row.

6. Select the fifth row. Bold the font.

Now when you preview the report, it looks like Figure 5-18.

Figure 5-18. The report preview with formatting

As you scroll through the report , you will see that the headings are lost on subsequent pages. To enable the headings to repeat on each page, follow these steps:

1. Switch back to design view.

2. Click the down arrow located on the right of the Column Groups window and select Advanced Mode as shown in Figure 5-19.

   

   Figure 5-19. Select Advanced Mode

3. When Advanced Mode is selected, the Row Groups and Column Groups will expand. You will see Static sections as shown in Figure 5-20.

   

   Figure 5-20. The expanded grouping window

4. Using the Properties window , change the RepeatOnNewPage value to True for each Static area found under Row Groups.

Now when you preview the reports, the headings will repeat on each page.

Building a Report with an Alternate Layout

After viewing the report, the manager verifies that the numbers are correct but would like a different layout. The alternate layout resembles the report you created with the wizard in Chapter 2 as shown in Figure 5-22.

Figure 5-22. An alternate report layout

To create a report with this layout follow these steps:

1. Add a new report to the project named Sales by Territory 2.

2. Add a data source to the report named AdventureWorks pointing to the AdventureWorks2016 shared data source.

3. Add an embedded dataset to the report named SalesByTerritory pointing to the AdventureWorks data source with this query:

   SELECT YEAR(OrderDate) AS OrderYear, C.CustomerID, SUM(TotalDue) AS Sales,
       T.TerritoryID, T.Name AS Territory, s.Name AS Store
   FROM sales.SalesOrderHeader AS SOH
   JOIN Sales.SalesTerritory AS T ON SOH.TerritoryID = T.TerritoryID
   JOIN Sales.Customer AS C ON SOH.CustomerID = C.CustomerID
   JOIN Sales.Store AS S ON S.BusinessEntityID = C.StoreID
   GROUP BY C.CustomerID, T.TerritoryID, T.Name,
       YEAR(OrderDate), S.Name;

4. Add a table to the report canvas.

5. Add CustomerID, Store, and Sales to the Data row.

6. Add a parent row group to CustomerID.

7. On the Tablix group dialog, fill in TerritoryID as the Group by property.

8. Check Add group header. Click OK to accept the properties.

9. Add a parent row group to TerritoryID.

10. Fill in OrderYear as the Group by property.

11. Check Add group header. Click OK to accept the properties. The report should look like Figure 5-23.

    

    Figure 5-23. The report design with grouping levels

12. Delete the Order Year and Territory ID columns. This does not delete the groups.

13. Right-click the Customer ID column and select Insert Column ➤ Left.

14. Repeat three more times so that there are four empty columns.

15. Type these values in the header row: Order Year, Average Sales, Territory ID, Name. The report design should look like Figure 5-24.

    

    Figure 5-24. The report layout after adding headings

16. The second row is part of the OrderYear group, but if you drag OrderYear to the cell underneath the Order Year heading, it will automatically sum since it is a numeric field. Type in [OrderYear] instead.

17. In the third row of the Territory ID column, type in [TerritoryID]. Again, make sure that this field doesn’t sum the values.

18. Add the Territory field to the cell under Name in the third row.

19. Using the Expression dialog, add this expression to the cell under Average Sales in the second row:

    =Sum(Fields!Sales.Value)/CountDistinct(Fields!TerritoryID.Value)

20. Add the Sales field to the two empty cells under the Sales header. These will automatically sum which is correct.

Now you have a grouping report with an alternate layout. When you preview the report, it should look like Figure 5-25.

Figure 5-25. The report preview before formatting

You are now ready to format the report. Follow these steps:

1. Format all cells involving Sales to currency with no decimal points and use a thousands separator.

2. Select the header row and view the Properties window. Change the BackgroundColor to CornflowerBlue.

3. Change the FontSize to 12 pt and the Color property in the Font section to White.

4. Decrease the width of Order Year, Average Sales, Territory ID, and Name columns.

5. Increase the width of the Store column.

6. Change the BackgroundColor of the second row to #8fb3f3.

7. Change the BackgroundColor of the third row to #c7d9f9.

8. Right-align the Sales column. The report design should look like Figure 5-26.

   

   Figure 5-26. The report layout with formatting

The report has very specific instructions for sorting each level. Follow these steps to sort the data:

1. Through the Group Properties dialog, change the sort order of the OrderYear group to descending.

2. Change the sort order of the detail row to Sales descending.

3. Change the sort order of the TerritoryID group to use Territory instead of TerritoryID.

The final set of tasks is to add the header and footer and modify the behavior of the headers. Follow these steps to complete the report:

1. Enable the Advanced Mode of the grouping window.

2. Set the RepeatOnNewPage to True in the Properties window for each Static level found in the Row Groups side.

3. Change the KeepWithGroup to After for each Static level found in the Row Groups.

4. To allow the header row to stay in place when scrolling down, select the top Static item.

5. Change the FixedData property to True.

6. Reposition the table so that it is in the top left corner.

7. Tighten the canvas by dragging in the edges.

8. Add a Report Page footer and Report Page header.

9. Add a text box to the header with the text Territory Sales.

10. Change the font size to 18 pt. Center the text box.

11. Add the Execution Time to the footer by dragging the field from the Built-in Fields folder in the Report Data window.

12. Add a text box with this expression to the footer:

    ="Page " & Globals!PageNumber & " of " & Globals!TotalPages

13. Change the report’s top and bottom margins to 0.25in or 0.635cm.

Preview the report and try out the scrolling feature. Make any additional adjustments that are needed. The report should look like Figure 5-27.

Figure 5-27. The Territory Sales report with an alternate layout

If you scroll down, you should see that the header row stays in place.

Building a Report with a Space-Saving Layout

The alternate layout shown in the previous section may be preferred, but it takes more space than the default layout. There is a way to save some space, however. Follow these steps to create a report with a space-saving layout:

1. Right-click the Sales by Territory 2 report in Solution Explorer and select Copy.

2. Press CTRL + V to create a copy of the report.

3. Change the name to Sales by Territory 3.

4. Double-click the new report so that it is open in design view.

5. Right-click the second row and select Insert Row ➤ Inside Group – Below.

6. In the cell found in the third row under Order Year, type Territory ID.

7. Expand the Order Year column.

8. In the cell found in the third row under Average Sales, type Name.

9. In the cell found in the fourth row under Order Year, add [TerritoryID]. Type in the placeholder to avoid summing the values.

10. In the cell found in the fourth row under Average Sales, add the Territory field.

11. Delete the Territory ID and Name columns. The report layout should look like Figure 5-28.

    

    Figure 5-28. The space-saving layout

You can now decrease the width of the report canvas or add some additional fields to the report as needed. You may also want to modify the some of the formatting such as bolding the second row.

You can take advantage of the Padding property of the text box to indent the contents of a cell to create a hierarchical effect. Follow these steps to see how to do this:

1. Align the first column to the left.

2. Select the four cells related to the territory as show in Figure 5-29.

   

   Figure 5-29. Selecting the territory cells

3. Open the Properties window.

4. Locate the Indent property in the Alignment category.

5. Change the Left Indent property to 15 pt.

6. Expand the width of the second column slightly. When you preview the report, it will look like Figure 5-30.

   

   Figure 5-30. The report with territory cells indented

Building a Matrix Report

The manager is happy with the Sales by Territory 2 report but has another request. He would like you to create a matrix report that summarizes the sales by territory pivoted by the order year. Pivoting means that the data in the field will become a column header.

While a matrix report may seem daunting at first, it is really easy to create. Just like a regular report, spend some time figuring out the grouping levels. In this case, the row level group will be TerritoryID and the column level group will be OrderYear. You also need to determine the data field. This is the value that you wish to aggregate.

Figure 5-31 shows the matrix control before any cells are populated. Notice that the Data cell is the intersection of row and column groups.

Figure 5-31. The matrix control

To create a matrix report, follow these steps:

1. Add a new report to the project named Sales by Territory Matrix.

2. Add a data source named AdventureWorks pointing to the AdventureWorks2016 shared data source.

3. Add an embedded dataset named SalesByTerritory pointing to the AdventureWorks data source with this query:

   SELECT YEAR(OrderDate) AS OrderYear, C.CustomerID, SUM(TotalDue) AS Sales,
       T.TerritoryID, T.Name AS Territory, s.Name AS Store
   FROM sales.SalesOrderHeader AS SOH
   JOIN Sales.SalesTerritory AS T ON SOH.TerritoryID = T.TerritoryID
   JOIN Sales.Customer AS C ON SOH.CustomerID = C.CustomerID
   JOIN Sales.Store AS S ON S.BusinessEntityID = C.StoreID
   GROUP BY C.CustomerID, T.TerritoryID, T.Name,
       YEAR(OrderDate), S.Name;

4. Drag a matrix control to the report.

5. In the Columns cell, add OrderYear. This is the pivoted data.

6. In the Rows cell, add TerritoryID.

7. In the Data cell, add Sales. It will automatically sum, which is what you need.

8. Right-click the first column and select Insert Columns ➤ Inside Group Right.

9. Add the Territory field to the new cell in the second row.

10. In the first row, change Territory heading to Name. The matrix layout should look like Figure 5-32.

    

    Figure 5-32. The matrix report layout

11. Format the data cell as currency with no decimal places and use a thousands separator.

12. Bold the first row.

13. Preview the report. It should look like Figure 5-33.

    

    Figure 5-33. The matrix report preview

14. Switch back to design view.

15. Right-click the Sum(Sales) cell and select Add Total ➤ Row.

16. Right-click the cell again and select Add Total ➤ Column. The report layout should look like Figure 5-34.

    

    Figure 5-34. The matrix after adding totals

17. Add the Sales field to the empty cell under Total. It will automatically sum.

18. That cell will not pick up the formatting, so format it like the other Sales cells.

19. Bold the bottom row.

20. Bold the rightmost column. When you preview the report, it should look like Figure 5-35.

    

    Figure 5-35. The matrix after formatting the totals

One of the interesting aspects of the table and matrix controls, is that you can start with one and, by modifying the groups, switch to the other. That is the reason that these controls are called Tablix. To change a matrix to a table, follow these steps:

1. Switch to design view.

2. Select the matrix and right-click the cross-section of the handle. Figure 5-36 shows where to right-click.

   

   Figure 5-36. Right-click the handle intersection

3. Click Copy and then paste into the report canvas to create a copy of the matrix.

4. Select the new matrix and click right-click the OrderYear2 grouping level in the column grouping window.

5. Delete the group.

6. On the Delete Group dialog, select Delete Group and Related Rows and Columns. Click OK.

The matrix is now a table as shown in Figure 5-37. You can also start with a table and turn it into a matrix by adding a column group.

Figure 5-37. The matrix is now a table

Summary

Creating reports is an iterative process. You may start with a simple request and need to work with the requester to finalize the design. Or, you may start with an elaborate set of requirements including a detailed layout. Whenever you are creating a report with multiple grouping levels or complex features, be sure to sketch out the layout if a design is not given to you.

Most reports require one or more grouping levels. This chapter walked you through adding grouping levels and formatting the report using several techniques. You also created a simple matrix report. Be sure you understand how to add and configure groups before moving on to the next chapter.

In Chapter 6, you will learn how to make your report dynamic by adding parameters, linking reports, allowing the user to control sorting, and more.