Monday, February 20, 2012

ssExcelPivot class

Summary of what this class can do:

  • Will create a pivot report inside excel or as html for you without needing much effort on your part.  You don't need to know automation nor pivoting to create wonderful reports
  • Gives you possible 84 report appearances on pivot report alone.
  • Gives you possible 84 report appearances on non-pivot report (looks differ than the pivot report output)
  • Allows user interaction or not
  • All you need is just a recordsource (cursor or table) with all the fields you need, declare values of some properties of the class (or not) and you are on the go.  No fuzz, no cuzz, straight mouth approach of report generation.
Properties that can be set to give the report its initial (default) look:

This one is a must:
  • _recordsource- specifies the record source which maybe a cursor or a table. 
 The rest are optional:

  • _filters - character, to set fields to be used on report filtering
  • _columns - character, to set the fields to be used as columns of report
  • _rows - character, to set the fields to be used as rows of the report
  • _values - character, to set the fields to be used as data in the report
  • _headings - character, if you want your report to have headings (just like ssToExcel 1 & 2)
  • _filename - character, if you want to specify a fixed filename other than the randomized filename
  • _foldername - character, if you want to specify other destination instead of  default user's temp folder
  • _fieldlist - logical, to show or hide field lists inside excel
  • _nochange - logical, to leave report as pivot type or non-pivot type (cannot be manipulated anymore)
  • _nopop - logical.  if you don't want to allow user interaction on manipulating the report from within the class before generating it.  If you set this to .T., then the class will appear as a simple button with no popup
  • _theme - character, to set the default style and look of the report
  • _layout - numeric,  also affects the look of report
  • _ntype - numeric, report output type: 1 (default) is excel worksheet, 2 is html
  • _integer - logical, affects data fields.  Default is .F. meaning show decimals, .T. hides decimals
  • _rowstripes - logical, report to have stripes/lines on rows?
  • _colstripes - logical, report to have stripes/lines on columns? 
  • _rowheaders - logical, report to have row headers 
  • _colheaders - logical, report to have column headers
  • _nodrill - logical, allows drilling or not?
Update February 24, 2012:

Gave further abilities per request of a subscriber.  Now you can almost do anything from within the class without resorting to pivoting inside excel (although that feature is still there unless _nochange is set to false).  Here now is the latest:

Effect on report when changing layout:


Update February 23, 2012:

Gave users further control by specifying the calculation type both for rows and values.  For rows, default calculation would be NONE.  To specify a default calculation, you should put a dash after the fieldname and then the number of calculation type which are:
  • 0 (or just plain field name) = No calculation or NONE
  • 1 = Automatic
  • 2 = Sum
  • 3 = Count
  • 4 = Average
  • 5 = Max
  • 6 = Min
Actually there are 6 more calculation type but since I am not using those, I simply ignore it.  However, if you think there is some use on your end you can do those like 7 = Product, 8 = Count Numbers, and so on....

So if you want to specify a calculation on a field, do something like this:

* Date Calculation is None, xCost is Sum
This._rows = 'xdate,xcost-2'
Same goes on Values or Data Field in Pivot Report:

  • 1 = Sum
  • 2 = Count
  • 3 = Average
  • 4 = Max
  • 5 = Min

This._values = 'amount-1' && Data Field Calculation is sum
This._values = 'amount-2' && Data Field Calculation is count
and so on....

Update: February 22, 2012

  • Adjusted the output pivot report to give you better appearances.  It can be any of the possible  84 appearances which I will leave up to you to decide.  ssOptSwitch plus a spinner gives you the power to alter the output.  Shown below are some possible appearances:

    • As seen above, I now allow (like ssToExcel2) the choice for output to be in HTML format so end users cannot change it.  
      Here is again the enhanced class:

      The 'buttons' on the Report Style is ssOptSwitch Theme 7 (new).

      • Added _fieldlist (default value of .F.).  When you set this to .T., the field list section inside excel where users can rearrange the pivot report will be hidden

      • Added _nochange (default value of .F.).  When you set this to .T., users can no longer change the report because it will no longer be a pivot report.  Very useful for a fixed type of report inside excel.

      Update: February 21, 2012

      While these hardcoded instructions are really already there in the below images (#2), maybe those escaped your attention.  Hardcoding is done through these properties:  _filters, _columns, _rows and _values.  Shown below is an image (also showing modified look of the class) showing what happens on first run with harcoded instructions:

      What is really new is the property _nopop.  If you put:

      This._nopop = .T.

      The class will generate the pivot report without further user interaction and the popup form will not appear as if the class is really just a button.

      Another enhancement I made is the data fields now have the needed commas, to make it look more professional.


      Do not know how to create a pivot report in excel?  Do not despair as ssExcelPivot is now here. With ssExcelPivot, all you need to do is have a table or create a cursor as the _RecordSource and a simple drag and drop will give you your pivot report inside excel. 

      Here are more:

      • Allows developers to instruct the class to have its default values for report filter, columns, rows and values
      • Allows user interaction to further manipulate the pivot report based on their desire by dragging fields back and forth from various sections; before generating pivot report inside excel
      • What is more is after it is inside excel, allows the users to manipulate the pivot report further from within excel.
      What it needs:  a cursor or a table
      What it do not need:  a) It does not need that you know automation b) it does not need that you know pivoting.  The class will do both for you:

        So from this initial pivot report from the sample given to subscribers, it can still be further manipulated to give you varying types of pivot reports, from this:

        to this:

        into this:



        1. Hello Jun,
          First of all, thanks for creating this wonderful class.
          However, I need your help, this class work flawlessly until one day I found this error:

          "Error &H80070057 The parameter in incorrect"

          It happen when I tried to open 1 year data - it worked fine at the first time, the pivot popup appear, but when I press the Create Pivot button, it gave me that strange system error.

          Thank you so much for your attention.

          Best regards,


          1. OLE error code 0x80070057: The parameter is incorrect

          2. Since it uses the EXPORT command of VFP, then the maximum number of rows it can accommodate would be 65,536. A year's data may exceed that. If you want to adjust it to have the capability to export more than that, then use Cetin Basoz approach here:

        2. How can you get the title bar to appear and have the Minimize button? Thanks Jun!!

          1. Welcome. Which titlebar Dan?

          2. Jun, Thanks for the response! I figured that out, but I just wanted to stop back in and say thank you and that you're a genius. I wish you worked with my team! You're flipping brilliant!!


        3. Hello Jun

          Actually i work with VFP front end and SQL Server database, i would like to know how i can build my own cursors to use them with your ssExcel as _recordsource.
          Can you give me some help.

          Many thanks for your help.
          Best regards
          Luis santos

        4. Hello Jun

          Please can you reply to my post, also i forget to ask the price of ssExcelPivot and ssToExcel.

          I hope you can answer me.

          Best regards
          Luis Santos

        5. Hello Jun

          Please don´t forget to answer my 2 posts.

          Best regards
          Luis SAntos

          1. Hi, sorry for a very late reply. I seldom visit this blog of mine except when I want to post new things.

            I haven't used SQL SERVER database but from what I know, it is the same as VFP's SQL SELECT.

            So all you need to do is create a cursor and use that as _recordsource, something like this:

            Select ... from yoursource .. joins... where conditions.. order by... group by.. into cursor junk

            This._recordsource = 'junk'

            ssExcelPivot and ssToExcel is part of the now free ssClasses. What is available for subscription now is ssUltinate. And enhanced version of tjose are there (ExcelPivot and ExcelExport classes, among other things)

            Please email me at ss.classes.jun@

          2. This comment has been removed by the author.