Chapter 13. Advanced Report Formatting

Thus far, this book has discussed some basic formatting, such as highlighting text in bold, changing background color, and adjusting report layout. This chapter provides a broader view on formatting.

Report Formatting, Report Border, and Layout toolbars (see Figure 13.1) are conveniently available to do some design-time formatting.

Figure 13.1. Report formatting related toolbars.

The Item Properties window and Property Pages dialog box shown in Figure 13.2 provide access to a full set of formatting properties, including runtime formatting.

Figure 13.2. Item Properties window and Property Pages dialog box.

Formatting-Related Report Item Properties

Formatting properties can be subdivided into three categories:

• Background-control properties—Designed to control background, such as background color, of data presented on a report. These properties are shown in Table 13.1

Table 13.1. Background-Related Report Item Formatting Properties

• Output text control properties—Designed to control textual (string, numeric, and date) output, such as color, font, currency, and date formatting. These properties are shown in Table 13.2

Table 13.2. Other Formatting Properties

• Border appearance control properties—Designed to control a border surrounding output. All report items, except Line have a border. These properties are shown in Table 13.2

Formatting properties accept expressions to provide dynamism of representation, based, for example, on the retrieved data or parameters. Please refer to Chapter 8, “Expressions,” for more details about expressions.

Other formatting properties (Table 13.2) control the appearance of text displayed in the item (font, color, international properties, alignment, and so on) and appearance of the border (color, width).

Formatting Numeric and Date/Time Values

Both numeric and date/time values allow for standard and custom formatting strings. A standard formatting string refers to a single character that specifies the desired output format for a value.

Any numeric format string that does not fit the definition of a standard format string (either numeric or date/time) is interpreted as a custom format string. For example, format string “d!” is interpreted as a custom format string because it contains two alphabetic characters, even though the character "d" is a standard date/time format specifier. This is true even if the extra characters are white spaces. Similarly, character "h" is interpreted as a custom format because it does not match any standard format specifiers.

In addition, developers can exercise greater control over how values are formatted by using custom format specifiers.

Resulting output strings are influenced by the settings in the Regional Options control panel and locale control properties: >Language, NumeralLanguage, and NumeralVariant. Computers with different locale-specific settings generate different formatting for numbers and dates.

For example, February 4, 2006 returns 2/4/2006 when run with the United States English (en-US) locale, but it returns 04.02.2006 when run with the German (de-DE) locale.

You can learn more about globalization and locale options at http://www.microsoft.com/globaldev/getWR/steps/WRG_lclmdl.mspx.

Standard Numeric Format Strings

Numeric format strings are used to format common numeric types. A standard format string takes the form Axx, where A is a single alphabetic character called the format specifier, and xx is an optional integer called the precision specifier. The format specifier must be one of the built-in format characters (see Table 13.3). The precision specifier ranges from 0 to 99 and controls the number of significant digits or zeros to the right of a decimal. The format string cannot contain white spaces.

If the format string does not contain one of the standard format specifiers, the format string is ignored.

The following abbreviations are used: letter ’d’ indicates a digit (0-9), letter ’E’ or ’e’ denotes an exponent, ’±’ indicates that you can use either the plus or minus sign in the expression. The exponent always consists of a plus or minus sign and a minimum of three digits. By default, SSRS prefixes negative numbers with a minus sign.

The precision specifier indicates the minimum number of digits desired. If required, the number is padded with zeros to produce the number of digits given by the precision specifier. The number is padded to its left for integer types formatted with Decimal or Hexadecimal specifiers and in digits after the decimal point for other specifiers. Padding is ignored for the ’R’ format specifier.

Table 13.3. Standard Numeric Format Strings

Additional information about standard numeric format strings can be found in the .NET Framework Developer’s Guide at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandardnumericformatstrings.asp.

Custom Numeric Format Strings

Table 13.4 shows custom numeric format strings, descriptions, and output examples. You can exercise greater control over how values are formatted by using custom format specifiers.

Table 13.4. Custom Numeric Format Strings

Numbers are rounded to as many decimal places as there are digit placeholders to the right of the decimal point. If the format string does not contain a decimal point, the number is rounded to the nearest integer. If the number has more digits than there are digit placeholders to the left of the decimal point, the extra digits are copied to the result string immediately before the first digit placeholder.

Additional information about custom numeric format strings can be found in the .NET Framework Developer’s Guide at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstrings.asp.

Standard Date/Time Format Strings

A standard Date/Time format string consists of a single character format specifier character from Table 13.5.

Table 13.5. Standard Date/Time Format Strings

Additional information about standard date/time format strings can be found in the .NET Framework Developer’s Guide at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandarddatetimeformatstrings.asp.

Custom Date/Time Formatting

Table 13.6 describes the custom format specifiers and examples of output. Note how the percent sign (%) converts standard to custom specifiers. For example,d specifies short date pattern, but %d specifies day of the month. When % is used with a character not reserved for custom formatting, the character displayed is literal. For example, a format string %n results in the output n

Table 13.6. Custom Date/Time Formatting

Additional information about custom date/time format strings can be found in the .NET Framework Developer’s Guide at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomdatetimeformatstrings.asp.

Creating Alternating Colors for the Lines on a Report

To alternate color of the lines in the table, you can use the function RowNumber().

To generate alternating colors for a table’s Detail row, similar to those displayed in Table 13.7, you can set the BackgroundColor property of all columns and the table’s Detail row to the expression =IIF((RowNumber("MyTable") Mod 2) = 0, "LightGrey", "White").

Table 13.7. Alternating Table Row Colors

What if alternating colors need to be set for the group header (or footer)? In this case, you can employ the RunningValue() function to return a row number of a group’s header.

If you have a table with a single group that uses =Fields!ProductId.Value as a group expression and you only display the group’s header in the result, you can set the BackgroundColor property for all columns in the group’s header equal to the expression =IIF(RunningValue(Fields!Name.Value, CountDistinct, Nothing) Mod 2=0,"Gainsboro", "White") to generate alternating colors, similar to Table 13.8.

Table 13.8. Design of Alternating Colors for the Group Header

The resulting output is presented in Table 13.9. Note the number of items displayed in the Items Sold column. Although each group contains more than one hundred rows—each group’s header is displayed only once, thus Items Sold displays aggregate number of rows in a group.

Table 13.9. Presentation of Alternating Colors for the Group Header/Footer

Similarly to a single group, to display alternating colors when there is more than one table group, you can use RunningValue() on the combination of all group expressions, like this:

=RunningValue(CStr(Fields!ProductId.Value) & CStr(Fields!InnerGrouping.Value), CountDistinct, Nothing)

Paging Report

Pagination support depends on a rendering extension. PDF and TIFF (Image) formats are page oriented and allow you to precisely set page properties. HTML and Microsoft Excel are not page oriented. CSV and XML do not support pagination and ignore pagination properties.

SSRS provides several properties to support pagination: PageBreakAtEnd, PageBreakAtStart, PageHeight, PageWidth, InteractiveHeight, and InteractiveWidth.

PageHeight and PageWidth

These properties are used to control physical page sizing for PDF- and image-rendering extensions to insert page breaks based on the value of those properties. These properties accept strings in the format {FloatingNumber}.{unit designator}, where a unit designator could be: in, mm, cm, pt, and pc.

InteractiveHeight and InteractiveWidth

These properties are used for logical page sizing by the HTML- and Excel-rendering extensions. HTML allows specifying interactive page size that creates interactive breaks and allows navigating through pages, using a toolbar. Excel-rendering extensions insert "soft" page breaks into the resulting spreadsheet (can be seen through the Excel menu View Page Breaks). Both HTML and Excel pages are based on approximate page size and provide less precise page breaks than page-oriented formats. These properties are similar in format to PageHeight/Width. You can disable soft page breaks by setting InteractiveHeight to 0. However, if the report contains a large amount of data, this might negatively impact perceived performance—the user will not see a report until rendering of all data is complete.

PageBreakAtEnd and PageBreakAtStart

Setting these properties to True allows report designers to add page breaks at the beginning or the end of a rectangle, table, matrix, list, chart, or group. To take advantage of these properties, the following approach is often used:

• Create a group with the grouping expression

=System.Math.Ceiling(RowNumber(Nothing)/{Number of Rows per page})

• Set PageBreakAtEndProperty = True

• Repeat table column headers by setting the table property

• Set RepeatHeaderOnNewPage = True

To dynamically adjust the number of rows in a page, a developer can pass a report parameter and use the following group expression:

=System.Math.Floor(RowNumber(Nothing)/(Parameters!RowsPerPage.Value)).

For physical page formatting, the PageBreak property adds page breaks in addition to those controlled by PageHeight and PageWidth. For example, when a report is rendered to Excel, this property breaks down the report to individual sheets. For HTML rendering, if PageBreakAtEnd and PageBreakAtStart are supplied, Interactive Height and Weight are ignored.

There are differences between different renderers on the amount of data included in a rendered report. For example, if a designer uses the toggle action, the PDF report renderer does not include a collapsed section, whereas the Excel renderer includes all sections, including collapsed ones, and provides an interface similar to the toggle action.

HTML pagination is based on current visibility. If a part of a report is collapsed, it will be included on the page if it fits in a collapsed state, even though expanding this section will take its size outside of the intended page size.

Pagination might improve perceived performance of a report—the first page will be rendered and presented to a user while SSRS continues rendering the remaining pages.

To access a particular page of a report, add the following to the URL:

&rc:Section={PageNumber}.

Summary

SSRS supports a comprehensive set of formatting capabilities through the report item’s properties, which control output appearance.

Some of the key properties are as follows:

• BackgroundColor—Controls background color of the item. When a report requires alternating colors for rows of output, this property can be used in conjunction with functions RowNumber() and RunningValue.

• Color—Controls the color of the text.

• Format—.NET Framework formatting string to apply to the item. The following are examples of format strings:

• ###.## formats a value of 012.30 to the following output 12.3

• (###)###-#### formats a value of 1234567890 to the output (123)456-7890, which is typical presentation of a phone number

• C formats a value of 1234.56 to typical presentation of currency $1,234.56

• yyyy-MM-dd HH:mm formats 11:59PM on 1/2/2006 to 2003-01-02 23:59

• Language—Controls locale formatting. For example, when set to "English (United Kingdom)" currency sign in formatting changes from $ to ε

• PageBreakAtEnd and PageBreakAtStart—Control pagination before and after an item.

• PageHeight and PageWidth—Control physical page sizing for PDF- and image-rendering extensions.

The following chapter discusses functionality that SSRS provides to simplify navigation in large reports and within the hierarchy of reports.