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:

Tabular 

 Compact

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:

into...........