12 dozen reports 1

[SMerrill]

I have 146 reports to go through and document. Without purchasing a product, I would like to construct a loop in VB code that opens up each RPT filename contained in a table, and using the Crystal Reports 8.5 object model, tells me what the Table Name(s) are in the Set Location dialog. This way, I can document the dependencies of each report.

I'll settle for half of this; that is, a loop in VB code that opens up each *.RPT filename contained in a table.

I probably can write all of this, I'm just lazy. If it has been done already, and you want to share it with me, feel free to do so.

Take care,
--Shaun

"I wish that my room had a floor; I don't care so much for a door.
But this crawling around without touching the ground is getting to be quite a bore!" -- Gelett Burgess

 

[dgillz]

Have your VB program execute each report, then export it with the format "report definition". This creates a text file that documents most of the report including formula fields, conditional formatting, record and group selection formulas, etc.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[SMerrill]

Nice idea. This gives everything BUT what I want to know.
You see, if I have aliased a table, view, or stored procedure, I am not told what the alias stands for.
Can we go deeper? Thanks. Have a nice 4th of July.

 

[SMerrill]

I've gotten this far, but I'd like to know the actual table names, not the alias names as this code retrieves. So please modify this code until it retrieves the actual name of the database object it is referring to, not the alias of the object. This code works if the aliases is what you want to get.

Function GetAliases(strFilename As String) As String
    On Error GoTo GetAliases_Error
    ' Return a pipe-delimited string containing each alias name.
    Dim CrystalApplication As CRPEAuto.Application
    Dim CrystalReport      As CRPEAuto.Report
    Dim db                 As CRPEAuto.Database
    Dim dbTables           As CRPEAuto.DatabaseTables
    Dim dbTable            As CRPEAuto.DatabaseTable
    Dim dbFields           As CRPEAuto.DatabaseFieldDefinitions
    Dim dbField            As CRPEAuto.DatabaseFieldDefinition
    Dim strAliases         As String

    Set CrystalApplication = New CRPEAuto.Application 'CreateObject("Crystal.CRPE.Application")
    Set CrystalReport = CrystalApplication.OpenReport(strFilename)
    Set db = CrystalReport.Database
    Set dbTables = db.Tables
    
    strAliases = ""
    
    'find the alias name of the first table in the database
    If dbTables.Count >= 1 Then
      For Each dbTable In dbTables
        Set dbFields = dbTable.Fields
        Set dbField = dbFields.Item(1)
        If Len(strAliases) > 0 Then
          strAliases = strAliases & "|"
        End If
        strAliases = strAliases & dbField.TableAliasName
      Next
    End If
    
GetAliases_Exit:
    GetAliases = strAliases
    
    Set dbField = Nothing
    Set dbFields = Nothing
    Set dbTable = Nothing
    Set dbTables = Nothing
    Set db = Nothing
    Set CrystalReport = Nothing
    Set CrystalApplication = Nothing
    Exit Function
  
GetAliases_Error:
  If CrystalApplication Is Nothing Then
      strAliases = "Unable to Create Object(""Crystal.CRPE.Application"")"
      Resume GetAliases_Exit
  End If
  If CrystalReport Is Nothing Then
      strAliases = "Unable to Open Report(""" & strFilename & """)"
      Resume GetAliases_Exit
  End If
  strAliases = "Unknown error in GetAliases routine"
  Resume GetAliases_Exit
End Function

Take care,
--Shaun

"I wish that my room had a floor; I don't care so much for a door.
But this crawling around without touching the ground is getting to be quite a bore!" -- Gelett Burgess

 

[SMerrill]

I answered my own question. Here is another useful routine for the purpose of documenting the interface between CR and the database layer.

Function GetTableLocations(strFilename As String) As String
    On Error GoTo GetTableLocations_Error
    
    Dim CrystalApplication As CRPEAuto.Application
    Dim CrystalReport      As CRPEAuto.Report
    Dim dbTables           As CRPEAuto.DatabaseTables
    Dim dbTable            As CRPEAuto.DatabaseTable
    Dim strLocations       As String

    Set CrystalApplication = New CRPEAuto.Application
    Set CrystalReport = CrystalApplication.OpenReport(strFilename)
    Set dbTables = CrystalReport.Database.Tables
    
    strLocations = ""
    
    'find the alias name of the first table in the database
    If dbTables.Count > 0 Then
      For Each dbTable In dbTables
        If Len(strLocations) > 0 Then
          strLocations = strLocations & "|"
        End If
        strLocations = strLocations & dbTable.Location
      Next
    End If
    
GetTableLocations_Exit:
    GetTableLocations = strLocations
    
    Set dbTable = Nothing
    Set dbTables = Nothing
    Set CrystalReport = Nothing
    Set CrystalApplication = Nothing
    Exit Function
  
GetTableLocations_Error:
  If CrystalApplication Is Nothing Then
      strLocations = "Unable to Create Crystal Object"
      Resume GetTableLocations_Exit
  End If
  If CrystalReport Is Nothing Then
      strLocations = "Unable to Open Report(""" & strFilename & """)"
      Resume GetTableLocations_Exit
  End If
  strLocations = "Unknown error in GetTableLocations routine"
  Resume GetTableLocations_Exit
End Function