FWH 15.03: Pivot Tables (New feature) - Usage

FWH 15.03: Pivot Tables (New feature) - Usage

Postby nageswaragunupudi » Thu Apr 09, 2015 12:15 am

FWH 15.03 offers support to generate pivot tables with a single function call. Viewing of pivot table is again a simple one xbrowse statement..

Excel Example:
Transactions data:
Image

Excel Pivot Table from this data.
Image
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  Expand view

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
 

Image
oBrw:InvertPivot() inverts the pivot table view:

Syntax of Pivot function:
Code: Select all  Expand view

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
 


Creating XBrowse also easy.
Code: Select all  Expand view

@ r, c XBROWSE oBrw [SIZE w,h] PIXEL OF oWnd DATASOURCE aPivot
 

We need not, rather should not, specify any clauses like COLUMNS, HEADERS, group headers, footer totals or group totals. XBrowse recognizes Pivot Array and takes care of columns, grouping, totalling, etc.. What we may specify are picture formats, colors, lines, etc. only.
The code can be as brief as this:
Code: Select all  Expand view

   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
 

We can see the sample code in fwh\samples\pivotdbf.prg and pivotado.prg.
Screen-shot from pivotdbf.prg.
Image

Clicking the button toggles the pivot view.
Image

More complex usage, using sql query as the source and expressions for columns and rows:
Code: Select all  Expand view

   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() }
 

Image
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10620
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: FWH 15.03: Pivot Tables (New feature) - Usage

Postby Adolfo » Thu Apr 09, 2015 3:29 pm

F****** Excelent.

Thanks a lot for your work
;-) Ji,ji,ji... buena la cosa... "all you need is code"

http://www.xdata.cl - Desarrollo Inteligente
----------
Asus TUF F15, 32GB Ram, 2 * 1 TB NVME M.2, GTX 1650
User avatar
Adolfo
 
Posts: 860
Joined: Tue Oct 11, 2005 11:57 am
Location: Chile

Re: FWH 15.03: Pivot Tables (New feature) - Usage

Postby HunterEC » Thu Apr 09, 2015 5:40 pm

Rao:

Antonio & you are the dream team. You guys are geniuses !!!! Great job !!!!
HunterEC
 
Posts: 723
Joined: Tue Sep 04, 2007 8:45 am

Re: FWH 15.03: Pivot Tables (New feature) - Usage

Postby joseluisysturiz » Fri Apr 10, 2015 4:21 pm

HunterEC wrote:Rao:

Antonio & you are the dream team. You guys are geniuses !!!! Great job !!!!


...y donde dejas a Daniel entre tantos...? :wink: saludos... :shock:
Dios no está muerto...

Gracias a mi Dios ante todo!
User avatar
joseluisysturiz
 
Posts: 2064
Joined: Fri Jan 06, 2006 9:28 pm
Location: Guatire - Caracas - Venezuela

Re: FWH 15.03: Pivot Tables (New feature) - Usage

Postby FranciscoA » Fri Apr 10, 2015 4:48 pm

joseluisysturiz wrote:...y donde dejas a Daniel entre tantos...? :wink: saludos... :shock:

+1
Francisco J. Alegría P.
Chinandega, Nicaragua.

Fwxh-MySql-TMySql
User avatar
FranciscoA
 
Posts: 2158
Joined: Fri Jul 18, 2008 1:24 am
Location: Chinandega, Nicaragua, C.A.

Re: FWH 15.03: Pivot Tables (New feature) - Usage

Postby artu01 » Fri Dec 04, 2020 6:03 am

nageswaragunupudi wrote:We can see the sample code in fwh\samples\pivotdbf.prg and pivotado.prg.
Screen-shot from pivotdbf.prg.
Image

Mr. Nages help me please
I can't run pivotado.prg, i get this error
Image



i took the sample and changed the string of conection. I work with ms-sql and fwh 17.12
Code: Select all  Expand view

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


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

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

REQUEST DBFCDX

static oCn

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

function Main()

   local aPivot

   msgRun( "Connecting to Sever...........", "PLEASE WAIT .......", ;
           { || oCn := AbreConexBD() } )       
       

   if oCn == nil
      ? "Connect Fail"
      return nil
   endif
   msgRun( "Creating Test TAble.............", "PLEASE WAIT .......", { || CreateSampleTable() } )
   ? "Start"
   aPivot   := FW_AdoPivotArray( oCn,  "PVTDATA", "REGION", "PRODUCT", "SALES", "SUM" ) // "SUM" optional/default
   BrowsePivot( aPivot )

return (0)

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

static function BrowsePivot( aPivot, cColFld )

   local oDlg, oFont, oBrw, aHead, oBtn
   local lInverted   := .f.

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

   DEFINE DIALOG oDlg SIZE 980,300 PIXEL FONT oFont ;
      TITLE "PIVOT TABLE"

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

   WITH OBJECT oBrw
      :bRClicked  := { || oBrw:InvertPivot() }
      :CreateFromCode()
   END

   @ 08,10 BTNBMP oBtn ;
      PROMPT { || "Change to: " + If( lInverted, "REGION\PRODUCT", "PRODUCT\REGION" ) } ;
      PIXEL OF oDlg FLAT CENTER ;
      ACTION ( oBrw:InvertPivot(), lInverted := ! lInverted, oBrw:SetFocus() )
   WITH OBJECT oBtn
      :lBoxSelect := .f.
      :nClrText   := { |l| If( l, CLR_YELLOW, CLR_BLACK ) }
      :bClrGrad   := { |l| If( l, { { 1, CLR_GREEN, CLR_GREEN } }, { { 1, oDlg:nClrPane, oDlg:nClrPane } } ) }
   END

   ACTIVATE DIALOG oDlg CENTERED
   RELEASE FONT oFont

return nil

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

static function CreateSampleTable()

   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", "PRINTER", "UPS" }
   local n, oRs, aData := {}

   if .NOT. FW_AdoTableExists( "PVTDATA" , oCn )
      FWAdoCreateTable( "PVTDATA", aCols, oCn )
      oRs   := FW_OpenRecordSet( oCn, "PVTDATA", 4 )
      for n := 1 to 400
         oRs:AddNew( { "REGION", "PRODUCT", "SALES" }, ;
            { aRegions[  HB_RandomInt( 1, 4 ) ], aProducts[ HB_RandomInt( 1, 6 ) ], ;
              HB_Random( 1000, 9999 ) } )

         if n % 100 == 0
            oRs:UpdateBatch()
         endif
      next
      oRs:Close()
   endif

return nil

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

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

//----------------------------------------------------------------------------//
Function AbreConexBD()
  LOCAL cCString, oError, oCon1

  xSOURCE   := "PYSASERVER"                // sql server name
  xPASSWORD := "Pysa123456"
  xPROVIDER := "SQLOLEDB"                  // oledb provider
  xCATALOG  := "PysaBD"                    // sql server database
  xUSERID   := "sa"
  xConnect  := 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Initial Catalog='+xCATALOG+';User Id='+xUSERID+';Password='+xPASSWORD

  TRY
    oCon1 := CreateObject( "ADODB.Connection" )
    oCon1:Open( xConnect )
  CATCH oError
     MsgStop( oError:Description )
  END

Return oCon1
 
fwh 17.12, harbour 3.2.0, pelles C, bcc7, Ms-Sql
artu01
 
Posts: 400
Joined: Fri May 11, 2007 8:20 pm
Location: Lima

Re: FWH 15.03: Pivot Tables (New feature) - Usage

Postby nageswaragunupudi » Sun Dec 06, 2020 10:11 pm

We are looking into this.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10620
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: FWH 15.03: Pivot Tables (New feature) - Usage

Postby nageswaragunupudi » Mon Dec 07, 2020 5:16 pm

In the sample program "pivotado.prg":

Please locate this line of code:
Code: Select all  Expand view
  aPivot   := FW_AdoPivotArray( oCn,  "PVTDATA", "REGION", "PRODUCT", "SALES", "SUM" ) // "SUM" optional/default
 


Please change this line as:
Code: Select all  Expand view
  aPivot   := FW_AdoPivotArray( oCn,  "SELECT * FROM PVTDATA", "REGION", "PRODUCT", "SALES", "SUM" ) // "SUM" optional/default
 


Also, please use FW_OpenAdoConnection() for opening the ado connection. This enables the library to know the correct syntax for constructing SQL statements.

Please change the Main() function like this:
Code: Select all  Expand view

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

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

REQUEST DBFCDX

static ConnSpec   := "MSSQL,PYSASERVER,PysaBD,SA,Pysa123456"
static oCn

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

function Main()

   local aPivot

   msgRun( "Connecting to Sever...........", "PLEASE WAIT .......", ;
           { || oCn := FW_OpenAdoConnection( ConnSpec, .t. ) } )
   if oCn == nil
      ? "Connect Fail"
      return nil
   endif
   msgRun( "Creating Test TAble.............", "PLEASE WAIT .......", { || CreateSampleTable() } )
   ? "Start"
   aPivot   := FW_AdoPivotArray( oCn,  "SELECT * FROM PVTDATA", "REGION", "PRODUCT", "SALES", "SUM" ) // "SUM" optional/default
   BrowsePivot( aPivot )

return (0)

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


Please let us know if it is working with this modification.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10620
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: FWH 15.03: Pivot Tables (New feature) - Usage

Postby artu01 » Wed Dec 09, 2020 2:51 am

Master, thank you for your response but i get the same error
show you the file error.log
Application
===========
Path and name: C:\Programa Contabilidad\ver6\factuhv20\pivotado.exe (32 bits)
Size: 3,827,200 bytes
Compiler version: Harbour 3.2.0dev (r1703231115)
FiveWin version: FWH 17.12
C compiler version: Borland/Embarcadero C++ 7.0 (32-bit)
Windows version: 6.2, Build 9200

Time from start: 0 hours 0 mins 2 secs
Error occurred at: 08-12-2020, 22:06:01
Error description: Error BASE/1004 No exported method: GETROWS
Args:
[ 1] = U

Stack Calls
===========
Called from: => GETROWS( 0 )
Called from: .\source\function\ADOFUNCS.PRG => FW_ADOPIVOTRS( 2232 )
Called from: .\source\function\ADOFUNCS.PRG => FW_ADOPIVOTARRAY( 2187 )
Called from: .\pivotado.PRG => MAIN( 0 )


source adofuncs.prg:
https://anonfiles.com/B304jbx6pa/adofuncs_prg
fwh 17.12, harbour 3.2.0, pelles C, bcc7, Ms-Sql
artu01
 
Posts: 400
Joined: Fri May 11, 2007 8:20 pm
Location: Lima

Re: FWH 15.03: Pivot Tables (New feature) - Usage

Postby nageswaragunupudi » Wed Dec 09, 2020 6:02 am

I checked the adofuncs.prg in version 17.12.

Please replace the three functions in adofuncs.prg

1. function FW_AdoPivotArray()
2. function FW_AdoPivotRS()
3. static function PivotSQL()


with the following:
Code: Select all  Expand view

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

function FW_AdoPivotArray( oCn, cTable, cRowFld, cColFld, cValFld, cAggrFunc )

   local n, oRs, aHead, aPivot

   DEFAULT cAggrFunc    := "SUM"

   oRs      := FW_AdoPivotRS( oCn, cTable, cRowFld, cColFld, cValFld, cAggrFunc )
   oRs:MoveFirst()
   aPivot   := RsGetRows( oRs ) //oRs:GetRows()
   oRs:MoveFirst()
   oRs:Close()

   aHead    := Array( oRs:Fields:Count() )
   for n    := 1 to Len( aHead )
      aHead[ n ]  := oRs:Fields( n - 1 ):Name
      if Left( aHead[ n ], 4 ) == "COL_"
         aHead[ n ]  := SubStr( aHead[ n ], 5 )
      endif
   next

   if ( n := At( " AS ", cColFld ) ) > 0
      cColFld  := AllTrim( SubStr( cColFld, n + 4  ) )
   endif

   AIns( aPivot, 1, aHead,  .t. )  // Make 1st row the Header Row
   aPivot[ 1, 1 ] := "PIVOT:" + aPivot[ 1, 1 ] + ':' + cColFld
   // The above enables XBrowse to detect that the array is Pivot Array

return aPivot

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

function FW_AdoPivotRS( oCn, cTable, cRowFld, cColFld, cValFld, cAggrFunc )

   local oRs, aCols, n
   local lUseCase
   local cSql

   DEFAULT cAggrFunc    := "SUM"

   lUseCase := ! FW_RDBMSName( oCn ) $ "DBASE,MSACCESS"

   if ( n := At( " AS ", cColFld ) ) > 0
      cColFld     := Left( cColFld, n + 3 ) + ;
                     FW_QuotedColSQL( AllTrim( SubStr( cColFld, n + 4 ) ) )
   else
      cColFld     := FW_QuotedColSQL( cColFld )
   endif


   // Get Column Names

   if Upper( Left( cTable, 7 ) ) == "SELECT "
      cTable   := "( " + cTable + " )"
   endif

   cSql     := "SELECT DISTINCT " + cColFld + " FROM " + cTable + " DST"
   oRs      := FW_OpenRecordSet( oCn, cSql )
   aCols    := RsGetRows( oRs ) //oRs:GetRows()
   oRs:Close()
   oRs      := nil
   aCols    := ArrTranspose( aCols )[ 1 ]
   AEval( aCols, { |n,i| If( ValType( n ) == 'N' .and. Int( n ) == n, aCols[ i ] := Int( n ), nil ) } )

   oRs      := FW_OpenRecordSet( oCn, PivotSQL( oCn, cTable, cRowFld, cColFld, cValFld, cAggrFunc, aCols ) )

return oRs

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

static function PivotSQL( oCn, cTable, cRowFld, cColFld, cValFld, cAggrFunc, aColNames )

   local cSql, cCol, lUseCase, cRdbms, n

   cRdbms    := FW_RDBMSName( oCn )
   lUseCase := !  cRdbms $ "DBASE,MSACCESS"

   if ( n := At( " AS ", cRowFld ) ) > 0
      cRowFld     := Left( cRowFld, n + 3 ) + ;
                     FW_QuotedColSQL( AllTrim( SubStr( cRowFld, n + 4 ) ) )
   else
      cRowFld     := FW_QuotedColSQL( cRowFld )
   endif

   if ( n := At( " AS ", cColFld ) ) > 0
      cColFld  := Trim( Left( cColFld, n - 1 ) )
   endif

   cSql  := "SELECT " + cRowFld
   for each cCol in aColNames
      if lUseCase
         cSql  += ", " + cAggrFunc + "( CASE WHEN " + cColFld + " = " + FW_ValToSQL( cCol ) + " THEN " + cValFld + " ELSE 0 END ) AS " + ;
            If( ValType( cCol ) == 'C', FW_QuotedColSQL( cCol ), CharRem( "-/.", "COL_" + cValToChar( cCol ) ) )
      else
         cSql  += ", " + cAggrFunc + "( IIF( " + cColFld + " = " + FW_ValToSQL( cCol ) + ", " + cValFld + ", 0 ) ) AS " + ;
            If( ValType( cCol ) == 'C', FW_QuotedColSQL( If( Empty( cCol ), "OTH", cCol ) ), CharRem( "-/.", "COL_" + cValToChar( cCol ) ) )
      endif
   next

   cSql += " FROM " + cTable + " PVTTBL GROUP BY " + ;
      If( ( n := At( " AS ", Upper( cRowFld ) ) ) > 0, Left( cRowFld, n - 1 ), cRowFld )

return cSql

//----------------------------------------------------------------------------//
 
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10620
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: FWH 15.03: Pivot Tables (New feature) - Usage

Postby artu01 » Wed Dec 09, 2020 4:48 pm

Mr. Rao
i have this error when compiliting

Image

i don't have that method into adofuncs.prg
Code: Select all  Expand view

function FW_AdoPivotArray( oCn, cTable, cRowFld, cColFld, cValFld, cAggrFunc )

   local n, oRs, aHead, aPivot

   DEFAULT cAggrFunc    := "SUM"

   oRs      := FW_AdoPivotRS( oCn, cTable, cRowFld, cColFld, cValFld, cAggrFunc )
   oRs:MoveFirst()
   aPivot   := RsGetRows( oRs ) //oRs:GetRows()
   oRs:MoveFirst()
   oRs:Close()

   aHead    := Array( oRs:Fields:Count() )
   for n    := 1 to Len( aHead )
      aHead[ n ]  := oRs:Fields( n - 1 ):Name
      if Left( aHead[ n ], 4 ) == "COL_"
         aHead[ n ]  := SubStr( aHead[ n ], 5 )
      endif
   next

   if ( n := At( " AS ", cColFld ) ) > 0
      cColFld  := AllTrim( SubStr( cColFld, n + 4  ) )
   endif

   AIns( aPivot, 1, aHead,  .t. )  // Make 1st row the Header Row
   aPivot[ 1, 1 ] := "PIVOT:" + aPivot[ 1, 1 ] + ':' + cColFld
   // The above enables XBrowse to detect that the array is Pivot Array

return aPivot
 
fwh 17.12, harbour 3.2.0, pelles C, bcc7, Ms-Sql
artu01
 
Posts: 400
Joined: Fri May 11, 2007 8:20 pm
Location: Lima

Re: FWH 15.03: Pivot Tables (New feature) - Usage

Postby nageswaragunupudi » Wed Dec 09, 2020 6:23 pm

If your Harbour build is earlier to (r1801...) then you can use
oRs:GetRows()
instead of RsGetRows( oRs )
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10620
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: FWH 15.03: Pivot Tables (New feature) - Usage

Postby artu01 » Fri Dec 11, 2020 2:42 am

thank you Master Rao!, the program already run
fwh 17.12, harbour 3.2.0, pelles C, bcc7, Ms-Sql
artu01
 
Posts: 400
Joined: Fri May 11, 2007 8:20 pm
Location: Lima

Re: FWH 15.03: Pivot Tables (New feature) - Usage

Postby artu01 » Sun Dec 13, 2020 2:34 am

Master Rao
Could you do an sample of pivot tables in excel?

From this link: viewtopic.php?f=6&t=11697&p=57640&hilit=pivot#p57640
gets this code some old but can understand how it works
Code: Select all  Expand view

FUNCTION main()
********
LOCAL oExcel, oHoja, oPivot, cFoglio, oWorkbook, oError, I
LOCAL cFile := HB_curdrive() + ":\" + CurDir() + "\" + "dati_ven.dbf"
LOCAL aDati := {  { CTOD( "
01/01/2008" ), "CATEGORIA 1", 125  },;
                  { CTOD( "
05/01/2008" ), "CATEGORIA 2", 132  },;
                  { CTOD( "
07/01/2008" ), "CATEGORIA 1", 321  },;
                  { CTOD( "
12/01/2008" ), "CATEGORIA 3", 456  },;
                  { CTOD( "
21/01/2008" ), "CATEGORIA 1", 654  },;
                  { CTOD( "
24/01/2008" ), "CATEGORIA 1", 350  },;
                  { CTOD( "
25/01/2008" ), "CATEGORIA 2", 425  },;
                  { CTOD( "
26/01/2008" ), "CATEGORIA 1", 310  },;
                  { CTOD( "
27/01/2008" ), "CATEGORIA 1", 789  } }
     

    IF !FILE( cFile )
      DBCREATE( cFile, {;
              { "
MOV_DAT",   "D",  8, 0 },;
              { "
CATEGORIA", "C", 15, 0 },;
              { "
VENDUTO"  , "N", 15, 2 } })
      SELECT 0
      USE (cFile) NEW ALIAS "
vendite"
      AEVAL( aDati, {|x| vendite->( dbAppend() ), i := 1, AEVAL( x, {|z| vendite->( fieldput( i++, z ) )  } )  } )
      vendite->( dbCloseArea() )
    ENDIF

    TRY

      oExcel := TOleAuto():New( "
Excel.Application" )
   
      // Excel not available
      if Ole2TxtError() != "
S_OK"
        MsgStop("
Excel non disponibile!" )
        BREAK
      endif
   
      CursorWait()
     
      oExcel:Visible := .F.
     
      oExcel:WorkBooks:Open( cFile )
      oWorkBook := oExcel:ActiveWorkBook

      oPivot := oWorkBook:PivotCaches:Add( 1, "
Database" )

      oPivot:CreatePivotTable( "
", "Tabella_pivot1", 1 )
     

      oExcel:Sheets:Select(1)
      oHoja := oExcel:Get( "
ActiveSheet" )
 
     
      oPivot := oHoja:PivotTables("
Tabella_pivot1"):PivotFields("MOV_DAT")
      oPivot:Orientation := 1 // xlRowField
      oPivot:Position := 1
     

      oPivot := oHoja:PivotTables("
Tabella_pivot1"):PivotFields("CATEGORIA")
      oPivot:Orientation := 1 // xlRowField
      oPivot:Position := 1 // 1
     
      oPivot := oHoja:PivotTables("
Tabella_pivot1"):PivotFields("VENDUTO")
      oPivot:Orientation := 4 // xlDataField
      oPivot:Position := 1    

      oExcel:Sheets(1):Select()
      oHoja := oExcel:Get( "
ActiveSheet" )
      oHoja:Range("
G1"):Select()

      oHoja:PivotTables("
Tabella_pivot1"):Format(3)  // xlReport4

      /* per nascondere elenco di commandi e lista campi    
      oExcel:CommandBars("
PivotTable"):Visible := .F.
      oWorkbook:ShowPivotTableFieldList := .F. // ActiveWorkbook.ShowPivotTableFieldList = False
      */
     
      oExcel:Sheets(1):Select() //   LEFT( RIGHT( cFile, 12 ), 8 )
      oHoja := oExcel:Get( "
ActiveSheet" )

      // per cancellare il foglio con i dati.
      /*    
      cFoglio := LEFT( RIGHT( cFile, 12 ), 8 )
      cFoglio := STRTRAN( cFoglio, "
\", "" )
      oExcel:DisplayAlerts := .F.
      oWorkBook:WorkSheets( cFoglio ):delete()
      oExcel:DisplayAlerts := .T.
      */
 
   
    CATCH oError
   
      MsgStop( oError:Operation+CRLF+oError:Description, APP_NAME )
   
    END TRY
     
         
    oExcel:Visible := .T.
   
    CursorArrow()

RETURN NIL


this is the result:
Image

this other code got from this link: viewtopic.php?f=6&t=35767&p=236766&hilit=tabla+dinamica#p236766
it's newer but can't understand how it works
Code: Select all  Expand view

STATIC FUNCTION TablaDinamica()
LOCAL Conn, cSql

Conn := "OLEDB;Provider=SQLOLEDB;Data Source=SERVER\SQLEXPRESS;Initial Catalog=VIKING_SYSTEM;User Id='user';Password='admin';"
cSql := "SELECT * FROM dbo.PRUEBA"    

// "prueba" es una vista construida específicamente con lo que quiero mostrar en la tabla dinámica con campos calculados y con nombres entendibles para el usuario de excel

ExcelDinamicConstructorSql( Conn, cSql )

return nil

FUNCTION ExcelDinamicConstructorSql( cConnStr, cQuery )
 LOCAL oExcel, oWorkbook, oPivotCache, xWin, oTargetRange, oTargetSheet, oPivot

  oExcel    :=    CreateObject( "excel.application" )
                   oExcel:DisplayAlerts      := .F.
                   oExcel:ScreenUpdating     := .F.
 
                  oWorkbook       := oExcel:Workbooks:Add()
                  oTargetSheet    := oWorkbook:Get( 'ActiveSheet' )
                  oTargetRange    := oTargetSheet:range("A4")
                 
                                     oTargetSheet:Cells:Font:Name := "Roboto Cn"
                                     oTargetSheet:Cells:Font:Size := 12
                                     oTargetSheet:Name            := "Tabla Dinámica"

                  oPivotCache     := oWorkbook:PivotCaches:Add(2)

                                     oPivotCache:Connection  := cConnStr
                                     oPivotCache:Commandtext := cQuery

                                     oPivotCache:CreatePivotTable( oTargetRange, "Tabla Dinámica Pruebas" )


      oTargetSheet:Cells( 4, 1 ):Select()
      xWin                      := oExcel:ActiveWindow
      oExcel:Visible            := .T.
      oExcel:DisplayAlerts      := .T.
      oExcel:ScreenUpdating     := .T.

      ShowWindow( oExcel:hWnd, 3 )
      BringWindowToTop( oExcel:hWnd )

 RETURN NIL
 
fwh 17.12, harbour 3.2.0, pelles C, bcc7, Ms-Sql
artu01
 
Posts: 400
Joined: Fri May 11, 2007 8:20 pm
Location: Lima

FWH 15.03: Pivot Tables (New feature) - Usage

Postby artu01 » Thu Dec 17, 2020 8:55 pm

Mr. Rao :
some help about pivot tables in excel?, please
fwh 17.12, harbour 3.2.0, pelles C, bcc7, Ms-Sql
artu01
 
Posts: 400
Joined: Fri May 11, 2007 8:20 pm
Location: Lima


Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: Google [Bot] and 48 guests