Cisco Acs 5x User Guide

							13-57
User Guide for Cisco Secure Access Control System 5.3
OL-24201-01
Chapter 13      Managing Reports
  Organizing Report Data
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)
Table 13-11 Examples of Functions (continued)
Function Description Example of use 
						

							13-58
User Guide for Cisco Secure Access Control System 5.3
OL-24201-01
Chapter 13      Managing Reports
  Organizing Report Data
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])
Table 13-11 Examples of Functions (continued)
Function Description Example of use 
						

							13-59
User Guide for Cisco Secure Access Control System 5.3
OL-24201-01
Chapter 13      Managing Reports
  Organizing Report Data
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])
Table 13-11 Examples of Functions (continued)
Function Description Example of use 
						

							13-60
User Guide for Cisco Secure Access Control System 5.3
OL-24201-01
Chapter 13      Managing Reports
  Organizing Report Data
Understanding Supported Operators
Table 13-12 describes the mathematical and logical operators you can use in writing expressions that 
create calculated columns.
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.
Table 13-12 Supported Operators to Use in Writing Expressions
Operator Description
x + y Addition of numeric values
x - y Subtraction of numeric values
x * y Multiplication of numeric values
x / y Division of numeric values
x% Percentage of a numeric value
x & y Concatenation of string values
x = y Test for equality of two values
x > y Tests whether x is greater than y
x < y Tests whether x is less than y
x >= y Tests whether x is greater than or equal to y
x 
						

							13-61
User Guide for Cisco Secure Access Control System 5.3
OL-24201-01
Chapter 13      Managing Reports
  Organizing Report Data
Using Multiply Values in Calculated Columns
To use multiply values in calculated columns:
Step 1Select a column. In the report, the new calculated column appears to the right of the column you select. 
Step 2Select Add Calculation. 
The Calculation dialog box appears.
Step 3In 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 4In 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.
Step 5Select 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 6Enter an asterisk ( * ) as the multiplication operator. You do not need to include a space after the column 
name.
Step 7Enter 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 8To verify the expression, click Va l i d a t e.
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 9After 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 1Select a column. 
The calculated column appears to the right of the column you select. 
Step 2Select Add Calculation. 
The Calculation dialog box appears.
Step 3In Column Label, type a name for the calculated column. For example, type Forecast Shipping Date.
Step 4In Enter Expression, type the letter A. 
A drop-down list appears, displaying functions that begin with A. 
Step 5Select ADD_DAY(date, daysToAdd).
Step 6For the first argument, type a left opening bracket ( [ ) and select the date column from the drop-down 
list. For example, select Order Date.  
						

							13-62
User Guide for Cisco Secure Access Control System 5.3
OL-24201-01
Chapter 13      Managing Reports
  Organizing Report Data
Step 7For the second argument, type the number of days to add. In this case, type 7.
Step 8Validate 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 1Select a column. 
In the report, the calculated column will appear to the right of the column you select.
Step 2Select Add Calculation. 
The Calculation dialog box appears.
Step 3In 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 4In Enter Expression, type the letter d. 
A drop-down list appears, displaying functions that begin with d. 
Step 5Select DIFF_DAY(date1, date2).
Step 6For 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 7For 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 8Validate the expression, then click Apply. 
The new calculated column appears in the report, displaying the difference between the two dates.
Working with Aggregate Data
Aggregating data involves performing a calculation over a set of data rows. For example, in a report that 
has a column to calculate price*quantity, you can take the sum of all those calculations to obtain the total 
price of all items. You can create up to three aggregations for a calculated column.
When you aggregate data, you choose whether the aggregation appears in the table header or footer. If 
the column is a detail column in a data group, you can display the aggregate value in the group header 
or footer. Figure 13-39 shows aggregate data in a group footer. 
						

							13-63
User Guide for Cisco Secure Access Control System 5.3
OL-24201-01
Chapter 13      Managing Reports
  Organizing Report Data
Figure 13-39 Aggregate Row for a Group
Table 13-13 shows the aggregate functions that you can use.
Table 13-13 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. 
						

							13-64
User Guide for Cisco Secure Access Control System 5.3
OL-24201-01
Chapter 13      Managing Reports
  Organizing Report Data
Creating an Aggregate Data Row
To create an aggregate data row:
Step 1Select 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 2From the Select Function menu, select the function you want to use.
The available functions depend on the type of data the column contains:
If the column contains text data, you can count all the values in the column or count the distinct 
values in the column, for example. 
If the column contains numeric data, you can count values, get an average value or a weighted 
average, total the values in the column, and so on. 
The syntax of the selected function appears in the lower portion of the Aggregation dialog box, as shown 
in Figure 13-40.
Figure 13-40 The Aggregation Dialog Box in Interactive Viewer
Step 3
In the Aggregate on field:
Select whether to display the aggregate value in the table header or the table footer. The default 
setting is to display the aggregate value in the table footer.
Select whether to display the aggregate value in the group header or group footer, if the selected 
column is a grouped column.
Step 4Click Apply. 
The aggregate data appears where you placed it in the report design.  
						

							13-65
User Guide for Cisco Secure Access Control System 5.3
OL-24201-01
Chapter 13      Managing Reports
  Organizing Report Data
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.
To add an aggregate row:
Step 1Select a calculated column that contains an aggregate row, then select Aggregation. 
The Aggregation window appears.
Step 2Click Add aggregation. 
An additional section appears in the Aggregation dialog box, as shown in Figure 13-41.
Figure 13-41 Adding an Aggregate Row for a Column in Interactive Viewer
Step 3
Create the second aggregate row, then click Apply. 
						

							13-66
User Guide for Cisco Secure Access Control System 5.3
OL-24201-01
Chapter 13      Managing Reports
  Hiding and Filtering Report Data
Deleting Aggregate Rows
To delete an aggregate row:
Step 1Select the calculated column that contains the aggregation you want to remove, then select Aggregation.
The Aggregation dialog box appears, displaying all the aggregations for the column. 
Step 2For the aggregation you want to remove, choose Delete Aggregation, then click Apply.
Hiding and Filtering Report Data
You can decide whether to hide or display data in your report in several ways:
You can specify whether to hide or display detail rows.
You can specify that you want to display only one copy of duplicate rows.
When the data set provides more information than your report needs, you can filter out unwanted 
data. You also can enable your users to specify the filter values by using run-time parameters.
Hiding or Displaying Column Data
Frequently, you do not want to display all the data in a report. For example, a column of detail can 
display duplicate values in consecutive data rows. When the duplication is unnecessary or makes the 
report difficult to read, you can suppress consecutive duplicate values.
In addition, as you design a report, you can collapse groups or sections to display only the column 
headings and summary data, such as aggregate data rows.
Suppressing Repeated Values
Before you begin formatting and adjusting the data in your report, data rows appear in the report exactly 
as they appear in the data source. In the report shown in Figure 13-42, for example, the Location column 
shows the city name each time the name occurs.
Figure 13-42 Duplicate Values in Columns
As Figure 13-43 shows, the report is easier to read when duplicate values are not repeated.