ssExcelPivot class Enhancements

Changelog as of December 20, 2012:


Added two validations in ssExcelPivot, one to check if you have used a RecordSource or not, another to check if there is a record on that RecordSource; before allowing the class to proceed, again per request of MK Sharma.  However, the suggested usage for the class is something like this:

SELECT ....FROM.. INTO CURSOR whatever NOFILTER
IF _Tally > 0
   This._RecordSource = 'whatever'
   * And a lot more settings
   DODEFAULT()
Else
   Messagebox('Sorry, no data to process!,0+64,'Opppssss!')
Endif 

============


I am currently relieving our Fleet Manager  while he is on holiday so I am stuck here in our head office and is not going around in the meantime.  And because of this, I was able to focus a bit on my BizCore ERP app and can think of some more things to do for ssClasses especially on ssExcelPivot class to make our reporting lives easier.  And as I touch things when I needed those, I touch these now because I needed them.  Here are some new properties to give users more power and flexibility in generating their pivot reports:

_papersize = this is an integer value to select paper size, default is 1 or letter size.  When you use an invalid integer value, the class will default your pivot report back to letter size
This._papersize = 9  && A4 Size

_orientation = 1 portrait (default), 2 = landscape
This._orientation = 2

_withfooter = to show or not (default) a footer of Page 1 of x that is centered.  It auto-adjusts the bottom margin when this is set to .T.
this._withfooter = .T.

Margins.  I now gave users the ability to specify the margins in cm as well.  There are four corners of a page which are top, left, right, and bottom.  All have default values of 1 cm.  In addition to that, since I added the capability to show a footer or not as seen above, when _withfooter property is set to .T., the bottom margin will auto-adjust by .25 cm to give way for the footer.
this._margintop = .25
this._marginleft = .25
this._marginright = .25
this._marginbottom = .25

FootNotes.  I created 3 possible footnotes and the proper delimiters should be followed if you want to use these features.  There are two delimiters I use which is a pipe sign to break the words and then an  underscore to break again the words with what you want to place there and on which column.  So this:

This._footnote1 = 'Prepared By:_A|Checked By:_E'

Will be treated as Prepared By word to be placed on Column A and Checked By word to be placed on Column E.

This._footnote1 = 'Prepared By:_A|Checked By:_E'

This._footnote2 = Upper(goMyApp.LoginFullName)+'_A|'+Upper(Alltrim(signajunk.signatory))+'_E'

This._footnote3 = goMyApp.LoginPosition+'_A|'+Alltrim(signajunk.xposition)+'_E'

These will result to this:

The decision to make it like that is to give developers more flexibility on placing those footnotes in your pivot report.  FootNote 1 will be placed 2 rows after the last row of the report.  FootNote 2 will be placed 3 rows after footnote1 and finally FootNote3 will be placed just under footnote2. It is up to you how many words you want to add as footnotes.

_FreezeRow .  While in the past, the class auto calculates where the headers will be and freezes rows after that, I created this property now to make it more flexible.  From now on, you will decide whether to freeze a row or not.  Default value is 0 or no freezing.  Any positive integer values will be treated as the row you want to freeze.  So:

this._FreezeRow = 8

Will freeze rows 1 to 7.

_columnAwidth = Since I am using autofit on columns, sometimes Column A will be wider than what we need or want.  To counter that effect, I added this property now where you can override that autofit happening on Column A.
This._columnAwidth = 15

_TitleRows = This is for repeating rows at top so every page will have the proper headings.
This._TileRows = '$1:$7'

That proper value should be followed because that is what Excel uses.  It has to start with a dollar sign, then the starting row followed by colon, another dollar sign and finally the last row.

_OnPreview = logical value whether to start the pivot report on preview or not.  Default value is .F.

_zoom = report scaling, default is 100%.
This._zoom = 80  && zoom it to 80%

_showStuff = Yesterday I am trying to trace some things and so I wanted to see how things are done during automation.  Since some may want this, I added it.  Default value is .F. or do not show what is going on and just show the report when everything is done.  Giving this a value of .T. will allow the end users to see things as they happen.

Well there you are, the new features of ssExcelPivot class.  That is the good news.  The bad news is those are only available at the moment via codes.  Creating pivot report on the ply via drag and drop do not have those yet.  Maybe when I have more time, I will add those as well.