Crystal Report Viewer - logon credentials

[randym]

I ran across a script in examples that will view Crystal Reports.  With very little tweaking, I am able to make it work, but unfortunately, my data source needs login credentials and I am at a loss as how to add them.  Was wondering if anyone has solved this dilemma?  Below is the code I am currently using.

The commented out section that opens the data source $DSN, is code I added to the original example in an attempt to solve my problem.  Although it succeeds in the open, did nothing to assist my problem, so I commented it out.

The error I am getting is a Crystal Reports Viewer error message box.

Logon Failed

Details: [Database Vendor Code: -103]

expandcollapse popup

#include <GUIConstantsEX.au3>
#include <WindowsConstants.au3>

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

;Global $DSN = "DSN=Calibration Data";    Sybase database as defined in ODBC
; ODBC
;$conn = ObjCreate("ADODB.Connection")
;$conn.Open($DSN)

;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 = "F:\METBASE\shared\reports\ShowCalResults.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.ActiveXReportViewer.1")
$CRViewer1 = ObjCreate("CrystalReports115.ActiveXReportViewer.1")
$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()

[randym]

As a follow up to the original post ...

I continue to look for solutions to this issue.  I have managed to get a solution working in VB.  I will post that code here and hopefully someone can see how best to translate it into AutoIT scripting.

reportpath = Application.StartupPath & "\ShowCalResults.rpt"    'keep your report into application startup path  
            Dim objrpt As New ReportDocument
            Dim rrmrpt As New CrystalDecisions.CrystalReports.Engine.ReportDocument
            objrpt.Load(reportpath)

            Dim ConInfo As New CrystalDecisions.Shared.TableLogOnInfo

            ConInfo.ConnectionInfo.UserID = "db_userid"
            ConInfo.ConnectionInfo.Password = "db_password"

            For i = 0 To objrpt.Database.Tables.Count - 1
                objrpt.Database.Tables(i).ApplyLogOnInfo(ConInfo)
            Next

Thanks, Randy

[0xdefea7]

Why not just run the VBS from your script. Embed it and then call it like this:

Dim $sTemp

$sTemp = 'reportpath = Application.StartupPath & "\ShowCalResults.rpt"' & @CRLF 
$sTemp &= 'Dim objrpt As New ReportDocument' & @CRLF
$sTemp &= 'Dim rrmrpt As New CrystalDecisions.CrystalReports.Engine.ReportDocument' & @CRLF
$sTemp &= 'objrpt.Load(reportpath)' & @CRLF
$sTemp &= 'Dim ConInfo As New CrystalDecisions.Shared.TableLogOnInfo' & @CRLF
$sTemp &= 'ConInfo.ConnectionInfo.UserID = "db_userid"' & @CRLF
$sTemp &= 'ConInfo.ConnectionInfo.Password = "db_password"' & @CRLF
$sTemp &= 'For i = 0 To objrpt.Database.Tables.Count - 1' & @CRLF
$sTemp &= 'objrpt.Database.Tables(i).ApplyLogOnInfo(ConInfo)' & @CRLF
$sTemp &= 'Next'

FileWrite(@TempDir & "\~123.vbs", $sTemp)
Run(@TempDir & "\~123.vbs")

[randym]

Unfortunately, this is not VBS but a VB .Net compiled program.  I had to install some kind of development package from SAP for Crystal and another from Sybase for the db just to be able to get the thing to execute on my machine.  But, it will not work on the regular workstations.   In order to use it, it seems an installation package of some kind is required in order to deploy the compiled program to the target workstations, and then must be run through an installation procedure on those workstations, but it seems they also need something from SAP and Sybase in order to actually work. 

Each of the workstations is already installed w/ Crystal Reports that came with installation of our primary software application.  I am simply trying to find a method to view the reports on those workstations in a clean and simple manner.  I had hopes that this CR Viewer would be that answer...  So close, but yet still so far...

I thank you for your code though... that concept is definitely interesting.  I had not thought of using AutoIT in that way.

[brycetech]

unless you are bound and determined to use the viewer (I have had absolutely zero luck with it-and Ive spent many hours trying to make it work with autoit)

I'm sure it will, just haven't found the keys to the city yet...

try this site http://www.rainforestnet.com/crystal-reports-exporter/index.htm and get the crexport.exe program (its free)

then you can use autoit's command line functions to run and export your reports

what I'm doing is creating an executable with the command line options and then putting that executable on the windows scheduler.

works like a charm...

the downside is you can't view them in real time.

I guess you could set it to look for the file you save it to and then have it shellexecute the file to open it.

If you can't skin a cat one way..try another.

gotta love autoit

Edited September 26, 2013 by brycetech

[randym]

I would truly love to make this viewer work as well, but I have had no luck yet either with anything but the most simple of reports on a DB that has no security -

In the meantime, this export utility looks like it has some serious potential...  and I really like the way the documentation is organized.  I have a couple of autoit projects in mind that are a lot more feasible with this tool.  Thanks for the tip.

BTW - for this particular project, I ended up cutting Crystal out of the solution altogether.  Ended up writing my own DB queries with autoit and loading the results into a list box for viewing purposes...  A lot more cumbersome, definitely not as flexible as Crystal when the users come back and change what they want to see... probably never happen

[brycetech]

If you do ever find a way to get it to work directly with the viewer, I'd love to know how.  Hours of searching and trial and error have been fruitless for me as well.

but..

this creport program offers tons of options and runs nicely and is easy to send command info to.  Create a simple gui where you can enter your parameter names and values and use a button to generate the runline code to send to the cmd window.  You can create an exe with that code which could even take in account moving dates and custom variables and run from windows scheduler (or write  your own if you like challenges)  This option allows you to run it hidden and not bother the user who may be on the computer.

there is another option out there (logicity) which also has a free version and uses text files to generate the reports.  However it has a lot of 'under the hood' changes to parameters that I personally dont care for (but you may find beneficial).  It splits comma delimited parameters by default (which I dont like..I use them for a lot of purposes beside what logicity thinks I should). The free version doesnt include moving date functionality or custom variables,  but it is another very powerful option which is very controllable by autoit.  Its not a 'hidden' method, but you can generate the textual executable RRD file and shellexecute it as well.

I use both ...crexport for one offs that I can run at different times or logicity to have many reports run at once sequentially.  I've found I can run 4 instances of either for faster report generations.

luck to u and glad this was helpful

[RichardL]

Can you create another DSN with same details, and access credentials?  (We prefer this as avoids putting password in the program.)

[randym]

I had that thought as well, and for some reason, that will not fly with Crystal ... not sure why.  I did that very thing in my ultimate solution that cut Crystal out of the loop so my DB queries against the DSN did not need any credentials.

Thanks for thinking of me!

[brycetech]

you could encrypt that so it wouldnt be visible and should be able to send it to a hidden cmd window.

I've been working on a interface and I plan to add that feature to it.

[brycetech]

short autoit script for crexport.exe control

 

#AutoIt3Wrapper_AU3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6
#AutoIt3Wrapper_Run_Tidy=y

main()

Func main()
;~  fill out the variables below
 Local $reportname = 'reportpath'

 Local $crexportloc = 'crexport.exe location'

 Local $db = 'dblocation'

 Local $dbname = 'dbname'
 Local $user = 'dbuser'
 Local $pass = 'dbpass'
 Local $format = 'pdf'
 Local $output = 'output.pdf'

;~ local $attributes =

;~ combined command
 Local $cmd = Chr(34) & $crexportloc & Chr(34) & _
   ' -F ' & Chr(34) & $reportname & Chr(34) & _
   ' -S ' & $db & _
   ' -D ' & $dbname & _
   ' -U ' & $user & _
   ' -P ' & $pass & _
   ' -E ' & $format & _
   ' -O ' & Chr(34) & $output & Chr(34)
;~ ' -a '
;~ uncomment lines below to view command window for testing
;~ #####START#####
;~ run("cmd")
;~ sleep(1000)
;~ send($cmd)
;~ sleep(1000)
;~ send("{Enter}")
;~ #####END#####

;~ comment next line if lines above are uncommented
 Local $pid = Run($cmd, "", @SW_HIDE)
;~  did you comment the line above if indicated?

 Local $tI

;~ establish some timelimit that is too long so that the program will abort
;~ (60000 * 180) / 50 = 3 hours
;~ change 180 to a multiple of 60 to change hour timelimit

 Local $timelimit = (60000 * 180) / 50
 While 1
  If $tI > $timelimit Then
   ProcessClose($pid)
   ExitLoop
  EndIf
  If ProcessExists($pid) = 0 Then
   If Not FileExists($output) And $tI < $timelimit Then
    MsgBox(0, "Error", "CRExport.exe terminated early. Check login and password settings.")
   EndIf
   ExitLoop
  EndIf
  If FileExists($output) Then
   MsgBox(0, "Info", "Report completed")

;~  show file
   ShellExecute($output, "", "", "", @SW_MINIMIZE)

;~ just in case process did not close
   If ProcessExists($pid) Then
    ProcessClose($pid)
   EndIf
   ExitLoop
  EndIf
  Sleep(50)
  $tI += 1
 WEnd

EndFunc   ;==>main

Edited October 4, 2013 by brycetech

[ryadical]

My crystal report is not using ODBC to connect, it is using the built in Oracle connection which looks like it might use OLE. I was searching all over the internet before I finally found a Delphi script that uses these classes and I converted the code to work with AutoIT. Here is the important bits...

 

$oCrystal = ObjCreate("CrystalRunTime.Application")

$oReport = $oCrystal.OpenReport("C:myreport.rpt", 1) 

 

$oReport.Database.Tables(1).ConnectionProperties.Item('User ID') = "username"

$oReport.Database.Tables(1).ConnectionProperties.Item('Password') = "password"

Edited February 7, 2014 by ryadical