Want pivot table class

[dbmanfwh]

Any,

I need class that can make pivot table by specifying field electively.

Want help if have someone.

[Antonio Linares]

Moon,

What is it a pivot table ?

Could you provide an example ? thanks

[dbmanfwh]

Dear Antonio,

I am working with DBF File.
I wish to can make pivottable rapidly like this.
http://courses.oreillyschool.com/dba1/dba110.html

Thank you.

[Antonio Linares]

Moon,

You can use FWH function ArrTranspose()

You can review an example of its use in FWH\source\classes\database.prg

[nageswaragunupudi]

Do you want for dbf or MySql?

[dbmanfwh]

Dear Rao,

for dbf.
When there are a lot of fields to dbf,
Should like to form automatically if specify two specification field.

[nageswaragunupudi]

There are several ways to construct pivot data.
I provide one sample

Code: Select all | Expand

/*
*
*   PivotDBF.PRG
*   Author: G.N.Rao, India
*   Mar 09-2015 07:11 PM
*
*/

#include "FiveWin.Ch"
#include "ord.ch"
#include "xbrowse.ch"

//----------------------------------------------------------------------------//

REQUEST DBFCDX

//----------------------------------------------------------------------------//

function Main()

   local aPivot

   CreateSampleDBF()
   aPivot   := ReadPivotTable( "PVTDATA.DBF", "REGION", "PRODUCT", "SALES" )
   BrowsePivot( AClone( aPivot ), "PRODUCT" )
   // Invert Pivot
   aPivot   := ArrTranspose( aPivot )
   aPivot[ 1, 1 ] := "PRODUCT"
   BrowsePivot( AClone( aPivot ), "REGION" )

return (0)

//----------------------------------------------------------------------------//

init procedure PrgInit

    SET DATE ITALIAN
    SET CENTURY ON
    SET TIME FORMAT TO "HH:MM:SS"
    SET EPOCH TO YEAR(DATE())-50

    SET DELETED ON
    SET EXCLUSIVE OFF

    RDDSETDEFAULT( "DBFCDX" )

    XbrNumFormat( 'A', .t. )
    SetKinetic( .f. )
    SetGetColorFocus()
    SetBalloon( .t. )

return

//----------------------------------------------------------------------------//

static function BrowsePivot( aPivot, cColFld )

   local oDlg, oFont, oBrw, aHead

   aHead       := aPivot[ 1 ]
   ADel( aPivot, 1, .t. )

   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14

   DEFINE DIALOG oDlg SIZE 760,250 PIXEL FONT oFont ;
      TITLE "PIVOT TABLE"

   @ 10,10 XBROWSE oBrw SIZE -10,-10 PIXEL OF oDlg ;
      DATASOURCE aPivot AUTOCOLS HEADERS aHead ;
      CELL LINES FOOTERS NOBORDER

   WITH OBJECT oBrw
      :SetGroupHeader( cColFld, 2, Len( aHead ) )
      :SetGroupTotal( cColFld, "TOTAL" )
      AEval( :aCols, { |o| o:nFooterType := AGGR_SUM }, 2 )
      :nStretchCol   := 1
      :MakeTotals()
      :CreateFromCode()
   END

   ACTIVATE DIALOG oDlg CENTERED
   RELEASE FONT oFont

return nil

//----------------------------------------------------------------------------//

static function ReadPivotTable( cDbf, cRowFld, cColFld, cValFld )

   local cPath, oCn, oRs, cSql, cCol
   local aCols    := {}
   local aPivot   := {}
   local lUseCase

   cDbf     := TrueName( cDbf )
   cPath    := cFilePath( cDbf )
   cDbf     := cFileNoExt( cDbf )

   oCn      := FW_OpenAdoConnection( cPath, .t. )
   lUseCase := ! FW_RDBMSName( oCn ) $ "DBASE,MSACCESS"

   // Get Column Names
   oRs      := FW_OpenRecordSet( oCn, "SELECT DISTINCT " + cColFld + " FROM " + cDbf )
   aCols    := oRs:GetRows()
   oRs:Close()
   oRs      := nil
   aCols    := ArrTranspose( aCols )[ 1 ]

   // Prepare Pivot Table SQL
   cSql  := "SELECT " + cRowFld 
   for each cCol in aCols
      if lUseCase
         cSql  += ", SUM( CASE WHEN " + cColFld + " = '" + cCol + "' THEN " + cValFld + " ELSE 0 END ) AS " + cCol
      else
         cSql  += ", SUM( IIF( " + cColFld + " = '" + cCol + "', " + cValFld + ", 0 ) ) AS " + cCol
      endif
   next

   cSql += " FROM " + cDbf + " GROUP BY " + cRowFld
   // ready

   oRs      := FW_OpenRecordSet( oCn, cSql )
   aPivot   := oRs:GetRows()
   oRs:MoveFirst()
   oRs:Close()
   oCn:Close()

   AIns( aCols, 1, cRowFld, .t. )  // First column header of first column
   AIns( aPivot, 1, aCols,  .t. )  // Make 1st row the Header Row

return aPivot

//----------------------------------------------------------------------------//

static function CreateSampleDBF()

   local aCols    := { ;
      { "REGION",    'C',  10,   0  }, ;
      { "PRODUCT",   'C',  10,   0  }, ;
      { "SALES",     'N',  14,   2  }  }

   local aRegions    := { "NORTH", "EAST", "WEST", "SOUTH" }
   local aProducts   := { "DESKTOP", "LAPTOP", "TABLET", "MOBILE" }
   local n, aData := {}

   DBCREATE( "PVTDATA.DBF", aCols, "DBFCDX", .t., "DAT" )
   for n := 1 to 10000
      DAT->( DBAPPEND() )
      DAT->( FieldPut( 1, aRegions[  HB_RandomInt( 1, 4 ) ] ) )
      DAT->( FieldPut( 2, aProducts[ HB_RandomInt( 1, 4 ) ] ) )
      DAT->( FieldPut( 3, HB_Random( 1000, 9999 ) ) )
   next n

   CLOSE DAT

return nil

//----------------------------------------------------------------------------//
 

[dbmanfwh]

Dear Rao,

Thank you.
Your solution is always wonderful.

[nageswaragunupudi]

We added pivot table support in 15.03.

Now it is as simple as:

Code: Select all | Expand

   local cDbf := "PVTDATA.DBF"
   local oCn

   oCn   := FW_OpenAdoConnection( cFilePath( TrueName( cDbf ) ) )
   cDbf  := cFileNoExt( cDbf )


   XBROWSER FW_AdoPivotArray( oCn, cDbf, "REGION", "PRODUCT", "SALES", "SUM" )
 

oBrw:InvertPivot() inverts the pivot and displays