>>This is the coolest technique since sliced bread.
>>
>>I have been creating very useful reports of accounting data as Excel sheets that use the Excel autofilter feature to present and total ad-hoc filtered views of data. Importing the accouning data and creating the spreadsheets is done directly from VFP in a straightforward, totally automatic and relatively fast way. When viewing the ad-hoc filtered views Excel runs super fast as usual. Seeing different cuts of the data one right after the other lets you get a good feel for the data.
>>
>>For the next step, I want to add the capability to drill-down into the data. The records are going to be there, just not visible until the user requests to see them. I think the Excel outline of rows feature will be the right tool, with calculated subtotals of hidden rows and then grand totals tha only sum detail data and not the intermediate subtotals. Cool.
>>
>>Suggestions are welcome.
>>
>>Alex
>
>If you have found it cool than you probably would like pivot:) It takes some time to see the power (simple pivot doesn't show it really) but once you do it is cooler I think. For example one of the features I like about pivot is that I can 'send' 200-300K rows to Excel sheet (and it may be old versions that had only 64K rows per sheet) under a second to get practically "cross tabbed" data. If you wonder where did a value come from you simply "expand details" and you get the rows that caused that aggregated value. Here is a sample:
>
>

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

>PS: Moving fields to different positions (ie: exchange yearOrdered with salesman or add to salesman or move to first position etc) show different summaries. Also simplest pivot show a single "data" item (the numbers) while you could add multiple calculations (like you can show sum, count, frequency, percentage of an item instead of just quantity sold). Click a number and + from toolbar:)
>
>Cetin

Thank you very much Cetin. Sounds very powerful. I will try it.

BTW, in the method I am so excited about I first prepare a cursor and then create the workbook (or add a worksheet) and pass it the data using a Cursor2Excel method based on code you gave me. Thank you for that. I am literally "Standing on the shoulders of giants".

Alex