Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Jun Tangunan
  Where is Jun Tangunan?
 Cabanatuan
 Philippines
 Jun Tangunan
 To: Cetin Basoz
  Where is Cetin Basoz?
 Izmir
 Turkey
 Cetin Basoz
 Tags
Subject: RE: Why Pivot Report? Why not Crosstab?
Thread ID: 345009 Message ID: 345087 # Views: 57 # Ratings: 0
Version: Visual FoxPro 9 Category: Microsoft Office Automation
Date: Friday, May 18, 2012 12:20:32 AM         
   


> > A pivot report is among the underrated and unused powerful feature of Excel. So I want to give some lights onto its importance because this is really helpful, not only to the end users, but also to the developers. Please read:
> >
> > sandstorm36.blogspot.com/2012/05/why-pivot-report-why-not-crosstab.html
> >
> >
> > http://sandstorm36.blogspot.com
> > http://weblogs.foxite.com/sandstorm36
>
> Jun,
> I skimmed your blog. Good work.
> Maybe you would like to add one important aspect of doing pivot report there:
> -You can "pump" rows that is beyond excel's row limit to a pivot table's cache in milliseconds/1-2seconds (even sending data to a spreadsheet would take much longer).
> -You can support multiple pivot tables within a singe pivot cache and refresh the data (they call it live dashboard?)
> -You can 'unpivot' a particular data using 'Show Details' to see how and where that data come from.
> -Can add formulas to calculations.
> -Can have more then one aggregation in a single pivot.
>
> Crosstab is a no match in summary:)
>
>
> Here is a sample code:
>
> *!* Excel Pivot sample
> *!*	Author:Cetin Basoz
> TEXT TO m.lcSQL NOSHOW TEXTMERGE PRETEXT 15
> SELECT RTRIM(emp.first_name) + ' ' +RTRIM(emp.last_name) as SalesMan,
> 	cs.company, pr.prod_name as ProductName, oi.quantity, YEAR(od.order_date) as yearOrdered
>   FROM  customer cs
>   INNER JOIN orders od on cs.cust_id = od.cust_id
>   inner join employee emp on od.emp_id = emp.emp_id
>   INNER JOIN orditems oi on od.order_id = oi.order_id
>   INNER JOIN products pr on oi.product_id = pr.product_id
> ENDTEXT
> 
> * Pivot sample
> *#include 'xlConstants.h'
> #define xlCount -4112
> #Define xlSum -4157
> #Define xlDataField 4
> #Define xlExternal 2
> 
> lcPageList = 'Company'
> lcRowList = 'ProductName,YearOrdered'
> lcColList = 'SalesMan'
> lcDataField = 'Quantity'
> 
> Alines(laRowFields,m.lcRowList,.T.,",")
> Alines(laColFields,m.lcColList,.T.,",")
> Alines(laPageFields,m.lcPageList,.T.,",")
> 
> lcOption = 'sum'
> 
> lcFunction = Iif(Lower(Evl(m.lcOption,'')) == "count","COUNT","SUM")
> lcCaption   = Iif(Upper(m.lcFunction)='SUM','Quantity Sold','Count of sold times')
> lnFunction  = Iif(Upper(m.lcFunction)='SUM',xlSum,xlCount)
> 
> lcConnStr = 'Provider=VFPOLEDB;Data Source='+_samples+'Data\Testdata.dbc'
> Local oExcel As 'Excel.Application'
> oExcel = Createobject('Excel.Application')
> With oExcel
>   .Visible = .T.
>   .Workbooks.Add
>   *-- Destination of the pivottable inside Excel
>   .ActiveWorkbook.ActiveSheet.Name = 'Product Sales'
>   With .ActiveWorkbook.ActiveSheet
>     oDestination = .Range('A1')
>     With oExcel.ActiveWorkbook.PivotCaches.Add(xlExternal)
>       .Connection  = 'OLEDB;'+m.lcConnStr
>       .CommandType = 2 && xlCmdSQL
>       .CommandText = m.lcSQL
>       .CreatePivotTable(oDestination, 'PivotTable')
>     Endwith
> 
>     With .PivotTables("PivotTable")
>       Do Case
>         Case !Empty(m.lcRowList) And !Empty(m.lcColList) And !Empty(m.lcPageList)
>           .AddFields(@laRowFields, @laColFields, @laPageFields)
>         Case !Empty(m.lcRowList) And !Empty(m.lcColList)
>           .AddFields(@laRowFields, @laColFields)
>         Case !Empty(m.lcRowList) And !Empty(m.lcPageList)
>           .AddFields(@laRowFields, , @laPageFields)
>         Case !Empty(m.lcRowList)
>           .AddFields(@laRowFields)
>         Case !Empty(m.lcColList) And !Empty(m.lcPageList)
>           .AddFields(, @laColFields, @laPageFields)
>         Case !Empty(m.lcColList)
>           .AddFields(, @laColFields)
>         Case !Empty(m.lcPageList)
>           .AddFields(, , @laPageFields)
>       Endcase
>       .PivotFields(m.lcDataField).Orientation = xlDataField
>       With .Datafields(1)
>         .Caption  = m.lcCaption
>         .Function = m.lnFunction
>       Endwith
>       If !Empty(m.lcRowList)
>         For ix = 1 To Alen(laRowFields)
>           With .PivotFields(laRowFields[ix])
>             .Subtotals(1) = .T. && To turn of subtotals
>             .Subtotals(1) = .F.
>           Endwith
>         Endfor
>         .Mergelabels = .T.
>       Endif
>     Endwith
>     .UsedRange.Columns.AutoFit
>   Endwith
>   If Type('.ActiveWorkbook.ShowPivotTableFieldList') = 'L'
>     .ActiveWorkbook.ShowPivotTableFieldList = .F.
>   Endif
> Endwith
> 

>
>
>

> Cetin Basoz
>
> .Net has got better.Think about moving - check my blog:
> My Blog
> Blog (mirror) - sounds to be down
>

> Support Wikipedia



Yes, those are good points. I will edit the post to add link to this thread so non-members of foxite can read it as well. Another feature that I forgot also to add is the ability to reduce size during printing like fit to page. Since we are using excel to present our reports, then we have in our hands a whole new more arsenals to customize our reports.



http://sandstorm36.blogspot.com
http://weblogs.foxite.com/sandstorm36

ENTIRE THREAD

Why Pivot Report? Why not Crosstab? Posted by Jun Tangunan @ 5/17/2012 8:44:08 AM
RE: Why Pivot Report? Why not Crosstab? Posted by Cetin Basoz @ 5/17/2012 2:12:18 PM
RE: Why Pivot Report? Why not Crosstab? Posted by Jun Tangunan @ 5/18/2012 12:20:32 AM