Generate metadata about existing Crystal Reports : The smart way
- Crystal Reports 2008
- Visual Studio 2010
- Assembly Info: CrystalDecisions.CrystalReports.Engine.dll, CrystalDecisions.ReportSource.dll, CrystalDecisions.Shared.dll, CrystalDecisions.Windows.Forms.dll
This is the story of Jason, a meager trainee in a middle level organization, who was given a boring documentation job on a Friday afternoon. He wanted to leave early, and his boss told him to prepare a datasheet about 762 Crystal Reports for a detailed report to the IT head.
For each report he would be required to find out the author name and Report Title from the Summery Info. Also required were database and table names. Go ahead young man, Mr. Murphy had explained : you just need to double click on each report to open it, go to File- Summery Info to get some details, go to Database- Database Expert to get the remaining stuff, and copy these to an excel sheet.
But Jason had no intention of doing this with so many clicks. Can’t I do this with a single click– he muttered? So he proceeded to create a VB.Net Windows Application, with a single button on a single Form.
The first thing he designed was the DataTable containing the columns that Mr. Murphy had told him put in this excel sheet.
Function GetTable() As DataTable
‘ Create new DataTable instance.
Dim table As New DataTable
‘ Create 5 columns in the DataTable.
table.Columns.Add(“Report Name”, GetType(String))
table.Columns.Add(“Report Title”, GetType(String))
He declared the Dataset and Data Table at Form level as they would be used by multiple subroutines.
Dim ds As DataSet
Dim ReportInfoTable As DataTable
Next he began coding for the list of events that needed to take place when he clicked on the button.
- First he needed to select the folder where the report files were placed. He used the FolderBrowserDialog Control for this.
- Once the proper directory was selected, only the “*.rpt” files needed to be processed.
- An instance of the Data Table is created to hold the data created by processing each file.
- A “For- Next” loop is used to process each report file by calling the sub-routine GetMetaData. This sub-routine would also add rows to the Data Table created in step 2 for each report.
- The final step would be to export the data generated in xml form. The xml file can be opened by excel application and saved as an excel file for Mr. Murphy.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim folderPath As String
Dim fileNames As Object
‘Let the user select the path for report files
FolderBrowserDialog1.SelectedPath = “c:\”
If FolderBrowserDialog1.ShowDialog() = DialogResult.OK Then
folderPath = FolderBrowserDialog1.SelectedPath
‘Only report files from selected directory are to be analyzed
fileNames = My.Computer.FileSystem.GetFiles(
folderPath, FileIO.SearchOption.SearchTopLevelOnly, “*.rpt”)
ReportInfoTable = GetTable()
‘Process Each Report File to get the required information
For Each fileName As String In fileNames
‘Export all the data generated as xml
ds = New DataSet()
The final step was to create the “GetMetaData” sub-routine to process each report. This was achieved by using the CrystalDecisions.CrystalReports.Engine.ReportDocument class and its methods.
Looping is necessary to handle cases where a single report may contain multiple database connections, or multiple tables in a single database connection.
Sub GetMetaData(ByVal ReportName As String)
Dim rdoc As CrystalDecisions.CrystalReports.Engine.ReportDocument
Dim reportDataRow As DataRow
rdoc = New CrystalDecisions.CrystalReports.Engine.ReportDocument
For j = 0 To rdoc.DataSourceConnections.Count – 1
For i = 0 To rdoc.Database.Tables.Count – 1
reportDataRow = ReportInfoTable.NewRow()
reportDataRow(“Report Name”) = rdoc.FileName
reportDataRow(“Author”) = rdoc.SummaryInfo.ReportAuthor
reportDataRow(“Report Title”) = rdoc.SummaryInfo.ReportTitle
reportDataRow(“Database”) = rdoc.DataSourceConnections.Item(j).DatabaseName
reportDataRow(“Tables”) = rdoc.Database.Tables.Item(i).Name
So Jason achieved his task by clicking on the single button of his custom application. He has added the CrystalDecisions.CrystalReports assemblies to his .Net project. If he was required to modify the reports, he would be required to use the CrystalDecisions.ReportAppServer assemblies. Perhaps next week Mr. Murphy will assign such a task to him.