Sign in to follow this  
Followers 0
ptrex

Adding Multi Dimentional Reporting

18 posts in this topic

#1 ·  Posted (edited)

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.

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 by ptrex

Share this post


Link to post
Share on other sites



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


Contributions

CheckUpdate - SelfUpdating script ------- Self updating script

Dynamic input validation ------------------- Use a Input masks can make your life easier and Validation can be as simple

MsgBox with CountDown ------------------- MsgBox with visual countdown

Display Multiline text cells in ListView ---- Example of pop-up or ToolTip for multiline text items in ListView

Presentation Manager ---------------------- Program to display and refresh different Border-less GUI's on a Display (large screen TV)

USB Drive Tools ------------------------------ Tool to help you with your USB drive management

Input Period udf ------------------------------ GUI for a period input

Excel ColorPicker ---------------------------- Color pickup tool will allow you to select a color from the standard Excel color palette

Excel Chart UDF ----------------------------- Collaboration project with water 

GetDateInString ------------------------------ Find date/time in a string using a date format notation like DD Mon YYYY hh:mm

TaskListAllDetailed --------------------------- List All Scheduled Tasks

Computer Info --------------------------------- A collection of information for helpdesk

Shared memory Demo ----------------------- Demo: Two applications communicate with each other through means of a memory share (using Nomad function, 32bit only)

Universal Date Format Conversion -------- Universal date converter from your PC local date format to any format

Disable Windows DetailsPane -------------- Disable Windows Explorer Details Pane

Oracle SQL Report Generator -------------  Oracle Report generator using SQL

SQLite Report Generator -------------------  SQLite Report generator using SQL

SQLite ListView and BLOB demo ---------- Demo: shows how binary (image) objects can be recognized natively in a database BLOB field

DSN-Less Database connection demo --- Demo: ActiveX Data Objects DSN-Less Database access

Animated animals ----------------------------- Fun: Moving animated objects

Perforated image in GUI --------------------- Fun: Perforate your image with image objects

UEZ's Perforator major update ------------- Fun: Pro version of Perforator by UEZ

Visual Crop Tool (GUI) ----------------------- Easy to use Visual Image Crop tool

Visual Image effect (GUI) -------------------- Visually apply effects on an image

 

 

 

Share this post


Link to post
Share on other sites

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 :o

Thank you for sharing it :)


SNMP_UDF ... for SNMPv1 and v2c so far, GetBulk and a new example script

wannabe "Unbeatable" Tic-Tac-Toe

Paper-Scissor-Rock ... try to beat it anyway :)

Share this post


Link to post
Share on other sites

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


AUTOIT[sup] I'm lovin' it![/sup]

Share this post


Link to post
Share on other sites

@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.

Share this post


Link to post
Share on other sites

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


Contributions

CheckUpdate - SelfUpdating script ------- Self updating script

Dynamic input validation ------------------- Use a Input masks can make your life easier and Validation can be as simple

MsgBox with CountDown ------------------- MsgBox with visual countdown

Display Multiline text cells in ListView ---- Example of pop-up or ToolTip for multiline text items in ListView

Presentation Manager ---------------------- Program to display and refresh different Border-less GUI's on a Display (large screen TV)

USB Drive Tools ------------------------------ Tool to help you with your USB drive management

Input Period udf ------------------------------ GUI for a period input

Excel ColorPicker ---------------------------- Color pickup tool will allow you to select a color from the standard Excel color palette

Excel Chart UDF ----------------------------- Collaboration project with water 

GetDateInString ------------------------------ Find date/time in a string using a date format notation like DD Mon YYYY hh:mm

TaskListAllDetailed --------------------------- List All Scheduled Tasks

Computer Info --------------------------------- A collection of information for helpdesk

Shared memory Demo ----------------------- Demo: Two applications communicate with each other through means of a memory share (using Nomad function, 32bit only)

Universal Date Format Conversion -------- Universal date converter from your PC local date format to any format

Disable Windows DetailsPane -------------- Disable Windows Explorer Details Pane

Oracle SQL Report Generator -------------  Oracle Report generator using SQL

SQLite Report Generator -------------------  SQLite Report generator using SQL

SQLite ListView and BLOB demo ---------- Demo: shows how binary (image) objects can be recognized natively in a database BLOB field

DSN-Less Database connection demo --- Demo: ActiveX Data Objects DSN-Less Database access

Animated animals ----------------------------- Fun: Moving animated objects

Perforated image in GUI --------------------- Fun: Perforate your image with image objects

UEZ's Perforator major update ------------- Fun: Pro version of Perforator by UEZ

Visual Crop Tool (GUI) ----------------------- Easy to use Visual Image Crop tool

Visual Image effect (GUI) -------------------- Visually apply effects on an image

 

 

 

Share this post


Link to post
Share on other sites

@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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

thanks, works great now.

Share this post


Link to post
Share on other sites

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

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Share this post


Link to post
Share on other sites

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

best regards, Kalvin

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0