ssExcelPivot class is that simple small insignificant looking button as shown below (ssButton4) that can create instant pivot reports in excel with ease. Its power lies with creating these reports with just a few switch, which is what you cannot do with any crosstab utilities or even when designing reports via .frx way. Shown is the sample form given to subscribers, data coming from VFP Samples directory:
It creates 12 sample pivot reports in Excel (in my sample) just to show how things can be easily done. However, you can create more than that, even hundreds of different format, styles, etc. At the very bottom is Create Your Own Report which allows user the ability to create a pivot report in excel of their own choosing.
I said it is easy to use this. So I will show below codes inside the sample form I give to subscribers. First of all, create a cursor to serve as its recordsource:
Select Customer.cust_id,
Customer.company, Customer.contact,;
Customer.city,
Customer.Region, Customer.country,;
orders.order_date,Products.prod_name,;
Orditems.unit_price,
Orditems.quantity, ;
Cast(Orditems.unit_price
* Orditems.quantity As N(12,2)) As amount,;
YEAR(orders.order_date)
As
Year,
Cmonth(orders.order_date)
As
Month;
FROM ;
(Home(2)+"data\customer");
INNER Join (Home(2)+"data\orders")
;
ON Customer.cust_id = orders.cust_id ;
INNER Join (Home(2)+"data\orditems")
;
ON orders.order_id = Orditems.order_id ;
INNER Join (Home(2)+"data\products")
;
ON Products.product_id =
Orditems.product_id;
INTO Cursor junk
After that, all you need to do is design how your pivot report in excel will look like. Shown below are codes and how the output will be:
Local lnIndex
lnIndex = Thisform.list1.ListIndex
With This
._recordsource =
"Junk"
._headings
= "PIVOT REPORT SAMPLES - "+Alltrim(Thisform.list1.Value)+"|"+;
"|Brought
to you by Sandstorm36|Generated by ssExcelPivot Class"
._values
= 'amount'
._filters='company'
._columns=''
._font
= 'Calibri-10'
._rowstripes=.T.
._colstripes
= .T.
._rowheaders
= .F.
._theme
= '2-10'
._layout
= 2
._ntype
= 1
Do Case
Case
m.lnIndex
= 1
._columns
= 'year'
._rows
= 'prod_name-1,order_date'
Case m.lnIndex = 2
._columns = 'year'
._rows = 'prod_name-1'
Case m.lnIndex = 3
._theme = '3-6'
._columns = 'year'
._rows = 'prod_name-1'
Case m.lnIndex = 4
._theme = '1-17'
._font = 'Arial-10'
._columns = 'year'
._rows = 'prod_name-1'
Case m.lnIndex = 5
._layout = 1
._theme = '3-2'
._filters='year'
._columns = 'country'
._rows = 'prod_name-1,Company'
Case m.lnIndex = 6
._theme = '2-9'
._rows = 'Company-1,prod_name-1'
Case m.lnIndex = 7
._columns = 'year'
._rows = 'Country,prod_name'
._layout = 1
._theme = '2-6'
Case m.lnIndex = 8
._filters = 'country'
._columns = 'month'
._theme = '2-14'
._layout = 1
._rowstripes=.F.
._rows = 'year-1,prod_name'
Case m.lnIndex = 9
._filters = ''
._columns = 'year'
._rows = 'month-1'
._nochange =.T.
Case m.lnIndex = 10
._filters = ''
._columns = 'year'
._rows = 'month-1'
._ntype=2
Endcase
If m.lnIndex = 15
._nopop = .F. && this is create your own format
._fieldlist = .T.
Else
._nopop = .T.
._fieldlist = .F.
Endif
Endwith
DoDefault()
And as you have seen, a new report is generated simply by changing values of properties such as _rows, _columns, etc.
Compare that with making reports using the Native VFP report and it evidently involves less work. Want another report aside from those? Then all you need to do is something like this:
Case && new report again
._columns =
._rows =
._Theme =
Presto! An entirely new pivot report. You cannot simply do that with any crosstab utilies or via creating an frx. Those will involve a helluva lot more work for you!
Actually, the purpose of this post is to inform subscribers (and interested parties) about the two more switches I added, i.e.:
1. The ability to have an extra space after each group in Column A:
Case m.lnIndex = 12
._withspace = .T.
._columns = 'year'
._rows = 'Company-1,prod_name'
2. The ability to Merge Labels:
Case m.lnIndex = 13
._withspace = .T.
._mergelabels = .T.
._columns = 'year'
._rows = 'Company-1,prod_name'
Just to spice up more our pivot reports inside excel.
Unless you set _NoChange = .T. or _nType = 2 (HTML), all pivot reports generated by this class has drilldown capabilities:
Further, you can also morph the report on the ply based on what you desire by filtering, dragging rows, adding more rows, changing columns, changing calculation types, etc..... until you get satisfied with the new format. Moreover, you can create charts based on the current pivot report output easily. Or make some adjustments like fitting reports to 1 page, etc. That is because you have the whole excel capabilities under your hands. Again, all of those features are not available with native VFP reports as well as any crosstab utilities out there I knew.
For instance my boss will call me over the phone saying "Hey Jun! Can I have a report of items purchased within these period?" and all I will say is "Yes Sir!". Report is done and emailed within a minute or so if I still have to create an SQL SELECT otherwise it can be done in seconds. Then my boss may again ask, "can we see only purchases of this department?". And all I need to do is employ filtering on departments from within excel report itself then send again another email. Filtering can be done on Filter field, Rows and Columns. You can employ several filters. On his end, if he wants to see any detail, a simple double-click to activate the drill down feature is needed.
While this post is about ssExcelPivot class, I am trying to relay the fact that an Excel Pivot, either done via my class or an excel automation or manually; is really a very powerful report. I hope you see the benefit of this report type as I do. Cheers!