Professional SSRS 2016 Preview: Chapter 7–Advanced Report Design

This post is part of the series: “Professional SSRS 2016 Book Preview Posts” which are excerpts for my Wrox Press book: Professional SQL Server 2016 Reporting Services and Mobile Reports.  Each of the posts is a condensed version of the material covered in a corresponding chapter from the book.  The goal for this posts is to provide useful and meaningful information you can use.  For more comprehensive details, I refer readers to the rather lengthy book itself.  Note that large portions are copied directly from the book manuscript that may refer to figures and screen capture images that have been removed for brevity.

• Pagination, and page headers and footers
• Report headers and footers
• Text formatting and textbox properties
• Embedded formatting and HTML text styling
• Master/detail reports
• Working with subreports
• Creating a document map

The samples and exercises for this chapter are included in the SSDT solution that was intro- duced in Chapter 3. If you have not set up the book samples and exercises, return to Chapter 3 and complete those tasks.

The real power behind Reporting Services is its ability to creatively use data groups and com- binations of report items and data regions. You can add calculations and conditional format- ting by using simple programming code. By programming code, I mean anything from a single line of code to an entire library. Whether you are an application developer or a business report designer, this chapter contains important information to help you design reports to meet your users’ requirements and to raise the bar with compelling report features

PAGINATION AND FLOW CONTROL

With respect to page layout, reports have two sizing modes: interactive and printable. When users run a report in their web browser and use it interactively, they typically don’t care that much about the page size. This is particularly true with reports that have wide content like a matrix region that can dynamically grow horizontally with the data. When a report is printed or rendered to a print- able format like a PDF or Word file, we need to be mindful about fitting the content on pages.

The report designer does not make page sizing and dimensions particularly obvious so it’s an easy thing to miss. Fortunately, the science behind page sizing is pretty simple. Page dimension properties are grouped into two objects that you can select in the designer; these are shown in Figure 7-1. With the Properties window visible, click outside the report body to show properties for the report. Here you will see the InteractiveSize and PageSize properties. Expand these to see the individual Width and Height properties for each group.

FIGURE 7-1: Report properties in the designer.

If you are not using this default paper size, you can get more options by right-clicking the Report area and choosing the option to show the Report Properties dialog (shown in Figure 7-2). Either way, you will be setting the same properties.

FIGURE 7-2: Report Properties, Page Setup page.

Aside from the default US paper sizes shown here because my machine is set up with an English/ US

locale, you can also choose from metric sizes or several specialized layouts. The margins are set to

1 inch by default, which means that we lose two inches of printable space in each page. I’ve changed my margins to a quarter inch, which will maximize the page space and work with most modern printers.

TIP Quarter–inch margins (about .64 cm) work with most modern printers. Some older laser printers can require up to a half inch of “gripper space” at the top of the page. This is the area of the paper the printer grabs and attaches to the drum during the print cycle. If you are trying maximize the print area, either test the report on each printer model or just be more conservative with the margin settings.

Click the Body (the area within the designer report boundaries) to check the Size properties, which are shown in Figure 7-3. To fit within a printable page, the body Width must be less than the report width added to the left and right margins.

FIGURE 7-3: Properties window, Size properties group.

TIP To prevent blank pages:

Body Width + Left Margin + Right Margin

must be less than Report Width. If not, the page will spill onto a second page. If there is no printable content, just empty space, the printer will feed blank paper between each page of report content. You can test this by saving to a PDF file to see if alternate pages are blank.

HEADERS AND FOOTERS

The terms “header” and “footer” can refer to three different areas of a report: the report header and footer, the page header and footer, and the header and footer areas of any data region(s) in the report. Table and matrix regions may have headers and footers associated with groups within the region.

NOTE In this paragraph, I refer to the “effective” report header and footer. My use of this term is important to understand because Reporting Services doesn’t actually designate a specific header or footer area. The area of the report body above a table or other repeating data region is “effectively” the page header.

The report header and the footer each occur only once: on the first page, at the top of the entire report (header), and at the end of the report, on the bottom of the last page (footer). In Reporting Services, there is no designated report header and footer area. The effective report header is simply the blank space containing textboxes and other report items placed above any data regions in the report body. Likewise, the effective report footer is the space below any data regions. If you were to place a table two centimeters below the top of the report body, this would give you a report header two centimeters tall. It’s as simple as that and because there is no set limit to the number of data regions or other items you can add to a report (and you can designate page breaks at any location), all the space above, below, and between these items is essentially header and footer space.

Follow along by opening the completed Ch07 – Headers and Footers report, which is in the Wrox

SSRS 2016 Sample project.

You have a lot of flexibility when displaying header and footer content. In addition to the standard report and page headers and footers, data region sections can be repeated on each page, creating additional page header and footer content. Figure 7-4 shows a table report with each of the header and footer areas labeled.

Group Row Headers

Table Header

FIGURE 7-4: Headers and footers in the designer for a typical table report.

Page Footer

Figure 7-5 shows the first rendered page of this report. Comparing the design view of the report in Figure 7-4 with the first page rendered in Figure 7-5, note the name of the report in bold text followed by execution date and summary of years and months included in the report, in the (report) header area. The page header is omitted from the first page. Below the table, you see the page footer showing the report user’s network ID and the page number summary.

FIGURE 7-5: Rendered table report with headers and footers.

Figure 7-6 shows the second page, beginning with the page header. Of particular note is the sum- mary of data displayed on this page in “phone book” or “dictionary” style where the year and month of the first and last items on the page are summarized in the header. Also note that even though the group for 2012 is split between pages one and two, the table header is displayed at the top of the page and the year is repeated for quarter 3. In the table, after all four quarters, a group footer shows a total of the year and then a grand total is displayed at the end of the table for all three years.

A common purpose of the page header is to display an abbreviated form of information in the report header. Naturally, you don’t want to show redundant information on the first page so it makes sense to hide the page header. Right-click the report body to add a page header and then right-click the page header to show the Page Header Properties dialog, used to set a number of related properties.

Uncheck the “Print on first page” property under “Print options.”

TIP Remember that any property displayed on a property page dialog like this is also available in the Properties window, to the right of the report designer

in SSDT. The Properties window can optionally be viewed in Report Builder, enabled from the View ribbon.

FIGURE 7-6: Page header shown on the second page.

FIGURE 7-7: Page Header Properties dialog.

Tablix Headers and Detail Cells

Listen up: This little golden nugget will help you make more sense of table and matrix reports that you inherit from other developers, or when you resume working on a report you haven’t touched for quite a while. When reviewing the sample report created for you, how do you know which cells in the table serve as group headers or are detail cells that aggregate values? Figure 7-8 shows you the secret. This feature is subtle but very useful.

FIGURE 7-8: Row group header boundary.

When designing a table, the fields that you drag into the Row Groups list create groups with row header cells. Fields that you drag into the table columns become ungrouped detail cells. A double broken vertical line (circled in Figure 7-8) is displayed to differentiate between group headers to the left and detail cells to the right of the line. It is apparent in this report that rows are grouped first

by Year and then by Quarter. The (Details) group, displayed in the Row Groups list, doesn’t have a group header, so the MonthName cell is on the right side of the double-broken line. Because the dataset already groups sales details by month and only returns one month per row, no month-level grouping is necessary in the table.

In a matrix, column group header cells are also separated from the detail cells with a horizontal double-broken line.

Designing the Page Headers

NOTE As I describe these steps, don’t be concerned with following along. You will step through this process in the exercise at the end of the chapter.

With the Report Data window visible and Built-in Fields expanded, I will step you through the process as I create the header. I can drag-and-drop fields directly into the report body. This is actually a slight misnomer because the “built-in” objects are not really fields per se, but global objects.

The word “field,” in this sense, refers to something that returns a value. Regardless, it is useful information you can add to the report header or footer areas. If I were to drag a built-in field, say [ExecutionTime , onto the report body, a textbox will be added at the drop location with a reference to the intended object. If the object or field were dropped into an existing textbox, the inserted value becomes a placeholder for an expression. The expression can coexist with other expression placeholders, and literal text in various formats, within a textbox.

I begin with the Execution Time field, which I will drag to the existing textbox in the top-left corner of the page header region. The textbox already contains the text Sales as of: in italicized format.

Next, I drop the Execution Time field/object just to the right of the existing text to create a composite message: Sales as of: [Execution Time]. Just peachy.

The others are similar. In the lower left-hand corner, I drag the UserID field to the textbox right after the text report user.

In the textbox located in the lower-right corner of the page footer, I add these built-in fields to assemble the following phrase: Page [&PageNumber] of [&TotalPages] as shown in Figure 7-9.

Assembling the page footer.

You can see that the page header and footer decorations are nearly complete. Our objective is to show the first and last set of month/year values displayed on the page. Well, because the page has already been paginated by the page rendering engine, there is no need to do this work if it’s already been done for us.

Instead of using “field” references to get these values, we can use the aggregate functions FIRST()

and LAST( with the report items in the table that already return the values. I right-click in the

textbox to create a placeholder and then edit the placeholder to add a value expression. I use the callouts in Figure 7-10 to add these expressions in order; first the Month and Year report items are referenced in the range “From” group, with the FIRST functions. In the second, range “To” group, the LAST aggregate function returns the last Year and Month value for that page.

=FIRST(ReportItems!Year.Value)

=FIRST(ReportItems!MonthName.Value)

=LAST(ReportItems!MonthName.Value)

FIGURE 7-10: Assembling the page header.

=LAST(ReportItems!Year.Value)

TIP Pay close attention to the spaces added to the literal text when assembling static text and expressions in a textbox.

The textbox below the large report title contains two placeholders with expressions, shown in Figure 7-11. The textbox contains the static text “Includes: ” followed by an expression in the first placeholder that concatenates the first month and year in the dataset results. The static text “ to ” separates the second placeholder with an expression that concatenates the last month and year in the dataset.

=First(Fields!MonthName.Value, “SalesSummaryByMonth”) & “, ” & First(Fields!Year.Value, “SalesSummaryByMonth”)

=Last(Fields!MonthName.Value, “SalesSummaryByMonth”) & “, ” & Last(Fields!Year.Value, “SalesSummaryByMonth”)

FIGURE 7-11: Assembling the report header.

This example demonstrates how values can be obtained from a dataset outside the boundaries of a data region like a table, list, or matrix. Outside of a data region, an aggregate function must be used to reduce multiple field rows to a single value and the dataset is passed in the second function argument.

Textbox Expressions and Placeholders

hen combining values in a textbox, you really have two choices. The previous examples demonstrate the use of multiple placeholders within a textbox. Think of a placeholder as if it were a separate textbox embedded in the text for the textbox. The advantage of using multiple text placeholders in a textbox is that each can be styled with separate properties for styling features like the font, weight, and color.

NOTE Internally, placeholder text is stored as a “textrun” object within the textbox paragraph element. This is important to understand for error reporting and debugging expressions.

Internally, textboxes contain two levels of objects: paragraphs and textruns, which the visual designer calls a placeholder. When using multiple placeholders in a textbox like the previous example, this is actually a paragraph containing multiple textrun elements. This would be apparent if an incorrect expression were entered, resulting in an error. If you see the text #Error displayed, use the Output window to see the error text, which might look something like this:

[rsRuntimeErrorInExpression] The Value expression for the textrun

‘Textbox4.Paragraphs[0].TextRuns[3]’ contains an error: The expression references an item ‘TotalPages_’, which does not exist in the Globals collection. Letters in the names of Globals collection items must use the correct case

Preview complete — 0 errors, 1 warnings

TIP When you see an expression error displayed on the report as #Error, use the

Output window in SSDT to view the error or warning information.

The other approach for combining values in a textbox is to use a single expression to concatenate all the values into a single text value. Rather than the compounded placeholder text used to display the page numbers, the following expression would display the same text without the option for mixed styling features within the text:

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

The Thing About Repeating Table Headers

You’ll notice that the table column headers are repeated on the second page, which is a sensible design pattern. If you look at the Tablix Properties (Figure 7-12), you’ll find settings to repeat column and row headers on each page. Seems pretty simple, right?

FIGURE 7-12: Tablix Properties dialog.

Somewhere in the product history, as the table and matrix data region evolved into the Tablix

(circa 2008), this feature stopped working but it is easy to fix. I do not have a good explanation for why the following step is necessary; and I think we can all agree that this might seem to be overly complicated, but I can at least show you how to get this working.

With the table selected, click the little down arrow icon displayed to the right of the Column Groups in the Grouping Window and switch on Advanced Mode (see Figure 7-13). Advanced Mode shows several hidden objects in the Tablix data region that are used internally to manage header, groups, and cell properties. As you can see, the inner workings of the Tablix are quite complex.

WARNING I can tell you from experience that the advanced properties of the Tablix can be a Pandora’s Box if you don’t know exactly what you’re doing in there. If you plan to experiment with these properties, I suggest you make a backup copy of the Tablix or your report before you proceed.

FIGURE 7-13: Setting up repeating page header in Advanced Mode.

Making this change is easy. Just select the first “Static” Tablix Member displayed in the Row

Groups list.

In the Properties window, change the RepeatOnNewPage property from False to True. That’s it! Use the down arrow icon to switch Advanced Mode off.

Test the report and you’ll see that the table header is repeated on each page.

COMPOSITE REPORTS AND EMBEDDED CONTENT

Beyond common, basic report design, more sophisticated reports are created by using more advanced and less common design techniques. In general, these fit into two different categories, which include using advanced properties of different report item components, and by combining multiple data regions to create composite report designs. The composite report design pattern is intriguing because there are virtually limitless options when allowing one grouped data region to repeat instances of another report item or region.

Before opening the door to the mind-blowing possibilities of composite data regions, let’s explore

the depth of some fundamental report item building blocks. OK, “mind-blowing” might be a stretch but some of the most fundamental report items have considerable capabilities, especially when used as components of a well-constructed solution.

UNLOCKING THE TEXTBOX

The textbox is one of the most fundamental and common report items. Generally, all text and data values are displayed using textboxes. The cells of a table and matrix contain individual textboxes. In addition to the text displayed, several useful properties manage the placement, style, and presentation of data.

The Font Color BackGroundColor, and BackGroundImage properties make it possible to dress up your report data with tremendous flexibility.

The BorderStyle properties of a textbox are similar to those of other report items (such as a rectangle, list, table, and matrix). Once you have mastered the textbox properties, you should be able to use these other items in much the same way. With a table, group separation lines are created by set- ting the border properties for textboxes in header and footer rows (typically by selecting the entire row and setting the textbox properties as a group).

Three property groups are used for borders. In the Properties window, these groups are expanded using the plus sign (+) icon to reveal individual properties. The group summary text can actually be manipulated without expanding the properties, but it’s usually easier to work with specific property values. The BorderColor BorderStyle, and BorderWidth properties each contain a Default value that applies to individual properties (Left, Right Top, and Bottom) that have not otherwise been set. This provides a means to set general properties and then override the exceptions. By default, a textbox has a black BorderColor and a 1-point BorderWidth, with the BoderStyle set to None. To add a border to all four sides, simply set the default BorderStyle to Solid. Beyond this, you can

use individual properties to add more creative border effects.

Padding and Indenting

Most report items support padding properties, which are used to offset the placement of text and other related content within the item. Padding is specified in points. A unit of measure from the printing industry, a PostScript point is 1/72nd of an inch, or approximately 1/28th of a centimeter.

Figure 7-14 shows the four padding properties, in the Padding group of the Properties pane, applied to all textbox items. The Padding properties provide an offset between textbox borders and the contained text. You can use this to indent text and provide an appropriate balance of white space.

FIGURE 7-14: Textbox Padding properties.

Three similar properties provide more flexibility for text indentation. You can use the HangingIndent,

LeftIndent, and RightIndent properties to control paragraph-style text in rich-formatted text- boxes. These properties also enable the new Word rendering extension to apply hanging, static text indentations.

Embedded Formatting

This feature allows the text in a textbox to be structured and formatted, much like a document or web page. Textboxes support two modes: a single-value expression or a range of text containing multiple expression placeholders.

To format a range of text, simply highlight the text in the textbox and use the toolbar or Properties window to set properties for the selected text. Figure 7-15 shows a range of highlighted text with the HangingIndent and LeftIndent properties set to 18 points and 12 points, respectively. Note that certain keywords and phrases within the text are also set using bold and italic. Some title text has also been isolated with bold and larger fonts.

FIGURE 7-15: Text with embedded formatting.

Embedded HTML Formatting

Another option is to embed simple HTML tags within text. This provides a great deal of flexibility for using expressions or custom code to return formatted text. The HTML tags listed in Table 7-1 are supported.

TABLE 7-1: HTML tags supported with embedded formatting

TAG	DE SC RIP T I O N
<A>	Anchor. For example: <A href=”http://www.somesite.com”>Click Here</A>
<FONT>	Sets font attributes for a group of text. Used with the attributes color face point size size, and weight For example: <FONT color=”Blue” face=”Arial” size=”6″>Hello</FONT>

continues

TABLE 7-1 (continued)

TAG	DE SC RIP T I O N
H1>, H2>, <H3>, H4>, …	Headings.
<SPAN>	Used to set text attributes for a range of text within a paragraph.
<DIV>	Used to set text attributes for a block of text.
<P>	Paragraph break.
<BR>	Line break.
<LI>	List new line.
<B>	Bold.
<I>	Italic.
<U>	Underscore.
<S>	Strikeout.
<OL>	Ordered list.
<UL>	Unordered list.

Embedded tags can be entered directly into a textbox or read from a dataset. When using static text, rather than text fed from a dataset, you must set the MarkupType property for the text placeholder. To do this, highlight the text containing the embedded HTML tags, right-click, and choose Text Properties. In the Text Properties dialog, on the General page, set the Markup type property to the selection shown in Figure 7-16, “HTML – Interpret HTML tags as styles.” When working with

data-bound text, the difference is subtle; highlight the field placeholder in the textbox, right-click, and choose Placeholder Properties….”

The Text Properties and Placeholder Properties dialogs have identical selections for the Markup type, shown in Figure 7-16.

I have provided an example in the sample report named Ch07 – Sales Order Notes. The

SalesOrderNote table contains formatted text with HTML markup tags like this:

<h2>Check This Order Before Shipping</h2>

Customer specifically needs 10 <b>RED</b> helmets.<br>She is <i>unhappy</i> that an earlier order was placed for <b>RED</b> helmets but helmets were <b>MAROON</b> colored.<br>If product is not bright red color, please:

<ol>

<li>Cancel and <b>do not ship</b> the order</li>

<li>Call the sales person</li>

<li>Do not charge the standard re-stocking fee</li>

</ol>

FIGURE 7-16: Placeholder Properties dialog.

When this text is interpreted, the formatting tags are applied and it will appear as it does in

Figure 7-17.

FIGURE 7-17: Previewed report with data-bound embedded formatting.

Would You Like Them in a Box? Using a Rectangle as a Container

I love containers. To be honest, I am a little obsessive about collecting boxes, bags, backpacks, and cases. Putting things in the right kind of container provides a sense of order and security. In Reporting Services, containers are used to encapsulate, repeat, and manage collections of content.

The rectangle report item is far more than a simple box to display on a report. It provides contain- ment for multiple items that can be treated and managed as a single unit. The rectangle also has sev- eral properties for managing the flow and placement of information on report pages. For example, placing a group of textboxes and other items in a rectangle ensures that they all end up on the same page. You can set the rectangle to force a page break before or after its contents. By default, borders of a rectangle are not displayed, so out of the box the rectangle is really more functional than it is a visual control. By setting a few properties, you can use a rectangle to display a background image, fill color, and borders. It can also be set to repeat with an adjacent Tablix group, and on each page and the table, list, or matrix splits across pages.

The utility of a rectangle is clear when you compare the behavior of a table to a list data region. Both data regions are based on the Tablix object. Functionally, a list acts like a table with a single column and detail row and no headers with one significant difference. Every cell in a table and matrix contains a textbox. The area of a list data region is, in fact, a rectangle—which is the reason that any report items dragged into that area remain the size and position you place them. If you

were to drag a rectangle into a table cell, it would replace the cell textbox with the rectangle. Then, constituent items dragged into the same space would no longer fill the cell.

Rectangles can simplify report design when used conservatively. Because the designer shows a rect- angle with gray borders, similar to a textbox, returning to a previously designed report may take some poking around to decipher where each of the container objects reside.

Figure 7-18 shows an example of a rectangle used to contain several textboxes to display the report parameters. During design, I may need to adjust the size and placement of the elements, and using the rectangle allows me to move the entire block of items as a group.

FIGURE 7-18: Report header content contained in a rectangle.

As requirements evolve, I can easily cut and paste the rectangle as a single object and place it in the page header (Figure 7-19) or put it below the matrix.

FIGURE 7-19: Page header content contained in a rectangle.

The following example shows how using a rectangle report item container manages page flow. In Figure 7-20, a page break is set with BreakLocation=End. This forces a page break after the rect-angle is rendered so the matrix content would be on the next page of the report.

FIGURE 7-20: Setting a page break in the rectangle properties.

The example report uses long parameter lists, which take up a lot of page space. I recently had a consulting client who asked that when a report similar to this one is used in interactive mode, the report header appear as usual, like the one shown in Figure 7-21, but when the report is rendered to Excel, the parameters be displayed only on a separate worksheet.

Using a rectangle to control the page name and conditional page break was quite easy by using

an expression on the Disabled property. Figure 7-22 shows the expression that conditionally toggles the Disabled property based on the built-in RenderFormat.Name property. This effectively disabled the page break in all cases where the report is not rendered to Excel.

FIGURE 7-21: Long parameter text in page header.

Expression

FIGURE 7-22: Setting the PageBreak Disabled property with an expression.

The resulting Excel workbook (shown in Figure 7-23) contains two worksheets, each named as a result of the PageName properties for the rectangle used to manage the parameters and the matrix. The Excel renderer translates any explicit page breaks in the reports as a new worksheet tab.

FIGURE 7-23: Report rendered to Excel.

DESIGNING MASTER/DETAIL REPORTS

Most data can be expressed in a hierarchal fashion. Whether stored in related tables in a relational database, as dimensional hierarchies in a cube or tabular structure, or as separate spreadsheets or files, structured data can usually be organized into different levels, which is often a natural way to present information for reporting. Common examples of master/detail data include invoices and line items, customers and orders, regions and sales, categories and products, colors and sizes, and man- agers and workers. The best way to organize this data in a master/detail report depends largely on how your users want to see it visualized. For each master record, details may be presented in a rigid tabular or spreadsheet-like form or in free-form layout with elements of different sizes and shapes placed at various locations within a repeating section. And, of course, details may also be expressed visually using charts, icons, and gauges.

The last consideration for master/detail report design is whether the data source for the master records and detail records can be combined into a single data stream. If records exist in different tables in the same database, this is a simple matter of joining tables using a query. If the records can’t be combined in a query or view, the two result sets should expose the fields necessary to join

them, and a subreport can be used. This section about composite reports explores techniques for combining data ranges to filter a single dataset and then uses subreports to combine two separate data sources.

When constructing a hierarchal report, you have a few different techniques at your disposal, including using a table, matrix, list, or subreport.

Repeating Data Regions: Table, Matrix, and List

In Chapter 6, you learned that the purpose of a data region is to repeat rows and columns for each instance of a group or detail record, and you’ve already seen several examples using a table and matrix. The purpose of a data region is to repeat report items. You can actually add just about any- thing in place of the default textbox or rectangle container that the designer creates in the detail cells for you.

Table as a Master/Detail Container

By default, all the cells in a table are textboxes unless you drag a different type of item into the

cell. Any embedded content will stretch to the dimensions of the cell and will grow vertically if text wrapping in any columns causes that row to grow. To prevent items from stretching, place a rectangle in the cell first and then add the new report item to the rectangle.

TIP To prevent items embedded in a table detail row from stretching vertically when an adjacent textbox wraps, place a rectangle in the cell first and then add the new report item to the rectangle.

Regions and items embedded in the detail row are repeated vertically, once for every unique detail group value. Detail rows in a table can be used as a container for all other report items and data regions. There is literally no limit to the number or level of embedded items that can be placed into a table, matrix, or list. All regions and report items contained in the table must be bound to the same dataset.

NOTE When using embedded data regions and report items within a table, matrix, or list, the contained data regions and items are exclusively bound to

the containing dataset via the DatasetName property. It is possible to reference a different dataset by using scoped and aggregated field expressions (for example, FIRST, SUM, and so on). You can also use LOOKUP and LOOKUPSET functions or custom code to reference another dataset.

The sample report named Ch07 – Product Category Sales Profile by Year contains a table data

region with drill-down on the first-level group (Year). The detail row contains an embedded column chart with the sales quantity grouped by product category values, as shown in Figure 7-24.

FIGURE 7-24: Multi-level table report with drill-down and repeating chart.

Matrix as a Container

Like using a table as a master/detail container, a matrix can encompass any data region or combination of report items, and repeat an instance of these items both on rows and columns. The detail cells of a matrix are textboxes by default but can be replaced with most any type of report item.

A variation of the theme presented in the previous section, the sample report named Ch07 – Product Category Sales Profile by Year and Country (shown in Figure 7-25) has a matrix based on a dataset containing one additional field, which is used to group columns on the country. The same column chart used in the previous example is repeated both on rows (for each quarter) and on columns for each country.

FIGURE 7-25: Matrix report with repeating chart.

List Data Region as a Container

A list is essentially a single-row, single-column table with no headers. Instead of containing a text- box by default, the detail “cell” or area of a list contains a rectangle. Any items placed within the

list area will remain the same size and position, rather than stretching to fill the area in the designer. This would roughly be the same thing as if you were to replace a table detail cell textbox with a rectangle and remove the header and footer rows.

A list is a great tool for repeating a region of formatted report items with every group of parent values in a hierarchy. Because a list is based on a Tablix object, like a table and matrix, it has the same essential rules of behavior. The repeated data regions and report items in the list area are bound to the same dataset as the container.

The following examples are sample reports that contain one or more report items contained within the cells or repeated detail area of a data region. The sample report named Ch07 – Product Cost and List Price – Embedded Chart is shown in Figure 7-26. A list data region contains several textboxes and a pie chart.

FIGURE 7-26: List report with free-form layout and embedded chart.

The report named Ch07 – Product Cost and List Price – Embedded Table and Chart is shown in Figure 7-27. The list data region in this report contains textboxes, a pie chart, and a table that shows the order detail history for each product.

FIGURE 7-27: Combination report with list, embedded table, and chart.

NOTE An important design consideration when using multiple, embedded data regions is that all report items are based on only one dataset. The dataset must include fields to group the containing region and the details for the data region it contains.

Because only one dataset can be used for both data regions (the list and table) in this example, all necessary fields and details are required. The dataset used to drive the list grouped by products must include the order details for the embedded table.

Subreport

A subreport is a container for another report, visually embedded within the main report. As a method of object reuse, subreports can reduce redundant effort and circumnavigate some other barriers but they do impose some compatibility issues with certain rendering formats. Be cautious and test your designs thoroughly. As a rule, I don’t use subreports unless they are necessary.

As a master/detail design pattern, subreports allow a data region to encompass detail records from a different dataset, or even an entirely different data source. I will demonstrate how to use them in the “Designing Subreports” section later in this chapter.

Drill-Through Navigation

Actions and report navigation are covered in Chapter 12. I include this topic here because report navigation is often an effective replacement for a complex, multi-level hierarchal report. It can be

an effective complement to a summary report that enables report users to get more information and insight by navigating to details rather than including them in a single, comprehensive report. Using navigation actions correctly can help you architect reporting solutions rather than big, cumbersome and monolithic reports.

Consider that a report with four levels of drill-down groups must execute a large query that returns all the records at the detail level, just so the users have the option to drill to a particular branch of detail. Rather than expanding the drill-down branch within the same report, a group item could display a link that allows the user to navigate to another report. In a well-orchestrated solution,

the users’ perception is that they are simply moving around within their report dashboard solution, instead of moving from report to report.

Groups and Dataset Scope

One of the fundamental reasons that master/detail reports work—and are relatively easy to construct—is the principle of dataset scope. The term scope refers to the portion of data from a dataset that is available within a group. When a data region, such as a table, list, or matrix, is rendered, the data is sectioned into the subranges according to a group definition. Any report items or data region items placed in a grouped area, header, or footer are visible only to the data currently in scope. This means that if a table, for example, has a group based on the ProductCategory field and another table is placed in the group header, a table is rendered for each distinct ProductCategory value. Each table instance “sees” a range of detail records filtered by this group value. This can be an incredibly powerful feature, because there is no stated limit on how many items can be embedded

within a group; nor is there a limit on group levels and nested embedded data regions. With that said, we have found it impractical to embed several data regions to create overly complex reports.

In this section, we will apply this principle of group embedded data regions for each data region container. This includes the list, table, and matrix.

More Aggregate Functions and Totals

Previously, you saw how the SUM and AVG functions work in a detail group and in group total rows. Reporting Services supports several aggregate functions and each function accepts one or two arguments that are passed in parentheses. The first argument is the field reference or the expression to aggregate. The second, optional argument is the name of a dataset, report item, or group name to indicate the scope of the aggregation. If not provided, the scope of the current data region or group is assumed. For example, suppose a table contains two nested groups based on the Category and Subcategory fields, respectively. If you were to drag the SalesAmount field into the Subcategory group footer, the designer would create an expression like this: =SUM(Fields!SalesAmount

Value). Remember that a field expression appears as a placeholder in the report design. For

example, the placeholder text [SUM(SalesAmount)] actually represents the full expression:

=SUM(Fields!SalesAmount.Value). Right-click and choose Expression to see the full expression.

The expression would return the sum of all SalesAmount field values within the scope of each distinct Subcategory group range. Table 7-2 shows all of the aggregate functions supported by Reporting Services with a brief description of each, for reference.

TABLE 7-2: Aggregate functions supported in report expressions

FUN C TI ON	DE SC RIP T I O N
AVG()	The average of all non-null values.
COUNT()	The count of values.
COUNTDISTINCT()	The count of distinct values.
COUNTROWS()	The count of all rows.
FIRST()	Returns the first value for a range of values.
LAST()	Returns the last value for a range of values.
MAX()	Returns the greatest value for a range of values.
MIN()	Returns the least value for a range of values.
STDEV()	Returns the standard deviation.
STDEVP()	Returns the population standard deviation.
SUM()	Returns a sum of all values.
VAR()	Returns the variance of all values.
VARP()	Returns the population variance of all values.

In addition to the aggregate functions, some special-purpose functions behave in a similar way as aggregates but have special features for reports, as shown in Table 7-3.

TABLE 7-3: Special-use dataset row functions

FUN C TI ON	DE SC RIP T I O N
LEVEL()	Returns an integer value for the group level within a recursive hierarchy. The group name is required.
ROWNUMBER()	Returns the row number for a group or range.
RUNNINGVALUE()	Returns an accumulative aggregation up to this row.

To demonstrate scoped aggregates, I start with a copy of the sample report: Ch06 – Sales Summary with Groups. As a refresher, Figure 7-28 shows the first page of the report with callouts containing the aggregate expressions for the last two columns.

=SUM(Fields!SalesAmount.Value)

FIGURE 7-28: Different aggregate functions used in totals.

=AVG(Fields!SalesAmount.Value)

The expressions for these two columns are the same for the detail row and the ProductCategory group footer row. With only the field name passed to an aggregate function, SUM and AVG in this case, the current group level is implied. For the details group, the result would be the same as:

=Sum(Fields!SalesAmount.Value, “Details”)

For the ProductCategory group, the implied aggregate value would be the same as:

=Sum(Fields!SalesAmount.Value, “ProductCategory”

With the group name specified in the second function argument, the expression will always apply to that group level. To demonstrate, I’ve removed the last column and then added a new column using this expression in the detail row. Figure 7-29 shows the results. As expected, the category total is applied to every calculation.

FIGURE 7-29: Using a scoped aggregation.

=SUM(Fields!SalesAmount.Value, “ProductCategory”)

Now that we have access to the aggregate value at any level in the group hierarchy, we can calculate the percentage of contribution to the bottom line. Let’s see it all the way through now. The final expression is pretty simple:

=Sum(Fields!SalesAmount.Value)/Sum(Fields!SalesAmount.Value, “ProductCategory”

To assemble the solution in Figure 7-30, The same expression is applied to the detail and group total footer cells in the last column. I’ve also modified the format property for these cells appropriately to display a percentage value.

FIGURE 7-30: Putting the calculations together in a meaningful solution.

DESIGNING SUBREPORTS

When I started using Reporting Services to design reports with nested groups and data regions, my first impulse was to use subreports as much as possible. This seemed like the best approach because I could design simple, modular reports and then put them together. The programming world pro-

motes the notion of reusable objects. However, the downside of this approach is that subreports can create some challenges for the report rendering engine, resulting in formatting issues and poorer performance. In SQL Server 2000 and 2005, subreports didn’t render at all in Excel. Improvements have since been made for Excel rendering, but there are inherent challenges for subreports to render consistently for all report rendering formats; particularly with Excel. When using subreports, care- fully test the report to be sure that it will render in the target format.

NOTE Subreports are useful for implementing a variety of design patterns but they are not a cure-all. If you can design a report by embedding data regions into a list, table, or matrix, you are likely to get better results than if you use a subreport to do the same thing.

A subreport is a standalone report that is embedded into another report. It can be independent, with its own dataset, or, using parameters, you can link the contents of a subreport to data in the main report.

NOTE Subreports fall into a small category of SSRS features that have some limitations that have changed and are likely to continue to change as the product evolves. This is why it is important to test your design in all the rendering for-

mats and scenarios where it will be used.

There are some limitations to the content and formatting that can be rendered within a subreport. For example, a multicolumn report simply doesn’t work in a subreport (depending on the rendering format used). If you plan to use multiple columns in a subreport, test your report with the rendering formats you plan to use.

Subreports generally have two uses. The first is for embedding one instance of a separate report into the body of another report with an unassociated data source. The other scenario involves using the subreport as a custom data region to display repeated master and detail records in the body of the main report. From a design standpoint, this makes perfect sense. Using a subreport allows you to separate two related datasets and perhaps even data sources, linked as you would join tables in

a SQL query. It allows you to reuse an existing report so that you don’t have to redesign functionality you’ve already created. However, there may be a significant downside. If the master report will consume more than just a few records, this means that the subreport must execute its query and render the content many times. For large volumes of data, this can prove to be an inefficient solution. Carefully reconsider the use of subreports with large result sets. It may be more efficient to construct one larger report with a more complex query and multiple levels of grouping, rather than assume the cost of executing a query many times.

NOTE I rarely use subreports in standard scenarios to produce master/detail reports. If I need to use a subreport, the main report is limited to just a few records.

A subreport can be linked to the main report using a correlated parameter and field reference so that it can be used like a data region, but this is not essential. A subreport could be used to show aggregated values unrelated to groupings or content in the rest of the report.

Creating a subreport is like creating any other report. You simply create a report and then add it to another report as a subreport. If you intend to use the main report and subreport as a Master/ Detail view of related data, the subreport should expose a parameter that can be linked to a field in the main report. In the following walk-through, you’ll build a simple report that lists products and

exposes a subcategory parameter. The main report will list categories and subcategories, and the product list report will then be used as a data region, like a table or list in previous examples.

Federating Data with a Subreport

When the data source for a master data region is different from the data source for detail records, using a subreport can be just the ticket for creating a master/detail report. The following example combines report data from two different data sources.

In the sample project, you will find two reports named Ch07 – Product Orders Subreport and Ch07

– Product Details (subreport container). The “container” report contains a list whose data source is the relational sample database: WroxSSRS2016. The other report contains a table with a data source based on the Adventure Works Multidimensional SSAS database.

This literally means that we’ll be using two different languages, T-SQL and MDX, and a parameterized expression will provide some translation between the two. Records in the Product table, located in the WroxSSRS2016 database, can be related using the ProductKey column. This column contains values from the Product table in the WroxSSRS2016 database.

The master report, Ch07 – Product Details (subreport container), is shown in Figure 7-31. This report contains a list data region that is bound to the following query and whose data source is the WroxSSRS2016 data warehouse database.

FIGURE 7-31: Adding a subreport to the main report.

The dataset for this report is pretty simple and just returns a set of products that have cost and price information. The ProductKey column is present so we can pass it to the subreport as a parameter:

SELECT

ProductKey, ProductName, ProductCategory, StandardCost, ListPrice

FROM

Product

WHERE (StandardCost IS NOT NULL) AND (ListPrice IS NOT NULL ORDER BY ProductName

Figure 7-32 shows the child report, Ch07 – Product Orders Subreport, in the Designer. This report is simply a table bound to an MDX query. The data source for this dataset is the Adventure Works Multidimensional SSAS database. I have added a callout to the figure to show the expression used to convert the report parameter to an MDX member reference passed into a query parameter named ProductUniqueName.

Within the dataset query expression, the ProductUniqueName parameter is used in the WHERE clause using the STRTOMEMBER MDX function. This is a standard filtering convention, commonly used in MDX queries.

FIGURE 7-32: Modify the dataset parameter using an expression.

NOTE For more examples of parameter-passing in MDX queries, refer to

Chapter 10.

SELECT

{

[Measures].[Reseller Sales Amount]

[Measures].[Reseller Order Quantity]

} on Columns, NON EMPTY [Date].[Date].[Date].Members

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, MEMBER_VALUE

on Rows

FROM [Adventure Works

WHERE

(

STRTOMEMBER( @ProductUniqueName ) [Date].[Calendar].[Calendar Year].&[2013

)

The details group for the master report list data region is set to the ProductName field. This satisfies the requirement that, for a data region to contain a nested data region object, it must have a group defined. You create the subreport by dragging and dropping the Ch07 – Product Orders Subreport report from the Solution Explorer into the list area.

Note that regardless of the dimensions of a subreport at design time, when dropped into a contain- ing report, it always appears as a square area that usually takes up more design space than neces- sary (which also expands the dimensions of its container). After resizing the subreport, I also had to resize the list to appear as it does in Figure 7-33.

Right-click the subreport and choose Subreport Properties to set the parameter/field mapping, as shown in Figure 7-33. The Subreport Properties dialog, shown in Figure 7-33, is used to map a field in the container report to a parameter in the subreport.

FIGURE 7-33: Product Details report with subreport in designer.

Navigate to the Parameters page, and then click Add to define a parameter mapping. Under the Name column, select the ProductKey parameter. Under the Value column, select the ProductKey field. Click OK to save these changes and close the Subreport Properties dialog.

This completes the report design. Using lists and subreports typically makes the design process more ad hoc and artful than when you use more rigid tables. Go back and check the size and placement

of items so that they fit neatly within the subreport space. You often have to go through a few itera- tions of preview and layout to make the appropriate adjustments.

At this point, you should be able to preview the report and see the nested table/subreport, as shown in Figure 7-34.

FIGURE 7-34: Product Details report in preview.

NAVIGATING REPORTS

Reports of yesterday were static, designed for print. At best, they could be previewed on a screen. To find important information, users had to browse through each page until they found the information they were looking for. Today, you have several options to provide dynamic navigation to important information—in the same report or to content in another report or an external resource.

Creating a Document Map

The document map is a simple navigation feature that allows the user to find a group label or item value in the report by using a tree displayed along the left side of the report. It’s sort of like a table of contents for report items that you can use to quickly navigate to a specific area of a large report.

You typically will want to include only group-level fields in the document map rather than including the detail rows.

NOTE The document map is limited to the HTML, Excel, and PDF rendering formats. In the Excel and HTML formats, the document map may not survive when you save report files to an older document format, such as Pocket Excel on an older Windows Mobile device.

The sample report Ch07 – Products by Category and SubCategory (Doc Map) demonstrates this feature. I’ve added the ProductCategory and ProductSubcategory groupings to the document map. In the Group Properties dialog for the Category row group, on the Advanced page, I set the Document map property using the drop-down list to the ProductCategory field.

WARNING Be careful to specify the document map label property only for items you want to include in the document map. For example, if you specify this property for a grouping, don’t do the same for a textbox containing the same value. Otherwise, you will see the same value appear twice in the document map.

Figure 7-35 shows a report with a document map. The report name is the top-level item in the document map, followed by the product category and subcategory names.

You can show or hide the document map using the leftmost icon in the Report Designer’s Preview or the Report View toolbar in the Report Manager or SharePoint Report Viewer web part after the report is deployed to the server.

NOTE My experience has been that the drill-down and document map features usually don’t work well together because they duplicate some functionality. Use the document map to navigate to a visible area of the report.

FIGURE 7-35: Multi-group table report with document map.

EXERCISES

In the book, you would work through two exercises. In Exercise 1, you will assemble expressions and titles in the report and page headers and save the resulting report as a template, which you can use to build new reports.