Crystal Reports

[Former Member]

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?

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

report definination is not done through the viewer it is done locally on the back end of the report

[Former Member · Accepted solution]

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.

[Former Member · Accepted solution]

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.

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

No problem on the points... I don't think I deserve any anyway!

Would you mind posting your final code, along with a bit of context for neophytes like me? In other words... if someone is starting from scratch (no software except CR itself), how would they go about using your code?

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

Unfortunately, I only have the "Professional" version, not the "Developer" version...

I'll still give it a try at some point though and see if maybe I can make it work somehow.

Whatever I come up with, I'll post here to share as well.

[Former Member · Accepted solution]

Hi Nick, could I also have the code as I am having a similar problem.

Thanks

[Former Member]

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.

[Former Member]

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

[Former Member]

you can export the report to report definition without running the report and get all the backend info

[former_member292966]

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