13-54
User Guide for Cisco Secure Access Control System 5.4
OL-26225-01
Chapter13 M anaging Reports
Organizing Report Data
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])
Table13-11 Examples of Functions (continued)
Function Description Example of use