on 10-15-2008 4:00 PM
I would like to be able to mass export all of our Crystal Reports to txt files (as report definitions). This would aid us when it is necessary to make database modifications. We could then do lookups on the definitions and find reports that will be candidates for updating. I have attempted to do this with visual basic using report viewer, however, when reading through the folder of reports and attempting to export the definition, we are prompted for the parameters for each report. I am not actually running the reports but want to merely export the definition to a text file. Any suggestions?
I have been unable to export the report definition using vb and crystal report viewer option. Instead I used vbs to create a clumsy proceedure to read, load the report in crystal reports and supply the keystrokes to (like a macro) export the definition. Which did work although quite slow. However, now I realize that I really also need the SQL statements, so I am back to the old problem. I did find a way to discern the parameter types on the fly for each report and supply a generic value, '1' for number, 'a' for string, 'today' for date... this seems to work quite well and I was able to copy out the SQL to text files, at first. The problem now is that I am getting subscripting errors on the database connection which I am not able to explain...
I get the subscript error at the point of ConnectionInfo.Item("server or provider") in the code below. Any suggestions?
Set myReport = myApplication.NewReport
Dim subReportObj As SubreportObject
Dim sect As Section
Dim rptObject As Object
For Each sect In myReport.Sections
For Each rptObject In sect.ReportObjects
If rptObject.Kind = crSubreportObject Then
subReportObj = rptObject
Set subReport = Report.OpenSubreport(subReportObj.SubreportName)
subReport.Database.Tables(1).ConnectionProperties("Data Source") = "xxxx"
subReport.Database.Tables(1).ConnectionProperties("user ID") = "xxxxx"
subReport.Database.Tables(1).ConnectionProperties("password") = "xxxxxx"
End If
Next
Next
Set myReport = myApplication.OpenReport(strRpt)
Set ConnectionInfo = myReport.Database.Tables(1).ConnectionProperties
If myReport.Database.Tables.Item(1).DatabaseType = crSQLDatabase Then
ConnectionInfo.Item("Provider") = "SQLOLEDB"
ConnectionInfo.Item("Data Source") = "xxxxx"
ConnectionInfo.Item("user ID") = "xxxxx"
ConnectionInfo.Item("password") = "xxxxx"
Else
ConnectionInfo.Item("Server") = "xxxxx"
ConnectionInfo.Item("user ID") = "xxxxxx"
ConnectionInfo.Item("password") = "xxxxxx"
End If
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Sharon,
Yes, you are correct. But I am attempting through vb and the viewer, to batch process all of our reports to export the report definitions and SQL statements to text files. That is the only method that I could come up with. I believe I am going to be able to accomplish it now if only I can get past the subscript error on making the database connections.
Nick,
Wish I could be more helpful here, but I don't know VB all that well and haven't used VBS in a few years...
If you manage to get your output to match the format of the normal "Report Definition" output though, you can use "Crystal Reports Documentor" (http://www.ml-consult.co.uk) to parse the output for you to determine the tables & columns that are used in the report.
Abbot,
After looking over my code, I realized my problem and have made adjustments that allow me to extract the SQL statement. I will be making the changes now that I am fairly sure now that allow me to export the file definitions and the sql... sorry about the points I am old school and unfamiliar with the points awarding...
Abbot,
My programming skills are not too great. I failed to mention that you need to code in VB6 and have CR 11.0 developer type full. You will also need to have the following references in your Visual basic project:
Crystal ActiveX Report Viewer Library 11.0
Crystal Reports Active X Designer RunTime library 11.0
Crystal Reports Active X Designer Design and RunTime Lib.11.0
I believe if you add all of that then in your tools should appear.
Crystal Active X Report Viewer.
I work for a fair sized IT staff and they were willing to spend the money for this. If you still want the code respond to this reply and I will post it for you. I don't mind showing you the code...
Almost forgot. Before I was able to get the vb report viewer option to work for me I resorted to a vbs script. I ran it and it worked well enough to export most of the reports we have into report definitions (.txt) files. We have well over 400 reports in this application and it tied up my pc for a long while, and I had to sit and monitor it because some reports caused problems and I had to respond to some of the messages that they caused to come up. But if you really want the report definitions it will work. You have to copy the rpt files directly under your c: drive. And the export ended up in an xml directory for some reason but you can probably fix that. Anyway create a text file copy this code in and rename to somename.vbs, and click to run. I know but it does work...and you don't need a thing to run it... I'm sure you can improve on this vbs code... works kinda like a macro...
Set FSO = CreateObject("Scripting.FileSystemObject")
rFolder = "path where your reports are"
Set folder = FSO.GetFolder(rFolder)
Set Files = folder.Files
For Each folderIdx In Files
one = UCase(Right(folderIdx, 3))
two = UCase(Left(folderIdx, 3))
RTPNME = folderIdx.Name
If UCase(Right(folderIdx, 3)) = "RPT" Then
newrpt = folderIdx.Name
WScript.sleep 1000
CreateObject("WScript.Shell").SendKeys newrpt
WScript.sleep 2000
CreateObject("WScript.Shell").SendKeys Chr(13)
WScript.sleep 7500
CreateObject("WScript.Shell").SendKeys "%(F)"
WScript.sleep 1000
CreateObject("WScript.Shell").SendKeys "E"
WScript.sleep 1000
CreateObject("WScript.Shell").SendKeys Chr(13)
WScript.sleep 2000
CreateObject("WScript.Shell").SendKeys "" CreateObject("WScript.Shell").SendKeys ""
CreateObject("WScript.Shell").SendKeys "" CreateObject("WScript.Shell").SendKeys ""
CreateObject("WScript.Shell").SendKeys "" CreateObject("WScript.Shell").SendKeys ""
CreateObject("WScript.Shell").SendKeys "" CreateObject("WScript.Shell").SendKeys ""
CreateObject("WScript.Shell").SendKeys "" CreateObject("WScript.Shell").SendKeys ""
CreateObject("WScript.Shell").SendKeys ""
WScript.sleep 1000
CreateObject("WScript.Shell").SendKeys Chr(13)
WScript.sleep 2000
CreateObject("WScript.Shell").SendKeys Chr(13)
WScript.sleep 3000
CreateObject("WScript.Shell").SendKeys"%(F)"
WScript.sleep 1000
CreateObject("WScript.Shell").SendKeys "C"
WScript.sleep 1000
CreateObject("WScript.Shell").SendKeys Chr(13)
WScript.sleep 1000
CreateObject("WScript.Shell").SendKeys"%(F)"
WScript.sleep 1000
CreateObject("WScript.Shell").SendKeys "X"
WScript.sleep 2000
End If
Next
WScript.Quit
Nick,
Have you made any progress on this issue yet? I am in the very same quandary and would love to colloborate on a solution....
This seems like something that virtually everyone using Crystal would want, wouldn't it? Manually exporting a report definition every time it changes strikes me as fraught with peril -- not sustainable or scalable.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Abbot,
I have made some headway. You can get the count of the parameters and the type (num, string, date...) and can set the parameter to a generic value for its specific type, 1 for num, a for string, todays date for date. That will get you past the input parameters. Indeed I got this to work, for some reports, however it appears that reports made with ADO connection strings may be bombing out with my current connection code because I am getting subscripting errors on many of my database connections as I loop through the reports. Any Ideas?
Set myReport = myApplication.NewReport
Dim subReportObj As SubreportObject
Dim sect As Section
Dim rptObject As Object
For Each sect In myReport.Sections
For Each rptObject In sect.ReportObjects
If rptObject.Kind = crSubreportObject Then
subReportObj = rptObject
Set subReport = Report.OpenSubreport(subReportObj.SubreportName)
subReport.Database.Tables(1).ConnectionProperties("Data Source") = "xxxxx"
subReport.Database.Tables(1).ConnectionProperties("user ID") = "xxxxx"
subReport.Database.Tables(1).ConnectionProperties("password") = "xxxxx"
End If
Next
Next
Set myReport = myApplication.OpenReport(strRpt)
Set ConnectionInfo = myReport.Database.Tables(1).ConnectionProperties
If myReport.Database.Tables.Item(1).DatabaseType = crSQLDatabase Then
ConnectionInfo.Item("Provider") = "SQLOLEDB"
ConnectionInfo.Item("Data Source") = "xxxxx"
ConnectionInfo.Item("user ID") = "xxxxx"
ConnectionInfo.Item("password") = "xxxxx"
Else
ConnectionInfo.Item("Server") = "xxxxx"
ConnectionInfo.Item("user ID") = "xxxxx"
ConnectionInfo.Item("password") = "xxxxx"
End If
you can export the report to report definition without running the report and get all the backend info
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Nick,
There's no way around this from a VB front-end. The Print Engine assumes you want to run the report, whether you are viewing, printing or exporting so when you set the destination and run the report, it will run the report as normal.
Thanks,
Brian
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
94 | |
11 | |
11 | |
10 | |
9 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.