Power BI Quick Measures is a Game Changer

Every few months a feature is added to Power BI that gets my attention and really seems to make  difference.  Quick Measures is one such feature that has my attention.  As a long-time SQL Server Analysis Services practitioner, I subscribe to the belief that anything that you want to calculate should be created as a measure.  The counter argument is that, just like in Excel, every numeric column can be assigned an aggregate function in the “Summarize By” property which makes the column behave like a simple measure.  I fought this at first and then learned to embrace the default behavior because that’s what most self-service BI users will expect.  These “implicit measures” work just fine in Power BI report visuals.  The catch is that client tools like Excel can only use real measures instead of numeric columns, to perform calculations.  In serious SSAS and Power BI solutions I still create measures for any numeric values that should be aggregated.  Here’s a peek at the new Quick measures dialog:

image

For my training classes and workshops, I have been demonstrating the Quick Calcs feature.  In a workshop yesterday, I went to find it and the feature wasn’t there.  This is the screenshot from my slide deck.

image

Change is inevitable but it can be hard to keep-up.  The Quick Calcs feature doesn’t generate any code and it doesn’t create a real measure (so it won’t work if I use Excel as a front-end for the Power BI model.)

I had added the new Quick Measures feature to Power BI Desktop in the Options/Preview page.  This, apparently disables Quick Calcs and enables Quick Measures.  Although it flustered me me for a minute in front of an audience, I found this to be welcome news.  So, what is Quick Measures?  It’s a DAX calculation generator that automatically writes useful measures.  Here’s how it works…  Start by right-clicking or clicking on the ellipsis for a numeric column in the Field list and choose Quick measure…

The Quick measures dialog prompts for the necessary fields, which might be different for each calculation.  The tool generates appropriately formatted DAX calculations.  It even includes conditions to raise errors if used in the wrong context.  There are currently 19 different calculation variations that the tool will generate.  Following are two examples.  Creating a Quick measure from my [Flights] measure and choosing the Airline field for categorization produces this calculation:

Average Flights per Airline =
AVERAGEX(
    KEEPFILTERS(VALUES(‘Airlines'[Airline])),
    CALCULATE(‘Airline Performance'[Flights])
)

I’m not a fan of this naming convention.  It’s silly to begin a measure name with “Average” or “Sum”.  Users struggle enough to find what they need in the field list.  As a rule-of-thumb, name measure with the most important information first.  In this case, perhaps “Flights Avg per Airline” would be a better choice.  If there were several variations of the “Flights” measure, they should be listed together in the field list.

This one calculates the average year-to-date departure delay:

DepDelay YTD =
IF(
    ISFILTERED(‘Flight Date'[Flight Date]),
    ERROR(“Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy”),
    TOTALYTD(
        AVERAGE(‘Airline Performance'[DepDelay]),
        ‘Flight Date'[Flight Date].[Date]
    )
)

Example is often the best teacher.  Beyond the obvious benefit of creating useful calculations, this feature provide several excellent examples that you can use to learn DAX function syntax and usage.  For me, this will be the most value.  I can easily write basic calculations but don’t always remember to use filtering and context preservation functions is ISFILTERED() or KEEPFILTERS(). 

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

WROX.COM CODE DOWNLOADS FOR THIS CHAPTER

The samples and exercises for this chapter are included in the SSDT solution that was introduced 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 cobinations 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.

clip_image002Click here to select Report

Click to select Body

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.

clip_image003clip_image003[1]clip_image003[2]clip_image003[3]

clip_image004Pagination and Flow Control 177

clip_image006

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

clip_image007

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 Quarterinch 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.

clip_image009clip_image010FIGURE 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

clip_image029

Page Header

clip_image030clip_image030[1]Effective Report Header

Detail Row

Group Footers

Effective Report Footer

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.

clip_image032FIGURE 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.

 

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

clip_image037

FIGURE 7-7: Page Header Properties dialog.

Tablix Headers and Detail Cells

clip_image039L

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.

clip_image040Designing 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 actu- ally 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 refer- ence 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 cor- ner 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 com- posite 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.

clip_image042In 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.

clip_image045

=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.

clip_image048=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

When combining values in a textbox, you really have two choices. The previous examples demon- strate the use of multiple placeholders within a textbox. Think of a placeholder as if it were a sepa- rate 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 exam- ple, 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?

clip_image052

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.

clip_image053With 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.

clip_image055FIGURE 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 rect- angle, 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.

clip_image057

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.

clip_image059

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>

clip_image060continues

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>

clip_image062

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.

clip_image064

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 containment for multiple items that can be treated and managed as a single unit. The rectangle also has several 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 rectangle 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.

clip_image066Figure 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.

clip_image068As 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 rectangle is rendered so the matrix content would be on the next page of the report.

clip_image070FIGURE 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 tog- gles 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.

clip_image072

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

Expression

clip_image074

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.

clip_image076

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, includ- ing 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.

clip_image080FIGURE 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.

clip_image082

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.

clip_image084

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.

clip_image086

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 bar- riers 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 con- struct—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 eans 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 argu- ments 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.

clip_image090=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”

clip_image093With 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”

clip_image095To 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 subre- port 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 render- ing 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 functional- ity 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 ren- der 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 parameter- ized 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.

clip_image100

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.

clip_image102

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.

clip_image105

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.

clip_image107

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.

clip_image112

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

…EXERCISES…

Professional SSRS 2016 Preview: Chapter 6–Grouping and Totals

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.

  • Introducing SQL Server Data Tools for Visual Studio
  • Sample reports projects and exercises
  • Using the graphical Query Designer
  • Understanding query groups and table joins
  • Understanding report data flow
  • Understanding report groups
  • Grasping expression basics
  • Utilizing group sorting and visibility

This chapter introduces and explains one of the most fundamental and essential concepts in SSRS report design. All of the data regions—tables, matrices, lists, and visual controls like charts—rely on groups. You will get started with the sample report project in SSDT that contains several completed examples. You will learn the differences between grouping and aggregating rows in queries and performing grouping on datasets within a report region. We explain the flow of data through the report along with the opportunities to filter, group, and aggregate values at multiple points along this path. You will add groups and related header tiles and footer totals to groups in a table and then a matrix. Then you’ll see how multiple groups for hierarchies of grouped results make for effective reporting.

Another important topic, essential to effective report design, is the use of expressions to define groups and properties. We introduce aggregate functions and aggregate scope, which will be expanded in Chapter 7. Finally, you will learn to design a table report with multi-level drill- down navigation so users can explore details within summary groups as needed.

SQL SERVER DATA TOOLS

So far, you have been working with Report Builder, the simplest of the two report design tools. Before moving into the next report design topic, you should become familiar with the other report designer: SQL Server Data Tools (SSDT) for Visual Studio. This report tool was created primarily with the IT professional in mind. An earlier version of the Visual Studio report design tool was once called Business Intelligence Development Studio (BIDS).

Honestly, most of the report design features in both Report Builder and SSDT are the same. Conversely, subtle differences and some capabilities in SSDT don’t exist in Report Builder. The tool you choose to use to accomplish your day-to-day report design work will likely depend on your organizational role and the complexity of the report design solutions. Before making a final decision, you should learn to use both tools, and then decide which one is right for you and your project.

NOTE After several years of experience, I have changed my approach to teaching people to use both design tools. Even so, it is continually a quandary for instructors and authors to develop the best instructional method. In classes, and in previous editions of this book, I gave instructions using one tool, and then

callout some of the differences along the way. Because there are so many subtle differences, this approach can confuse new users. Consequently, if you really want to learn serious report design, educate yourself on both tools, one at a time; and then make your choice.

Getting Started

Before you get started using SSDT, decide where you prefer to store the project files. As you’ll see when you create a new project, the default project file path is in your Windows Documents library. If you are new to Visual Studio, I recommend you use the default project path for this quick introductory walkthrough. I like to keep mine in a folder named Projects in my OneDrive, or in a folder named Projects right off the root of the main storage drive. Whatever your choice, decide now, and create a folder named Projects in your preferred storage location.

The following numbered steps are not a complete exercise, but rather a few simple instructions to help you create a new project. The project and report names you use in this little practice run are not critical so just use some names that make sense to you. You will quickly move on and not use this project and report in future exercises.

1. The first thing is to open SQL Server Data Tools 2015. Depending on your version of Windows, either use the SQL Server 2016 program group, or just type the name of the program until you see it in the search results.

TIP Because SQL Server Data Tools now installs as a separate setup package from the link provided in the SQL Server Installation Center, it may not appear under the SQL Server 2016 program group. This may change over time with sub- sequent version upgrades, or if you had upgraded from a previous version of SQL Server. On my Windows 2012 development server, which has a fresh install, SQL Server Data Tools 2015 shows up in the Apps list under the letter “S.”

2. Run SQL Server Data Tools 2015.

NOTE Running Visual Studio 2015 is exactly the same as running SQL Server Data Tools 2015. Keep in mind that Visual Studio 2015 was the current version of Visual Studio at the time of publication. Newer versions of Visual Studio will work with appropriate updates to the SSDT add-in.

3. After the Visual Studio shell opens, from the File menu, choose New ➪ Project….

4. The New Project dialog opens. From the Templates pane on the left, expand Business

Intelligence and choose Reporting Services, as shown in Figure 6-1.

clip_image005

FIGURE 6-1: Choosing Reporting Services from the New Project dialog.

5. Within the project templates list in the middle of the dialog, select Report Server Project.

6. At the bottom of the window, Enter the name for the project as My first report project

NOTE This should be a short name including spaces and descriptive text, which will create a folder and file names. I usually use a name that briefly describes

the purpose of the project and that I will recognize later. The project name I just recommended is only to get you started and give you some experience with new project setup. As I mentioned earlier, you will soon leave this project behind and then open an existing project I have prepared for you.

7. Note the default project folder path. If you prefer, use the Browse button to navigate to a

Projects folder you created earlier to complete the path in the Location box.

The Solution name will be the same as the Project name unless you change it. In larger,

8. If the project won’t be managed as part of a larger solution, just leave the names the same.

When you’re finished making changes, click the OK button.

9. With a report project open and the Solution Explorer visible on the right-side of the SSDT/ Visual Studio main window, right-click the Reports folder and choose Add ➪ Existing Item….

10. From the Add New dialog, select Report and then give the report file a name. This should normally be a friendly name with spaces and mixed case. When you’re ready, click the Add button.

When the Report Designer opens, notice there are different window panes containing controls and properties docked to both sides of the main window. These are labeled in Figure 6-2, and the key that follows explains the labels. The four tool windows used for reports design include, on the left, the Report Data and Toolbox windows arranged as tabs, and, on the right, the Solution Explorer and Properties windows arranged as docked windows, one above the other with a sliding separation bar. You will see additional windows that you can ignore or hide if you prefer.

1. Report Formatting toolbars

Set properties for the currently selected object or objects in the Report Designer when in Design view. Standard properties include Font Name, Font Size, Weight, Italic, Underline, Foreground color, Background color, Alignment, List styles, and Indentation.

clip_image009

FIGURE 6-2: Window panes in Report Designer.

2. Report Designer view selection tabs

Switch between Design and Preview.

3. Report Data window

The Toolbox window is also displayed in this area. Switch windows using the selection tabs at the bottom of the window pane.

4. Solution Explorer window

Navigate the solution, projects, and project files. Right-click to set project properties, add new items, and perform actions.

5. Report Designer window

This is the main design canvas used to design reports and set properties for data regions and report items.

6. Grouping window

Add, remove, and set properties for row and column groups.

7. Properties window

Displays and manages all properties for the selected object or group of selected objects.

8. Tabbed documents selection

In the default view, windows in this pane are selected using these tabs. The Report Data and Toolbox are used for report design, and other windows are available. Windows can be added with the View menu. Use the window pane toolbar to hide, move, pin, and auto-hide windows.

9. Sliding window separator

In the default view, windows in this pane are docked with a movable separator bar. The Solution Explorer and Properties windows are used for report design and solution management. Use the window pane toolbar to hide, move, pin, and auto-hide windows.

The best way to get started with Visual Studio is to create a test project and spend some time familiarizing yourself with the interface. You can dock, hide, and show windows so that you have what you need at your fingertips, as well as remove the ones you don’t need. If you are new to Visual Studio, don’t go crazy moving things around and closing windows until you are familiar with these tools enough to set things back.

Each window has a familiar pushpin icon that will either “pin” the window in place, or allow it to auto-hide when the mouse moves away from it. Use the auto-hide feature and resize tool windows rather than closing windows you’ll need again. If you close or hide a window using the right-click menu or drop-down arrow in the window header, you can get it back using the View menu. Some

of these windows are only available on the View menu when certain items are selected in the design interface. For example, if you were to close the Report Data window, the option to display it again is listed on the View menu only when you set focus to the report in the designer.

In the next section you will be using a sample solution that contains a set of existing projects pro- vided for you. If prompted to save your changes to this new project, answer “Yes,” but you’re not actually going to use that project any more.

Getting Started with Sample Reports Projects

As mentioned in Chapter 3, copies of all the reports used throughout the book are provided on this book’s website at www.wrox.co . These are organized into two projects contained in a Visual Studio solution in the folders you extract with the book download sample files.

1. On the File menu, select Open ➪ Project / Solution….

2. Use the Open File dialog to navigate to the location where you extracted the book sample files. Locate and open the folder named Wrox SSRS 2016 Report Solution

3. Open the file named Wrox SSRS 2016 Report Solution.sln. SSDT opens with the solution and related projects listed in the Object Explorer on the right side of the report design interface. Figure 6-3 shows SSDT open with a report open in the Design window.

The solution contains multiple projects. Use the Solution Explorer window on the right side of the main window to expand and collapse each project in order to access the files it contains. Reports and shared data sources are also added in Solution Explorer.

Generally, you should use shared data sources as a best practice. The only reason typically not to use them is when you want a report to be completely self-contained. But otherwise, having one place to manage data source connection information makes a lot of sense when these objects all get shipped off to the report server and are making important things happen out there in Business User Land.

clip_image010

FIGURE 6-3: A report in the Design window.

Shared datasets have advantages under very specific circumstances and are required when designing mobile reports. Therefore, you will use them in chapters 17, 18, 19, and 20. As a default choice, for most paginated reports, I generally recommend using embedded datasets.

The Wrox SSRS 2016 Exercises project contains finished copies of all the exercises found at the end of each chapter. The Wrox SSRS 2016 Samples project includes many topic-specific examples, each prefixed with the chapter number. For the chapter samples, it is important to realize that explicit instructions are not provided for every single click and menu selection. Because you have already mastered the essentials getting started creating a new report, data source, and dataset, there is no need to repeat those steps. Some unfinished reports are provided with steps completed up to

the task or topic that you can finish. You will be instructed as to which report to open. In some of cases, you’ll examine a completed report to see how it works.

To follow along, open a copy of the sample report, rather than making changes to the original, so you can go back and start over if necessary. The best way to do that is to copy and paste the report in the Solution Explorer.

TIP Whenever you use the copy/paste method to back up a report, ensure that you save your changes first, because this technique actually makes a copy of the last saved file, rather than the version of the report sitting in memory that may have unsaved changes.

This step is pretty simple, but not entirely intuitive at first. My preferred method is to select the report in Solution Explorer (not the report file name as if renaming the file, but just click once to select the report), and then use Ctrl+C (copy) and Ctrl+V (paste). If you right-click to Copy, then

you must right-click the project to Paste. I typically remove the Copy of file prefix and then describe the backup state in parentheses at the end of the filename. You can see this pattern in the sample project.

TIP Making regular backups provides peace of mind by ensuring that you don’t lose your work. There are three common backup methods for reports: using a version control system such as TFS, making routine copies of the report files in the project folder to a different storage location, and keeping a secondary report project in the solution. The latter allows you to use the earlier-mentioned copy- and-paste backup method.

If you are disciplined about using integrated version control, then it can provide a safety net to prevent catastrophic loss. However, it doesn’t help with the typical cadence of report design. When working on a challenging report, I often create and keep one or more backup copies of the report I’m working on. This provides a way to experiment with different techniques without messing up a

working design. If something goes wrong, rather than trying to untangle it, you can just revert to an earlier copy. After testing to make sure the design is working, you can delete the backup copies.

Graphical Query Designer

The reason there are different query tools is that report developers have varied levels of experience with T-SQL. Therefore, individuals develop strong preferences. When I began my career with databases, at first I relied on graphical query design tools. Now I find it easier to hand-write queries rather than using the graphical Query Designer. These helper tools exist for good reason, and they work well. If you are not a T-SQL aficionado, you should learn how to use the graphical query design tool, but it shouldn’t always be your central experience. With a little practice, many find it easier to hand-code simple queries, rather than relying on the Query Designer.

When using SQL Server as a data source, the dataset in Query Designer is more elaborate than in Report Builder. This graphical Query Designer is actually borrowed from the SQL Server client tools, and is also available in SQL Server Management Studio (SSMS).

Open the Sample Report

If you don’t already have the Wrox SSRS 2016 Report Solution open in SSDT, open it now.

1. In the Object Explorer, expand the Wrox SSRS 2016 Samples project.

2. Expand the Reports folder.

3. In the Object Explorer, click once to select the Ch06 – Sales Summary (query completed report.

TIP There is a subtle difference between selecting an object and selecting the name of the object. For example, when you select a report in Object Explorer, the entire line is highlighted. If you click once and then click again, the name

of the report is selected. The same behavior applies to textboxes in the designer where clicking twice will select the text within the object, rather than the object itself. If this happens, click outside the object and then click it once.

4. Press Ctrl+C and then Ctrl+V to make a copy of the report. The new report should appear in the Object Explorer preceded by the text Copy of in the filename.

5. Double-click the new report copy to open it in the designer.

6. Expand the Datasets node in the Report Data window.

7. Right-click the SalesSummaryMonthProductRegion dataset and select Query… to open the graphical Query Designer.

8. Use this finished query to familiarize yourself with the query design interface as you read the following description about designing a query with this tool. When you’re finished, cancel out of the Query Designer and don’t save changes.

9. Optionally, create a new dataset in this report and follow these steps to duplicate this query.

Save your changes, and then switch back to the original query to check the results.

To design a query in SSDT, create a dataset just like you did previously using Report Builder. Click the Query Designer… button to open the graphical Query Designer. You add tables to the query by using the right-most icon on the toolbar. In the example shown in Figure 6-4 is the end result after completing the following steps. Please note that the following six steps are for reference and are explained in greater detailer in the subsequent steps.

clip_image015

FIGURE 6-4: Tables added to Query Designer.

1. Add the Date Sales, and Product tables.

2. Verify that the join lines were added by the query designer.

3. Use the check boxes to select the fields you see in the example: MonthNumber, MonthName ProductCategory ProductSubcategory ProductName, OrderQuantity, and SalesAmount.

4. Click the Use Group By button on the toolbar.

5. For the OrderQuantity and SalesAmount fields, use the drop-down list to change the Group

By column selection to Sum

6. For the OrderQuantity and SalesAmount fields, copy and paste the field names (in the

“Column” column) to the Alias column.

The designer has four panes separated vertically. The top pane shows the tables that were added to the query, joins, and selected columns. The second pane lets you rename columns, group, aggregate, filter the results, and apply parameters. The query text pane shows the T-SQL script generated by

the designer, and the results pane shows the rows returned by the executed query. Test and run the query by clicking the red exclamation mark icon on the toolbar.

Grouping Query Results

Figure 6-4 shows the Group By button on the toolbar (second from the right). This option adds every column to the GROUP BY clause in the query, and displays the text “Group By” in the seventh column of the fields list pane.

Any numeric columns that should be aggregated (rather than grouped in the query) must have the Group By option changed. Figure 6-5 shows the OrderQuantity and SalesAmount columns changed from Group By to Sum. When you do this, a column alias is created for each aggregated column using placeholder names like Expr1. These should be changed to something that makes more sense. You can actually use the original column name for the alias. To differentiate the alias

from the original column name, the original names have been appended with the word Total, so the alias names are OrderQuantityTotal and SalesAmountTotal

clip_image017

FIGURE 6-5: Column names modified.

Despite the desire to filter data by the Year, that column doesn’t need to be returned in the query results. You can see that the Year column has been included from the Date table and the Output checkbox has been unchecked. To add a parameter, type the parameter name you want to use in the Filter cell preceded by an equal sign (=) and an ampersand (@). In the graphical query designer, a column that is used only for filtering is excluded from the GROUP BY list by changing the selection to “Where.” In this case, the designer references the Year column in a HAVING clause to implement filtering.

Query Joins and Join Types

Because these tables have relationships defined in the database, the tables are automatically joined

on the key columns. In the example, the Sales table is joined to the Date table using the key column in the Date table named TheDate, and the OrderDate column in the Sales table. Likewise, Sales and Product tables are joined on the respective ProductKey columns.

The designer applies inner joins by default, meaning that there must be matching records on both sides of the join. The Date table contains one record for every day in the years 2005 through 2014, but there are not orders for every date, so the inner join will only return date information for exist- ing orders. An outer join returns all records from a specified table on one side of the join, and then all the matching records from the table on the other side.

Changing the join type is simple. Right-click the diamond on the line between the two tables and select all rows from the table on the outer side. In this case, you right-click the join between Date and Sales tables, and then choose “Select All rows from Date.” The join will be designated either LEFT OUTER RIGHT OUTER, depending on the order they were added in the Query Designer.

Execute the query by clicking the exclamation mark icon in the toolbar. When prompted for the

Year parameter, type 2013, and then click OK. Notice in the results pane, a row for December,

2013, for which there are no existing orders. This is the effect of the outer join.

Report Data Flow

You will find that most objects within the Report Designer have many properties and features, and many of them are only used when necessary. Standard objects you will use in the reports all the time have properties that you probably won’t use in most reports, but they provide tremendous flexibility and opportunities to address unique business requirements using creative design techniques.

Some of these properties are depicted in Figure 6-6. You can see (moving left to right) that after query results are processed in a dataset query, an optional filter can be applied after the results are produced. Likewise, conditional filters can be applied as the data enters any data region. After that, the results can be sorted before they are presented to the first group expression. Each data region handles grouping a little differently, but the core concepts are the same. Some data regions support multiple levels of grouping.

clip_image019

FIGURE 6-6: Using Report Designer properties and features.

REPORT GROUPS

Report groups are one of the most important concepts in fundamental report design. Whether your reports will visualize data in a table, matrix, or any type of chart, you will define group expressions either implicitly by dragging and dropping fields in the designer, or explicitly by writing

the expression. Table and list reports do not require a group to be defined, but have limited utility without any groups. Beginning with a table, let’s a take a look at how groups work.

1. In SSDT, if you have any unsaved work you want to keep, save that report.

2. From the Object Explorer on the right, open the report named Ch06 – Sales Summary with Groups

3. You can see that the table is bound to the dataset named

SalesSummaryMonthProductRegion

4. In the Row Groups list below the designer window, use the down arrow button next to the ProductCategory group to choose Group Properties… to open the properties dialog for that group, as shown in Figure 6-7.

clip_image021FIGURE 6-7: The Group Properties dialog.

It’s probably no surprise that the “Group expression” refers to the ProductCategory field (see Figure 6-8), but there are several optional properties and features associated with groups. For example, page breaks can be managed for each group, so a new page is inserted before or after the grouped field value changes.

5. Use the page list on the left to switch to the Page Breaks page in the Group Properties dialog shown in Figure 6-9.

clip_image023

FIGURE 6-8: ProductCategory field as a group expression.

clip_image025

FIGURE 6-9: Managing page breaks.

6. Check the first box to set a page break between each instance of a group.

7. Leave the other checkboxes unchecked.

8. Click the OK button to accept the group changes and close the Group Properties dialog.

There’s no need to inspect the ProductSubcategory group because it’s set to group by the ProductSubcategory field. But, what about the Details) group? This group isn’t normally set to group records for a field by default, so let’s find out if someone might have changed it.

9. Edit the group properties for the Details) group and open the Group Properties dialog.

Hey, look at that! The (Details group has been changed and set up to group by the ProductName field. Why would this make a difference when that field is already being grouped within the detail rows returned by the query? It seems senseless to explicitly group by the lowest-level value that is being returned in the query results.

10. Now check the behavior in the Table Designer. Hover the mouse pointer over and

re-select the two numeric fields for the detail cells in the Order Quantity and Sales Amount columns. You see that the designer applies the SUM function. This is because of the group definition in that Detail group.

The first two groups are set up for drill-down navigation by hiding levels that are expanded with toggle items. With any level collapsed, it is important that the value displayed on this line be an aggregated total representing all the hidden details. This will only happen if an aggregate function (such as SUM) has been applied in each expression on the detail row.

Adding Totals to a Table or Matrix Report

The table, matrix, and list data regions are all based on a common object called the Tablix, and many design techniques are similar for each of these data regions. Each of these objects provides dif- ferent layout options, but the fundamental concept of grouping is the same.

In a table, adding a total to a row group adds a new row that applies an aggregate function (for example, SUM) to all the members of that group. The same applies to a total added to a matrix col- umn group. By adding a total row or column, you’re actually adding a total that applies to the par– ent of the group. Consider this example. Suppose columns are grouped by quarter and then by year. If you were to add a total to the Quarter column group, the total would be for all the quarters add- ing into the year. This means that a total applied to the topmost group will always return the grand total for all records in the data region. This is evident in the example shown in Figure 6-10 where the table data region is grouped and totaled by Subcategory.

clip_image027

FIGURE 6-10: Grouped table data region with totals.

Defining a total for a group at a lower level would create a subtotal break. Totals can be placed before after group values. Adding totals before a row group shows the total above the group in a heading row, and adding the total after shows the total below the group in a footer row.

Subsequently, for a column group in a matrix data region, inserting a total before the group places totals to the left of the group. Adding totals after the group inserts a total column to the right of the group.

Groups, headers, footers, and totals are all related design elements that can take a simple report to the next level and provide significant value. Groups are an essential design concept, and a number of more advanced capabilities have been added as Reporting Services has evolved through newer versions. At the group level, you can now conditionally control things like page breaks and page numbers.

As you continue to review the completed Ch06 – Sales Summary (query completed report, use the following steps to see how the report was be designed.

1. Switch back to Design view.

2. Take a look at the SalesAmount column. The heading label Sales Total was changed from the original field name to make it more readable.

3. The last column was added by right-clicking the header of the column to its left and choosing the option to add a new, blank column. The column label was changed to Avg Sales.

4. In the detail row, right-click the Avg Sales textbox and choose Expression….

5. Review the expression that was added: =AVG(Fields!SalesAmount.Value . Before you click OK to complete the expression, select and copy this text to the clipboard.

6. Right-click the textbox in the Total row of the new column and choose Expression….

7. Review this expression that was added by pasting the expression you copied from the detail cell. Click the OK button.

8. Preview and test the report.

Expression Basics

Looking at the last few examples, when you create a field reference in the designer by dragging

and dropping or selecting from the field list, you will see a field or expression placeholder in square brackets, such as ProductCategory] [SUM(OrderQuantity)]. What you see in the designer

is actually a simplified version of the expression that is stored in the report definition. To view

the actual expression, right-click the placeholder text and choose Expression…. This opens the Expression Editor, showing the entire expression. Expressions always begin with an equal sign and contain a full object reference. Table 6-1 shows these two examples.

TABLE 6-1: Expression Placeholders

PLAC E H O LDE R

E XPRESSION

ProductCategory

=Fields!ProductCategory.Value

[SUM(OrderQuantity)

=SUM(Fields!OrderQuantity.Value)

In this chapter’s exercise, you will see that expressions can include multiple functions and objects. When used in a data region bound to a dataset, an expression simply references a field or fields in that dataset, but can also reference different datasets, as you will see in more advanced examples in the following chapters. Expressions are the real magic behind both simple and complex report designs. This chapter introduces several features and capabilities that are demonstrated in greater detail in the chapters that follow.

Introducing Aggregate Functions and Totals

When you drop a numeric field into a group or table footer cell, an expression is added applying the SUM( aggregate function. The designer assumes that you will want to sum these values, but this function can be replaced with one of several others. Reporting Services supports several aggregate functions, similar to those supported by the T-SQL query language. For now, let’s just consider the concept of basic aggregation using the SUM function.

NOTE You learn about all the functions in Chapter 7, “Advanced Design

Techniques.

When an aggregate function expression is used in a group detail, header, or footer row, 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. If you were to drag the SalesAmount field into the Subcategory group footer, the SUM(SalesAmount expression would return the sum of all SalesAmount values within the scope of each distinct Subcategory group range.

Sorting

You have a few options to sort data in a report, and the best choice will depend on a few factors. Other features (like grouping) require data to be sorted and may negate the capability to sort data after it is grouped. Be mindful that with a large result set, sorting is a costly operation that will add to the overall report execution time. Options may include the following:

Sort Records in the Query—Typically, if records will be presented in a particular order, the most efficient method is to sort the data in the query before it gets to the report. This will aid in grouping as effectively as possible. If other sorting options will be used within the report, presorting in the query may be wasted cycles.

Table Interactive Sorting—Normally used in ungrouped table reports, this feature can be applied to any or all columns of the table. Interactive sorting is applied to the textbox in the table heading for each column. Clicking the sort icon displayed in the column header will

re-order rows the records displayed in that table. Clicking again toggles between ascending or descending order. In a table or matrix data region with groups, interactive sorting can be applied to the rows within a group.

Group Sorting—Every group has an optional Sorting expression. There are times when you may need to group on one set of field values and sort on a different set of corresponding val- ues. This would make sense only when the grouping and sorting fields are distinct within the same set of value ranges.

An example of group sorting can be found in the sample report Ch06 – Orders Matrix Group

Sort. The design of this report is quite simple, and Figure 6-11 shows this in the SSDT designer.

clip_image030

FIGURE 6-11: Design of sample report.

This report began with two datasets. The main query returns the MonthNumber, MonthName Country, and OrderQuantity columns. Take a look at the Year parameter properties and you’ll see that the YearList dataset query is used to populate the parameter list. Edit the SalesSummaryByMonthCountry dataset and you’ll see that the Year parameter is used to filter the results in the query’s WHERE clause. The matrix is added to the report body. The MonthName

field is dragged and dropped onto the Rows header, the Country field is dropped onto the Columns

header, and the OrderQuantity field is dropped in the Values cell to create the expression

=SUM(Fields!OrderQuantity.Value .

If you were to preview the report at this point in the design process, it would look like Figure 6-12. Out of the box, the rows and column groups are naturally sorted alphabetically.

This is probably fine for the country names, but obviously not for the months, which are in alpha- betic order, but not sorted chronologically. This is the reason the MonthNumber column has been included in the query. To fix the group order, you switch to Design view and edit the row group.

Figure 6-13 shows the Sorting page of the Group Properties dialog for the row group.

clip_image032

FIGURE 6-12: Previewing the report.

clip_image034

FIGURE 6-13: Sorting page of the Group Properties dialog.

Changing the “Sort by” expression allows you to display one field and sort by another, as you can see with the report previewed in Figure 6-14.

 

FIGURE 6-14: Displaying by one field and sorting by another.

…EXERCISE…

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.

clip_image002

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.

clip_image006

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 Quarterinch 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.

clip_image009

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

clip_image029

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.

clip_image032

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.

clip_image035

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

clip_image037

FIGURE 7-7: Page Header Properties dialog.

Tablix Headers and Detail Cells

clip_image039

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.

clip_image042In 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.

clip_image045

=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.

clip_image048

=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?

clip_image052

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.

clip_image055

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.

clip_image057

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.

clip_image059

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>

clip_image060continues

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>

clip_image062

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.

clip_image064FIGURE 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.

clip_image066

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.

clip_image068

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.

clip_image070

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.

clip_image072

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

Expression

clip_image074

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.

clip_image076

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.

clip_image080

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.

clip_image082

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.

clip_image084

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.

clip_image086

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.

clip_image090

=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”

clip_image093

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”

clip_image095

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.

clip_image099

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.

clip_image101

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.

clip_image106

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.

clip_image111

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.

Storm Chasers’ Tribute to Bill Paxton

At great effort and considerable coordination, storm trackers gathered in key positions in fields throughout three midwestern states to pay tribute and honor Bill Paxton, star of the movie Twister.  Pretty amazing.

http://variety.com/2017/film/news/storm-chasers-honor-bill-paxton-with-moving-twister-tribute-1201996845/