A replacement for VFPXTab.prg that creates cross-tab tables and cursors
Switch branches/tags
Nothing to show
Clone or download
Permalink
Failed to load latest commit information.
fastxtab 1.6 Initial commit Mar 19, 2018
fastxtab6 1.6 Initial commit Mar 19, 2018
README.md Added link to Villi's profile. Mar 20, 2018

README.md

FastXTab

This project is an upgrade to the FastXTab class, originally created by Alexander Golovlev, by Vilhelm-Ion Praisach. The original class can be downloaded from http://www.universalthread.com/ViewPageNewDownload.aspx?ID=9944.

FastXTab is a replacement for VFPXTab which comes with VFP. It expects to find a table or cursor with at least three columns. By default, the data in the first column becomes the rows in the result, the data in the second column becomes the columns in the result, and the data in the third column is aggregated (summed, by default) to form the data in the result. VFPXTab is quite slow and has limited capabilities. FastXTab is much faster and has a lot more functionality.

All of the source code is containing in FastXTab.prg. There are two folders containing source code and samples:

  • FastXTab 1.6 is for VFP 9
  • FastXTabs6 1.6 is for VFP 6.

New properties

See the Properties section below for a complete list of properties.

  • nAvePrec: precision when using AVE function (DEFAULT=3) The data type is Double precision
  • cPageField: allow specifying the field used for page by using either a column name, either an expression
  • cRowField: allow specifying the field used for rows by using either a column name, either an expression
  • nRowField2: allow distribution by specifiyng cRowField (and cPageField if needed) when nRowField2=0
  • cColField: allow specifying the field used for columns by using either a column name, either an expression
  • cDataField: allow specifying the field used for cells by using a column name
  • nFunctionType: aggregate function 1 Sum 2 Count 3 Avg 4 Min 5 Max 6 Custom (DEFAULT=1 for numeric fields and DEFAULT=5 for nonnumeric fields)
  • cFunctionExp: expression when nFunctionType=6
  • cCondition: WHERE condition
  • cHaving: HAVING condition
  • nMultiDataField: if nMultiDataField > 1 the for each column can be defined more DataField / FunctionType / FunctionExp
  • anDataField[1],anFunctionType[1],acFunctionExp[1],acDataField[1]: equivalent properties for nDataField, nFunctionType, cFunctionExp, cDataField when nMultiDataField > 1

New behavior

  • When EMPTY(cRowField) and nRowField=0 the pivot only distribute the values by columns, according to cDataField, nFunctionType, cFunctionExp and cColField; (values for nFunctionType<> 6 are ignored)
  • permission for aggregation functions on non-numeric fields (1 for Sum and 3 for Avg are ignored, Max is by default)

Resulting cursor data types:

  1. When EMPTY(cRowField) and nRowField=0 (distribution by columns):

    • same with the field type when nFunctionType<>6
    • taken from results when nFunctionType=6
  2. When !EMPTY(cRowField) or nRowField<>0:

    • Integer when nFunctionType=2 (COUNT)
    • Double precision when nFunctionType=3 (AVERAGE); decimal precision given by nAvePrec property
    • taken from results when nFunctionType=6 or nFunctionType=1 (to avoid data overflow)
    • same with the field type in rest

Other upgrades

  • improved mdot
  • added local variables declaration
  • SYS(2015) for internal cursors name

Some examples:

In the Test form in the sample Crosstab project, the Foxite1 method shows solutions for a few recent threads on Foxite. Other examples are posted as comment in the Click method of the cmdFastXtab command button.

  1. For http://www.foxite.com/archives/sql-help-0000401315.htm:

    oXTab.cRowField='cstcode'
    oXtab.cColField = 'subj'
    oXtab.nMultiDataField=3
    oXtab.acDataField[1] = 'subj'
    oXtab.anFunctionType[1] = 2
    oXtab.anFunctionType[2] = 6
    oXtab.acFunctionExp[2]="SUM(IIF(attend='P',1,0))"
    oXtab.anFunctionType[3] = 6
    oXtab.acFunctionExp[3]="SUM(IIF(attend='P',1,0))/COUNT(attend)*100"
  2. For http://www.foxite.com/archives/row-to-column-0000401353.htm:

    oXtab.nRowField = 0 
    oXtab.cRowField = ''
    oXtab.cColField='ids'
    oXtab.cDataField ='qty'
  3. For http://www.foxite.com/archives/split-numbers-2-0000400387.htm:

    oXtab.nRowField = 0 
    oXtab.cRowField = ''
    oXtab.cColField='floor(no/10)+1'
    oXtab.cDataField ='no'
  4. For http://www.foxite.com/archives/split-numbers-2-0000400495.htm:

    oXtab.nRowField = 0 
    oXtab.cRowField = ''
    oXtab.cColField='floor(no/100000)+1'
    oXtab.cDataField ='no'

Properties

Property Description
Input cursor / table
lCloseTable .T. the cursor / table which holds the data source is closed
Output cursor / table
lCursorOnly .T. The result is stored in a cursor, otherwise in a free table
cOutFile Name of the cursor / table which holds the result
lDisplayNulls .T. / .F. => Set null ON / OFF
lBrowseAfter Specifies whether to open a Browse window on the cross tab output
CrossTab: a. Rows
cRowField Field name / Field expression for rows (group)
nRowField Field position (row number in AFIELDS(,cSource)) for rows (group)
cPageField Field name / Field expression for rows supergroup (optional)
nPageField Field position (row number in AFIELDS(,cSource)) for rows supergroup
b. Columns
cColField Field name / Field expression for columns (group)
nColField Field position (row number in AFIELDS(,cSource)) for columns (group)
c. Each column field holds a single data (cell) column
cDataField Field name for cells
nDataField Field position (row number in AFIELDS(,cSource)) for cells
nFunctionType Aggregate function used for cells: 1 = Sum, 2 = Count, 3 = Avg, 4 = Min, 5 = Max, 6 = Custom
cFunctionExp The expression used for cells when nFunctionType=6 (ignored if nFunctionType<>6)
d. Some columns contains more than a single data (cell) column
nMultiDataField Number of data (cell) columns (default=1)
acDataField Array with field names for cells
anDataField Array with field positions (row number in AFIELDS(,cSource)) for cells
anFunctionType Array with aggregate functions used for cells: 1 = Sum, 2 = Count, 3 = Avg, 4 = Min, 5 = Max, 6 = Custom
acFunctionExp Array with the expressions used for cells when anFunctionType()=6
e. Miscellaneous
nAvePrec Decimal precision when nFunctionType = 3 (average)
cCondition Expression for a where condition
cHaving Expression for a having condition
nRowField2 When nRowField2 = 0 and !empty(cRowField), FastXTab distribute cells by columns and rows (according to cRowField and cColField). Ignored when nRowField2 <> 0 or empty(This.cRowField)
lTotalRows When .T. a supplementary row with totals is added

Notes

There are three type of outputs:

  1. When nRowField2 = 0 and !empty(cRowField), FastXTab distributes cells by columns and rows (according to cRowField and cColField); no aggregate functions are performed. If nFunctionType / anFunctionType = 6, cells contains the expression from cFunctionExp / acFunctionExp. Otherwise, cells contains the field from cDataField.

  2. When nRowField = 0 and EMPTY(cRowField), FastXTab distributes cells by columns (according to cColField); no aggregate functions are performed. If nFunctionType / anFunctionType = 6, cells contains the expression from cFunctionExp / acFunctionExp. Otherwise, cells contains the field from cDataField.

  3. Otherwise FastXTab applies aggregate functions and distributes results by columns and rows (according to cPageField, cRowField and cColField).

    • If nFunctionType / anFunctionType = 1, cells contains SUM(cDataField).
    • If nFunctionType / anFunctionType = 2, cells contains COUNT(cDataField).
    • If nFunctionType / anFunctionType = 3, cells contains AVERAGE(cDataField).
    • If nFunctionType / anFunctionType = 4, cells contains MAX(cDataField)
    • If nFunctionType / anFunctionType = 5, cells contains MIN(cDataField)
    • If nFunctionType / anFunctionType = 6, cells contains the expression from cFunctionExp / acFunctionExp (must be valid expression from the point of the aggregation)