* Pivot Table - Flight Delays * Click Event * Lou Shepherd (08/14/2007 Presentation) WAIT WINDOW NOWAIT "Building Pivot Table and Pivot Chart..." WITH Thisform Which = .optWhich.Value ShowCounts = .chkCounts.Value ShowMinutes = .chkMinutes.Value StartTime = .txtFrom.Value EndTime = .txtTo.Value ENDWITH IF NOT ShowCounts AND NOT ShowMinutes MESSAGEBOX("You must check Counts or Minutes", 16, "Invalid Entry") RETURN .F. ENDIF DataPath = "c:\afug\delays\fltdelays.dbf" ODBC = .F. #DEFINE xlDatabase 1 #DEFINE xlAverage -4106 #DEFINE xlSum -4157 #DEFINE xlRowField 1 #DEFINE xlColumnField 2 #DEFINE xlPageField 3 #DEFINE xlDataField 4 #DEFINE xlPercentOfTotal 3 #DEFINE xlNormal -4143 CREATE TABLE fltdelays FREE ; (opdate d, cust_id c(4), fltno c(6), aircraft c(6), org c(4), dst c(4), m i,; delaycode c(4), delaydesc c(15) NULL, nc i, ndc i, dc i, status c(12), flts i, ontime i) IF Which = 1 && Departure SELECT f.fltdate as opdate, f.cust_id, f.fltno, f.aircraft, f.org, f.dst,; f.dptdelaymin1 as min1, f.dptdelaycode1 as code1, d1.desc as desc1, ; f.dptdelaymin2 as min2, f.dptdelaycode2 as code2, d2.desc as desc2, ; f.dptdelaymin3 as min3, f.dptdelaycode3 as code3, d3.desc as desc3, ; IIF(d1.type = 3, 1, 0) as nc1, IIF(d1.type = 2, 1, 0) as ndc1, IIF(d1.type = 1, 1, 0) as dc1,; IIF(d1.type = 3, "Non Ctrl", IIF(d1.type = 2, "Non-Dir Ctrl", IIF(d1.type = 1, "Dir Ctrl", "On Time "))) as status1, ; IIF(d2.type = 3, 1, 0) as nc2, IIF(d2.type = 2, 1, 0) as ndc2, IIF(d2.type = 1, 1, 0) as dc2,; IIF(d2.type = 3, "Non Ctrl", IIF(d2.type = 2, "Non-Dir Ctrl", IIF(d2.type = 1, "Dir Ctrl", "On Time "))) as status2, ; IIF(d3.type = 3, 1, 0) as nc3, IIF(d3.type = 2, 1, 0) as ndc3, IIF(d3.type = 1, 1, 0) as dc3,; IIF(d3.type = 3, "Non Ctrl", IIF(d3.type = 2, "Non-Dir Ctrl", IIF(d3.type = 1, "Dir Ctrl", "On Time "))) as status3, ; 1 as flts, IIF(f.dptdelaymin1 + f.dptdelaymin2 + f.dptdelaymin3 < 1, 1, 0) as ontime; FROM flights f; LEFT OUTER JOIN dlycodes d1 ON d1.code = f.dptdelaycode1; LEFT OUTER JOIN dlycodes d2 ON d2.code = f.dptdelaycode2; LEFT OUTER JOIN dlycodes d3 ON d3.code = f.dptdelaycode3; WHERE BETWEEN(f.out, starttime, endtime); AND org <> dst; INTO CURSOR fdlys ELSE && Arrival SELECT f.fltdate as opdate, f.cust_id, f.fltno, f.aircraft, f.org, f.dst,; f.arrdelaymin1 as min1, f.arrdelaycode1 as code1, d1.desc as desc1, ; f.arrdelaymin2 as min2, f.arrdelaycode2 as code2, d2.desc as desc2, ; f.arrdelaymin3 as min3, f.arrdelaycode3 as code3, d3.desc as desc3, ; IIF(d1.type = 3, 1, 0) as nc1, IIF(d1.type = 2, 1, 0) as ndc1, IIF(d1.type = 1, 1, 0) as dc1,; IIF(d1.type = 3, "Non Ctrl", IIF(d1.type = 2, "Non-Dir Ctrl", IIF(d1.type = 1, "Dir Ctrl", "On Time "))) as status1, ; IIF(d2.type = 3, 1, 0) as nc2, IIF(d2.type = 2, 1, 0) as ndc2, IIF(d2.type = 1, 1, 0) as dc2,; IIF(d2.type = 3, "Non Ctrl", IIF(d2.type = 2, "Non-Dir Ctrl", IIF(d2.type = 1, "Dir Ctrl", "On Time "))) as status2, ; IIF(d3.type = 3, 1, 0) as nc3, IIF(d3.type = 2, 1, 0) as ndc3, IIF(d3.type = 1, 1, 0) as dc3,; IIF(d3.type = 3, "Non Ctrl", IIF(d3.type = 2, "Non-Dir Ctrl", IIF(d3.type = 1, "Dir Ctrl", "On Time "))) as status3, ; 1 as flts, IIF(f.arrdelaymin1 + f.arrdelaymin2 + f.arrdelaymin3 < 1, 1, 0) as ontime; FROM flights f; LEFT OUTER JOIN dlycodes d1 ON d1.code = f.arrdelaycode1; LEFT OUTER JOIN dlycodes d2 ON d2.code = f.arrdelaycode2; LEFT OUTER JOIN dlycodes d3 ON d3.code = f.arrdelaycode3; WHERE BETWEEN(f.in, starttime, endtime); AND org <> dst; INTO CURSOR fdlys ENDIF SELECT fdlys SCAN inserted = .F. FOR i = 1 TO 3 z = ALLTRIM(STR(i)) SCATTER MEMVAR m.m = fdlys.min&z m.delaycode = fdlys.code&z m.delaydesc = fdlys.desc&z m.nc = fdlys.nc&z m.ndc = fdlys.ndc&z m.dc = fdlys.dc&z m.status = fdlys.status&z IF EMPTY(fdlys.code&z) IF NOT inserted INSERT INTO fltdelays FROM MEMVAR ENDIF ELSE INSERT INTO fltdelays FROM MEMVAR ENDIF inserted = .T. ENDFOR ENDSCAN USE IN fltdelays *======================================== oExcel = CREATEOBJECT("Excel.Application") oWorkbook = oExcel.Workbooks.Add(1) oTargetSheet = oWorkbook.Sheets[1] oTargetSheet.Name = "Pivot Table" *---------------------- * Make the Report Title *---------------------- WITH oTargetSheet .Range["A1"].Value = "Flight Delay Analysis" IF Which = 1 .Range["A2"].Value = "Departure" ELSE .Range["A2"].Value = "Arrival" ENDIF .Range["A1:A3"].Font.Size = 12 .Range["A1:A3"].Font.Bold = .T. ENDWITH *--------------------------------------------- * Set the upper left corner of the pivot table *--------------------------------------------- oTargetRange = oTargetSheet.Range("A8") IF ODBC DIMENSION aSource[2] aSource[1] = "Driver={Microsoft Visual FoxPro Driver};UID=;SourceType=DBF;SourceDB=" + DataPath aSource[2] = "select * from " + DataPath oPivotTable = oExcel.Sheets[1].PivotTableWizard(2, @aSource, oTargetRange, "PivotTable", .T., .T.) ELSE * Create a pivot cache object oPivotCache = oWorkbook.PivotCaches.Add(2) oPivotCache.Connection = "OLEDB;Provider=vfpoledb.1;data source=" + DataPath oPivotCache.Commandtext = "select * from " + dataPath oPivotTable = oPivotCache.CreatePivotTable(oTargetRange, "PivotTable") ENDIF WITH oPivotTable .PivotFields["OpDate"].Orientation = xlRowField .PivotFields["Opdate"].Name = "Date" .PivotFields["Status"].Orientation = xlColumnField .PivotFields["status"].Name = "Status" .PivotFields["DelayCode"].Orientation = xlPageField .PivotFields["DelayCode"].Name = "Delay Code" .PivotFields["Cust_id"].Orientation = xlPageField .PivotFields["cust_id"].Name = "Customer" .PivotFields["DelayDesc"].Orientation = xlPageField .PivotFields["DelayDesc"].Name = "Reason" IF ShowCounts .PivotFields["Date"].Orientation = xlDataField .PivotFields["Count of Date"].Name = "Delays" .PivotFields["Delays"].NumberFormat = "###,###" ENDIF IF ShowMinutes .PivotFields["m"].Orientation = xlDataField .PivotFields["Sum of M"].Name = "Minutes" .PivotFields["Minutes"].NumberFormat = "###,###" ENDIF LOCAL ARRAY GroupArray[7] && Seconds, Minutes, Hours, Days, Months, Quarters, Years STORE .T. TO GroupArray[4], GroupArray[5], GroupArray[7] && Days, Months, Years .PivotFields["Date"].LabelRange.Group(.T.,.T.,,@GroupArray) .PivotFields["Months"].Subtotals(1) = .F. .PivotFields["Months"].Subtotals(1) = .T. ENDWITH oTargetSheet.Range("A8").Select oTargetSheet.Columns[2].ColumnWidth = 15.57 oWorkbook.Charts.Add oTargetSheet = oWorkbook.Sheets[1] oTargetSheet.Name = "Pivot Chart" * Move "Pivot Table tab to the first position oWorkBook.Worksheets[1].Move(oWorkBook.ActiveSheet) * Hide the Pivot Table Toolbar oExcel.Application.CommandBars("PivotTable").Visible = .F. * Hide the Pivot Table Field Dialog oWorkBook.ShowPivotTableFieldList = .F. * add ShowCountsNormal Macro oMacroModule = oExcel.Modules.Add() oMacroModule.InsertFile("c:\afug\delays\ShowCountsNormal.txt") oMacroModule.Name = "ShowCountsNormal" * add ShowCountsPercent Macro oMacroModule = oExcel.Modules.Add() oMacroModule.InsertFile("c:\afug\delays\ShowCountsPercent.txt") oMacroModule.Name = "ShowCountsPercent" * add ShowMinutesNormal Macro oMacroModule = oExcel.Modules.Add() oMacroModule.InsertFile("c:\afug\delays\ShowMinutesNormal.txt") oMacroModule.Name = "ShowMinutesNormal" * add ShowMinutesPercent Macro oMacroModule = oExcel.Modules.Add() oMacroModule.InsertFile("c:\afug\delays\ShowMinutesPercent.txt") oMacroModule.Name = "ShowMinutesPercent" oExcel.Application.Visible = .T. WAIT CLEAR *!* oTargetSheet.Range("F6").AddComment("A Comment")