Friday, November 18, 2011

Extending FastXtab (Cross Tab)

What is a Cross Tab?

Cross tabulation is the process of creating a contingency table from the multivariate frequency distribution of statistical variables. Heavily used in survey research, cross tabulations (or crosstabs for short) can be produced by a range of statistical packages, including some that are specialised for the task. Survey weights often need to be incorporated. Unweighted tables can be easily produced by some spreadsheets and other business intelligence tools, where they are commonly known as pivot tables.  - Wiki


FastXTab is another cross tab utility developed and shared to us by Alexander Gorovlev which aims to replace the cross tab utility shipped with VFP (VFPXtab).  Its name lives to its reputation of being faster than VFPXtab.  It can be downloaded here:

Originally I do cross-tabbing (pivoting) inside Excel via automation and it has its own merits but though it offers more features that after the pivot sheet is created that you can still manipulate it, it appears to be slower and of course you have to do a lot of codings to attain it.

Enter FastXTab, as I mentioned a very fast cross tabulating utility.  One thing though that I noticed with it is on the bottom, the author has placed these:

* TODO:    Support for long field names
*        lTotalRows property

And I needed at that time that lTotalRows property.  So since it is not yet there (in the downloadable zip), I took the liberty to add that feature on my end and now decided to create this entry so interested users of FastXTab who has not added or made that property work yet can add it on their end as well.  Here is infused portion for lTotalRows:

Set Talk &cTalkStat
Set Null &cNullStat
Wait Clear

* If rows are totalled
If This.lTotalRows
      Alter Table Alias() Add Column "Total" N(12,2)
      Local lcField, lnTotal, lnloop
            lnTotal = 0
            For lnloop = 2 To Fldcount() - 1
                  lcField = Field(m.lnloop)
                  lnTotal = lnTotal + &lcField
            Replace Total With m.lnTotal In Alias()

If This.lBrowseAfter
      Browse Nowait Normal

Ignore the ones colored in red.  Those are already there.  I just copied it here so you will know where I inserted the lTotalRows feature.

And if you are a user of ssToExcel, then combining FastXTab with that class will allow you to create your report in excel very fast and with good formatting plus column totals as well like this:

1 comment:

  1. Hi Jun

    This code ...
    For lnloop = 2 To Fldcount() - 1
    lcField = Field(m.lnloop)
    lnTotal = lnTotal + &lcField

    should be...
    For lnloop = 2 To Fldcount() - 1
    lnTotal = m.lnTotal + eval(Field(m.lnLoop))