User Guide for the Cisco Secure Access Control System 5.0
Managing Reports in the Monitoring & Report Viewer
Downloads: This chapterpdf (PDF - 853.0KB) The complete bookPDF (PDF - 12.93MB) | Feedback

Managing Reports in the Monitoring & Report Viewer

Table Of Contents

Managing Reports in the Monitoring & Report Viewer

Available Reports in the Catalog

Viewing Reports

About Standard Viewer

About Interactive Viewer

About Interactive Viewer's Context Menus

Navigating Reports

Using the Table of Contents

Exporting Report Data

Printing Reports

Saving Report Designs in Interactive Viewer

Formatting Reports in Interactive Viewer

Editing Labels

Formatting Labels

Formatting Data

Resizing Columns

Changing Column Data Alignment

Formatting Data in Columns

Formatting Data in Aggregate Rows

Formatting Data Types

Formatting Numeric Data

Formatting Fixed or Scientific Numbers or Percentages

Formatting Custom Numeric Data

Formatting String Data

Formatting Custom String Data

Formatting Date and Time

Formatting Custom Date and Time

Formatting Boolean Data

Applying Conditional Formats

Setting Conditional Formatting for Columns

Deleting Conditional Formatting

Setting and Removing Page Breaks in Detail Columns

Setting and Removing Page Breaks in a Group Column

Organizing Report Data

Displaying and Organizing Report Data

Reordering Columns in Interactive Viewer

Removing Columns

Hiding or Displaying Report Items

Hiding Columns

Displaying Hidden Columns

Merging Columns

Selecting a Column from a Merged Column

Sorting Data

Sorting a Single Column

Sorting Multiple Columns

Grouping Data

Adding Groups

Grouping Data Based on Date or Time

Removing an Inner Group

Creating Report Calculations

Understanding Supported Calculation Functions

Understanding Supported Operators

Using Numbers and Dates in an Expression

Using Multiply Values in Calculated Columns

Adding Days to an Existing Date Value

Subtracting Date Values in a Calculated Column

Working with Aggregate Data

Creating an Aggregate Data Row

Adding Additional Aggregate Rows

Deleting Aggregate Rows

Hiding and Filtering Report Data

Hiding or Displaying Column Data

Displaying Repeated Values

Hiding or Displaying Detail Rows in Groups or Sections

Working with Filters

Types of Filter Conditions

Setting Filter Values

Creating Filters

Modifying or Clearing a Filter

Creating a Filter with Multiple Conditions

Deleting One Filter Condition in a Filter that Contains Multiple Conditions

Filtering Highest or Lowest Values in Columns

Understanding Charts

Modifying Charts

Filtering Chart Data

Changing Chart Subtype

Changing Chart Formatting

Managing Reports

Adding Reports to Your Favorites

Running Favorites Reports

Deleting Reports from Favorites

Running Catalog Reports

Deleting Catalog Reports

Running Named Reports

Understanding the Report_Name Page

Customizing Reports

Restoring Reports


Managing Reports in the Monitoring & Report Viewer


The Monitoring and Reports drawer appears in the primary web interface window and contains the Launch Monitoring & Report Viewer option.

Click Launch Monitoring & Report Viewer to open the Monitoring & Reports Viewer in a secondary web interface window, which contains these drawers:

Monitoring and Reports

Monitoring Configuration. (See Monitoring & Report Viewer System Operations, page 13-1.)

This section contains information about functions when you select Monitoring and Reporting > Reports and contains the following topics:

Available Reports in the Catalog

Viewing Reports

Managing Reports

Related Topics

Dashboard Pages, page 10-2

Managing Alarms, page 10-7

Monitoring & Report Viewer Troubleshooting, page 12-1.

Available Reports in the Catalog

Table 11-1 describes the reports available when you select Monitoring and Reports, launch Monitoring & Report Viewer, then select Monitoring and Reports > Reports > Catalog.

Table 11-1 Available ACS Reports  

Report Name
Description
Logging Category
AAA Protocol

AAA diagnostics

Shows AAA diagnostics based on the severity for a selected time period.

Policy diagnostics, identity stores diagnostics, authentication flow diagnostics, RADIUS diagnostics, TACACS+ diagnostics

Authentication Lookup

Shows RADIUS and TACACS+ authentication details for a particular user or MAC Address (if applicable) for a selected time period.

Passed authentications, failed attempts

Authentication Trend

Shows RADIUS and TACACS+ authentication summary information, along with a graphical representation, for a selected time period.

Passed authentications, failed attempts

RADIUS Accounting

Shows RADIUS accounting information for a selected time period.

RADIUS accounting

RADIUS Authentication

Shows RADIUS authentication details for a selected time period.

Passed authentications, failed attempts

TACACS Accounting

Shows TACACS+ user or command accounting information for a selected time period.

TACACS accounting

TACACS Authentication

Shows TACACS+ Authentication details for a selected time period.

Passed authentications, failed attempts

TACACS Authorization

Shows TACACS+ Authorization details for a selected time period.

Passed authentications, failed attempts

Access Service

Access Service Authentication Summary

Shows the RADIUS/TACACS+ authentication summary information for a particular access service, along with a graphical representation, for a selected time period.

Passed authentications, failed attempts

Top N Authentications By Access Service

Shows the top N passed/failed/total authentications for the RADIUS/TACACS+ protocol with respect to access service for a selected time period.

Passed authentications, failed attempts

ACS Instance

ACS Administration Logins

Shows administrator login details for both the web interface and the CLI for a selected time period.

Administrative audit

ACS configuration Changes

Shows all configuration changes done in ACS by the administrator for a selected time period.

Administrative audit

ACS Health Summary

Shows a summary of process status and system utilization information such as CPU and memory utilization, RADIUS/TACACS+ latency and throughput (in tabular and graphical formats), process status, process downtime, and disk space utilization for a selected ACS instance in a selected time period.

System statistics

ACS Instance Authentication Summary

Shows the RADIUS/TACACS+ authentication summary information for a particular ACS instance, along with a graphical representation, for a selected time period.

Passed authentications, failed attempts

ACS System Diagnostics

Shows system diagnostics details based on severity for a selected time period.

Internal Operations Diagnostics, distributed management, administrator authentication and authorization

Top N Authentication by ACS Instance

Shows the top N passed/failed/total authentication count for RADIUS/TACACS+ protocol for an ACS instance for a selected time period

Passed authentications, failed attempts

Endpoint

Endpoint MAC Authentication Summary

Shows the RADIUS authentication summary information for a particular MAC/Host Lookup, along with a graphical representation, for a selected time period.

Passed authentications, failed attempts

Top N Authentications By Endpoint MAC Address

Shows the top N passed/failed/total authentication count for RADIUS protocol for a MAC/Host Lookup address for a selected time period.

Passed authentications, failed attempts

Top N Authentications By Machine

Shows the top N passed/failed/total authentication count for RADIUS protocol for a machine for a selected time period.

Passed authentications, failed attempts

Failure Reason

Authentication Failure Code Lookup

Shows the detailed information and appropriate resolution steps for a particular failure reason.

N/A

Failure Reason Authentication Summary

Shows the RADIUS/TACACS+ authentication summary information for a particular failure reason, along with a graphical representation, for a selected time period.

Passed authentications, failed attempts

Top N Authentications By Failure Reason

Shows the top N failed authentications count for RADIUS/TACACS+ protocol for a Failure Reason for a selected time period.

Passed authentications, failed attempts

Network Device

Network Device Authentication Summary

Shows the RADIUS/TACACS+ authentication summary information for a particular network device, along with the graphical representation, for a selected time period.

Passed authentications, failed attempts

TACACS+ Command Audit by Network Device

Shows TACACS+ audit details for a particular network device for a selected time period.

TACACS+ accounting

TACACS+ Command Authorization by Network Device

Shows TACACS+ authorization details including authorization status (passed/failed) for a particular network device for a selected time period.

Passed authentications, failed attempts

Top N Authentications by Network Device

Shows the top N passed/failed/total authentication count for RADIUS/TACACS+ protocol for a network device for a selected time period.

Passed authentications, failed attempts

Session Directory

RADIUS Active Sessions

Shows RADIUS active, authenticated, and terminated session details.

Passed authentications, RADIUS accounting

RADIUS Session History

Shows the RADIUS session history summary including total authenticated, active, and terminated sessions, total and average session duration, and throughput for a selected time period.

Passed authentications, RADIUS accounting

RADIUS Session Lookup

Shows all RADIUS authenticated, active, and terminated session details for a selected time period.

Passed authentications, RADIUS accounting

TACACS Active Sessions

Shows TACACS+ active and terminated session details.

TACACS+ accounting

TACACS Session History

Shows the TACACS+ session history summary including total active and terminated sessions, total and average session duration, and throughput for a selected time period.

TACACS+ accounting

TACACS Session Lookup

Shows all TACACS+ active and terminated session details for a selected time period.

TACACS+ accounting

User

TACACS Command Audit By User

Shows the TACACS+ audit details for a particular user for a selected time period.

TACACS+ accounting

TACACS Command Authorization By User

Shows the TACACS+ authorization details and authorization status (passed/failed) for a particular user for a selected time period.

Passed authentications, failed attempts

Top N Authentications By User

Shows the top N passed/failed/total authentication count for RADIUS/TACACS+ protocol for users for a selected time period.

Passed authentications, failed attempts

User Authentication Summary

Shows the RADIUS/TACACS+ authentication summary information for a particular user, along with the graphical representation, for a selected time period.

Passed authentications, failed attempts


Viewing Reports

This section describes the viewing tasks you can perform in either Standard Viewer or Interactive Viewer. In Standard Viewer or Interactive Viewer, you can navigate 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.

This section contains the following topics:

About Standard Viewer

About Interactive Viewer

About Interactive Viewer's Context Menus

Navigating Reports

Exporting Report Data

Printing Reports

Saving Report Designs in Interactive Viewer

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 11-1 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 11-1 Context Menu for Column Data in Interactive Viewer

Figure 11-2 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 11-2 Context Menu for Labels in Interactive Viewer

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

Figure 11-3 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 11-4. 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 11-4 Paging Tool

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

Figure 11-5 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 11-6 shows a report in Standard Viewer with the table of contents open.

Figure 11-6 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 11-7 shows an expanded entry.

Figure 11-7 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 11-8 shows a sample CSV output file.

Figure 11-8 Report Data Exported to Microsoft Excel

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


Step 1 In the viewer, select Export Data. The Export Data dialog box appears, as shown in Figure 11-9.

Figure 11-9 The Export Data Dialog Box

Available Result Sets lists the tables in the report.

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.


Step 1 Click Save. The Save dialog box appears. An example is shown inFigure 11-10.

Figure 11-10 Save Dialog Box

Step 2 Navigate to the location where you want to save the file. Then, type a file name and click Save.

Step 3 Click OK on the confirmation message that appears.


Formatting Reports in Interactive Viewer

You can use the Interactive Viewer to format reports.

This section contains the following topics:

Editing Labels

Formatting Labels

Applying Conditional Formats

Setting and Removing Page Breaks in Detail Columns

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.


Step 1 In Interactive Viewer, select and right-click a label. The context menu appears, as shown in Figure 11-11.

Figure 11-11 Interactive Viewer Context Menu

Step 2 Select Change Text. The Edit Text dialog box appears.

Step 3 Modify the text as desired and click Apply.


Formatting Labels


Step 1 To modify the formatting of a label, click on the label and then select Style > Font. The Font dialog box appears.

Step 2 Select the formats you desire, then click Apply.


Formatting Data

There are several ways to modify how the report data is formatted. You can:

Resize a column.

Change the alignment of data in a column.

Hide, show, delete, or reorder columns.

Specify that repeat values do not appear consecutively in a column.

Filter the data values.

Sort the data values.

Modify the font, color, style, and other properties of the text.

Specify that the column displays uppercase or lowercase.

Modify the default formatting of the data value in an aggregate row.

Format the data type. For example, if the column displays numbers, you can format the data as currency, percentages, or scientific numbers.

Resizing Columns

In the Interactive Viewer, you can change the width of the column and the alignment of the data in the column


Step 1 To change a column's width, select and right-click the column.

Step 2 Select Column > Column Width from the context menu.The Column Properties form appears.

Step 3 In Column Properties form, type the new column width, then click Apply.


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 11-12 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.

Figure 11-12 Formatting Data in an Aggregate Row


Step 1 Select an aggregate data value, then click Font. The Font dialog box appears.

Step 2 Modify the settings as desired, then click Apply.


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 11-2 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 11-2 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 11-13 shows the numeric formats you can use.

Figure 11-13 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 1 Select a column that contains numeric data, then select Format. The Number column format dialog box appears.

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 11-3 shows examples of custom format patterns and their effects on numeric data

.

Table 11-3 Results of Custom Number Format Patterns 

Format pattern
Data in the data set
Result of formatting

0000.00

12.5
124.5
1240.553

0012.50
0124.50
1240.55

#.000

100
100.25
100.2567

100.000
100.250
100.257

$#,###

2000.00
20000.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 11-3.

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 11-2 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 11-4 describes the symbols you can use to define custom string formats

.

Table 11-4 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 11-5 for examples.

&

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

!

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

>

Converts string characters to uppercase.

<

Converts string characters to lowercase.


Table 11-5 shows examples of custom string format patterns and their effects on text data.

Table 11-5 Results of Custom String Format Patterns 

Format pattern
Data in the data source
Results of formatting

(@@@) @@@-@@@@

6175551007
5551007

(617) 555-1007
(   ) 555-1007

(&&&) &&&-&&&&

6175551007
5551007

(617) 555-1007
() 555-1007

!(@@@) @@@-@@@@

6175551007
5551007

(617) 555-1007
(555) 100-7

!(&&&) &&&-&&&&

6175551007
5551007

(617) 555-1007
(555) 100-7

!(@@@) @@@-@@@@ + ext 9

5551007

(555) 100-7    + ext 9

!(&&&) &&&-&&&& + ext 9

5551007

(555) 100-7 + ext 9

>&&&-&&&&&-&&

D1234567xy

D12-34567-XY

<&&&-&&&&&-&&

D1234567xy

d12-34567-xy



Step 1 Select a string data column, then click Format. The String column format window appears.

Step 2 In Format String as field, select Custom. A second field, Format Code, appears.

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

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:

4/3/06

Table 11-2 shows the standard date-and-time data type formats.


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 11-6 shows examples of custom formats and their effects on a date that is stored as 4/15/2006 in the data source.

Table 11-6 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)



Step 1 To create a custom date or time format, 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 11-6.

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 11-14.

Figure 11-14 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 11-15 shows Conditional Formatting.

Figure 11-15 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 11-16. In this field, you type the comparison value. All data that is less than or equal to your value triggers the conditional formatting.

Figure 11-16 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 11-17. The names of all customers whose orders total between $10,000 and $100,000 take the conditional formatting.

Figure 11-17 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 11-18.

Figure 11-18 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. You set the formatting by using the same Font dialog box used for formatting labels, as shown in Figure 11-19.

Figure 11-19 The Font Dialog Box

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

Step 5 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


Step 1 To remove conditional formatting for a column, 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 11-20. To remove all conditional formatting for a column, delete all the conditional formatting rules.

Figure 11-20 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.


Step 1 Select and right-click a grouped column.

Step 2 From the context menu, choose Group > Page Break. The Page Break window appears, as shown in Figure 11-21.

Figure 11-21 Setting a Page Break

Step 3 Specify whether to set a page break before every group, or for every group except the first or last groups. To delete an existing page break, select None in Before group or After group.

Step 4 Click Apply.


Organizing Report Data

You can use Interactive Viewer to organize report data.

This section contains the following topics:

Displaying and Organizing Report Data

Merging Columns

Sorting Data

Grouping Data

Creating Report Calculations

Working with Aggregate 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


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 11-22 shows the customer Name column as the selected column.

Figure 11-22 Report Displaying Customers Grouped by Country

Step 2 Select Column > Move to Group Header. The Move to Group Header window appears, as shown in Figure 11-23.

Figure 11-23 Move to Group Header Dialog Box

Step 3 From the Move to Group field, select a value.

Step 4 In the Header row field, select the row number in which to move the value you selected in Step 3.

Step 5 Click Apply. The data value from the first detail row in each group appears in each group header, as shown in Figure 11-24. The Name column displays one customer name beside each country group header.

Figure 11-24 Report Displaying Customer Name in Each Group Header


Removing Columns

To remove a column, select the column and click Delete. When you remove a column from the report, you are not deleting the column from the information object or other data source.

To remove multiple columns, press Ctrl and select the columns to remove. Then, click Delete.

Hiding or Displaying Report Items


Step 1 Select and right-click a column.

Step 2 Select Hide or Show Items. The Hide or Show Items dialog box appears, similar to Figure 11-25.

Figure 11-25 Hiding or Displaying Report Items

Step 3 Select any items you want to hide. Deselect any hidden items you want to display. To display all hidden items, click Clear.

Step 4 Click Apply.


Hiding 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


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 11-26 the columns under the City and state or province column header are separate.

Figure 11-26 Separate Columns

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

Figure 11-27 Merged Column


Step 1 To merge data in multiple columns, 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.


Step 1 To select one column from a merged column, 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.

There are three ways to sort data:

Sorting a Single Column

Sorting Multiple Columns

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 11-28 shows the result of this multiple-column sort.

Figure 11-28 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 simply 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 11-29 shows the first page of an ungrouped report.

Figure 11-29 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 11-30 shows the same inventory, grouped according to the category, with one category on each page.

Figure 11-30 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


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 11-31, the group expands to show all the detail rows. To collapse the group, click the minus sign ( - ) beside the group name.

Figure 11-31 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 2 From the context menu, select Group > Add Group. The Group Detail dialog box appears, as shown in Figure 11-32.

Figure 11-32 Grouping Date or Time Data

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 11-33.

Figure 11-33 Data Grouped by Month


Removing 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 11-34. In this illustration, the Item Totals column displays the product of the unit price times the quantity.

Figure 11-34 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 11-35, 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 11-35 Selecting a Function

Understanding Supported Calculation Functions

Table 11-7 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 11-7 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 11-8 describes the mathematical and logical operators you can use in writing expressions that create calculated columns.

Table 11-8 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


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.

Step 4 In the Enter Expression field, type a left square bracket ( [ ). A list of the columns in the report appears. This list includes any calculated columns the report contains.

Select the column that contains the multiplier. For example, to multiply a unit price times the quantity ordered, select the column that contains unit prices.

Step 5 Enter an asterisk ( * ) as the multiplication operator. You do not need to include a space after the column name.

Step 6 Enter another left square bracket ( [ ) and select the multiplicand. For example, if the multiplier is the unit price, select the column that contains the quantity ordered as the multiplicand.

Step 7 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 8 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. Select Add Calculation. The Calculation dialog box appears.

Step 2 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 3 In Enter Expression, type the letter d. A drop-down list appears, displaying functions that begin with d.

Step 4 Select DIFF_DAY(date1, date2).

Step 5 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 6 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 7 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 11-36 shows aggregate data in a group footer.

Figure 11-36 Aggregate Row for a Group

Table 11-9 shows the aggregate functions that you can use.

Table 11-9 Aggregate Functions 

Aggregate functions
Description

Average

Calculates the average value of a set of data values.

Count

Counts the data rows in the column.

Count Value

Counts distinct values in the column.

First

Returns the first value in the column.

Last

Returns the last value in the column.

Max

Returns the highest value in the column.

Median

Returns the median value in the column.

Min

Returns the lowest value in the column.

Mode

Returns the most frequently-occurring value in the column.

Quartile

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


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 11-37.

Figure 11-37 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.


Step 1 To add an aggregate row, select a calculated column that contains an aggregate row, then select Aggregation. The Aggregation window appears.

Step 2 Click Add aggregation. An additional section appears in the Aggregation dialog box, as shown in Figure 11-38.

Figure 11-38 Adding an Aggregate Row for a Column in Interactive Viewer

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


Deleting Aggregate Rows


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

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

Step 3 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 11-39, for example, the Location column shows the city name each time the name occurs.

Figure 11-39 Duplicate Values in Columns

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

Figure 11-40 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 11-40, 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.


Step 1 Select and right-click the column that contains duplicate values.

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


Displaying Repeated Values

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

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 11-41 shows a report where the data is grouped by creditrank and the detail rows for each creditrank value are shown.

Figure 11-41 Group Detail Rows Displayed

Figure 11-42 shows the results of hiding the detail rows for the creditrank grouping.

Figure 11-42 Group Detail Rows Hidden


Step 1 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.

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

Step 3 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 11-10 describes the conditions you can select.

Table 11-10 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 11-11 describes the types of filter conditions and provides examples of how filter conditions are translated into instructions to the data source.

Table 11-11 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 11-43. These fields allow you to find and select a filter value.

Figure 11-43 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


Step 1 In Interactive Viewer, select a detail column and choose Filter.

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 11-10 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


Step 1 Select the column that uses the filter.

Step 2 Select Filter. The Filter dialog box opens, displaying the existing filter condition

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

Step 4 To remove the filter, click Clear.

Step 5 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 11-44.

Figure 11-44 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.


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 8 Follow steps Step 3 to Step 7 to create each additional desired filter condition.

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


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 1 To perform this type of filter, right-click a selected column, then select Filter > Top or Bottom N. The Top or Bottom N dialog box appears.

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

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 11-45 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 11-46 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 11-46, the category series contains a set of regions, and the value series contains a set of sales figure values.

Figure 11-46 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.


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.


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 11-47.

Figure 11-47 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.

Managing Reports

You can run reports that are located in your Favorites page or the Catalog pages, add reports to your Favorites page for easy access, and customize and delete reports.

Reports can reside in these locations:

The Favorites page—Monitoring & Reports > Reports > Favorites.

The Reports > Catalog pages—Monitoring & Reports > Reports > Catalog > <report_type> > <report_name>, where report_type is the type of report, and report_name is one report in the list of report names.

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

This section contains the following topics:

Adding Reports to Your Favorites

Running Favorites Reports

Running Catalog Reports

Customizing Reports

Restoring Reports

Deleting Reports from Favorites

Adding Reports to Your Favorites

You can create favorite reports for reports that you access often, a similar idea to creating favorite bookmarks in browsers.


Note Every ACS administrator account is assigned one or more administrative roles. Depending upon the roles assigned to your account, you may or may not be able to perform the operations or see the options described in the following procedure. See Configuring System Administrators and Accounts, page 14-2 to configure the appropriate administrator privileges.


To add a report to your Favorites page:


Step 1 Select Monitoring & Reports > Reports > Catalog > <report_type> >, where report_type is the type of report.

The available reports for the report type you selected are displayed.

Step 2 Click the radio button next to the report you want to add to your favorites. See Table 11-15 for valid field options.

Step 3 Click Add to Favorites. The Add to Favorite page appears.

Step 4 Modify fields in the Add to Favorites page as required.

Step 5 Click Add to Favorite.

The report is added to your Favorites page.


Running Favorites Reports

To run a report in your Favorites page:


Step 1 Select Monitoring & Reports > Reports > Favorites.

The Favorites page appears with the fields described in Table 11-12:

Table 11-12 Favorites Page 

Option
Description

Favorite Name

The name of the favorites report. Click to open a summary of an associated report.

Report Name

The report name associated with a Catalog (Report) type.

Report Type

The general grouping name associated with the report.


Step 2 Do one of the following:

Click the check box next to the report name that you want to run and click Run.

Click the name of the report that you want to run.

The report is generated in the page.

Step 3 Click Launch Interactive Viewer for more options.


Deleting Reports from Favorites


Note When you delete a system report from the Favorites page, the system report remains in the appropriate Reports > Catalog page.


To delete a report from the Favorites page:


Step 1 Select Monitoring & Reports > Reports > Favorites.

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 Favorites page appears without the deleted reports.


Running Catalog Reports

To run a report that is in the Catalog:


Step 1 Select Monitoring & Reports > Reports > Catalog > <report_type>, where report_type is the type of report you want to run.

The available reports for the report type you selected are displayed with the information shown in Table 11-13.

Table 11-13 <report_type> Page 

Option
Description

Report Name

The available reports based on the report type you selected.

Type

The type of report.

Modified At

The time that the associated report was last modified by an administrator, in the format Ddd Mmm dd hh:mm:ss timezone yyyy, where:

Ddd = Sun, Mon, Tue, Wed, Thu, Fri, Sat.

Mmm = Jan, Feb, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec.

dd = A two-digit numeric representation of the day of the month, from 01 to 31.

hh = A two-digit numeric representation of the hour of the day, from 00 to 23.

mm = A two-digit numeric representation of the minute of the hour, from 00 to 59.

ss = A two-digit numeric representation of the second of the minute, from 00 to 59.

timezone = The time zone.

yyyy = A four-digit representation of the year.


Step 2 Click the radio button next to the report name you want to run, then select one of the options under Run:

Run for Today—The report you specified is run and the generated results are displayed.

Run for Yesterday—The report you specified is run using the previous day's values and the generated results are displayed.

Query and Run—The Run Report screen appears where you can enter parameters to use when generating the report.

Step 3 Click Reset Reports to revert to the default report parameters. A message appears asking you to confirm whether to reset the system report files in the catalog to the factory default.

Step 4 Click Launch Interactive Viewer for more options.


Deleting Catalog Reports

To delete a report from the Reports > Catalog pages:


Step 1 Select Monitoring & Reports > Reports > Catalog > <report_type>, where report_type is the type of report that you want to delete.


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 11-13), and report_name is the name of the report that you want to access or run. Table 11-14 describes the available types and names on which you can run reports.

Table 11-14 Reports > Report Types and Names 

<report_type>
<report_name>

AAA Protocol

AAA Diagnostics

Authentication Lookup

Authentication Trend

RADIUS Accounting

RADIUS Authentication

TACACS Accounting

TACACS Authentication

TACACS Authorization

Access Service

Access Service Authentication Summary

Top N Authentications By Access Service

ACS Instance

ACS Administrator Logins

ACS Configuration Changes

ACS Health Summary

ACS Instance Authentication Summary

ACS System Diagnostics

Top N Authentications By ACS Instance

Endpoint

Endpoint MAC Authentication Summary

Top N Authentications By Endpoint MAC Address

Top N Authentications By Machine

Failure Reason

Authentication Failure Code Lookup

Failure Reason Authentication Summary

Top N Authentications By Failure Reason

Network Device

Network Device Authentication Summary

TACACS Command Audit By Network Device

TACACS Command Authorization

Top N Authentications By Network Device

Session Directory

RADIUS Active Sessions

RADIUS Session History

RADIUS Session Lookup

TACACS Active Sessions

TACACS Session History

TACACS Session Lookup

Shared

All reports saved under the Shared folder appear here.

User

TACACS Command Audit By User

TACACS Command Authorization By User

Top N Authentications By User

User Authentication Summary


Understanding the Report_Name Page


Note Not all options listed in Table 11-15 are used in selecting data for all reports.


Table 11-15 <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

Critical

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.

Clear

Click to delete the contents of an associate text box.

Run

Click to run the report for which you have made selections.


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.