Konu: Excel pivot ornek
Visual Fox Pro
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