Excel Example:
Transactions data:
Excel Pivot Table from this data.
With FWH, we can have the transaction data in either DBF or any SQL table. Assuming we have the same data in c:\\fwh\\samples\\pvtdata.dbf, this is the code to generate pivot table:
Code: Select all
oCn := FW_OpenAdoConnection( "c:\\fwh\\samples\\" )
aPivot := FW_AdoPivotArray( oCn, "PVTDATA.DBF", "REGION", "PRODUCT", "SALES", "SUM" ) // Extract Pivot data in array
XBROWSER aPivot SETUP oBrw:bRClicked := { |r,c,f,o| o:InvertPivot() } // View Pivot Array
oBrw:InvertPivot() inverts the pivot table view:
Syntax of Pivot function:
Code: Select all
FW_AdoPivotArray( oCn, // Ado Connection Object
cTable, // Table Name or Sql Query
cRowFld, // Row Field Name or Expression
cColFld, // Column Field Name of Expression
cValFld, // Value Field Name
[AggrFunc] // Aggregate function "SUM","AVG","COUNT", etc. Default is "SUM"
) --> PivotArray
Code: Select all
@ r, c XBROWSE oBrw [SIZE w,h] PIXEL OF oWnd DATASOURCE aPivot
The code can be as brief as this:
Code: Select all
DEFINE DIALOG oDlg SIZE 980,300 PIXEL FONT oFont TITLE "PIVOT TABLE"
@ 30,10 XBROWSE oBrw SIZE -10,-10 PIXEL OF oDlg DATASOURCE aPivot ;
CELL LINES FOOTERS NOBORDER
oBrw:CreateFromCode()
@ 08,10 BUTTON "Invert Pivot" SIZE 60,12 PIXEL OF oDlg ACTION oBrw:InvertPivot()
ACTIVATE DIALOG oDlg CENTERED
Screen-shot from pivotdbf.prg.
Clicking the button toggles the pivot view.
More complex usage, using sql query as the source and expressions for columns and rows:
Code: Select all
oCn := FW_OpenAdoConnection( "c:\\fwh\\samples\\" )
cSql := "( SELECT S.NAME,C.AGE,C.SALARY FROM CUSTOMER C LEFT JOIN STATES S ON C.STATE = S.CODE WHERE C.STATE LIKE 'A%' )"
apivot := FW_AdoPivotArray( oCn, cSql, "NAME AS ST", "INT(AGE/10)*10 AS AGEGROUP", "SALARY", "SUM" )
XBROWSER aPivot SETUP oBrw:bRClicked := { |r,c,f,o| o:InvertPivot() }
Ji,ji,ji... buena la cosa... "all you need is code"
