Crystal Reports helper class

Introduction

Integrating Crystal Reports in a .NET application can be a real challenge. There are a number of things you need to know to successfully run reports from inside your application. The helper class described in this article offers a number of methods that can be useful when you want to integrate Crystal Reports in your .NET application. Integrating Crystal Reports will be a lot easier and faster when you use this helper class.

The helper class contains methods that will help you to:

• Assign database connections or DataSet objects to a report
• Print a report from code in a WinForms application
• Export a report from code to disk in a WinForms application
• Export a report to an ASP.NET form.
• Download the report to an internet client from inside an ASP.NET application.
• Tweak the way the Crystal Reports viewer presents itself in a Windows Forms application.
• Assign values to the parameter fields.

The demo solution, which you can download, demonstrates a lot of the features of this class. You will need SQL Server 2000 with the Pubs and NorthWind database to use it.

Background

I started building this helper class a few years back, when I had to integrate Crystal Reports in an Windows GUI application. I had to solve the following problems:

• Assign DataSet objects to a report.
• Assign a database connection to a report.
• Export the report to an Adobe PDF file.
• Integrate the Crystal Reports viewer in the application.

The helper class was originally designed for use with .NET 1.1 applications. Visual Studio 2003 came with a trimmed down version of Crystal Reports 9, which was called Crystal for .NET. At this moment, Visual Studio 2005 comes with an updated version which is based on Crystal Reports 10.

Common actions handled by the CrystalHelper class

Assign a connection to a report

One of the problems most developers face when integrating Crystal Reports is getting it to work on another system. This problem is caused by the fact that Crystal requires a valid database connection. When you develop a report, this is usually a connection to your development database. But, when you deploy a report, you will need to assign the correct database connection to each table in your report. The following code shows how this can be done:

Hide   Copy Code

ConnectionInfo connection = new ConnectionInfo();

connection.DatabaseName = "DatebaseName";
connection.ServerName   = "ServerName";
connection.UserID       = "UserId";
connection.Password     = "Password";

// First we assign the connection to all tables in the main report
//
foreach (CrystalDecisions.CrystalReports.Engine.Table 
         table in _reportDocument.Database.Tables)
{
    // Cache the logon info block
    TableLogOnInfo logOnInfo = table.LogOnInfo;

    // Set the connection
    logOnInfo.ConnectionInfo = connection;

    // Apply the connection to the table!
    table.ApplyLogOnInfo(logOnInfo);
}

If you have one or more subreports, then it gets even more complex. You will need to perform the above action for each of the subreports. To do this, you will need to check all sections in the report, and then assign the connection info again to all tables in each subreport. For example:

Hide   Copy Code

foreach (CrystalDecisions.CrystalReports.Engine.Section 
         section in _reportDocument.ReportDefinition.Sections)
{
    // In each section we need to loop through all the reporting objects
    foreach (CrystalDecisions.CrystalReports.Engine.ReportObject 
             reportObject in section.ReportObjects)
    {
        if (reportObject.Kind == ReportObjectKind.SubreportObject)
        {
            SubreportObject subReport = (SubreportObject)reportObject;
            ReportDocument  subDocument = 
                            subReport.OpenSubreport(subReport.SubreportName);

            foreach (CrystalDecisions.CrystalReports.Engine.Table 
                     table in subDocument.Database.Tables)
            {
                // Cache the logon info block
                TableLogOnInfo logOnInfo = table.LogOnInfo;

                // Set the connection
                logOnInfo.ConnectionInfo = connection;

                // Apply the connection to the table!
                table.ApplyLogOnInfo(logOnInfo);
            }
        }
    }
}

The two code sections above are handled by the Open() method in the CrystalHelper class.

Assign a DataSet to a report

A similar problem occurs when you want to assign a DataSet to a report. The DataSet must be assigned not just to the report, but also to all subreports. The code to do this will look like this:

Hide   Copy Code

// Now assign the dataset to all tables in the main report
//
_reportDocument.SetDataSource(dsReportData);

// Now loop through all the sections
// and its objects to do the same for the subreports
//
foreach (CrystalDecisions.CrystalReports.Engine.Section section 
         in _reportDocument.ReportDefinition.Sections)
{
    // In each section we need to loop through all the reporting objects
    foreach (CrystalDecisions.CrystalReports.Engine.ReportObject 
             reportObject in section.ReportObjects)
    {
        if (reportObject.Kind == ReportObjectKind.SubreportObject)
        {
            SubreportObject subReport = (SubreportObject)reportObject;
            ReportDocument  subDocument = 
               subReport.OpenSubreport(subReport.SubreportName);

            subDocument.SetDataSource(dsReportData);
        }
    }
}

This code is also part of the Open() method in this CrystalHelper class.

Using the code to integrate a report

Print report

The first code sample shows how you can print a report when the report is an embedded resource and the data is stored in a DataSet:

Hide   Copy Code

private void PrintReport(SqlConnection connection)
{
    using (DataSet ds = new TestData())
    {
        SqlHelper.FillDataset(connection,
            CommandType.Text, "SELECT * FROM Customers", ds, new string [] {"Customers"});
    
        using (CrystalHelper helper = new CrystalHelper(new TestReport()))
        {
            helper.DataSource = ds;
            helper.Open();
            helper.Print();
            helper.Close();
        }
    }                                      
}

As you can see, in this example, the code to handle the report is very simple and straightforward. The Open()method will ensure the DataSet is assigned to all sections.

When you use embedded queries in your report, then you will need to assign the database connection instead of assigning a DataSet as in the example above. The code would then look like this:

Hide   Copy Code

private void PrintReport()
{ 
    using (LCrystalHelper helper = new LCrystalHelper(new TestReport()))
    {
        helper.DatabaseName = "DatabaseName";
        helper.ServerName   = "ServerName";
        helper.UserId       = "User";
        helper.Password     = "Password";
                    
        helper.Open();
        helper.Print();
        helper.Close();
    }                                      
}

Again, you see that using Crystal Reports with this helper class makes integrating reports very simple. In the example above, I used a fully qualified connection with a valid user ID and password. The version of Crystal Reports that is included with Visual Studio 2005 also supports integrated security. For that purpose, the CrystalHelper class also features a property IntegratedSecurity. This property defaults to false, but setting it to true will allow you to use integrated security, as show in this example:

Hide   Copy Code

private void PrintReport()
{ 
    using (LCrystalHelper helper = new LCrystalHelper(new TestReport()))
    {
        helper.DatabaseName = "DatabaseName";
        helper.ServerName   = "ServerName";
        helper.IntegratedSecurity = true;
                    
        helper.Open();
        helper.Print();
        helper.Close();
    }                                      
}

If the report is included in your solution as a content file, rather than an embedded resource, then you can replace this line:

Hide   Copy Code

using (CrystalHelper helper = new CrystalHelper(new TestReport()))

with this line:

Hide   Copy Code

using (CrystalHelper helper = new CrystalHelper(@"C:\ReportLocation\ReportFile.rpt"))

Export a report

Exporting a report can be done to the following Export formats:

• Word (*.doc)
• Excel (*.xls)
• Rich text (*.rtf)
• Portable Doc Format (*.pdf)

The helper class features four methods to perform an export. The first two simply export the file to a specified location. These two methods are useful for WinForms applications, or when you need to export files to a server in an ASP.NET environment. The first of these has a file name as an argument. The type of export is determined by checking the extension given to the file:

Hide   Copy Code

helper.Export(@"C:\ExportLocation\MyFile.pdf");

The second method also allows you to specify the Export format as an argument:

Hide   Copy Code

helper.Export(@"C:\ExportLocation\MyFile.pdf", CrystalExportFormat.PortableDocFormat);

When you use these methods in an ASP.NET application, you will probably want the files to be exported to the client. For this purpose, the helper class implements two methods which require you to pass the HttpResponseobject. The first of these methods will simply write the exported file to the response:

Hide   Copy Code

helper.Export(Response, CrystalExportFormat.PortableDocFormat);

This will result in the report being shown inside the client internet browser, provided the application that supports the export format is installed. The second method allows you to specify if the export has to be sent as an attachment, in which case you can specify the file name for the export. The user will be shown a dialog in which he/she can specify a download location.

Hide   Copy Code

helper.Export(Response, CrystalExportFormat.PortableDocFormat, 
              true, "AnExportedDocument.pdf");

Showing the report in the Crystal Reports viewer control

The last option to show a report is using the Crystal Reports viewer control. Assuming you have the control on your form (Windows or ASP.NET), you can do the following:

Hide   Copy Code

private void PrintReport(SqlConnection connection)
{
    using (DataSet ds = new TestData())
    {
        SqlHelper.FillDataset(connection, 
            CommandType.Text, "SELECT * FROM Customers", 
                              ds, new string [] {"Customers"});
    
        CrystalHelper helper = new CrystalHelper(@"C:\ReportLocation\ReportFile.rpt");
        helper.DataSource = ds;
        helper.Open();
        crystalReportViewer1.ReportSource = _crystalHelper.ReportSource;
    }                                      
}

Setting the values for report parameters

To set a value for a parameter, you would normally need to write about 6 lines of code. And, you will need to know when to do this. Normally, just before assigning the database connection. The helper class also assists you in setting values for the report parameter fields by providing the SetParameter method. The class will keep an internal list of all the parameters you wish to set, and will apply the values at the right time. The following example shows you how you can use this method:

Hide   Copy Code

using (CrystalHelper hlp = new CrystalHelper())
{
    _crystalHelper.SetParameter("CategoryId", "Beverages");

    _crystalHelper.ServerName   = "localhost";
    _crystalHelper.DatabaseName = "Pubs";
    _crystalHelper.UserId       = "UserId";
    _crystalHelper.Password     = "Password";

    _crystalHelper.ReportSource = new TestReport();

    _crystalHelper.Open();
    _crystalHelper.Export("C:\\Test.pdf", 
                          CrystalExportFormat.PortableDocFormat);
    _crystalHelper.Close();
}

The demo solution which you can download here also demonstrates this feature.

Tweaking the Crystal Reports viewer control

Change the name of a tab

When the report is shown, the viewer will always show a tab. This is a very useful feature when you have subreports or drill-down features in your report. The only thing is that the name of the tab is always "Main Report" or the name of your subreport. Changing the name of a tab is easily done by making the following call:

Hide   Copy Code

CrystalHelper.ReplaceReportName(crystalReportViewer1, 
             "MainReport", "My name for the report");

The first argument is the viewer control. The second is always the current name of the tab. The last argument is the new name for the tab. You need to be aware though, that changing the names of the tabs is a bit tricky. First of all, you are only sure that the first tab (MainReport) is always there. The other tabs are only shown when a user clicks to enter a subreport or drill-down section. You will need to respond to the events thrown by the viewer to make sure the names of the tabs are adjusted when a new tab is made visible.

Hide the tabs

If you want to hide the tabs, then you can simply use this call:

Hide   Copy Code

CrystalHelper.ViewerTabs(crystalReportViewer1, false);

This is of course reversible by calling the same method with true as the second argument.

Hide the statusbar

The report viewer also shows a status bar. This can be hidden by using the following call:

Hide   Copy Code

CrystalHelper.ViewerStatusBar(crystalReportViewer1, false);

This is of course reversible by calling the same method with true as the second argument.

Valuable resources

A lot of the information I needed to build this helper class was found on the Internet. I found the following resources to be very useful when you need to do something with Crystal Reports:

• Crystal knowledge base at Business Objects
• Forums section on BusinessObjects.com
• Crystal Reports forum at Microsoft Forums

I strongly recommend anyone who wants to learn more about Crystal Reports to visit these links. I find most of the answers to questions related to Crystal Reports on one of the above locations.

Source : https://www.codeproject.com/Articles/15024/Crystal-Reports-helper-class