Generating a PDF of a Crystal Report via code

January 31, 2008 in C# by Michael Chrisman 0 Comments

When I first started working on how to do this, I didn't think I would be writing a blog entry on it as I found so much information on-line (with sample code) on how to do this. As it turns out, I found the following problems: 1) most of the code is in VB (or VBA), 2) most examples assume you use a viewer object, and 3) I couldn't find anything on how to restrict a report to just one record when it only has parameters on the sub-report. After a lot of trial and error, I finally have a working solution.

My goal was to, in an automated solution, create a pdf version of a report. The biggest problem is that the report I was trying to run does not have any parameters on the main report, only on the sub-report, and you can set a parameter on a sub-report.

So, let's get started. The first step is to add references to two crystal report DLLs: CrystalDecisions.CrystalReports.Engine and CrystalDecisions.Shared. The following code is for connecting to a SQL server installation. For Oracle you will need to "google" a little to find the correct connection method. This the following example, assume that the report does not have any parameters on the main report. I was dealing with Crystal Report 9.2, but this should work with newer versions too.

This first section just sets up the report in memory.

   1:  //Create the report document object. this is the main Crystal Reports objects

   2:  ReportDocument crReportDocument;

   3:   

   4:  //Create an instance of a report

   5:  crReportDocument = new ReportDocument();

   6:  // and load the report

   7:  crReportDocument.Load("c:reportfoldermyreport.rpt");

Make sure that line 7 points to the actual location of the report.

Now we have to add the log on information to the report.

   1:  // Create a connection info object. this is used for access the database

   2:  ConnectionInfo crConnectionInfo = new ConnectionInfo ();

   3:  // Create a tableLogOnInfo object which will be applied to each table in the report

   4:  TableLogOnInfo crTableLogOnInfo;

   5:   

   6:  // setup the connectionInfo object with the connection data

   7:  crConnectionInfo.ServerName = "SystemDSN"; // this must be a System DSN on the box this app is running

   8:  crConnectionInfo.DatabaseName = "catalog";

   9:  crConnectionInfo.UserID = "Username";

  10:  crConnectionInfo.Password = "password";

I used a ConnectionInfo object so that it is easier to apply. On line 7, the ServerName value is really a System DSN name that is setup on the computer and points to the database. The Databasename is the SQL Server catalog that the tables are in.

The next thing we have to do is apply this connection data to each table in the report. This also includes each table in every sub report. The following code will programmatically apply this data to all tables on the report and all sub reports.

   1:  // get the report database object

   2:  Database myDatabase = crReportDocument.Database;

   3:  // now get the table collection

   4:  Tables myDatabaseTables = myDatabase.Tables;

   5:  // set the connection info for each table in teh base report

   6:  for (int i = 0; i < myDatabaseTables.Count; i++)

   7:  {

   8:      crTableLogOnInfo = myDatabaseTables.LogOnInfo;

   9:      crTableLogOnInfo.ConnectionInfo= crConnectionInfo;

  10:      myDatabaseTables.ApplyLogOnInfo(crTableLogOnInfo);

  11:  }

  12:   

  13:  //Now we have to set the connection Info for the tables in each sub report

  14:  // grab each section in the report

  15:  foreach (Section mySection in crReportDocument.ReportDefinition.Sections)

  16:  {

  17:      // now grab each Report Object in that section

  18:      foreach(ReportObject crReportObject in mySection.ReportObjects)

  19:      {

  20:          if(crReportObject.Kind == ReportObjectKind.SubreportObject)  // if the report object is a subreport

  21:          {

  22:              // get the database/table collection from the sub report

  23:              SubreportObject crSubreportObject = (SubreportObject) crReportObject;

  24:              ReportDocument crSubreportDocument = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName);

  25:              myDatabase = crSubreportDocument.Database;

  26:              myDatabaseTables = myDatabase.Tables;

  27:   

  28:              // add the connection info for each table in the sub report

  29:              for (int i = 0; i < myDatabaseTables.Count; i++)

  30:              {

  31:                  crTableLogOnInfo = myDatabaseTables.LogOnInfo;

  32:                  crTableLogOnInfo.ConnectionInfo = crConnectionInfo;

  33:                  myDatabaseTables.ApplyLogOnInfo(crTableLogOnInfo);

  34:              }

  35:          }

  36:      }

  37:  }