Please try this test program:
#include "fivewin.ch"
REQUEST DBFCDX
function Main()
local oCn, cDBF, cTable
cDBF := "MYDATA.DBF"
cDBF := TrueName( cDBF )
CreateDBF( cDBF )
oCn := FW_OpenAdoConnection( cFilePath( cDBF ) )
cTable := "SELECT YEAR,ITEM,AMOUNT FROM " + cFileNoExt( cDBF ) + " WHERE YEAR >= 2015"
XBROWSER FW_AdoPivotArray( oCn, cTable, "ITEM", "YEAR", "AMOUNT" )
return nil
function CreateDBF( cDBF )
local aCols, aData
aCols := { { "YEAR", "N", 4, 0 }, ;
{ "ITEM", "C", 10, 0 }, ;
{ "AMOUNT", "N", 12, 2 } }
aData := { ;
{ 2014, "Materials", 11300 }, ;
{ 2014, "Power", 13799 }, ;
{ 2014, "Wages", 9500 }, ;
{ 2015, "Materials", 12345 }, ;
{ 2015, "Power", 15000 }, ;
{ 2015, "Wages", 10200 }, ;
{ 2016, "Materials", 15000 }, ;
{ 2016, "Power", 16500 }, ;
{ 2016, "Wages", 12300 }, ;
{ 2017, "Materials", 16500 }, ;
{ 2017, "Power", 17750 }, ;
{ 2017, "Wages", 14500 } }
DBCREATE( cDBF, aCols, "DBFCDX", .T., "DTA" )
FW_ArrayToDBF( aData )
XBROWSER "DTA"
CLOSE DTA
return nil
The program creates a raw data table. Years are recorded in a single field "YEAR". This is how the raw data looks.
Now we need to display the data with different years in columns and other details as rows. We also want to select only years from 2016 onwards.
This is done by these lines of code:
oCn := FW_OpenAdoConnection( cFilePath( cDBF ) )
cTable := "SELECT YEAR,ITEM,AMOUNT FROM " + cFileNoExt( cDBF ) + " WHERE YEAR >= 2015"
XBROWSER FW_AdoPivotArray( oCn, cTable, "ITEM", "YEAR", "AMOUNT" )
Result:
