Pivot ornek

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, od.order_date as dateOrdered

  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'

 

lcPageList = 'Company'

lcRowList = 'ProductName,DateOrdered'

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')

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")

      If Empty(lcPageList)

        .AddFields(@laRowFields, @laColFields)

      Else

        .AddFields(@laRowFields, @laColFields, @laPageFields)

      Endif

      .PivotFields(lcDataField).Orientation = xlDataField

      With .PivotFields('Sum of '+m.lcDataField)

        .Caption  = m.lcCaption

        .Function = m.lnFunction

      Endwith

      For ix = 1 To Alen(laRowFields)

        With .PivotFields(laRowFields[ix])

          .Subtotals(1) = .T. && To turn of subtotals

          .Subtotals(1) = .F.

        Endwith

      Endfor

      .Mergelabels = .T.

    Endwith

    .UsedRange.Columns.AutoFit

  Endwith

  If Type('.ActiveWorkbook.ShowPivotTableFieldList') = 'L'

    .ActiveWorkbook.ShowPivotTableFieldList = .F.

  Endif

Endwith