FastXTab version 1.6

A crosstab class. This is an enhanced version of the FastxTab class, created by Alexander Golovlev. The original class can be found here  http://www.universalthread.com/ViewPageNewDownload.aspx?ID=9944  There are many extensions, like formula for every element and multiple cells. Download from : For VFP9 http://www.foxite.com/uploads/c3f8e1bb-a40a-4a21-814d-05c3026f34d6.rar For VFP6 http://www.foxite.com/uploads/ffe853b8-264f-404e-b104-f31f07ec6a42.rar Superb description made by Tamar E. Granor - Generating crosstabs in VFP Properties 1 Input cursor / table - lCloseTable .T. the cursor / table which holds the data source is closed 2 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 3 CrossTab a) Rows - cRowField Field name / Field expression for rows (group) - nRowField Field position (row number in AFIELDS(,cSource)) for rows (group) - cPageField (optional) Field name / Field expression for rows supergroup - 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) When 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 (default=1) Number of data (cell) columns - 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) Miscelaneous - 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 (accoding 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 distribute cells by columns and rows (accoding 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 distribute cells by columns (accoding 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 distribute results by columns and rows (accoding 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 a valid expression from the point of the aggregation) Examples Demo 1 A table with inputs and outputs from some companies for each year. RAND(-1) CREATE CURSOR cTest (cName C(10),nYear I,nInput N(10,2),nOutput N(10,2)) FOR lnC=1 TO 5     FOR lnY=2010 TO 2014         INSERT INTO cTest VALUES ('Company '+TRANSFORM(m.lnC),m.lnY,100000*RAND(),100000*RAND())     NEXT NEXT BROWSE 1 Get inputs Local oXtab oXtab = NewObject("FastXtab", "fastxtab.prg") oXtab.cRowField = 'cName' && rows are company names oXtab.cColField = 'nYear' && columns are years oXtab.cDataField = 'nInput' && cells contains Inputs oXtab.RunXtab() Result is a table with a random name 2  Get inputs and show (browse) the output Local oXtab oXtab = NewObject("FastXtab", "fastxtab.prg") oXtab.cRowField = 'cName' && rows are company names oXtab.cColField = 'nYear' && columns are years oXtab.cDataField = 'nInput' && Inputs oXtab.lBrowseAfter = .T. oXtab.RunXtab() 3 Choose a name for the output table Local oXtab oXtab = NewObject("FastXtab", "fastxtab.prg") oXtab.cRowField = 'cName' && rows are company names oXtab.cColField = 'nYear' && columns are years oXtab.cDataField = 'nInput' && Inputs oXtab.lBrowseAfter = .T. oXtab.cOutFile="xx" && table is named xx oXtab.RunXtab() 4 Output into a cursor rather than a table Local oXtab oXtab = NewObject("FastXtab", "fastxtab.prg") oXtab.cRowField = 'cName' && rows are company names oXtab.cColField = 'nYear' && columns are years oXtab.cDataField = 'nInput' && Inputs oXtab.lBrowseAfter = .T. oXtab.cOutFile="xx" oXtab.lCursorOnly = .T. oXtab.RunXtab() 5 Show null values (if exists) Local oXtab oXtab = NewObject("FastXtab", "fastxtab.prg") oXtab.cRowField = 'cName' && rows are company names oXtab.cColField = 'nYear' && columns are years oXtab.cDataField = 'nInput' && cells contains Inputs oXtab.lBrowseAfter = .T. oXtab.cOutFile="xx" oXtab.lCursorOnly = .T. oXtab.lDisplayNulls = .F. oXtab.RunXtab() 6 Close the input table after run Local oXtab oXtab = NewObject("FastXtab", "fastxtab.prg") oXtab.cRowField = 'cName' && rows are company names oXtab.cColField = 'nYear' && columns are years oXtab.cDataField = 'nInput' && cells contains Inputs oXtab.lBrowseAfter = .T. oXtab.cOutFile="xx" oXtab.lCursorOnly = .T. oXtab.lDisplayNulls = .F. oXtab.lCloseTable = .F. oXtab.RunXtab() 7 Add a supplementary row with total Local oXtab oXtab = NewObject("FastXtab", "fastxtab.prg") oXtab.cRowField = 'cName' && rows are company names oXtab.cColField = 'nYear' && columns are years oXtab.cDataField = 'nInput' && cells contains Inputs oXtab.lBrowseAfter = .T. oXtab.cOutFile="xx" oXtab.lCursorOnly = .T. oXtab.lDisplayNulls = .F. oXtab.lCloseTable = .F. oXtab.lTotalRows = .T. oXtab.RunXtab() 8 Show both nInput and nOutput Local oXtab oXtab = NewObject("FastXtab", "fastxtab.prg") oXtab.cRowField = 'cName' && rows are company names oXtab.cColField = 'nYear' && columns are years oXtab.nMultiDataField = 2 oXtab.acDataField[1] = 'nInput' && cells contains Inputs oXtab.acDataField[2] = 'nOutput' && cells contains Outputs oXtab.lBrowseAfter = .T. oXtab.cOutFile="xx" oXtab.lCursorOnly = .T. oXtab.lDisplayNulls = .F. oXtab.lCloseTable = .F. oXtab.lTotalRows = .T. oXtab.RunXtab() 9 Show three columns : nInput, nOutput and nInput - nOutput Local oXtab oXtab = NewObject("FastXtab", "fastxtab.prg") oXtab.cRowField = 'cName' && rows are company names oXtab.cColField = 'nYear' && columns are years oXtab.nMultiDataField = 3 oXtab.acDataField[1] = 'nInput' && cells contains Inputs oXtab.acDataField[2] = 'nOutput' && cells contains Outputs oXtab.anFunctionType[3] = 6 oXtab.acFunctionExp[3] = 'SUM(nInput - nOutput)' oXtab.lBrowseAfter = .T. oXtab.cOutFile="xx" oXtab.lCursorOnly = .T. oXtab.lDisplayNulls = .F. oXtab.lCloseTable = .F. oXtab.lTotalRows = .T. oXtab.RunXtab() * Note the use of SUM() Demo 2 A table with inputs and outputs from some companies for each month of the each year. Some values are missing RAND(-1) CREATE CURSOR cTest (cName C(10),nYear I,nInput N(10,2),nOutput N(10,2)) FOR lnC=1 TO 5     FOR lnY=2010 TO 2014         INSERT INTO cTest VALUES ('Company '+TRANSFORM(m.lnC),m.lnY,100000*RAND(),100000*RAND())     NEXT NEXT BROWSE 1 Get total / year (inputs) Local oXtab oXtab = NewObject("FastXtab", "fastxtab.prg") oXtab.cRowField = 'cName' && rows are company names oXtab.cColField = 'nYear' && columns are years oXtab.cDataField = 'nInput' && cells contains Inputs oXtab.lBrowseAfter = .T. oXtab.cOutFile="xx" oXtab.lCursorOnly = .T. oXtab.lDisplayNulls = .F. oXtab.lCloseTable = .F. oXtab.lTotalRows = .T. oXtab.RunXtab() 2 Get average / mounth (inputs) Local oXtab oXtab = NewObject("FastXtab", "fastxtab.prg") oXtab.cRowField = 'cName' && rows are company names oXtab.cColField = 'nYear' && columns are years oXtab.cDataField = 'nInput' && cells contains Inputs oXtab.nFunctionType = 3 oXtab.lBrowseAfter = .T. oXtab.cOutFile="xx" oXtab.lCursorOnly = .T. oXtab.lDisplayNulls = .F. oXtab.lCloseTable = .F. oXtab.lTotalRows = .T. oXtab.RunXtab() 3 Count the mounths with data for each company in every year (inputs) Local oXtab oXtab = NewObject("FastXtab", "fastxtab.prg") oXtab.cRowField = 'cName' && rows are company names oXtab.cColField = 'nYear' && columns are years oXtab.cDataField = 'nInput' && cells contains Inputs oXtab.nFunctionType = 2 oXtab.lBrowseAfter = .T. oXtab.cOutFile="xx" oXtab.lCursorOnly = .T. oXtab.lDisplayNulls = .F. oXtab.lCloseTable = .F. oXtab.lTotalRows = .T. oXtab.RunXtab() 4 Get worst (minimum) monthly input value from each year / each company Local oXtab oXtab = NewObject("FastXtab", "fastxtab.prg") oXtab.cRowField = 'cName' && rows are company names oXtab.cColField = 'nYear' && columns are years oXtab.cDataField = 'nInput' && cells contains Inputs oXtab.nFunctionType = 4 oXtab.lBrowseAfter = .T. oXtab.cOutFile="xx" oXtab.lCursorOnly = .T. oXtab.lDisplayNulls = .F. oXtab.lCloseTable = .F. oXtab.lTotalRows = .T. oXtab.RunXtab() 5 Get the best (maximum) monthly input value from each year / each company Local oXtab oXtab = NewObject("FastXtab", "fastxtab.prg") oXtab.cRowField = 'cName' && rows are company names oXtab.cColField = 'nYear' && columns are years oXtab.cDataField = 'nInput' && cells contains Inputs oXtab.nFunctionType = 5 oXtab.lBrowseAfter = .T. oXtab.cOutFile="xx" oXtab.lCursorOnly = .T. oXtab.lDisplayNulls = .F. oXtab.lCloseTable = .F. oXtab.lTotalRows = .T. oXtab.RunXtab() 6 Get total / semester (inputs) Local oXtab oXtab = NewObject("FastXtab", "fastxtab.prg") oXtab.cRowField = 'cName' && rows are company names oXtab.cColField = 'PADL(nYear,4)+[_sem]+iif(nMonth<7,[1],[2])' && columns are semesters oXtab.cDataField = 'nInput' && cells contains Inputs oXtab.lBrowseAfter = .T. oXtab.cOutFile="xx" oXtab.lCursorOnly = .T. oXtab.lDisplayNulls = .F. oXtab.lCloseTable = .F. oXtab.lTotalRows = .T. oXtab.RunXtab() 7 Get quarterly average for nInputs - nOutputs Local oXtab oXtab = NewObject("FastXtab", "fastxtab.prg") oXtab.cRowField = 'cName' && rows are company names oXtab.cColField = 'PADL(nYear , 4) + [_qtr] + PADL(1 + FLOOR((nMonth - 1) / 3) , 1)' && columns are quarters oXtab.nFunctionType = 6 oXtab.cFunctionExp = 'AVG(nInput - nOutput)' oXtab.lBrowseAfter = .T. oXtab.cOutFile="xx" oXtab.lCursorOnly = .T. oXtab.lDisplayNulls = .F. oXtab.lCloseTable = .F. oXtab.lTotalRows = .T. oXtab.RunXtab() 8 Get quarterly average for nInputs - nOutputs, but columns are companies, and rows are quarters Local oXtab oXtab = NewObject("FastXtab", "fastxtab.prg") oXtab.cPageField = 'nYear' && 'pages' are years oXtab.cRowField = '1 + FLOOR((nMonth - 1) / 3)' && rows are quarters oXtab.cColField = 'cName' && columns are company names oXtab.nFunctionType = 6 oXtab.cFunctionExp = 'AVG(nInput - nOutput)' oXtab.lBrowseAfter = .T. oXtab.cOutFile="xx" oXtab.lCursorOnly = .T. oXtab.lDisplayNulls = .F. oXtab.lCloseTable = .F. oXtab.lTotalRows = .T. oXtab.RunXtab() 9 Get quarterly average for nInputs - nOutputs, columns are companies, and rows are quarters but only for quarters with results < 0 Local oXtab oXtab = NewObject("FastXtab", "fastxtab.prg") oXtab.cPageField = 'nYear' && 'pages' are years oXtab.cRowField = '1 + FLOOR((nMonth - 1) / 3)' && rows are quarters oXtab.cColField = 'cName' && columns are company names oXtab.nFunctionType = 6 oXtab.cFunctionExp = 'AVG(nInput - nOutput)' oXtab.cHaving = 'AVG(nInput - nOutput) < 0' && Having oXtab.lBrowseAfter = .T. oXtab.cOutFile="xx" oXtab.lCursorOnly = .T. oXtab.lDisplayNulls = .F. oXtab.lCloseTable = .F. oXtab.lTotalRows = .T. oXtab.RunXtab() 10 Get quarterly average for nInputs - nOutputs, columns are companies, and rows are quarters, for quarters with results < 0 but only for first company Local oXtab oXtab = NewObject("FastXtab", "fastxtab.prg") oXtab.cPageField = 'nYear' && 'pages' are years oXtab.cRowField = '1 + FLOOR((nMonth - 1) / 3)' && rows are quarters oXtab.cColField = 'cName' && columns are company names oXtab.nFunctionType = 6 oXtab.cFunctionExp = 'AVG(nInput - nOutput)' oXtab.cHaving = 'AVG(nInput - nOutput) < 0' && Having oXtab.cCondition = 'cName==[Company 1]'     && Where oXtab.lBrowseAfter = .T. oXtab.cOutFile="xx" oXtab.lCursorOnly = .T. oXtab.lDisplayNulls = .F. oXtab.lCloseTable = .F. oXtab.lTotalRows = .T. oXtab.RunXtab() Demo 3 A table with athletes and their training results RAND(-1) CREATE CURSOR cTest (cName C(10),nResult N(8,2)) FOR lnA=1 TO 5     FOR lnR=1 TO 20         IF RAND()>0.2             INSERT INTO cTest VALUES ('Athlete '+TRANSFORM(m.lnA),100*RAND())         ENDIF     NEXT NEXT BROWSE 1 Group each athlete's results by tens of seconds Local oXtab oXtab = NewObject("FastXtab", "fastxtab.prg") oXtab.cPageField = '' oXtab.cRowField = 'cName' && rows are athletes oXtab.nRowField2 = 0 && distribution oXtab.cColField = 'floor(nResult/10)' && columns are tens results oXtab.cDataField = 'nResult' && cells are results oXtab.lBrowseAfter = .T. oXtab.cOutFile="xx" oXtab.lCursorOnly = .T. oXtab.RunXtab() 2  Group the results by tens of seconds, regardless whose are Local oXtab oXtab = NewObject("FastXtab", "fastxtab.prg") oXtab.cPageField = '' && 'pages' are years oXtab.cRowField = '' && rows are irrelevant oXtab.nRowField = 0 && distribution oXtab.cColField = 'floor(nResult/10)' && columns are tens results oXtab.cDataField = 'nResult' && cells are results oXtab.lBrowseAfter = .T. oXtab.cOutFile="xx" oXtab.lCursorOnly = .T. oXtab.RunXtab() 3 Group each athlete's results by tens of seconds, In each column, only the fraction is shown Local oXtab oXtab = NewObject("FastXtab", "fastxtab.prg") oXtab.cPageField = '' oXtab.cRowField = 'cName' && rows are athletes oXtab.nRowField2 = 0 && distribution oXtab.cColField = 'floor(nResult/10)' && columns are tens results oXtab.nFunctionType = 6 oXtab.cFunctionExp = 'nResult/10 - floor(nResult/10)' oXtab.lBrowseAfter = .T. oXtab.cOutFile="xx" oXtab.lCursorOnly = .T. oXtab.RunXtab()