Cross Tabs

Command Overview (Requires version 1.32)

The xt command is used to prepare cross tabulations of data. The command supports four sources of data for testing:

Data file in tab separated value format
WorkSheet
Selected range within a worksheet
Database (e.g. MS-Access, MySQL, etc)

Syntax

The command is entered on the command line as the command name ("xtab", or just "xt") followed by one or more command parameters. Each command parameter consists of an argument name, immediately followed by an equal sign ("=") and then immediately followed by a value. The value may be enclosed with quotation marks, and must be enclosed by quotation marks if the value consists of any embedded spaces.

An example of a command parameter to specify that the file name to be processed is test.txt is entered as "file=test.txt" (without the quotations). Each argument on the command line must consist of only valid parameter names. If a required parameter name is missing, then the command line will be colored yellow, an error message will be displayed on the Excel status bar and an audible tone will be sounded.

The following is a listing of all parameter values for the XT command:

Argument name	Description	Example
Common Parameters (Required for all)
col1	the first column to be analyzed	Gender
col2	the second column to be analyzed	Race
amount	a numeric value to be tabulated in addition to counts
recap	the worksheet where the results are to be stored	$Results
File Data Source (ds=file)
file	the name of the file to be processed	\test\invoice.txt
Worksheet Data Source (ds=rng)
sheet	the name of the worksheet to be processed	LU
ulc	upper left hand corner of the range to be processed	a2
Worksheet Selected Range (ds=sel)
sheet	the name of the worksheet to be processed	LU
Database (ds=db)
sql	the SQL query to obtain the data to be used as a source	"select number, luhn10 from lu "
conn	the connection string for the database (varies by type of database)	"c:\ms\data\lu.mdb"

An example

The command to be perform a cross tabulation between type and gender on the file named \program files\ezs\xlac\xtab.txt , storing the results on the worksheet named "xtab type gender " is as follows:

xt ds=file file="c:\program files\ezs\xlac\data\xtab.txt" col1=type col2=gender amount=amount recap="xtab type gender"

xt ds=rng sheet=xtab col1=type col2=gender amount=amount recap="xtab type gender" ulc=a1

xt ds=sel col1=type col2=gender amount=amount recap="xtab type gender"

xt ds=db conn="c:\program files\ezs\xlac\data\test.mdb" col1=0 col2=1 amount=2 recap="xtab type gender" sql="select gender, type, amount from xtab"

Output from the processing will be a cross tabulation of counts for the variables specified

The command bar to process an xt command is as follows:

Output from the command is stored on a worksheet, an example of which is shown below:

Main Form

Complete Auditor resources
Links \| Privacy Policy \| FAQ \| Downloads \| Trademarks