VB code with Crystal Reports

[nezster]

Hello,

I am working for a company that writes many Crystal Reports each day. We now have approaching 500. We use Stored Procedures to query the SQL database.

Occasionaly, but more anf more frequently, we have a problem where the database requires a change such as uppercasing a field to use a bad example. We would like to know how many reports use this field.

Is it possible to write code in VB that uses the Crystal Objects to determine the Stored Procedure it uses? If a Stored Procedure isn't used then the Command code needs to be evaulated? I can then write some code to search the Stored Procedure code to locate any field matches?

Anyone done anything like this before or can point me in the right direction?

[brucevde]

Yes, you can use the Crystal objects to figure out which stored procedure is used by reports. The following code is for Crystal 8.5, I would assume it is similar with other versions. You need a reference to the Crystal Reports ActiveX Run-Time Library.

Dim objCrystalApp As CRAXDRT.Application
    Dim objReport As CRAXDRT.Report
 
    Dim objDB  As CRAXDRT.Database
    Dim objTables    As CRAXDRT.DatabaseTables
    Dim objTable     As CRAXDRT.DatabaseTable
    
    Dim objSections  As CRAXDRT.Sections
    Dim objSection As CRAXDRT.Section
    
    Dim objReportObjects As CRAXDRT.ReportObjects
    Dim objSubReports As CRAXDRT.SubreportObject
    Dim objSubReport As CRAXDRT.Report
    
    Dim lngIdx As Long
    
    Set objCrystalApp = New CRAXDRT.Application
    Set objReport = objCrystalApp.OpenReport(ReportFileName, 0)
    
    Set objDB = objReport.Database
    Set objTables = objDB.Tables
        
    For Each objTable In objTables
         Debug.Print objTable.Location 'stored procedure or database table name
    Next
 
    'Check any SubReports in the Main report.
    'Access to Sub Reports is through the Sections collection
 
    Set objSections = objReport.Sections
 
    For Each objSection In objSections
        
        Set objReportObjects = objSection.ReportObjects
        
        If objReportObjects.Count > 0 Then
 
            For lngIdx = 1 To objReportObjects.Count
                'make sure the report object is a subreport.
                If objReportObjects(lngIdx).Kind = crSubreportObject Then
 
                    Set objSubReports = objReportObjects(lngIdx)
                    Set objSubReport = objReport.OpenSubreport(objSubReports.SubreportName)
 
                    Set objDB = objSubReport.Database
                    Set objTables = objDB.Tables
 
                    For Each objTable In objTables
                        Debug.Print objTable.Location
                    Next
                End If
            Next
        End If
    Next

[nezster]

Thank you very much for your help. This is EXACTLY what I am looking for.

One quick question. Where you obtain the Stored Procedure name or Database Table, would you get the 'Command' code if that is the method of obtaining the data from within the report?

[brucevde]

I am sorry, I don't understand what you mean by 'Command' code.

[nezster]

Hello,

'Command' Code is an area of the report where you can paste in SQL code directly to execute or your can place EXEC 'SP_NAME' in there instead.

Don't worry too much about it. You have helped me plenty so far. I have an idea for an mini application that can assist us with our 600+ Crystal reports. I don't have a copy of VB6 so I am trying to convince my boss that getting hold of a copy will result in me being able to solve a problem. Once I can get that then I will work on him to get .net