Crystal Reports Viewer in AutoIT

[ptrex]

Crystal Reports Viewer

Hello All this is my second project:

(With a lot of help of SvenP, Thanks)

This is a CR Report Viewer using AutoIt, making it a nice reporting engine.

But I need some help in catching the file from the Menu -> file Open.

And use that one in the variable $oReport (line 53) instead of the hard coded path and file name

expandcollapse popup

#include <GUIConstants.au3>
;
; Embedding an Crystal Reports Viewer control inside an AutoIt GUI
;
; THIS EXAMPLE REQUIRES THE CRYSTAL REPORTS ACTIVEX VIEWER CONTROL V8.0 or higher !!
;
; The Crystal Reports ActiveX viewer is a component from the Crystal Reports suite and can not be obtained separately
;
; See also: http://support.businessobjects.com/ , search for "ActiveX"

; Initialize my error handler
$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")
Dim $Gui_edit
Dim $Gui_fileExit
Dim $file

; You can test this from:
; https://emr.kcms.msu.edu/touchworks/Common/Components/Printing/activexviewer.cab
; expand the cab and run regsvr32.exe on the file crviewer.dll

$oCRViewer      = ObjCreate("CRViewer.CRViewer"); Create a Crystal Reports Viewer control
$oCRViewerEvt   = ObjEvent($oCRViewer,"CRViewerEvent_") ; Catch events from the control

if IsObj($oCRViewer) then
    
    $oCRViewer.DisplayBorder = False        ;MAKES REPORT FILL ENTIRE FORM
    $oCRViewer.DisplayTabs = True        ;THIS REPORT DOES NOT DRILL DOWN, NOT NEEDED
    $oCRViewer.EnableDrillDown = True    ;REPORT DOES NOT SUPPORT DRILL-DOWN
    $oCRViewer.EnableRefreshButton = True ;ADO RECORDSET WILL NOT CHANGE, NOT NEEDED

#comments-start
; Using a local database and the crystal reports application
; Example from: http://www.vbmysql.com/samplecode/cr9vbmysql.html
    
; Open your data source here....
    $conn = CreateObj("ADODB.Connection")
    $conn.CursorLocation = $adUseClient  ;SERVER-SIDE NOT RECCOMENDED
    $conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
        & "SERVER=127.0.0.1;" _
        & "DATABASE=test;" _
        & "UID=testuser;" _
        & "PWD=12345;" _
        & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384 ;SET ALL PARAMETERS
    $conn.Open      
    
; Create a recordset
    $rs = CreateObj("ADODB.Recordset")
    $rs.Open("SELECT * FROM report", $conn, $adOpenStatic, $adLockReadOnly)
#comments-end   

;MANAGES REPORTS
    $oCrystal = ObjCreate("CrystalRunTime.Application")
    $oReport = $oCrystal.OpenReport("C:TmpWeek Capacity Utilized by Machine.rpt") ;OPEN OUR REPORT
    
; Link the data source to your report
;$oReport.DiscardSavedData                   ;CLEARS REPORT SO WE WORK FROM RECORDSET
;$oReport.Database.SetDataSource $rs         ;LINK REPORT TO RECORDSET
    
; Now link the viewer with the report
    $oCRViewer.ReportSource = $oReport

; Using a web server connection
; See also: http://support.businessobjects.com/communityCS/TechnicalPapers/cr_troubleshooting_activex_viewer.pdf
    
;$oWebBroker = ObjCreate("WebReportBroker.WebReportBroker")
;$oWebSource = ObjCreate("WebReportSource.WebReportSource")
;$oWebSource.ReportSource = $oWebBroker
;$oWebSource.URL = "file:///C:TmpIT costs 2005.rpt" ; Change this to your URL.
;$oWebSource.PromptOnRefresh = True
    
; Now link the Webviewer with the report
;$oCRViewer.ReportSource = $oWebSource  
    
; Create a simple GUI for our output
    GUICreate ( "Embedded ActiveXcrystal", 780, 740 )
    
; Create File Menu
    $GUI_FileMenu = GUICtrlCreateMenu    ("&File")
;$GUI_FileNew   = GUICtrlCreateMenuitem ("&New"      ,$GUI_FileMenu)
    $GUI_FileOpen = GUICtrlCreateMenuitem ("&Open..."    ,$GUI_FileMenu)
;$GUI_FileSave = GUICtrlCreateMenuitem ("&Save"     ,$GUI_FileMenu)
;$GUI_FileSaveAs = GUICtrlCreateMenuitem ("Save As..." ,$GUI_FileMenu)
    $GUI_FileSepa = GUICtrlCreateMenuitem (""            ,$GUI_FileMenu) ; create a separator line
    $GUI_FileExit = GUICtrlCreateMenuitem ("E&xit"      ,$GUI_FileMenu)

    $GUI_ActiveX    = GUICtrlCreateObj      ( $oCRViewer,   10, 10 , 750 , 580 )
    $GUI_Edit       = GUICtrlCreateEdit ( "",               10, 600 , 750 , 120 )

    GUISetState ()   ;Show GUI

    $oCRViewer.ViewReport ;Show Report
    
; Waiting for user to close the window
    While 1
        $msg = GUIGetMsg()
    Select
        Case $msg = $GUI_EVENT_CLOSE
            ExitLoop
        Case $msg = $GUI_FileExit
            ExitLoop
        Case Else
            if $msg = $GUI_FileOpen Then
            $file = FileOpenDialog("Select RPT Source File", ".", "RPT (*.rpt)", 3);Filter "." means all
            EndIf
        EndSelect
    Wend

    GUIDelete ()

EndIf

Exit

; This is my 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

; -----Catch all CRViewer events

Func CRViewerEvent_($Event)
    If Isdeclared("GUI_Edit") then GUICtrlSetData($GUI_Edit,"CRViewer Unused Event: " & $Event & @CRLF, "append")
EndFunc

; Catch the print button event

Func CRViewerEvent_PrintButtonclicked()
    MSgbox (0,"Print?","Print button clicked!")
EndFunc

Attached is a sample CR Report for testing

The sooner I have reply, the sooner I can share this among the Forum users.

Edited September 14, 2012 by ptrex

[ptrex]

File not accepted because of being an RPT type.

The is the second attemp

[SvenP]

File not accepted because of being an RPT type.

The is the second attemp

<{POST_SNAPBACK}>

ptrex,

I am not a Crystal Reports expert. But when I remember it well, you have to tell CR both the name of the report AND the datasource to use with the report.

According to the RPT it requires a 'Baan Server' with a datasource using the 'pdsbaan.dll' engine.

OR did you save some example STATIC data WITH (inside) the RPT file ? I currently don't know how to run a report that includes static data. Possibly the knowledgebase at http://support.businessobjects.com has some answers about this?

Regards,

-Sven

[ptrex]

@SvenP

Hello again.

The question is not running the report in AutoIT, because this is already finished.

The problem I have the the report path is hard coded and I wanted to pick the report using the FILE -> OPEN menu.

(FileOpenDialog("Select RPT Source File", ".", "RPT (*.rpt)", 3)

When selected the CRiewer should know where to find the file.

This should be much better than coding the path&File in the script

The GUI should then select the CR report and display it.

I will take care of updating the figures, because we use a native database driver.

[JSThePatriot]

Did you ever get the FIleOpenDialog fixed? If not maybe you can use my sample code below to get it working. If you already have it working then gj and gl

$fileOD = FileOpenDialog("Select File to Create Links", @ScriptDir, "All Files (*.*)")

$file = FileOpen($fileOD, 0)
If $file = -1 Then
    MsgBox(0, "Error", "Unable to open file OD.")
    Exit
EndIf

FileClose($file)

JS

[ptrex]

@JSThePatriot

Thanks for the sample code.

I will give a try. And you know the status.

[ptrex]

@JSThePatriot

Your code example did it.

Attached is the compiled version of the CRViewer.

To run it you need to register the CRViewer.dll and a CRreport.

There is also a possibility to print.

I only need to fix the GUI so that it fits on a screen with low resolustion settings.

Is there a funciton in AutoIT the manages the Gui based on the local res. settings ?

Feedback on this is welcome.

If you want the code let me know.

[PrgSkidmark]

Since I use Crystal Reports all the time I stumbled upon this Script.

I created a simple documented sample using the documentation from Crystal Reports Technical Reference Guide and the AutoIT help. That way someone can easily pick it up.

Doug

expandcollapse popup

#include <GUIConstants.au3>

Dim $sReportName
Dim $CRApp
Dim $CrystalReport
Dim $CRViewer1

;If the version number of the application is not specified, CreateObject
;will create an application running against the most recently installed version of craxdrt.dll
;A reference to the report is first obtained in the form of a Report object 
;representing a Crystal Report Designer Component
;The OpenReport method opens an existing report file, creating an instance of the Report object.
;ReportSource is a property of the Report Viewer’s CRViewer object which
;corresponds directly to the Crystal Report Viewer Control.
;In this case, that control has been named CRViewer1. 
;Finally, the ViewReport method is called. 
;This method has no parameters and has the job simply of displaying the specified report 
;inside the Crystal Report Viewer Control.
    
$sReportName = "C:\Temp\Report1.rpt"
$CRApp = ObjCreate("CrystalRuntime.Application")
If $CRApp = 0 Then
    MsgBox(0, "Error", "Could not create CrystalRuntime Object")
    Exit
EndIf
$CrystalReport = $CRApp.OpenReport($sReportName)
If $CrystalReport = 0 Then
    MsgBox(0, "Error", "Could not open report: " & $sReportName)
    Exit
EndIf

$CRViewer1 = ObjCreate("CRViewer.CRViewer")
$CRViewer1.ReportSource = $CrystalReport

; Create a simple GUI for our output
$hndReportViewer = GUICreate ( "Embedded Crystal Reports control Test", 640, 580,(@DesktopWidth-640)/2, (@DesktopHeight-580)/2 , $WS_OVERLAPPEDWINDOW + $WS_VISIBLE + $WS_CLIPSIBLINGS)
If $hndReportViewer = 0 Then
    MsgBox(0, "Error", "Could not create Window to display Crystal Reports control")
    Exit
EndIf
;Creates an ActiveX control in the GUI.
$GUIActiveX = GUICtrlCreateObj ( $CRViewer1, -1, -1, 640, 580)
GUICtrlSetResizing ( $GUIActiveX, $GUI_DOCKAUTO)

; Show GUI
GUISetState () 

$CRViewer1.ViewReport

While 1
    $msg = GUIGetMsg()
    
    If $msg = $GUI_EVENT_CLOSE Then ExitLoop
Wend
GUIDelete()

Edited August 11, 2006 by PrgSkidmark

[ptrex]

@PrgSkidmark

Good to see that I am not the only one using CR !!

Works like a charm for me.

Might not work on all the versions of CR though.

I use Version 8.

See you around.

[Teldin]

This is great guys! Nice work.

Here is how to update PrgSkidmark's app for recent CR versions:

Change the line "$CRViewer1 = ObjCreate("CRViewer.xxxxx")" to reflect your version of CR

version 8.5 Crystal Report Viewer.

CRViewer.CRViewer

version 9 Crystal Report Viewer.

CRViewer9.CRViewer

version 10.0 Crystal Report Viewer.

CrystalReports10.ActiveXReportViewer.1

version 11.0 Crystal Report Viewer.

CrystalReports11.ActiveXReportViewer.1

version XI R2 Crystal Report Viewer.

CrystalReports115.ActiveXReportViewer.1

Some info:

http://technicalsupport.businessobjects.co...%200%2019508206

and

http://technicalsupport.businessobjects.co...%200%2019472390

and

http://technicalsupport.businessobjects.co...%200%2019542229

Edited April 1, 2008 by Teldin

[PrgSkidmark]

I have been trying to figure out this whole "Parameters" thing! If I had .Net I'd be all good I think.

I created a script to load and export my report. Then I made another one to fill out the parameters in the "Enter Values" dialog. I made a few workarounds. Because of the single thread, when ViewReport is called the thread halts until the "Enter Values" dialog is filled out. And I upgraded to Crystal Reports XI so the ActiveX control name change threw me off. I've spent waaay to much time on this.

So here is my Export code for you to check out.

expandcollapse popup

#include <GUIConstants.au3>
AutoItSetOption("WinTitleMatchMode", 2)

; If the version number of the application is not specified, CreateObject
; will create an application running against the most recently installed version of craxdrt.dll
; A reference to the report is first obtained in the form of a Report object 
; representing a Crystal Report Designer Component
; The OpenReport method opens an existing report file, creating an instance of the Report object.
; ReportSource is a property of the Report Viewers CRViewer object which
; corresponds directly to the Crystal Report Viewer Control.
; In this case, that control has been named objCRViewer. 
; The ViewReport method is called. 
; This method has no parameters and has the job simply of displaying the specified report 
; inside the Crystal Report Viewer Control.
; Although the Crystal Report Viewer Control is designed primarily for displying reports on screen, 
; users frequently want a hard-copy of the data.  
; The PrintReport method provides printing functionality to the user.
    
Dim $sReportName
Dim $objCRApp
Dim $objCRReport
Dim $objCRViewer
Dim $CrystalExportOptions
Dim $ExportFileName
Dim $ExportType

$sReportName = FileOpenDialog("Select RPT Source File", ".", "RPT (*.rpt)", 3);Filter "." means all
$objCRApp = ObjCreate("CrystalRuntime.Application.11")
If $objCRApp = 0 Then
    MsgBox(0, "Error", "Could not create CrystalRuntime Application Object")
    Exit
EndIf
$objCRReport = $objCRApp.OpenReport($sReportName)
If $objCRReport = 0 Then
    MsgBox(0, "Error", "Could not open report: " & $sReportName)
    Exit
EndIf

$objCRViewer = ObjCreate("CrystalReports11.ActiveXReportViewer.1")

; Create a simple GUI for our output
$hndReportViewer = GUICreate ( "Embedded Crystal Reports Export test", 640, 580,(@DesktopWidth-640)/2, (@DesktopHeight-580)/2 , $WS_OVERLAPPEDWINDOW + $WS_VISIBLE + $WS_CLIPSIBLINGS)
If $hndReportViewer = 0 Then
    MsgBox(0, "Error", "Could not create Window to display Crystal Reports control")
    Exit
EndIf
;Creates an ActiveX control in the GUI.
$GUIActiveX = GUICtrlCreateObj ( $objCRViewer, -1, -1, 640, 580)
If $GUIActiveX = 0 Then
    MsgBox(0, "Error", "Could not create ActiveX Control in GUI")
    Exit
EndIf
GUICtrlSetResizing ( $GUIActiveX, $GUI_DOCKAUTO)

; Show GUI
GUISetState () 

$objCRViewer.ReportSource = $objCRReport

Run("C:\FillInFacility.exe")

; Display the Report to watch automation
$objCRViewer.ViewReport
; The report displays a "0" in control 20005 while the report is loading
While ControlGetText("Embedded Crystal Reports printing test", "", 20005) = "0"
    Sleep(250)
WEnd

$CrystalExportOptions = $objCRReport.ExportOptions
$ExportFileName = "C:\ExportedReport.PDF"
$ExportType = 31

$CrystalExportOptions.DiskFileName = $ExportFileName
$CrystalExportOptions.FormatType = $ExportType
$CrystalExportOptions.DestinationType = 1
$objCRReport.Export ( False )
Sleep(500)
WinWaitClose("Export", "", 30)
GUIDelete()

And the FillInFacility Code. I added the Sleep because it seemed to skip a step sometimes.

AutoItSetOption("MouseCoordMode",0)
AutoItSetOption("SendKeyDelay", 100)
WinWaitActive("Enter Values")
Sleep(500)
;Select the Enter Discreet Value Text Box
MouseClick("left", 35, 240)
Sleep(500)
Send("FACILITYNAME")
;Click the button to add FACILITY NAME to Selected Values list box
MouseClick("left", 335, 235)
Send("{ENTER}")

[Timo]

Hello,

can someone please help me to get your script working? I've tried and tried but I still have the problem by creating the crystalruntime-object.

I think I need a step by step explanation, I'm to silly....

My system: MS Vista, CrystalReports Developer 8.5.

Thanks,

Timo

[Teldin]

Just wanted to update some stuff. (also see my post above for url updates)

you can find the ActiveX files via:

http://www.google.com/search?hl=en&q=f...tgoing%2Fehf%2F

Also use:

ObjCreate("CrystalReports115.ActiveXReportViewer.1")

for XI R2

but:

ObjCreate("CrystalRuntime.Application")

does not work anymore

Distribution files:

http://support.businessobjects.com/fix/merge_modules.asp

Troubleshooting the activex viewer:

http://resources.businessobjects.com/suppo...ivex_viewer.pdf

Edited April 1, 2008 by Teldin

[ptrex]

@Teldin

Thanks for the update.

But the link you added doesn"t work ?

regards

ptrex

[Teldin]

Do you get a google search results page?

The link shows different CR versions of the activex packages

Basically there are all in

ftp://ftp.crystaldecisions.com/outgoing/ehf/

but the folder is not browseable so you have to know the exact filename.

[igorm]

Sorry to bring old thread up, but I thought I share my experience with this. First, big thanks to ptrex for making this for AutoIt, I really needed reports in my project. In my case reports pulls data from SQLite database using ODBC connection. Now, my experience. I used Crystal Reports 9 to make my report. Then I went to test it on virtual machine which never had Crystal Reports installed. I registered crviewer9.dll, but this was not enough. This action would fail:

$oCrystal = ObjCreate("CrystalRunTime.Application")

So, I analyzed file access attempts with Process Monitor, and I discovered that I also need following files. By the way, I keep files in @CommonFilesDir&'Crystal Decisions2.0bin, and in @CommonFilesDir&'Crystal Decisions2.0crystalreportviewersActiveXViewer.

So, you need following files in bin folder:

craxdrt9.dll (this is ActiveX Runtime supports, needs DLL registration)

crdb_odbc.dll (driver for ODBC connection)

crqe.dll (query engine, needs DLL registration)

querybuilder.dll (query builder)

ufmanager.dll

In ActiveXViewer folder you need:

crviewer9.dll (ActiveX Viewer, needs DLL registration)

Other version of Crystal Reports might have different file names.

I hope this will help somebody.

Cheers

Edited October 26, 2012 by igorm

[randym]

I am glad I ran across this.  I did have to include WindowsConstants.au3 to make it work, but not big deal.  I do have a question I hope someone can answer... My crystal report is prompting for logon credentials.  Is there a way to build that into the script?

[JLogan3o13]

Hi, randym. I would suggest creating a thread in the General Help and Support forum (so you have more eyes on the issue). Be sure to post the code you're using, as well as a link to this thread where you got the UDF. We will do our best to assist