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


> 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

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