>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