The Monitoring & Report Viewer offers you a powerful dashboard that you can use to monitor the health of all ACS servers in your deployment. The dashboard also provides information on network access patterns and trends in traffic that you can use to administer your network efficiently.

The Monitoring & Report Viewer provides you real-time data and vital statistics that help you proactively manage your network and prevent any attacks.

ACS comes with a set of predefined reports that you can run to obtain meaningful information from the log and configuration data obtained from ACS servers. Table 13-2 lists the reports that are available in ACS under various categories. These reports provide information related to authentication, session traffic, device administration, ACS server configuration and administration, and troubleshooting. You can view these reports as tables, graphs, or charts and drill down further for more granular data.

Further, ACS allows you to:

•Filter the data in your report based on your requirements

•Export the report in a CSV format and print it

•Add the report to your list of favorites, from which you can access them frequently

For easy access, you can add reports to your Favorites page, from which you can customize and delete reports. You can customize the reports that must be shared within your group and add them to the Shared page. The Catalog pages provide a rich set of reports on log, diagnostic, and troubleshooting data retrieved from the ACS servers in your deployment.

The reports that reside in these pages can be:

•System reports—Preconfigured with the ACS software; you can view the list of system reports in the Reports > Catalog pages.

•Customized reports—System reports that you have configured and saved (see Customizing Reports).

Note Performance of reports in Internet Explorer (IE) 7.0 is slow because of a phishing filter, which is a new feature in IE 7.0. To resolve this issue, you must get the latest security updates from Microsoft. For more information on this, go to http://support.microsoft.com/kb/928089/.

In addition, ACS 5.2 introduces the Dynamic Change of Authorization (CoA) feature through a new report, the RADIUS Active Sessions report, which allows you to dynamically control active RADIUS sessions. With this feature, you can send a reauthenticate or disconnect request to a NAD to:

•Troubleshoot issues related to authentication—You can use the Disconnect:None option to follow up with an attempt to reauthenticate again.

You must not use the disconnect option to restrict access. To restrict access, use the shutdown option.

•Block a problematic host—You can use the Disconnect:Port Disable option to block an infected host that sends a lot of traffic over the network.

The RADIUS protocol currently does not support a method for re-enabling a port that is shut down.

•Force endpoints to reacquire IP addresses—You can use the Disconnect:Port Bounce option for endpoints that do not have a supplicant or client to generate a DHCP request after VLAN change.

•Push an updated authorization policy to an endpoint—You can use the Re-Auth option to enforce an updated policy configuration, such as a change in the authorization policy on existing sessions based on the administrator's discretion.

For example, if posture validation is enabled, when an endpoint gains access initially, it is usually quarantined. After the endpoint's identity and posture are known, it is possible to send the CoA Re-Auth command to the endpoint for the endpoint to acquire the actual authorization policy based on its posture.

Legacy NAS devices do not support the CoA feature. Cisco plans to support CoA in all its devices as part of the NPF program.

Note For the CoA commands to be understood correctly by the device, it is important that you configure the options appropriately.

For the CoA feature to work properly, you must configure in ACS the shared secret of each and every device for which you want to dynamically change the authorization. ACS uses the shared secret configuration, both for requesting access from the device and for issuing CoA commands to it.

Working with Favorite Reports

You can add reports that you most frequently use to your Favorites page so that you do not have to navigate each time to get to your favorite report. The Monitoring & Report Viewer allows you to:

•View the parameters that are set for each of your favorite reports

•Edit the parameters before you run the reports

For example, after you add a report to your list of favorites, the next time you want to view the same report with a different set of parameters, you need not create another report. Instead, you can simply edit the parameters in your favorite report to generate the report with different parameters.

It also gives process status, process downtime, and disk space utilization for a particular ACS instance in a selected time period.

System statistics

ACS Instance Authentication Summary

Provides RADIUS and TACACS+ authentication summary information for a particular ACS instance for a selected time period; along with a graphical representation.

This report could take several minutes to run depending on the number of records in the database.

When you reload this report, if rate of incoming syslog messages is around 150 messages per second or more, the total number of passed and failed authentications that appear above the graph and the passed and failed authentication count that is displayed in the table do not match.

Passed authentications, failed attempts

ACS Log Information

Provides ACS log information for a particular log category and ACS server for a selected time period.

All log categories

ACS Operations Audit

Provides all the operational changes done in ACS by the administrator for a selected time period.

Administrative and operational audit

ACS System Diagnostics

Provides system diagnostic details based on severity for a selected time period.

Deleting Catalog Reports

Note You cannot delete system reports from the Reports > Catalog pages; you can delete customized reports only.

Step 2 Check one or more check boxes next to the reports you want to delete, and click Delete.

Step 3 Click OK to confirm that you want to delete the selected report(s).

The Catalog listing page appears without the deleted report.

Running Named Reports

Use this page to run reports on specific named reports.

Select Monitoring & Reports > Reports > Catalog > report_type >report_name, where report_type is the type of report (see Table 13-3), and report_name is the name of the report that you want to access or run. Table 13-4 describes the available types and names on which you can run reports.

Understanding the Report_Name Page

Note Not all options listed in Table 13-5 are used in selecting data for all reports.

Table 13-5 <report_name> Page

Option

Description

User

Enter a username or click Select to enter a valid username on which to configure your threshold.

MAC Address

Enter a MAC address or click Select to enter a valid MAC address on which to run your report.

Identity Group

Enter an identity group name or click Select to enter a valid identity group name on which to run your report.

Device Name

Enter a device name or click Select to enter a valid device name on which to run your report.

Device IP

Enter a device IP address or click Select to enter a valid device IP address on which to run your report.

Device Group

Enter a device group name or click Select to enter a valid device group name on which to run your report.

Access Service

Enter an access service name or click Select to enter a valid access service name on which to run your report

Identity Store

Enter an identity store name or click Select to enter a valid identity store name on which to run your report.

ACS Instance

Enter an ACS instance name or click Select to enter a valid ACS instance name on which to run your report.

Failure Reason

Enter a failure reason name or click Select to enter a valid failure reason name on which to run your report.

Protocol

Use the drop down list box to select which protocol on which you want to run your report. Valid options are:

•RADIUS

•TACACS+

Authentication Status

Use the drop down list box to select which authentication status on which you want to run your report. Valid options are:

•Pass Or Fail

•Pass

•Fail

Radius Audit Session ID

Enter the RADIUS audit session identification name on which you want to run a report.

ACS Session ID

Enter the ACS session identification name on which you want to run a report.

Severity

Use the drop down list box to select the severity level on which you want to run a report. This setting captures the indicated severity level and those that are higher within the threshold. Valid options are:

•Fatal

•Error

•Warning

•Info

•Debug

End Point IP Address

Enter the end point IP address on which you want to run a report.

Command Accounting Only

Check the check box to enable your report to run for command accounting.

Top

Use the drop down list box to select the number of top (most frequent) authentications by access service on which you want to run your report. Valid options are:

•10

•50

•100

•500

•1000

•All

By

Use the drop down list box to select the type of authentications on which you want to run your report. Valid options are:

•Passed Authentications

•Failed Authentications

•Total Authentications

Administrator Name

Enter the administrator username, or click Select to select the administrator username, for which you want to run your report.

Object Type

Enter a valid object type on which you want to run your report.

Object Name

Enter the name, or click Select to select the object name, of the object on which you want to run your report.

Authorization Status

Use the drop down list box to select which authentication status on which you want to run your report. Valid options are:

•Pass Or Fail

•Pass

•Fail

Time Range

Use the drop down list box to select the time range on which you want to run your report. Valid options are:

•Last Hour (for the ACS Health Summary report only)

•Today

•Yesterday

•Last 7 Days

•Last 30 Days

•Custom—You must configure a Start Date and End Date, or a Day.

Note Some options are not valid for some Time Range entries of the various reports.

Start Date

Enter a date, or click the date selector icon to enter the start date for which you want run your report.

End Date

Enter a date, or click the date selector icon to enter the end date for which you want run your report.

Day

Enter a date, or click the date selector icon to enter the end date for which you want run your report.

Step 2 Configure CoA on the NAD as follows, which is connected to the supplicant.

aa server radius dynamic-author

client {<ip_addr> - <name>} [vrf <vrfname>] [server-key<string>]

server-key [0 - 7] <string>

port <port-num>

auth-type {any - all - session-key}

ignore session-key

ignore server-key

Step 3 Configure the authentication order (Flex-Auth).

Changing Authorization and Disconnecting Active RADIUS Sessions

Note Some of the NADs in your deployment do not send an Accounting Stop or Accounting Off packet after a reload. As a result of this, you might find two sessions in the Session Directory reports, one of which has expired. Hence, when you want to dynamically change the authorization of an active RADIUS session or disconnect an active RADIUS session, ensure that you always choose the most recent session.

A failed dynamic CoA will be listed under failed RADIUS authentications.

Customizing Reports

To customize a system report:

Step 1 Select Monitoring & Reports > Reports > Catalog >report_type > report_name, where report_type is the type of report, and report_name is the name of the report that you want to customize.

The Reports page appears.

Step 2 Click the radio button next to the name of the report you want to customize, or click the name of the report you want to customize.

Step 3 Click Run.

The Run Report page appears.

Step 4 Modify fields in the Run Reports page as required.

Step 5 Click Run.

The report appears.

Step 6 Click Launch Interactive Viewer.

Step 7 Modify the report contents as required and click Save As to save the customized report with the same, or a different, report name in any Catalog > Reports page.

Note If you save the customized report with the same name as the original system report (overwriting the original system report), you cannot delete it. To restore a customized report to the default, preconfigured system report settings, see Restoring Reports.

The customized report is saved to your specified location.

Restoring Reports

Use the procedure described in this topic, to restore a system report that you have customized back to its original preconfigured system report settings.

This procedure resets all reports that reside in a report catalog type. For example, if you want to reset a specific customized report that resides in the Monitoring & Reports > Reports > Catalog > Endpoint page, this procedure completes that task but also resets all other reports within the Endpoint page.

Step 1 Select Monitoring & Reports > Reports > Catalog > report_type, where report_type is the type of report in which the customized report that you want to reset to the original system report settings resides.

Step 2 Click Reset Reports.

Step 3 Click Yes to confirm that you want to reset the System Report files to the factory default.

The page is refreshed, and the reports in Catalog > report_type are reset to the factory default.

Viewing Reports

This section describes how to view the reports and perform various tasks in Standard or Interactive Viewer. In Standard Viewer or Interactive Viewer, you can navigate to the report, print the data, and export the data to another format. Interactive Viewer also provides the ability to modify the content, organization, and formatting of the report.

About Standard Viewer

From Standard Viewer, you can open a table of contents, navigate the report, export data to spreadsheet format, and print the report.

You can click Launch Interactive Viewer to close Standard Viewer and view the report in Interactive Viewer. See About Interactive Viewer.

About Interactive Viewer

Interactive Viewer provides all the features of Standard Viewer and also supports modifying many aspects of the report's layout and formatting. The Interactive Viewer toolbar is much more extensive than that in Standard Viewer. See About Interactive Viewer's Context Menus.

About Interactive Viewer's Context Menus

Interactive Viewer provides context menus that support many of the formatting, sorting, and grouping tasks you perform on data. Figure 13-4 shows the context menu for columns. Using this menu, you can add or delete a column, sort data, add or delete a group, and work with fonts and text alignment.

You also can sort and filter data, and create calculations and aggregate rows. To access this context menu, select a column, then right-click the column.

Figure 13-4 Context Menu for Column Data in Interactive Viewer

Figure 13-5 shows the context menu you use to modify labels in Interactive Viewer. To display this menu, select and right-click a label. Use this menu to edit the label text or change the text alignment or font properties of the label.

Figure 13-5 Context Menu for Labels in Interactive Viewer

If the report contains a chart, you can use the context menu for charts, shown in Figure 13-6, to modify the chart's formatting, subtype, and other properties.

Figure 13-6 Context Menu for Charts in Interactive Viewer

In each context menu, selecting an entry with a right arrow provides access to related context menu choices. For example, right-clicking a column and choosing Alignment enables you to choose Left, Center, or Right to specify the desired type of alignment.

Navigating Reports

When you open a report in the viewer, you see the first page of data. To view or work with data, you use tools that help you navigate the report.

In the viewer, you can page through a report by using the paging tool shown in Figure 13-7. Using this tool, you can click an arrow to view the first or last page in the report, or move forward or back through the report page by page.

Figure 13-7 Paging Tool

The viewer also supports going to a specific page by typing a page number in Go to page, as shown in Figure 13-8, and clicking the arrow beside the field.

Figure 13-8 Going to a Specific Page

Using the Table of Contents

In the viewer, you can open a table of contents to view the report structure and navigate the report. To open the table of contents, choose the table of contents button in the toolbar. Figure 13-9 shows a report in Standard Viewer with the table of contents open.

Figure 13-9 Using the Table of Contents to Navigate

Each entry in the table of contents is a link to a page in the report. If an entry has a plus sign (+) beside it, you can expand the entry by clicking the plus sign. Figure 13-10 shows an expanded entry.

Figure 13-10 Table of Contents Expanded Entry

To navigate to a specific page, click the related link.

Exporting Report Data

The viewer supports the ability to export report data to an Excel spreadsheet as a comma-separated values (.csv) file, pipe-separated values (.psv) file, or a tab-separated values (.tsv) file. You can select an option to export the column's data type.

The spreadsheet data is formatted like the data in the information object or the template. If you edited column headers or formatted numeric data in the report design, for example, the spreadsheet does not reflect your edits. Figure 13-11 shows a sample CSV output file.

Figure 13-11 Report Data Exported to Microsoft Excel

In Excel, you can resize columns and format the data as you would do for any other spreadsheet.

Available Columns lists the columns you can export from the specified table. You can export any of the data the report uses, including the data in aggregate rows and calculated columns. If the report uses more than one data source, you can export data from each data source separately.

Step 2 In Available Columns, select a column to export and choose the single right arrow.

To select all the columns, choose the double right arrow and click OK.

The File Download dialog box appears displaying the default name assigned to the file and the.csv file extension. You can change the name of the file in Step 4.

Step 3 Click Save.

Step 4 In the Save As dialog box:

•In Save in, navigate to the folder in which you want to save this file.

•In File name, change the name of the file.

Step 5 Click Save.

The CSV file appears in the new location.

When you open the file, you see the data as an editable Microsoft Excel worksheet. You might need to expand columns to view all the data.

Printing Reports

You can print a report that appears in the viewer in HTML or PDF format. Because you can modify the report in Interactive Viewer, Interactive Viewer supports printing either the original report or the report as you modify it.

Step 1 In the viewer, select Print Report.

The Print dialog box appears.

Step 2 In Format field, select HTML or PDF.

Step 3 In the Page Range field, select the pages you want to print.

Step 4 Click OK.

Step 5 Depending on your format selection in Step 2, you have the following options:

•If you selected HTML format, a print dialog box appears in which you can specify printer options.

•If you selected PDF format, the report appears in a PDF viewer plug-in in your web browser. You can review the report then choose Print to specify printer options.

Saving Report Designs in Interactive Viewer

You can save a report design from Interactive Viewer when you are working in the design environment or in Interactive Viewer.

Editing Labels

Labels are fields that can contain static text, such as the report title and items of the footer. In a typical report, some labels are editable and others are not editable.

For example, a template might provide a label to display a confidentiality statement and a label to display the report author's name in the report footer. The confidentiality statement is not editable but the author can insert his or her name in the footer.

If a label such as a column header is editable, you can modify properties such as the font, the font size, the background color, and so on. You also can edit the text of the label.

The text of a column header comes from the data source. If the data source displays column headers in capital letters with no spaces between words, the report design displays column header names in the same way. You can change the content of the column header by using a context menu.

The formatting of the column header comes from the report template or from the theme. If the formatting comes from a report template, you cannot change the formatting. If the formatting comes from a theme, you can change the formatting by changing the theme.

Changing Column Data Alignment

To change the alignment of data in a column, right-click the column and select Alignment from the context menu. Then, choose one of the alignment options: Left, Center, or Right.

Formatting Data in Columns

The default formatting for column data comes from the data source. Typically, you modify the formatting of column data to enhance the appearance of the report. When you format column data, you create the same format for the entire column, except for the column header and aggregate rows. You cannot modify the data itself.

Step 1 Select and right-click a column.

The context menu appears.

Step 2 Select Style > Font.

Step 3 In the Font form, modify any of the style properties you want to change.

You can see your changes as you make them in the Preview field.

Step 4 Indicate whether to apply the new text style to all columns in the report or to the selected column.

The default setting is to apply the new style to the column you selected.

Step 5 Click Apply.

Formatting Data in Aggregate Rows

An aggregate row displays a total, average, or other summary data for a column. You learn how to create an aggregate row in a later chapter. Figure 13-15 shows an aggregate row at the end of a report. Typically, the default formatting of the aggregate row comes from the template or the theme.

You can modify the formatting of the aggregate data value and the formatting of the label that precedes the data value. You cannot modify the text of the label or the data value.

Formatting Data Types

In an information object, as in the relational databases on which information objects are based, all the data in a column is of the same data type, excluding the column header. The column can display numeric data, date-and-time data, or string data.

Each data type has a range of unique formats. Numeric data, for example, can appear as currency, percentages, or numbers with decimal values. Similarly, dates can be long dates, which include the full name of the month, or short dates, in which the month is represented by a number.

Table 13-6 shows the data type formats you can use. Most of the examples in the table reflect the U.S. English locale. If you work in a different locale, you can use the Custom format option to format data for your locale

.

Table 13-6 Data Types and Formats

Data type

Option

Description

Date and Time

Unformatted

The data retains the default format set by the template or theme.

General Date

June 5, 2006 12:00:00 AM GMT +00:00

Long Date

June 5, 2006

Medium Date

Jun 5, 2006

Short Date

6/5/06

Long Time

12:00:00 AM GMT +00:00

Medium Time

12:00:00 AM

Short Time

12:00

Custom

The format depends on a format code you type. For example, typing yyyy/mm results in 2006/10. You learn more about custom formatting later in this chapter.

Number

Unformatted

The number retains the default format set by the template or theme.

General Number

6066.88 or 6067, depending on the decimal and thousands separator settings

Currency

$6,067.45 or

¥

6067, depending on the locale and optional settings

Fixed

6067 or 6,067 or 6067.45, depending on optional settings

Percent

45% or 45.8%, depending on optional settings

Scientific

2E04 or 2.67E04, where the number after the E represents the exponent of 10, depending on optional settings. For example, 2.67E04 means 2.67 multiplied by 10 raised to the fourth power.

Custom

The format depends on a format code you type. For example, typing #,### results in a format with a comma as a thousands separator and no decimal points. You learn more about custom formats later in this chapter.

String

Unformatted

The string retains the default format set by the template or theme.

Uppercase

The string displays in all uppercase, for example GREAT NEWS.

Lowercase

The string displays in all lowercase, for example great news.

Custom

The format depends on the format code you type. Use custom formatting for postal codes, telephone numbers, and other data that does not match standard formats.

Formatting Numeric Data

Numeric data can take several forms. A column of postal codes requires different formatting from a column of sales figures. Figure 13-16 shows the numeric formats you can use.

Figure 13-16 Formats for Numeric Data

The data type of a column is determined by the data source. Keep in mind that a text or string data type can contain numeric digits. A telephone number, for example, is frequently string data in the data source. The title of the formatting dialog box tells you what data type the column contains.

Step 2 In the Format Number as field, select General Number, Currency, Fixed, Percent, or Scientific.

The bottom section of Number column format displays fields that support that type of formatting.

Step 3 In Symbol field, select a currency symbol.

Step 4 In Symbol Position:

•Select Before to place the currency or percentage symbol before the number.

•Select After to place the symbol after the number.

Step 5 In Decimal Places, select the number of places after the decimal marker to display

Step 6 Select Use 1000s Separator if you want to use a thousands separator such as a comma (,) or a period (.).

Your locale settings determine the separator character.

Step 7 In Negative Numbers, select an option for displaying negative numbers, by using either a minus sign before the number or parentheses around the number.

Step 8 Click Apply.

Formatting Fixed or Scientific Numbers or Percentages

Step 1 Select a column that contains numeric data, then click Format.

The Number column dialog box appears.

Step 2 In Format Number as field, select Fixed, Scientific, or Percent.

Step 3 In Decimal Places field, select the number of decimal places to display.

Step 4 Select Use 1000s Separator if you want to use a thousands separator such as a space, a comma (,), or a period (.).

Step 5 In Negative Numbers, select an option for displaying negative numbers: use a minus sign before the number, or use parentheses around the number.

Step 6 Click Apply.

Formatting Custom Numeric Data

To define a custom format, you use special symbols to construct a format pattern. A format pattern shows where to place currency symbols, thousands separators, decimal points or commas. Table 13-7 shows examples of custom format patterns and their effects on numeric data

.

Table 13-7 Results of Custom Number Format Patterns

Format pattern

Data in the data set

Result of formatting

0000.00

12.5124.51240.553

0012.500124.501240.55

#.000

100100.25100.2567

100.000100.250100.257

$#,###

2000.0020000.00

$2,000$20,000

ID #

15

ID 15

Step 1 Select a numeric data column, then click Format.

The Number column format appears.

Step 2 In the Format Number as field, select Custom from the drop-down list.

A second field, Format Code, appears.

Step 3 In Format Code field, type a format pattern similar to those shown in Table 13-7.

Step 4 Click Apply.

Formatting String Data

Step 1 To define the format for a column that contains string data, select the column, then click Format.

The String column format appears.

Step 2 Select an option from the drop-down list. See Table 13-6 for the standard string data type options.

Step 3 Click Apply.

Formatting Custom String Data

You can format string data to include special formatting, such as a space or a punctuation mark at a specific place in the string. For example, you can display telephone numbers in one of the following formats.

(415) 555-2121 ext. 2296

415.555.2121

415-555-2121

You can create custom formats for string data. Table 13-8 describes the symbols you can use to define custom string formats

.

Table 13-8 Symbols for Defining Custom String Formats

Symbol

Description

@

Character placeholder. Each @ character displays a character in the string. If the string has fewer characters than the number of @ symbols that appear in the format pattern, spaces appear. Placeholders are filled from right to left, unless you specify an exclamation point (!) at the beginning of the format pattern. See Table 13-9 for examples.

&

Same as @, except that if the string has fewer characters, spaces do not appear. See Table 13-9 for examples.

!

Specifies that placeholders are to be filled from left to right. See Table 13-9 for examples.

Step 3 In the Format Code field, enter a format pattern such as those shown in Table 13-9.

Step 4 Click Apply.

Formatting Date and Time

The appearance of date and time data depends on the locale in which you are working. For example, the following date and time are correct for the U.S. English locale for Pacific Standard Time zone:

March 5, 2007 11:00:00 AM PST

The following example shows the same date and time for a French (France) locale:

5 mars 2007 11:00:00 HNP (ÈUA)

In a date-and-time column, a data source can provide both a date and a time, or only the date, or only the time. If the data source provides both date and time data, you can format the column to display only a date, only a time, or both a date and a time. You also can select the exact format for the date or time. For example, if the data column displays the following value:

April 3, 2006 11:35 a.m.

selecting the Short Date format for the column displays only the date portion of the data, in the following format:

Step 1 Select a column that contains date or time data, then click Format.

The Date and Time Format window appears.

Step 2 In Format Date or Time As field, select the desired option.

Step 3 Click Apply.

Formatting Custom Date and Time

You can set custom date formats. Use custom date formatting, however, only if your report will be viewed in a single locale. Custom formats always display dates in the format you set and that format might not be clear in multiple locales.

For example, if you use the format MM-dd-yy, the date January 10, 2006 always appears as 01-10-06, regardless of the locale in which the report is viewed. For locales in which dates are displayed in date-month-year format, a 01-10-06 date is interpreted as October 1, 2006.

Table 13-10 shows examples of custom formats and their effects on a date that is stored as 4/15/2006 in the data source.

Table 13-10 Results of Custom Date Formats

Format

Result of formatting

MM-dd-yy

04-15-06

E, M/d/yyyy

Fri, 4/15/2006

MMM d

Apr 15

MMMM

April

yyyy

2006

W

3 (the week in the month)

w

14 (the week in the year)

D

105 (the day in the year)

To create a custom date or time format,

Step 1 Select a date-and-time column, then click Format.

The Date or Time column format window appears.

Step 2 In Format Date or Time As field, select Custom.

A second field, Format Code, appears.

Step 3 In the Format Code field, type a format pattern such as those shown in Table 13-10.

Step 4 Click Apply.

Formatting Boolean Data

A Boolean expression evaluates to True or False. For example, you create a calculated column with the following expression:

ActualShipDate <= TargetShipDate

If the actual ship date is before or on the target ship date, the expression evaluates to True. If the actual ship date is after the target ship date, the expression evaluates to False.

If you do not format a column of Boolean data type, the column displays the values True and False. To specify different labels, select the column and choose Format Data. Then type the labels in Boolean Column Format, as shown in Figure 13-17.

Figure 13-17 Specifying Display Values for True and False

Applying Conditional Formats

Conditional formatting changes the formatting of data when a certain condition is true. For example, in a report that shows customers' past-due invoices, you can highlight in red any customer name that has an invoice 90 days or more past due. Then, you can highlight in blue any customer name that has an invoice 60 days or more past due.

To set conditional formats, open the Conditional Formatting dialog box by right-clicking a column and selecting Style > Conditional Formatting. You can set up to three conditional formatting rules for a single column. You also can remove or modify conditional formatting.

On Conditional Formatting, you create a rule, or condition, for displaying the data in the column you selected. The rule includes both the condition that must be true and the formatting to apply. Figure 13-18 shows Conditional Formatting.

Figure 13-18 Conditional Formatting in Interactive Viewer

You can affect the formatting of one column based on the value in another column. For example, if you select the CustomerName column, you can base the condition on the creditRank column so that conditional formatting applies to the customer name if the customer's creditRank is a particular value.

You set various types of comparisons, such as whether the data in the comparison column is null or False. You can also compare the column value to one or two other values. For example, you can specify that data that is less than or equal to a value triggers the conditional formatting.

You also can check whether a value is between or not between two values. For example, you can specify conditional formatting to apply if the order total is between $10,000 and $100,000. With this setting, the names of all customers whose orders total between $10,000 and $100,000 take the conditional formatting.

After you create the condition, you set the format in which to display data that meets the condition. The format applies to the column in Select Column, not to the column you use to set the condition.

Setting Conditional Formatting for Columns

You can set conditional formatting or modify conditional formatting for a column.

Step 1 Right-click on the column that you want to display the conditional formatting.

The context menu appears.

Step 2 Select Style > Conditional Formatting.

The Conditional Formatting dialog box appears. The Selected Column field displays the name of the column that will display the conditional format.

Step 3 Set the conditional formatting rule:

a. In the first drop-down list, select the column that contains the values that determine whether the conditional format takes effect. For example, if the condition is that customer names should appear in blue if customer numbers are between 1000 and 1999, select CustomerNumber.

The column you select in this field can be the same as or different from the column in Selected Column.

b. In the next field, use the drop-down list to select the operator to apply to the column you selected. You can select Equal to, Less than, Less than or Equal to, and so on.

Depending on your selection, zero, one, or two fields appear to the right. If you selected Is Null, Is Not Null, Is True, or Is False, zero fields appear to the right. If you selected an operator that requires a comparison between two values, one or more additional fields appear.

c. Enter a comparison value in each field that appears to the right, if any.

For example, if you selected Less than or Equal to, a third field appears, as shown in Figure 13-19. In this field, you type the comparison value. All data that is less than or equal to your value triggers the conditional formatting.

Figure 13-19 Comparison Value Field

If you selected Between or Not Between, two fields appear to the right. In these fields, type the lower and upper values, respectively. For example, in the third field, type an order total of 10,000 and in the fourth field, type an order total of 100,000, as shown in Figure 13-20.

The names of all customers whose orders total between $10,000 and $100,000 take the conditional formatting.

Figure 13-20 Two Comparison Value Fields Appear for the Between Operator

The values for the comparison can be typed in directly or derived from the specified report column. Select Change Value to display the Value dialog, as shown in Figure 13-21.

Figure 13-21 Specifying Literal or Column Values

A literal value can be directly typed or chosen from a list of values in the specified column. They also can be derived from the values in another specified column in the report.

Step 4 On Conditional Formatting, choose Format, and set the formatting for the conditional text. You can set the font, font size, font color, and background color. You also can specifying displaying the data in bold, italic, or underlined font style.

Step 5 You set the formatting by using the same Font dialog box used for formatting labels, as shown in Figure 13-22.

Figure 13-22 The Font Dialog Box

After you set the format, the Preview section of the window shows the formatting you selected.

To add additional conditional formatting rules, select Add Rule and repeat steps 3 and 4 for each new rule.

Step 6 Click Apply.

The report design appears with the specified conditional formatting applied.

Deleting Conditional Formatting

To remove conditional formatting for a column:

Step 1 Select and right-click the column.

Step 2 Select Style > Conditional Formatting.

Step 3 In the Conditional Formatting dialog box, click Delete Rule for each conditional formatting rule you want to delete, as shown in Figure 13-23.

To remove all conditional formatting for a column, delete all the conditional formatting rules.

Figure 13-23 Removing a Conditional Format in Interactive Viewer

Step 4 Click Apply.

Setting and Removing Page Breaks in Detail Columns

In Interactive Viewer, you can force page breaks after a preset number of rows.

Step 1 Select and right-click a detail column.

Step 2 From the context menu, select Group > Page Break.

The Page Break window appears.

Step 3 In the Interval field, enter a numerical value for the row after which you want to force a page break.

Step 4 Click Apply.

Setting and Removing Page Breaks in a Group Column

In Interactive Viewer, if your report design has grouped data, you can set page breaks before or after the grouped data.

Displaying and Organizing Report Data

After you access a data source and select the data set to use, you determine the best way to display the data in a report. There are several ways to organize data sets:

•Sort a data column in ascending or descending order.

•Organize data into groups. A group displays all the information about a type of item in one place. For example, you can display all the information about one customer, then all the information about the next customer, and so on.

•Create aggregate data. At the end of a customer group, for example, you can display the total amount of the customer's purchases or the average amount of each order. You can also create calculations, such as sums, standard deviations, rankings, and differences.

Reordering Columns in Interactive Viewer

To reorder columns:

Step 1 Select and right-click a column.

Step 2 From the context menu, select Column > Reorder Columns.

The Arrange Columns window appears

Step 3 Select the column you want to move.

Note You can select only detail rows, not groups or sections.

Step 4 Click the up or down arrows at the right until the column is in the correct position.

Step 5 Click Apply.

Moving Data Values from Columns to Group Headers

You can move data values from the details rows of a column to column headers.

Step 1 Select and right-click on a column.

For example, Figure 13-25 shows the customer Name column as the selected column.

Step 3 Select any items you want to hide or Deselect any hidden items you want to display.

To display all hidden items, click Clear.

Step 4 Click Apply.

Hiding Columns

To hide or display columns:

Step 1 Select and right-click a column.

Step 2 Select Column > Hide Column.

Interactive Viewer displays the report without the hidden column.

Displaying Hidden Columns

TO display hidden columns:

Step 1 Select and right-click a column.

Step 2 Select Column > Show Columns.

The Show Columns dialog box appears.

Step 3 Select any items you want to display. Use Ctrl to select several columns.

Step 4 Click Apply.

Merging Columns

You can merge the data from two or more columns into one column. For example, in Figure 13-29 the columns under the City and state or province column header are separate.

Figure 13-29 Separate Columns

In Figure 13-30, the data from these two columns is merged into one column.

Figure 13-30 Merged Column

To merge data in multiple columns:

Step 1 Select and right-click the columns

Step 2 Select Column > Merge Columns.

Selecting a Column from a Merged Column

You can aggregate, filter, and group data in a column that contains data that is merged from multiple columns. You must first select one of the columns on which to aggregate, filter, or group data.

To select one column from a merged column,

Step 1 Select and right-click the merged column, then select a command to apply from the context menu, such as Aggregation, Filter > Filter, or Group > Add Group.

The Select data item dialog box appears.

Step 2 From the Select data drop-down list, select the column name to which you want to apply the command, then click Apply.

If you must provide more information to apply a command, Interactive Viewer displays a dialog box. For example, if you choose Aggregation, the Aggregation dialog box appears.

Sorting Data

When you place data in a report design, the data source determines the default sort order for the data rows. If the data source sorts a column in ascending order, the column is sorted in ascending order in the design. Typically, however, data appears randomly in the data source.

A column is likely to display customer names, for example, in the order in which the customers were added to the database, rather than in alphabetical order. Sorting data, therefore, is an important task in creating a useful report.

Sorting a Single Column

To perform a simple sort on a single column, select a column and select either Sort Ascending or Sort Descending. All the data rows in the report adjust to reflect the new sort order.

To return the data to its original order after performing a simple sort, choose Undo.

Sorting Multiple Columns

You can sort multiple columns in a report.

Step 1 Select and right-click a column.

Step 2 From the Context menu, select Sort > Advanced Sort.

Step 3 Select a column from the first drop-down list and choose either Ascending or Descending order. Select the next column and choose a sort order, and so on.

When you sort multiple columns, it is important to understand the order of precedence for the sort. In Advanced Sort, the first column you select is the primary sorting column. Report data is sorted first by this column.

If the primary column is Customer and the order is Ascending, for example, the report displays customers in alphabetical order. If you select Location as the next column and the order is Ascending, then within each Customer entry, the locations are sorted in ascending order.

If the third column you select is Order Number and the order is Ascending, then within each location, the order numbers are sorted in ascending order. Figure 13-31 shows the result of this multiple-column sort.

Figure 13-31 Sorting Multiple Columns

If the report uses grouped data, the drop-down lists in Advanced Sort show only the detail columns in the report, not the columns you used to group the data.

Grouping Data

A report can contain a great deal of data. Consider the task of listing every item a corporation owns, along with information such as the purchase price, purchase date, inventory tag number, and the supplier for each item.

If a report presents all these items in an unorganized list, there is no way to determine how much the corporation spends for heavy equipment because heavy equipment items are scattered throughout the report.

The report cannot help you see which supplier sells office furniture at the lowest prices. Nor is it easy to tell which inventory items are located at a field office and which items are in the warehouse. Figure 13-32 shows the first page of an ungrouped report.

Figure 13-32 Ungrouped Data

To organize all this information into a useful inventory report, you create data groups and data sections. Data groups contain related data rows.

For example, you can create a report that lists all heavy equipment in one group, all office furniture in another group, all telephony equipment in a third group, and so on. For each group, you can show aggregate data, such as the total purchase price or a count of the items in a group.

Grouping data gives your report a more polished, organized look and makes it easier to create useful comparisons and calculations.

Figure 13-33 shows the same inventory, grouped according to the category, with one category on each page.

Figure 13-33 Grouped Data

You can group data in the report design editor or in Interactive Viewer. The changes you make in the viewer do not affect the report design. If you work in Enterprise mode, you can save report output that reflects your changes.

You can add or remove data groups in Interactive Viewer if the report design does not contain the grouping desired during that use of Interactive Viewer.

Adding Groups

To add groups:

Step 1 Select and right-click the column you want to use to create a group.

Step 2 From the Context menu, select Group > Add Group.

The new group appears in the viewer. As shown in Figure 13-34, the group expands to show all the detail rows. To collapse the group, click the minus sign ( - ) beside the group name.

Figure 13-34 Adding a Group

Grouping Data Based on Date or Time

When you create a group based on a column that contains date or time data, you have the option of setting a grouping interval. For example, if the column contains time data, you can group on hours, minutes, or seconds.

Step 1 Select and right-click the column you want to use to create a group.

Step 3 To show every date or time value, leave the default setting Group using individual values.

Step 4 To set a grouping interval, select Group every and enter a value and select the grouping interval.

For example, to create a new group for every month, type 1 and select Month from the drop-down list. The report displays monthly data groups, as shown in Figure 13-36.

Figure 13-36 Data Grouped by Month

Removing an Inner Group

To remove an inner group:

Step 1 Select and right-click the column for the group you want to remove.

Step 2 From the context menu, select Group > Delete Inner Group.

Creating Report Calculations

Most reports require some sort of calculations to track sales, finances, inventory, and other critical business activities. You might want to keep a simple count of items in a warehouse or you might need to provide more complex financial data such as tracking stock portfolio performance over time.

You can use typical mathematical functions such as counting, addition, subtraction, multiplication, and division. In addition, you can write expressions that extend these basic functions.

To display calculated data in a report, you create a calculated column, such as the one shown in Figure 13-37. In this illustration, the Item Totals column displays the product of the unit price times the quantity.

Figure 13-37 Calculated Column

To create a calculation, you

•Provide a title for the calculated column.

•Write an expression that indicates which data to use and how to display the calculated data in the report.

The expression contains a function and one or more arguments. Arguments indicate the data you want to use to create the calculation.

As shown in Figure 13-38, when you type the first letter of a function name in Enter Expression, Calculation presents a list of functions that begin with that letter. You select a function and provide the arguments.

Figure 13-38 Selecting a Function

Understanding Supported Calculation Functions

Table 13-11 provides examples of the functions you can use to create calculations.

Note The Calculation dialog box does not support the use of uppercase TRUE and FALSE functions in expressions.Calculation also does not support the use of initial capital letters for True and False. These functions must be expressed in lowercase only.

Table 13-11 Examples of Functions

Function

Description

Example of use

ABS(num)

Displays an absolute value for the data in a column.

ABS([TemperatureCelsius])

ADD_DAY(date, daysToAdd)

Adds a specified number of days to a date value and displays the result as a date value.

ADD_DAY([ClosingDate], 30)

ADD_HOUR(date, hoursToAdd)

Adds a specified number of hours to a time value and displays the result as a time value.

ADD_HOUR([OpenHour], 8)

ADD_MINUTE(date, minutesToAdd)

Adds a specified number of minutes to a time value and displays the result as a time value.

ADD_MINUTE([StartTime], 60)

ADD_MONTH(date, monthsToAdd)

Adds a specified number of months to a date value and displays the result as a date value.

ADD_MONTH([InitialRelease], 2)

ADD_QUARTER(date, quartersToAdd)

Adds a specified number of quarters to a date value.

ADD_QUARTER([ForecastClosing], 2)

ADD_SECOND(date, secondsToAdd)

Adds a specified number of seconds to a time value.

ADD_SECOND([StartTime], 30)

ADD_WEEK(date, weeksToAdd)

Adds a specified number of weeks to a date value and displays the result as a date value.

ADD_WEEK([askByDate], 4)

ADD_YEAR(date, yearsToAdd)

Adds a specified number of years to a date value.

ADD_YEAR([HireDate], 5)

AND

Combines two conditions and returns records that match both conditions. For example, you can request records from customers who spend more than $50,000 a year and also have a credit rank of A.

This function is used to connect clauses in an expression and does not take arguments.

AVERAGE(expr)

Displays an average value for the column.

AVERAGE([CostPerUnit])

AVERAGE(expr, groupLevel)

Displays the average value at the specified group level.

AVERAGE([TotalCost], 2)

BETWEEN(value, upperBound, lowerBound)

For a specified column, displays True if a value is between two specified values and False otherwise.

String values and date or time values must be enclose in quotation marks. For dates and times, use the short date and short time formats.

BETWEEN([PostalCode], 11209, 12701)

BETWEEN([ReceiptDate],
"10/01/06", "12/31/06")

CEILING(num, significance)

Rounds a number up, away from 0, to the nearest specified multiple of significance.

For data that has been converted from a double or float to an integer, displays the smallest integer that is greater than or equal to the float or double.

CEILING([PortfolioAverage], 1)

COUNT( )

Counts the rows in a table.

COUNT( )

COUNT(groupLevel)

Counts the rows at the specified group level.

COUNT(2)

COUNTDISTINCT(expr)

Counts the rows that contain distinct values in a table.

COUNTDISTINCT([CustomerID])
COUNTDISTINCT([Volume]*2)

COUNTDISTINCT(expr, groupLevel)

Counts the rows that contain distinct values at the specified group level.

COUNTDISTINCT([CustomerID], 3)

DAY(date)

Displays the number of a day in the month, from 1 to 31, for a date-and-time value.

DAY([forecastShipping])

DIFF_DAY(date1, date2)

Displays the difference between two date values, in the number of days.

DIFF_DAY([checkoutDate],
[returnDate])

DIFF_HOUR(date1, date2)

Displays the difference between two time values, in the number of hours.

DIFF_HOUR([StartTime],[Finish
Time])

DIFF_MINUTE(date1, date2)

Displays the difference between two time values, in the number of minutes.

DIFF_MINUTE([StartTime],
[FinishTime])

DIFF_MONTH(date1, date2)

Displays the difference between two date values, in the number of months.

DIFF_MONTH([askByDate],
[shipByDate])

DIFF_QUARTER(date1, date2)

Displays the difference between two date values, in the number of quarters.

DIFF_QUARTER([PlanClosing],
[ActualClosing])

DIFF_SECOND(date1, date2)

Displays the difference between two time values, in the number of seconds.

DIFF_SECOND([StartTime],
[FinishTime])

DIFF_WEEK(date1, date2)

Displays the difference between two weeks as a number.

DIFF_WEEK([askByDate],
[shipByDate])

DIFF_YEAR(date1, date2)

Displays the difference between two years as a number.

DIFF_YEAR([HireDate],
[TerminationDate])

False

The Boolean False. This function is used in expressions to indicate that an argument is false.

In the following example, False indicates that the second argument, ascending, is false and therefore the values should be returned in descending order.

RANK([Score], false)

FIND(strToFind, str)

Displays the index of the first occurrence of specified text. The index is zero-based. The search is case sensitive and the search string cannot include wildcards.

The value in the strToFind argument must be enclosed in quotation marks.

FIND("HQ", [OfficeName])

FIND(strToFind, str, startPosition)

Similar to FIND(strToFind, str) but supports providing a start position for the search. The index is zero-based.

FIND("HQ", [OfficeName], 3)

FIRST(expr)

Places the first value that appears in a specified column into the calculated column. This function supports viewing a row-by-row comparison against a specific value.

FIRST([customerID])

FIRST(expr, groupLevel)

Displays the first value that appears in the specified column at the specified group level.

FIRST([customerID], 3)

IF(condition, doIfTrue, doIfFalse)

Displays the result of an If...Then...Else statement.

IF([purchaseVolume] >5 , 7 , 0)

where

•[purchaseVolume] is the column name and >5 is the test condition.

•7 is the value to place in the new column if the condition is true.

•0 is the value to place in the new column if the condition is false.

IN(value, check)

Displays True if a data row contains a value specified by the check argument and False otherwise.

String values and date or time values must be enclosed in quotation marks. For dates and times, use the short date and short time formats for your locale.

IN([custID], 101)

IN([city], "New Haven")

IN([FinishTime], "16:09")

IN(value, check1, ..., checkN)

Displays True if a data row contains any value specified by the check argument list and False otherwise.

String values and date or time values must be enclosed in quotation marks. For dates and times, use the short date and short time formats for your locale.

IN([city], "New Haven", "Baltimore",
"Cooperstown")

IN([ShipDate], "05/01/06",
"05/10/06", "05/15/06")

ISBOTTOMN(expr, n)

Displays True if the value is within the lowest n values for the expression, and False otherwise.

ISBOTTOMN([OrderTotals], 50)

ISBOTTOMN(expr, n, groupLevel)

Displays True if the value is within the lowest n values for the expression at the specified group level, and False otherwise.

ISBOTTOMN([OrderTotals], 50, 2)

ISBOTTOMNPERCENT(expr, percent)

Displays the lowest n percentage.

ISBOTTOMNPERCENT([Sales Total], 5)

ISBOTTOMNPERCENT(expr, percent, groupLevel)

Displays the lowest n percentage for the expression at the specified group level.

ISBOTTOMNPERCENT([Sales Total], 5,
3)

ISNULL(value)

Displays True if a row does not display a value. Displays False if a row displays a value.

ISNULL([DepartmentName])

ISTOPN(expr, n)

Displays True if the value is within the highest n values for the expression, and False otherwise.

ISTOPN([OrderTotals], 10)

ISTOPN(expr, n, groupLevel)

Displays True if the value is within the highest n values for the expression at the specified group level, and False otherwise.

ISTOPN([OrderTotals], 10, 3)

ISTOPNPERCENT(expr, percent)

Displays True if the value is within the highest n percentage, and False otherwise.

ISTOPNPERCENT([SalesTotals], 5)

ISTOPNPERCENT(expr, percent, groupLevel)

Displays True if the value is within the highest n percentage values for the expression at the specified group level, and False otherwise.

ISTOPNPERCENT([SalesTotals],
5, 3)

LAST(expr)

Displays the last value in a specified column.

LAST([FinishTime])

LAST(expr, groupLevel)

Displays the last value for the expression at the specified group level.

LAST([FinishTime], 3)

LEFT(str)

Displays the character at the left of the specified string.

LEFT([city])

LEFT(str, n)

Displays the specified number of characters in a column's string, counting from the left.

LEFT([city], 3)

LEN(str)

Displays the length of a string, including spaces and punctuation marks.

LEN([Description])

LIKE(str)

Displays True if the values match, and False otherwise. Use SQL syntax to specify the string pattern.

The following rules apply:

•Literal pattern characters must match exactly. LIKE is case-sensitive.

•A percent character (%) matches zero or more characters.

•An underscore character (_) matches any single character.

•Escape a literal percent, underscore, or backslash character (\) with a backslash character.

LIKE([customerName], "D%")

LIKE([quantityOrdered], "2_")

LOWER(str)

Displays the string in a specified column in lowercase.

LOWER([cityName])

MAX(expr)

Displays the highest value in the specified column.

MAX([OrderTotal])

MAX(expr, groupLevel)

Displays the highest value for the expression at the specified group level.

MAX([OrderTotal], 2)

MEDIAN(expr)

Displays the median value in a specified column.

MEDIAN([HomePrices])

MEDIAN(expr, groupLevel)

Displays the median value for the expression at the specified group level.

MEDIAN([HomePrices], 2)

MIN(expr)

Displays the lowest value in the specified column.

MIN([OrderTotal])

MIN(expr, groupLevel)

Displays the lowest value for the expression at the specified group level.

MIN([OrderTotal], 1)

MOD(num, div)

Displays the remainder after a number is divided by a divisor. The result has the same sign as the divisor.

MOD([Salary], 12)

MONTH(date)

Displays the name of the month for a specified date-and-time value.

MONTH([ForecastShipDate])

MONTH(date, option)

Displays the month of a specified date-and-time value, in one of three optional formats:

•1 - Displays the month number of 1 through 12.

•2 - Displays the complete month name in the user's locale.

•3 - Displays the abbreviated month name in the user's locale.

MONTH([Semester], 2)

MOVINGAVERAGE(expr, window)

Displays an average value over a specified window, such as an average price or volume over a number of days.

MOVINGAVERAGE([Price],
[Days])

NOTNULL(value)

For a specified column, displays True if a data value is not empty. Displays False if a data value is empty.

NOTNULL([DepartmentID])

NOW( )

Displays the current time stamp.

NOW([PastDueDate])

OR

The logical OR operator.

This function is used to connect clauses in an expression and does not take arguments.

PERCENTILE(expr, pct)

Displays a percentile value, a value on a scale of 100 that indicates the percent of a distribution that is equal to or below the specified value.

Valid pct argument ranges are 0 to 1. 0 returns the minimum value of the series. 1 returns the maximum value of the series.

PERCENTILE([Rank], 1)

PERCENTILE(expr, pct, groupLevel)

Displays a percentile value for the expression at the specified group level.

Valid pct argument ranges are 0 to 1. 0 returns the minimum value of the series. 1 returns the maximum value of the series.

PERCENTILE([Income], 60, 1)

PERCENTRANK(expr)

Displays the percentage rank of a value.

PERCENTRANK([TestScores])

PERCENTRANK(expr,groupLevel)

Displays the percentage rank of a value at the specified group level.

PERCENTRANK([TestScores], 2)

PERCENTSUM(expr)

Displays a value as a percentage of a total.

PERCENTSUM([OrderTotals])

PERCENTSUM(expr, groupLevel)

Displays a value as a percentage of a total at the specified group level.

PERCENTSUM([OrderTotals], 3)

QUARTER(date)

Displays the quarter number, from 1 through 4, of a specified date-and-time value.

QUARTER([ForecastCloseDate])

QUARTILE(expr, quart)

Displays the quartile value, where the quart argument is an integer between 0 and 4.

QUARTILE([OrderTotal], 3)

QUARTILE(expr, quart, groupLevel)

Displays the quartile value for the expression at the specified group level, where the quart argument is an integer between 0 and 4.

QUARTER([OrderTotal], 2, 3)

RANK(expr)

Displays the rank of a number, string, or date-and-time value, starting at 1. Duplicate values receive identical rank but the duplication does not affect the ranking of subsequent values.

RANK([AverageStartTime])

RANK(expr, ascending, groupLevel)

Displays the rank of a number, string, or date-and-time value in either ascending or descending order, at the specified group level.

To display values in ascending order, use True as the second argument. To display values in descending order, use False as the second argument.

RANK([Score], false, 3)

RANK([Score], true, 2)

RIGHT(str)

Displays the character at the right of a string.

RIGHT([name])

RIGHT(str, n)

Displays the specified number of characters in a string, counting from the right.

RIGHT([name], 3)

ROUND(num)

Rounds a number.

ROUND([SalesTarget])

ROUND(num, dec)

Rounds a number to the specified number of digits. The default value for dec is 0.

ROUND([StockValue], 2)

ROUNDDOWN(num)

Rounds a number down.

ROUNDDOWN([StockPrice])

ROUNDDOWN(num, dec)

Rounds a number down, away from 0, to the specified number of digits. The default value for dec is 0.

ROUNDDOWN([StockPrice], 2)

ROUNDUP(num)

Rounds a number up.

ROUNDUP([TotalValue])

ROUNDUP(num, dec)

Rounds a number up, away from 0, to the specified number of digits. The default value for dec is 0.

ROUNDUP([TotalValue], 2)

RUNNINGSUM(expr)

Displays a running total, adding the values in successive data rows.

RUNNINGSUM([StockValue])

SEARCH(pattern, str)

Case-insensitive search function that can use wildcard characters.

An asterisk ( * ) matches any sequence of characters, including spaces.

A question mark ( ? ) matches any single character.

The following search yields New York, New Haven, and so on from the City column:

SEARCH([CustomerData:city], "new*")

SEARCH(pattern, str, startPosition)

Searches for a specified pattern in a string, starting at a specified position in the string. A case-insensitive search function that can use wildcard characters.

SEARCH([Location], "new", 1)

SQRT(num)

Displays the square root of a value.

SQRT([PrincipalValue])

STDEV(expr)

Displays the standard deviation.

STDEV([PurchaseFrequency])

SUM(expr)

Displays the sum of two specified values.

SUM([Price]+[Tax])

TODAY( )

Displays a time stamp value equal to midnight of the current date.

TODAY([DueDate])

TRIM(str)

Displays a string with all leading and trailing blank characters removed. Also removes all consecutive blank characters. Leading and trailing blanks can be spaces, tabs, and so on.

TRIM([customerName])

TRIMLEFT(str)

Displays a string with all leading blanks removed. Does not remove consecutive blank characters.

TRIMLEFT([PortfolioName])

TRIMRIGHT(str)

Displays a string with all trailing blanks removed. Does not remove consecutive blank characters.

TRIMRIGHT([Comments])

True

The Boolean True. This function is used in expressions to indicate that an argument is true.

In the following example, True indicates that the second argument, ascending, is true and therefore the values should be returned in ascending order.

RANK([Score], true)

UPPER(str)

Displays a string in a specified column in all uppercase.

UPPER([cityName])

UPPER("new haven")

VAR(expr)

Displays a variance for the specified expression.

VAR([EstimatedCost])

WEEK(date)

Displays the number of the week, from 1 through 52, for a date-and-time value.

WEEK([LeadQualifyingDate])

WEEKDAY(date, option)

Displays the day of the week in one of the following format options:

•1 - Returns the day number, from 1 (Sunday) through 7 (Saturday). 1 is the default option.

•2 - Returns the day number, from 1 (Monday) through 7 (Sunday).

•3 - Returns the day number, from 0 (Monday) through 6 (Sunday).

•4 - Returns the weekday name according to the user's locale.

•5 - Returns the abbreviated weekday name according to the user's locale.

WEEKDAY([DateSold], 4)

WEIGHTEDAVERAGE(value, weight)

Displays a weighted average of a specified value.

WEIGHTEDAVERAGE([Score], weight)

YEAR(date)

Displays the four-digit year value for a date-and-time value.

YEAR([ClosingDate])

Understanding Supported Operators

Table 13-12 describes the mathematical and logical operators you can use in writing expressions that create calculated columns.

Table 13-12 Supported Operators to Use in Writing Expressions

Operator

Description

x + y

Addition of numeric values

x - y

Subtraction of numeric values

x * y

Multiplication of numeric values

x / y

Division of numeric values

x%

Percentage of a numeric value

x & y

Concatenation of string values

x = y

Test for equality of two values

x > y

Tests whether x is greater than y

x < y

Tests whether x is less than y

x >= y

Tests whether x is greater than or equal to y

x <= y

Tests whether x is less than or equal to y

x <> y

Tests whether x is not equal to y

x AND y

Tests for values that meet both condition x and condition y

x OR y

Tests for values that meet either condition x or condition y

NOT x

Tests for values that are not x

Using Numbers and Dates in an Expression

When you create an expression that contains a number, you must type the number according to the conventions of the US English locale. In other words, use a period (.), not a comma (,), as the decimal separator. For example:

Correct: 1234.56

Incorrect: 1234,56

When you create an expression that contains a date, type the date according to the conventions of the locale you chose when you logged in. For example, in the French (France) locale type 03/12/2007 to represent December 3, 2007, not 12/03/2007. You can enter a date or a date and time. Dates and times must be enclosed in double quotes ("), for example:

"03/12/2007"

"03/12/2007 11:00 AM"

ns.

Using Multiply Values in Calculated Columns

To use multiply values in calculated columns:

Step 1 Select a column. In the report, the new calculated column appears to the right of the column you select.

Step 2 Select Add Calculation.

The Calculation dialog box appears.

Step 3 In the Column Label field, type a header for the calculated column.

The header must start with a letter and can contain only letters, numbers, underscores, and spaces.

For example, if the multiplier is the unit price, select the column that contains the quantity ordered as the multiplicand.

Step 8 To verify the expression, click Validate.

If the expression syntax is correct, the dialog box displays a validation message.

If the expression syntax is incorrect, the dialog box displays a message explaining the error.

Step 9 After validating the expression, click Apply.

The calculated column appears in the report, by using the column header you typed.

Adding Days to an Existing Date Value

You can create a column that displays date values that are greater than the date values in another column.

Step 1 Select a column.

The calculated column appears to the right of the column you select.

Step 2 Select Add Calculation.

The Calculation dialog box appears.

Step 3 In Column Label, type a name for the calculated column. For example, type Forecast Shipping Date.

Step 4 In Enter Expression, type the letter A.

A drop-down list appears, displaying functions that begin with A.

Step 5 Select ADD_DAY(date, daysToAdd).

Step 6 For the first argument, type a left opening bracket ( [ ) and select the date column from the drop-down list. For example, select Order Date.

Step 7 For the second argument, type the number of days to add. In this case, type 7.

Step 8 Validate the expression, then click Apply.

The new calculated column appears in the report. For every value in the Order Date column, the calculated column displays a date seven days later than the order date.

Subtracting Date Values in a Calculated Column

You can display the difference between two date values.

Step 1 Select a column.

In the report, the calculated column will appear to the right of the column you select.

Step 2 Select Add Calculation.

The Calculation dialog box appears.

Step 3 In Column Label, type a name for the calculated column.

For example, to subtract the actual shipping date from the date requested, type Shipping Delay.

Step 4 In Enter Expression, type the letter d.

A drop-down list appears, displaying functions that begin with d.

Step 5 Select DIFF_DAY(date1, date2).

Step 6 For the first argument, type a left opening bracket ( [ ) and select the first date column from the drop-down list.

For example, select Date Requested.

Step 7 For the second argument, type a left opening bracket ( [ ) and select the second date column from the drop-down list.

For example, select Actual Shipping Date.

Step 8 Validate the expression, then click Apply.

The new calculated column appears in the report, displaying the difference between the two dates.

Working with Aggregate Data

Aggregating data involves performing a calculation over a set of data rows. For example, in a report that has a column to calculate price*quantity, you can take the sum of all those calculations to obtain the total price of all items. You can create up to three aggregations for a calculated column.

When you aggregate data, you choose whether the aggregation appears in the table header or footer. If the column is a detail column in a data group, you can display the aggregate value in the group header or footer. Figure 13-39 shows aggregate data in a group footer.

Returns one of four equal-sized sets of data, based on the rank you select.

For example, you can request the first quartile to get the top quarter of the data set or the fourth quartile to get the fourth quarter of the data set.

Standard Deviation

Returns the standard deviation, the square root of the variance.

Sum

Adds the values in the column.

Variance

Returns a value that indicates the spread around a mean or expected value.

Weighted average

Returns the weighted average of a numeric field over a set of data rows. In a weighted average, some numbers carry more importance, or weight, than others.

Creating an Aggregate Data Row

To create an aggregate data row:

Step 1 Select a column, then select Aggregation.

The Aggregation dialog box appears. The name of the column you selected is listed in the Selected Column field.

Step 2 From the Select Function menu, select the function you want to use.

The available functions depend on the type of data the column contains:

•If the column contains text data, you can count all the values in the column or count the distinct values in the column, for example.

•If the column contains numeric data, you can count values, get an average value or a weighted average, total the values in the column, and so on.

The syntax of the selected function appears in the lower portion of the Aggregation dialog box, as shown in Figure 13-40.

Figure 13-40 The Aggregation Dialog Box in Interactive Viewer

Step 3 In the Aggregate on field:

•Select whether to display the aggregate value in the table header or the table footer. The default setting is to display the aggregate value in the table footer.

•Select whether to display the aggregate value in the group header or group footer, if the selected column is a grouped column.

Step 4 Click Apply.

The aggregate data appears where you placed it in the report design.

Adding Additional Aggregate Rows

After you create a single aggregate row for a column, you can add up to two more aggregate rows for the same column. For an item total column, for example, you can create a sum of all the values, count all the values, and get the average order total.

An additional section appears in the Aggregation dialog box, as shown in Figure 13-41.

Figure 13-41 Adding an Aggregate Row for a Column in Interactive Viewer

Step 3 Create the second aggregate row, then click Apply.

Deleting Aggregate Rows

To delete an aggregate row:

Step 1 Select the calculated column that contains the aggregation you want to remove, then select Aggregation.

The Aggregation dialog box appears, displaying all the aggregations for the column.

Step 2 For the aggregation you want to remove, choose Delete Aggregation, then click Apply.

Hiding and Filtering Report Data

You can decide whether to hide or display data in your report in several ways:

•You can specify whether to hide or display detail rows.

•You can specify that you want to display only one copy of duplicate rows.

•When the data set provides more information than your report needs, you can filter out unwanted data. You also can enable your users to specify the filter values by using run-time parameters.

Hiding or Displaying Column Data

Frequently, you do not want to display all the data in a report. For example, a column of detail can display duplicate values in consecutive data rows. When the duplication is unnecessary or makes the report difficult to read, you can suppress consecutive duplicate values.

In addition, as you design a report, you can collapse groups or sections to display only the column headings and summary data, such as aggregate data rows.

Suppressing Repeated Values

Before you begin formatting and adjusting the data in your report, data rows appear in the report exactly as they appear in the data source. In the report shown in Figure 13-42, for example, the Location column shows the city name each time the name occurs.

Figure 13-42 Duplicate Values in Columns

As Figure 13-43 shows, the report is easier to read when duplicate values are not repeated.

Figure 13-43 Suppressed Values

You can suppress duplicate values to make your report easier to read. You can suppress only consecutive occurrences of duplicate values. In the Location column in Figure 13-43, the Boston value is suppressed in the second, third, fourth, and fifth rows. If Boston occurs again after the listing for NYC, that occurrence of Boston is visible and subsequent consecutive occurrences are suppressed. The values must be exact duplicates to be suppressed.

If a column splits across multiple pages, the first row on each page displays a value, even if duplicate values are suppressed for that column.

Displaying Repeated Values

Step 1 Select and right-click the column that does not repeat duplicate values.

Step 2 From the context menu, select Column > Repeat Values.

Hiding or Displaying Detail Rows in Groups or Sections

If a report contains groups, you can collapse and expand a group by using the context menu.

For example, Figure 13-44 shows a report where the data is grouped by creditrank and the detail rows for each creditrank value are shown.

Figure 13-44 Group Detail Rows Displayed

Figure 13-45 shows the results of hiding the detail rows for the creditrank grouping.

Figure 13-45 Group Detail Rows Hidden

•To collapse a group or section, select and right-click a member of the group or section that you want to collapse.

The context menu appears.

•To display the group members without their detail rows, select Group > Hide Detail.

•To display the group members with their detail rows, select Group > Show Detail.

Working with Filters

Filters limit the data that appears in reports. For example, by using a database of customer data, you can use filters to run a report that lists only the customers in a specific state or province, or only the customers whose purchases total more than $1.5 million.

To limit the data even more, you can, for example, list customers in a specific state who have credit limits of less than $50,000 and who have not made a purchase in the past 90 days.

A filter is based on one or more fields in a report.

To create a filter based on a single field, you select a condition and a value. For example, you can create a filter that returns values that are equal to a specified value, less than a specified value, between two values, and so on. Table 13-14 describes the conditions you can select.

Table 13-14 Conditions to Use with Filters

Condition

Description

Any Of

Returns any of the values you specify.

Between

Returns values that are between two specified values. When you select Between, a second Value field appears for the second default value.

Bottom N

Returns the lowest n values in the column.

Bottom Percent

Returns the lowest n percent of values in the column.

Equal to

Returns values that are equal to a specified value.

Greater Than

Returns values that are greater than a specified value.

Greater Than or Equal to

Returns values that are greater than or equal to a specified value.

Is False

In a column that evaluates to True or False, returns data rows that contain false values.

Is Not Null

Returns data rows that contain values.

Is Null

Returns data rows that do not contain values.

Is True

In a column that evaluates to True or False, returns data rows that contain true values.

Less Than

Returns values that are less than another value.

Less Than or Equal to

Returns values that are less than or equal to another value.

Like

Returns strings that match all or part of the specified string. % matches zero or more characters. _ matches one character.

Not Between

Returns values that are not between two specified values. When you select Not Between, a second Value field appears for the second default value.

Not Equal to

Returns values that are not equal to another value.

Not Like

Returns strings that do not match all or part of the specified string. % matches zero or more characters. _ matches one character.

Top N

Returns the top n values in the column.

Top Percent

Returns the top n percent of values in the column.

Types of Filter Conditions

Table 13-15 describes the types of filter conditions and provides examples of how filter conditions are translated into instructions to the data source.

Table 13-15 Examples of Filter Conditions

Type of filter condition

Description

Examples of instructions to data source

Comparison

Compares the value of one expression to the value of another expression using:

•Equal to

•Not Equal to

•Less Than

•Less Than or Equal to

•Greater Than

•Greater Than or Equal to

quantity = 10

custName = 'Acme Inc.'

custName > 'P'

custState <> 'CA'

orderDate > {d '2005-06-30'}

Range

Tests whether the value of an expression falls or does not fall within a range of values using Between or Not Between. The test includes the endpoints of the range.

price BETWEEN 1000 AND 2000

custName BETWEEN 'E' AND 'K'

orderDate BETWEEN
{d '2005-01-01'} AND {d '2005-06-30'}

Membership

Tests whether the value of an expression matches one value in a set of values using Any Of.

officeCode IN (101,103,104)

itemType IN ('sofa', 'loveseat', 'endtable',
'clubchair')

orderDate IN
({d '2005-10-10'}, {d '2005-10-17'})

Pattern-matching

Tests whether the value of a string field matches or does not match a specified pattern using Like or Not Like. % matches zero or more characters. _ matches one character.

custName LIKE 'Smith%'

custName LIKE 'Smiths_n'

custState NOT LIKE 'CA%'

Null value

Tests whether a field has or does not have a null, or missing, value using Is Null or Is Not Null.

manager IS NULL

shipDate IS NULL

shipDate IS NOT NULL

Setting Filter Values

After you choose a condition, you set a filter value.

Step 1 To view all the values for the selected column, select Select Values.

Additional fields appear in the Filter dialog box as shown in Figure 13-46. These fields allow you to find and select a filter value.

Figure 13-46 Selecting a Filter Value in Interactive Viewer

Step 2 To search for a value, type the value in the Find Value field, then click Find.

All values that match your filter text are returned. For example, if you type:

40

the text box displays any values in the column that begin with 40, such as:

40

400

4014

40021

When you see the value you want in the large text box, double-click the value. The value appears in the Value field.

Creating Filters

If the detail column you selected is a merged column, the Select data item dialog box appears.

Step 2 From the Select date drop-down list, select the column name for which you want to apply a filter, then click Apply.

The Filter dialog box appears. The name of the column you selected appears in the Filter By field. You cannot change the name.

Step 3 From the Condition pulldown menu, select a condition. Table 13-14 describes the conditions you can select.

•If you select Between or Not Between, Value From and Value To, additional fields appear to display a range of values.

•If you select Is False, Is True, Is Null, or Is Not Null, no value fields appear. For all other selections, a single value field appears.

Step 4 Enter values in each of the available fields.

To view all possible values for the column, click Select Values and select from the drop-down list.

Step 5 Click Apply.

The results of applying the filter are displayed.

Modifying or Clearing a Filter

To modify or clear a filter:

Step 1 Select the column that uses the filter.

Step 2 Select Filter.

The Filter dialog box opens, displaying the existing filter condition

•To modify the filter, change the setting in the Condition field or change the values.

•To remove the filter, click Clear.

Step 3 Click Apply.

Creating a Filter with Multiple Conditions

You can create a filter with more than one condition. For example, you can create a filter that retrieves the names of customers who have a credit rank of either A or B, and who have open orders totaling between $250,000 and $500,000.

To create a filter with multiple conditions, you choose Advanced Filter on the Filter dialog to use the Advanced Filter dialog box. The Advanced Filter dialog box for Interactive Viewer is shown in Figure 13-47.

Figure 13-47 The Advanced Filter Dialog Box in Interactive Viewer

Advanced Filter provides a great deal of flexibility in setting the filter value. For conditions that test equality and for the Between condition, you can either set a literal value or you can base the value on another data column.

For example, you can request actual shipping dates that are greater than the forecast shipping dates, or actual sales that are less than sales targets. To do this:

Step 1 Select a column, then select Filter.

The Filter dialog box appears.

Step 2 Click Advanced Filter.

The Advanced Filter dialog box appears. The Filter By field displays the name of the first column in the report.

Step 3 From the Filter By menu, select the column that contains the data you want to filter.

Step 4 In the Condition field, select a condition, such as Equal To, Between, or Less Than.

Step 5 In Value, select one of the following options:

•Specify literal value—This is the default selection. To specify a literal value, type a value in the field provided. If you click Select Values, a field appears displaying all data values for the specified column. For long lists, you can find a value by typing the value in the Filter Text field and clicking Find.

•Use value from data field—When you select Use value from data field, a drop-down list of columns appears. The columns in this list have the same data type as the column you selected in the Filter By field.

Step 6 Click Add Condition.

The filter condition appears in Filters.

Step 7 Validate the filter syntax by clicking Validate.

You have now created a filter with one condition. The next step is to add conditions.

Step 9 In Filters, adjust the filter conditions to achieve the desired filtering. You can combine the conditions in the following ways:

•Using AND, OR, and NOT

By default, the second filter condition is preceded by AND. AND means that both conditions must be true for a data row to appear in the report. You can change AND to OR by choosing OR. OR means that only one condition has to be true for a data row to appear in the report.

If you choose NOT, NOT appears after the AND or OR. NOT means that the condition must be false for a data row to appear in the report.

•If you add more than one condition, you can use the parentheses buttons to group conditions.

If you enclose two or more filter conditions in parentheses, the conditions in the parentheses are evaluated first. Then, the entire filter expression is evaluated.

For example, A AND B OR C is evaluated from left to right, so A and B must be true or C must be true for a data row to appear in the report. In A AND (B OR C), B OR C is evaluated first, so A must be true and B or C must be true for a data row to appear in the report.

Deleting One Filter Condition in a Filter that Contains Multiple Conditions

To delete a filter condition:

Step 1 Select a detail column, then select Filter.

The Filter dialog box appears.

Step 2 Click Advanced Filter.

The Advanced Filter dialog box appears. The lower portion of Advanced Filter displays all the filter conditions in the report.

Step 3 Select a filter condition to delete, then click Delete.

Step 4 Click Apply.

Filtering Highest or Lowest Values in Columns

When a table contains hundreds of rows, it can be helpful to display the highest or lowest values in a column. For example, you might want to view the ten sales representatives who produce the most revenue or the top twenty-five percent of energy consumers.

Step 2 From the Filter pulldown menu, select a particular number of rows or a percentage of rows, as shown in Figure 13-48.

Step 3 Enter a value in the field next to the Filter pulldown menu to specify the number or percentage of rows to display.

For example, to select the top 10 sales representatives by sales volume, after you have selected the column that contains sales volume data and chosen Filter > Top or Bottom N, in the Top or Bottom N dialog box, select Top N and enter 10.

Figure 13-48 Sorting Top or Bottom Values in a Column

Step 4 Click Apply.

Understanding Charts

A chart is a graphical representation of data or the relationships among data sets. Charts display complex data in an easy-to-assimilate format. Figure 13-49 shows the parts of a basic bar chart. A chart displays data as one or more sets of points. The chart organizes data points into sets of values called series. The two types of series are:

•Category series— The category series typically determines what text, numbers, or dates you see on the x-axis.

•Value series—The value series typically determines the text, numbers, or dates on the y-axis.

In Figure 13-49, the category series contains a set of regions, and the value series contains a set of sales figure values.

Figure 13-49 Parts of a Basic Bar Chart

There are a variety of chart types. Some types of data are best depicted with a specific type of chart. Charts can be used as reports in themselves and they can be used together with tabular data report styles.

Modifying Charts

The basic characteristics of a chart are determined in the report design editor. Such things as the chart type and the data source are part of the report design and cannot be changed in the viewer. You can change the following aspects of the chart:

The Interactive Viewer presents the capability to:

•Filter the data presented in the chart

•Change the chart subtype

•Change the chart format

Select these options from a context menu by right-clicking on the chart in Interactive Viewer.

Filtering Chart Data

The data displayed in the chart can be filtered in much the same way that any data column is filtered. With a chart you can filter either the x-axis or the y-axis. To do this:

Step 1 Right-click on the chart to display the context menu.

Step 2 Select Filter.

The Chart Filter dialog box appears.

Changing Chart Subtype

charts have subtypes, which you can change as needed:

•Bar chart—Side-by-Side, Stacked, Percent Stacked

•Line chart—Overlay, Stacked, Percent Stacked

•Area chart—Overlay, Stacked, Percent Stacked

•Meter chart—Standard, Superimposed

•Stock chart—Candlestick, Bar Stick

Many chart types offer two-dimensional subtypes, in which the chart shape appears flat against the chart background. Some charts also can be displayed with depth. A chart with depth appears to have added dimension. To do this:

Step 1 Right-clicking the chart whose subtype you want to modify.

Step 2 Select Chart Subtype.

The Chart Subtype dialog box appears.

Step 3 Select the desired chart subtype.

Changing Chart Formatting

Some of the formatting for a chart, such as the colors of the bars in a bar chart and the background color of the chart, comes from the report template or the theme.

When viewing the report you can modify other items of the chart's format, including the fonts and font sizes of the chart title and axis labels, and the height and width of the chart. You can hide axis labels, place labels at an angle relative to the axis, and hide the legend or determine where to display the legend in relation to the chart.

You can modify other aspects of the chart's appearance by right-clicking the chart and choosing Format. In the dialog box that appears, choose the desired formatting properties.

To modify other aspects of the chart's appearance, use Format Chart, shown in Figure 13-50.

Figure 13-50 Chart Formatting Options

You use this page to:

•Edit and format the default chart title.

•Edit and format the default title for the category, or x-, axis.

•Modify settings for the labels on the x-axis. You can:

–Indicate whether to display x-axis labels.

–Indicate whether to rotate x-axis labels and set the degree of rotation.

–Indicate whether to stagger x-axis labels. For example, you can show data points for every third month, every ten days, every other year, and so on.

–Set the interval for staggered x-axis labels.

•Edit and format the default title for the y-axis, if the chart uses a y-axis.

•Set the chart's height and width.

•Select the dimension. The options are 2-dimensional and 2-dimensional with depth.

•Indicate whether to flip, or reverse, the chart's x- and y-axes.

•Indicate whether to show a legend, and if so, whether to place it above the chart, below the chart, or to the left or right of the chart.