Cisco Acs 5x User Guide

							13-47
User Guide for Cisco Secure Access Control System 5.3
OL-24201-01
Chapter 13      Managing Reports
  Organizing Report Data
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, page 13-47
Sorting Multiple Columns, page 13-47
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 1Select and right-click a column.
Step 2From the Context menu, select Sort > Advanced Sort.
Step 3Select a column from the first drop-down list and choose either Ascending or Descending order. Select 
the next column and choose a sort order, and so on.
When you sort multiple columns, it is important to understand the order of precedence for the sort. In 
Advanced Sort, the first column you select is the primary sorting column. Report data is sorted first by 
this column.
If the primary column is Customer and the order is Ascending, for example, the report displays 
customers in alphabetical order. If you select Location as the next column and the order is Ascending, 
then within each Customer entry, the locations are sorted in ascending order. 
If the third column you select is Order Number and the order is Ascending, then within each location, 
the order numbers are sorted in ascending order. Figure 13-31 shows the result of this multiple-column 
sort. 
						

							13-48
User Guide for Cisco Secure Access Control System 5.3
OL-24201-01
Chapter 13      Managing Reports
  Organizing Report Data
Figure 13-31 Sorting Multiple Columns
If the report uses grouped data, the drop-down lists in Advanced Sort show only the detail columns in 
the report, not the columns you used to group the data.
Grouping Data
A report can contain a great deal of data. Consider the task of listing every item a corporation owns, 
along with information such as the purchase price, purchase date, inventory tag number, and the supplier 
for each item. 
If a report presents all these items in an unorganized list, there is no way to determine how much the 
corporation spends for heavy equipment because heavy equipment items are scattered throughout the 
report. 
The report cannot help you see which supplier sells office furniture at the lowest prices. Nor is it easy to 
tell which inventory items are located at a field office and which items are in the warehouse. 
Figure 13-32 shows the first page of an ungrouped report. 
						

							13-49
User Guide for Cisco Secure Access Control System 5.3
OL-24201-01
Chapter 13      Managing Reports
  Organizing Report Data
Figure 13-32 Ungrouped Data
To organize all this information into a useful inventory report, you create data groups and data sections. 
Data groups contain related data rows. 
For example, you can create a report that lists all heavy equipment in one group, all office furniture in 
another group, all telephony equipment in a third group, and so on. For each group, you can show 
aggregate data, such as the total purchase price or a count of the items in a group.
Grouping data gives your report a more polished, organized look and makes it easier to create useful 
comparisons and calculations.
Figure 13-33 shows the same inventory, grouped according to the category, with one category on each 
page.
Figure 13-33 Grouped Data
You can group data in the report design editor or in Interactive Viewer. The changes you make in the 
viewer do not affect the report design. If you work in Enterprise mode, you can save report output that 
reflects your changes.
You can add or remove data groups in Interactive Viewer if the report design does not contain the 
grouping desired during that use of Interactive Viewer. 
						

							13-50
User Guide for Cisco Secure Access Control System 5.3
OL-24201-01
Chapter 13      Managing Reports
  Organizing Report Data
Adding Groups
To add groups:
Step 1Select and right-click the column you want to use to create a group. 
Step 2From the Context menu, select Group > Add Group. 
The new group appears in the viewer. As shown in Figure 13-34, the group expands to show all the detail 
rows. To collapse the group, click the minus sign ( - ) beside the group name.
Figure 13-34 Adding a Group
Grouping Data Based on Date or Time
When you create a group based on a column that contains date or time data, you have the option of setting 
a grouping interval. For example, if the column contains time data, you can group on hours, minutes, or 
seconds.
Step 1Select and right-click the column you want to use to create a group. 
Step 2From the context menu, select Group > Add Group. 
The Group Detail dialog box appears, as shown in Figure 13-35.
Figure 13-35 Grouping Date or Time Data
Step 3
To show every date or time value, leave the default setting Group using individual values. 
						

							13-51
User Guide for Cisco Secure Access Control System 5.3
OL-24201-01
Chapter 13      Managing Reports
  Organizing Report Data
Step 4To set a grouping interval, select Group every and enter a value and select the grouping interval. 
For example, to create a new group for every month, type 1 and select Month from the drop-down list. 
The report displays monthly data groups, as shown in Figure 13-36.
Figure 13-36 Data Grouped by Month
Removing an Inner Group
To remove an inner group:
Step 1Select and right-click the column for the group you want to remove. 
Step 2From the context menu, select Group > Delete Inner Group.
Creating Report Calculations
Most reports require some sort of calculations to track sales, finances, inventory, and other critical 
business activities. You might want to keep a simple count of items in a warehouse or you might need to 
provide more complex financial data such as tracking stock portfolio performance over time. 
You can use typical mathematical functions such as counting, addition, subtraction, multiplication, and 
division. In addition, you can write expressions that extend these basic functions.
To display calculated data in a report, you create a calculated column, such as the one shown in 
Figure 13-37. In this illustration, the Item Totals column displays the product of the unit price times the 
quantity. 
						

							13-52
User Guide for Cisco Secure Access Control System 5.3
OL-24201-01
Chapter 13      Managing Reports
  Organizing Report Data
Figure 13-37 Calculated Column
To create a calculation, you
Provide a title for the calculated column. 
Write an expression that indicates which data to use and how to display the calculated data in the 
report. 
The expression contains a function and one or more arguments. Arguments indicate the data you 
want to use to create the calculation. 
As shown in Figure 13-38, when you type the first letter of a function name in Enter Expression, 
Calculation presents a list of functions that begin with that letter. You select a function and provide 
the arguments.
Figure 13-38 Selecting a Function 
						

							13-53
User Guide for Cisco Secure Access Control System 5.3
OL-24201-01
Chapter 13      Managing Reports
  Organizing Report Data
Understanding Supported Calculation Functions
Table 13-11 provides examples of the functions you can use to create calculations. 
NoteThe Calculation dialog box does not support the use of uppercase TRUE and FALSE functions in 
expressions.Calculation also does not support the use of initial capital letters for True and False. These 
functions must be expressed in lowercase only.
Table 13-11 Examples of Functions
Function Description Example of use
ABS(num) Displays an absolute value for the data in a column.
ABS([TemperatureCelsius])
ADD_DAY(date, 
daysToAdd)Adds a specified number of days to a date value and 
displays the result as a date value. ADD_DAY([ClosingDate], 30)
ADD_HOUR(date, 
hoursToAdd)Adds a specified number of hours to a time value 
and displays the result as a time value.ADD_HOUR([OpenHour], 8)
ADD_MINUTE(date, 
minutesToAdd)Adds a specified number of minutes to a time value 
and displays the result as a time value.ADD_MINUTE([StartTime], 60)
ADD_MONTH(date, 
monthsToAdd)Adds a specified number of months to a date value 
and displays the result as a date value.ADD_MONTH([InitialRelease], 2)
ADD_QUARTER(date, 
quartersToAdd)Adds a specified number of quarters to a date value. ADD_QUARTER([ForecastClosing], 2)
ADD_SECOND(date, 
secondsToAdd)Adds a specified number of seconds to a time value.ADD_SECOND([StartTime], 30)
ADD_WEEK(date, 
weeksToAdd)Adds a specified number of weeks to a date value 
and displays the result as a date value.ADD_WEEK([askByDate], 4)
ADD_YEAR(date, 
yearsToAdd)Adds a specified number of years to a date value.ADD_YEAR([HireDate], 5)
AND Combines two conditions and returns records that 
match both conditions. For example, you can 
request records from customers who spend more 
than $50,000 a year and also have a credit rank of A.This function is used to connect clauses in 
an expression and does not take 
arguments.
AVERAGE(expr) Displays an average value for the column. 
AVERAGE([CostPerUnit])
AVERAGE(expr, 
groupLevel)Displays the average value at the specified group 
level.AVERAGE([TotalCost], 2)
BETWEEN(value, 
upperBound, lowerBound)For a specified column, displays True if a value is 
between two specified values and False otherwise. 
String values and date or time values must be 
enclose in quotation marks. For dates and times, use 
the short date and short time formats.BETWEEN([PostalCode], 11209, 12701)
BETWEEN([ReceiptDate], 
“10/01/06”, “12/31/06”)
CEILING(num, 
significance)Rounds a number up, away from 0, to the nearest 
specified multiple of significance. 
For data that has been converted from a double or 
float to an integer, displays the smallest integer that 
is greater than or equal to the float or double.CEILING([PortfolioAverage], 1) 
						

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

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

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