Adding Multi Dimentional Reporting

[ptrex]

Excel PivotTable Object

Using the PivotTable object you can get a multidimentional view of you data.

This example is based on an Excel file as database, but you can use this against any ADO connection type.

expandcollapse popup

Opt("WinTitleMatchMode", 4) ;1=start, 2=subStr, 3=exact, 4=advanced, -1 to -4=Nocase
Opt("WinSearchChildren", 1) ;0=no, 1=search children also

#include<GUIConstantsEx.au3>
#include<WindowsConstants.au3>
#NoTrayIcon

;Vars
Dim $oMyError

Const $adOpenStatic = 3
Const $adLockOptimistic = 3
Const $adCmdText = 0x0001

Const $WS_EX_NOPARENTNOTIFY = 0x4
Const $WS_EX_NOINHERITLAYOUT = 0x100000

Global $s_Filename=FileGetShortName(@MyDocumentsDir & "\Northwind.xls")
Global $s_Tablename = "[Sheet1$]"

; Initialize error handler
$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

;Declare objects
$oPivotTable1 = ObjCreate("OWC11.PivotTable") ; Default to Office 2003

If not IsObj($oPivotTable1 ) Then
    $oPivotTable1 = ObjCreate("OWC10.PivotTable") ; Office XP   
EndIf
IF not IsObj($oPivotTable1 ) Then
    $oPivotTable1 = ObjCreate("OWC9.PivotTable") ; Office 2000
EndIf

    If IsObj($oPivotTable1) Then
        with $oPivotTable1
            .AllowPropertyToolbox = True
            .DisplayOfficeLogo = False
            .DisplayPropertyToolbox = False
            ;.DisplayTitleBar = True
            .DisplayToolbar = True
            ;.ViewOnlyMode = True
            .DisplayFieldList = True
            .autofit = False
            .Height = 690
            .Width = @DesktopWidth-35
            ;.Maxwidth = 600
            ;.Maxheight = 600
            EndWith
    EndIf

$sConn = (  "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source="&$s_Filename&";" & _
            "Extended Properties=""Excel 8.0;HDR=Yes;"";")


$oPTConstants = $oPivotTable1.Constants
$oPivotTable1.ConnectionString = $sConn

$oPivotTable1.CommandText = "SELECT * FROM " & $s_Tablename & ";"

If IsObj($oPivotTable1) Then
        $oPTView = $oPivotTable1.ActiveView
        ;$oPTView.AutoLayout()

        $fset0 = $oPTView.FieldSets(9)
        $fset1 = $oPTView.FieldSets(1)
        $fset1.Caption = "ID"

        $fset2 = $oPTView.FieldSets(4)
        $fset3 = $oPTView.FieldSets(6)
    
        $oPTView.FilterAxis.InsertFieldSet ($fset0)
        $oPTView.RowAxis.InsertFieldSet ($fset1)
        $oPTView.ColumnAxis.InsertFieldSet ($fset2)
        $oPTView.DataAxis.InsertFieldSet ($fset3)
        
    Else
MsgBox(0,"Reply","Not an Object",4)
EndIf


;Main Gui
$GUI = GuiCreate("Excel PivotTable Object", @DesktopWidth, @DesktopHeight-40,(@DesktopWidth-@DesktopWidth)/2, _
                                            ((@DesktopHeight-40)-(@DesktopHeight-40))/2 , _
                                            BitOR($WS_OVERLAPPEDWINDOW, $WS_VISIBLE, $WS_CLIPCHILDREN))


$GUI_ActiveX = GUICtrlCreateObj ($oPivotTable1, 10, 30 , @DesktopWidth-30 , @DesktopHeight-170)
GUICtrlSetStyle ( $GUI_ActiveX, $WS_VISIBLE )
GUICtrlSetResizing ($GUI_ActiveX,$GUI_DOCKAUTO)     
GuiSetState()

; Get ATL classes
$sATLClass = ""
$aClasses = StringSplit(WinGetClassList($GUI,""),@LF)
For $i = 1 To $aClasses[0]
If StringLeft($aClasses[$i],4) = "ATL:" Then
$sATLClass = $aClasses[$i]
        ConsoleWrite( $aClasses[$i] & @CRLF)
ExitLoop
EndIf
Next

; Get the handles to the controls that must have their styles modified
$hATL = ControlGetHandle($GUI, "", "[CLASS:"&$sATLClass&"]")
    ConsoleWrite("$hATL (should not be 0 or blank):" & $hATL & @crlf)
$hTLB1 = ControlGetHandle($GUI, "", "[CLASS:msvb_lib_toolbar]")
    ConsoleWrite("$hTLB1 (should not be 0 or blank):" & $hTLB1 & @crlf)
$hTLB2 = ControlGetHandle($GUI, "", "[CLASS:OCToolbar11]")
    ConsoleWrite("$hTLB2 (should not be 0 or blank):" & $hTLB2 & @crlf)
$hTLB3 = ControlGetHandle($GUI, "", "[CLASS:Toolbar20WndClass]")
    ConsoleWrite("$hTLB2 (should not be 0 or blank):" & $hTLB3 & @crlf)
$hTLV = ControlGetHandle($GUI, "", "[CLASSNN:CScrollBar111]")
    ConsoleWrite("$hTLH (should not be 0 or blank):" & $hTLV & @crlf)
$hTLH = ControlGetHandle($GUI, "", "[CLASSNN:CScrollBar112]")
    ConsoleWrite("$hTLV (should not be 0 or blank):" & $hTLH & @crlf)
    
;ATL:38D2B9E01
; MSComCtlToolbar111
;   msvb_lib_toolbar1
; MSComCtlImageList111
; CScrollBar111
;WaitTaint111
;OCToolbar111
; Toolbar20WndClass1
;CClipWindow111
;CClipWindow112
;ATL:38D2B4081  

$hATLClassStyle = BitOR($WS_CHILD, $WS_CLIPCHILDREN, $WS_CLIPSIBLINGS, $WS_VISIBLE) ; 0x56080000
$hATLClassStyleEx = 0x0 ;BitOR($WS_EX_NOINHERITLAYOUT, $WS_EX_NOPARENTNOTIFY) ; 0x00100004
$hTLB1ClassStyle = BitOR($WS_CHILD, $WS_CLIPCHILDREN, $WS_CLIPSIBLINGS, $WS_VISIBLE) ; 0x56000000
$hTLB1ClassStyleEx = BitOR($WS_EX_NOINHERITLAYOUT, $WS_EX_NOPARENTNOTIFY) ; 0x00100004
$hTLB2ClassStyle = BitOR($WS_CHILD, $WS_CLIPCHILDREN, $WS_CLIPSIBLINGS, $WS_VISIBLE) ; 0x56000000
$hTLB2ClassStyleEx = BitOR($WS_EX_NOINHERITLAYOUT, $WS_EX_NOPARENTNOTIFY) ; 0x00100004
$hTLB3ClassStyle = BitOR($WS_CHILD, $WS_CLIPCHILDREN, $WS_CLIPSIBLINGS, $WS_VISIBLE) ; 0x56000000
$hTLB3ClassStyleEx = BitOR($WS_EX_NOINHERITLAYOUT, $WS_EX_NOPARENTNOTIFY) ; 0x00100004
$hTLVClassStyle = BitOR($WS_CHILD, $WS_CLIPCHILDREN, $WS_CLIPSIBLINGS, $WS_VISIBLE) ; 0x56000000
$hTLVClassStyleEx = BitOR($WS_EX_NOINHERITLAYOUT, $WS_EX_NOPARENTNOTIFY) ; 0x00100004
$hTLHClassStyle = BitOR($WS_CHILD, $WS_CLIPCHILDREN, $WS_CLIPSIBLINGS, $WS_VISIBLE) ; 0x56000000
$hTLHClassStyleEx = BitOR($WS_EX_NOINHERITLAYOUT, $WS_EX_NOPARENTNOTIFY) ; 0x00100004

_SetStyle($hATL,$hATLClassStyle,$hATLClassStyleEx)
_SetStyle($hTLB1,$hTLB1ClassStyle,$hTLB1ClassStyleEx)
_SetStyle($hTLB2,$hTLB2ClassStyle,$hTLB2ClassStyleEx)
_SetStyle($hTLB3,$hTLB3ClassStyle,$hTLB3ClassStyleEx)
;_SetStyle($hTLV,$hTLHClassStyle,$hTLHClassStyleEx)
;_SetStyle($hTLH,$hTLVClassStyle,$hTLVClassStyleEx)

Func _SetStyle($hwnd,$style,$exstyle)
DllCall("user32.dll", "long", "SetWindowLong", "hwnd", $hwnd, "int", -16, "long", $style)
DllCall("user32.dll", "long", "SetWindowLong", "hwnd", $hwnd, "int", -20, "long", $exstyle)
EndFunc


While 1
    $msg = GuiGetMsg()
    Select
    Case $msg = $GUI_EVENT_CLOSE
        ExitLoop
    EndSelect
WEnd
    $oExcel = ""
Exit



;This is custom error handler
Func MyErrFunc()
$HexNumber=hex($oMyError.number,8)
Msgbox(0,"AutoItCOM Test","We intercepted a COM Error !" & @CRLF & @CRLF & _
             "err.description is: " & @TAB & $oMyError.description & @CRLF & _
             "err.windescription:" & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: " & @TAB & $HexNumber & @CRLF & _
             "err.lastdllerror is: " & @TAB & $oMyError.lastdllerror & @CRLF & _
             "err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF & _
             "err.source is: " & @TAB & $oMyError.source & @CRLF & _
             "err.helpfile is: " & @TAB & $oMyError.helpfile & @CRLF & _
             "err.helpcontext is: " & @TAB & $oMyError.helpcontext _
            )
SetError(1) ; to check for after this function returns
Endfunc

Enjoy !!

Regards

ptrex

Northwind.zip

Edited September 14, 2012 by ptrex

[GreenCan]

Excel PivotTable Object

Using the PivotTable object you can get a multidimentional view of you data.

This example is based on an Excel file as database, but you can use this against any ADO connection type.

Hi ptrex,

This looks interesting. Certainly something to explore further.

I am not a specialist of pivot tables but your script seems more powerful and flexibel than the one embedded in Excel. How can that be?

Multiple selection seems not possible in Excel and moving the columns is far much easier with the script.

Thanks for sharing

Wondering how I can integrate this in my Oracle report...

GreenCan

[ptrex]

@GreenCan

Thanks for the feedback.

If I see some time I will create an Oracle Example (Which I can use myself as well).

Be patient.

Regards

ptrex

[ptrex]

@GreenCan

Updated first post.

Hope this gets you going.

regards,

ptrex

[enaiman]

I wish I were paying more attention to pivot tables; I have never been able to understand them ... maybe later ...

If that happens, I know where to find a good tool

Thank you for sharing it

[ptrex]

@enaiman

Thanks

Give it a try !

Regards,

ptrex

[slayerz]

Well, another great example from ptrex....thanks for sharing

[ptrex]

@slayerz

You're welcome !!

Regards

ptrex

[dmob]

@ptrex,

Great stuff, i have been trying to do this from an example I found on the forum and was not able to get it to work. I will try it out tonight.

Excellent and inspiring work, as always.

[GreenCan]

Hi ptrex,

I tried Oracle pivot with real data and the result is fabulous.

Please continue with this project, this is a very useful piece of script.

You've got my 4 stars.

GreenCan

[ptrex]

@GreenCan

Thansk again for the appreciation, and good to see you have a good use for this.

You Microsoft has so many candy hidden on your system, that it is always hard for me to stay focussed on 1 project.

Most of the time when starting I run into many other interesting stuff, that I have to force mysefl to let go for the time being.

I don't know what to add more on this as it is self explanatory.

The only shortcoming here is that the COM object always jumps in the AU3 Gui.

I need support from the GUI experts to figure out why all COM object seem to jump around when clicking on them ?i

This behaviour has been in AU3 for years using COM objects (Or I don't know how to fix it)

Any help is welcome.

BTW

An other level up the ladder is Offline Database OLAP Cubes. Veeeerry intersting stuff.

MDX (Multidimensional Expression)

Regards,

ptrex

[dmob]

Hi @ptrex

When I run code I get "Invalid Class String" error on line 25

CODE

$oPivotTable1 = ObjCreate("OWC11.PivotTable") ; Default to Office 2003

I use WinXp SP2 and Office 2007

I tried

CODE

$oPivotTable1 = ObjCreate("OWC12.PivotTable") ; Default to Office 2007

did not work. Any help?

[ptrex]

@

This means that you don't have the proper objects installed on your machine.

Go here for Office XP OWC 10

Go here for Office 2003 OWC 11

Regards,

ptrex

[dmob]

thanks, works great now.

[BlueLord]

I have the same error: "Invalid Class String" error on line 25

I use Office 2010. I haven't find the OWC

[kalvin]

Hi ptrex,

I really hope you are still around.

I've been playing with your pivot table example. Great work. I'm trying to mimic a pivot table that I have made manually and not sure where to turn for more information.

In my manual table I am able to click on an item count in a row and colum and it will bring up a new page with all the details from that count.

I guess what I need is where do I find more information on FieldSets? or InsertFieldSet? for piviot tables. I'm new to this and not too sure of the correct terminology to search for more information.

cheers, Kalvin.

[ptrex]

@Kalvin,

After you installed the OWC compontents, you can find all needed help in the helpfiles in your PC.

This would be a good start : "C:Program Files (x86)Common Filesmicrosoft sharedWeb Components111033OWCDPL11.CHM"

Rgds

ptrex

[kalvin]

Thank you for the fast reply ptrex. Found the info. Now need time to read and play/experiment.

best regards, Kalvin