>TEXT to m.lcSQL noshow >Select c.cust_id, c.company, ; > o.order_id, o.order_date, ; > e.First_Name-(' '+e.Last_Name) As Employee, ; > oi.line_no, p.prod_name,; > oi.unit_price, oi.quantity; > FROM testdata!customer c ; > INNER Join testdata!orders o; > ON c.cust_id = o.cust_id ; > INNER Join testdata!Employee e ; > ON o.emp_id = e.emp_id ; > INNER Join testdata!orditems oi; > ON o.order_id = oi.order_id ; > INNER Join testdata!products p ; > ON oi.product_id = p.product_id ; > order By p.prod_name >ENDTEXT >m.lcSQL1 = Chrtran(m.lcSQL,';'+Chr(13)+Chr(10),'')+; > ' where c.cust_id < "C"' >m.lcSQL2 = Chrtran(m.lcSQL,';'+Chr(13)+Chr(10),'')+; > ' where c.cust_id >= "C"' > > >lcConnPath = _samples+'data' > >Alines(laRowFields,'prod_name') >Alines(laColumnFields,'Employee') >Alines(laPageFields,'cust_id') > >#include xlConstants.h >oExcel = Createobject('Excel.Application') >With oExcel > .Workbooks.Add > .Visible = .T. > With .ActiveWorkbook > oRange1 = .ActiveSheet.Range('A3') > oRange2 = .WorkSheets('Sheet2').Range('A3') > > With .PivotCaches.Add(xlExternal) > .Connection = "OLEDB;Provider=VFPOLEDB;Data Source="+m.lcConnPath > .CommandType = xlCmdSQL > .CommandText = m.lcSQL1 > .CreatePivotTable(m.oRange1, "myPivTable") > .CreatePivotTable(m.oRange2, "myPivTable2") > Endwith > With .ActiveSheet.PivotTables("myPivTable") > .AddFields(@laRowFields, @laColumnFields, @laPageFields) > .PivotFields('Quantity').Orientation = xlDataField > Endwith > > Alines(laRowFields,'cust_id') > Alines(laColumnFields,'Employee') > > With .WorkSheets('Sheet2').PivotTables("myPivTable2") > .AddFields(@laRowFields, @laColumnFields) > .PivotFields('Quantity').Orientation = xlDataField > Endwith > Endwith > Wait Window Timeout 10 > ?? Chr(7)+Chr(7) && Changing data in cache > With .ActiveWorkbook.PivotCaches(1) > .CommandText = m.lcSQL2 > .Refresh > Endwith >Endwith >In this sample there is a single cache and 2 pivots (sheet 1 and 2) and 2 SQLs. 2nd kicks in after a 10 seconds delay in background (why? I don't know, just wrote for sampling refreshing the cache and nobody was changing the data so I did by changing the SQL).