Creating Crystal Reports Using the Push Method in .NET

In our development careers, many of us are and will be called upon to integrate either a stand-alone application or a web application to Crystal Reports. For must of these applications, you will not have access to a relational database where you could simply attach your report. So, there needs to be a way that you can display your data using Crystal Reports without the need for a dedicated database server.

Crystal Reports, like many other applications, connects to a database by way of a database driver, where each driver is designed to handle a specific database type or method of data access. With Crystal Reports, you can use one of two models of accessing data: "Pull" and "Push." These two models are built into the database drivers for Crystal Reports, giving the developer greater flexibility in their design approach for data access.

Pull Model

In this model, the report will connect to the database, using the parameters given to it a design time, to pull the data as needed. In this mode, all of the work is done by the report: connecting to the database and issuing the SQL commands; these are all handled without any coding from the developer. This is the default model of Crystal Reports.

Push Model

The push model involves code written by a developer that: creates and calls a connection on the database, executes the SQL commands, creates a recordset or dataset that matches the fields in the report, and then assignes the object to the report. This model also allows the shaping and filtering of data before it reaches the report.

Using the push model

In this article, I will lay out a simple solution that accepts XML data that can then be fed to a Crystal report. The method used in this article is called the "Push" model because the data is pushed to the report, as opposed to the data being retrieved by the report. The "Push" model opens the door for a multitude of ways for your application to accept data, converting it to XML if needed, and then displayed in a pretty report.

For this article, you will create a simple windows form application that reads XML data from a file, but you are free to use any data source you desire. The project will use Visual Studio 2005, and the code is written in C#.

You will modify an existing report that comes with Visual Studio, using your own dataset for a database. The report being used is the Product Catalog.rpt file that comes with the sample Crystal Decisions packaged with Visual Studio 2005.

Creating the Project

  1. Open Visual Studio and create a C# Windows Application.
  2. In the New Project dialog: Change the Name, Location, and Solution Name to that of your liking. For this article:
    • Name: CrystalReportFeeder
    • Location: Remains unchanged
    • Solution Name: CrystalReportDemo
  3. Create directory for solution is checked.
  4. Add to Source Control is unchecked.
  5. Click the Ok button.
  6. In Solution Explorer, right-click the project name and choose the Add->New Folder.
  7. Change the folder name to Reports.
  8. Create two more folders and call one XmlData, and the other Datasets.

Creating Your Dataset

Before you do anything else, you first need to create the dataset that will be used by Crystal Reports to read from. I like to build the dataset first, so that when it comes time to create the report file all you need to do is select the dataset as the data source.

  1. Right-click the Datasets folder in Solution Explorer.
  2. Select Add->New Item.
  3. In the Add New Item dialog, click the DataSet object.
  4. Change the dataset name to Products.xsd.
  5. Click the Add button.

On the newly created Products.xsd page, right-click and select Add->DataTable. Change the name of the DataTable to product_type. Then, add the following items to the table:

  1. Right-click product_type dataset and select Add->Column a. Change the column name to type_id.
  2. In the Properties pane: AllowDBNull = False, DataType = System.Int32, Unique = True.
  3. Repeat Steps 1 and 2, adding two more columns, named type_name and description.
  4. In the Properties pane for both columns: AllowDBNull = True, DataType = System.String, Unique = False.

You now have created a data table that specifies the types of products that can be reported on. Next, you will create the data table that contains the products. So, use the previous steps to create a table named product. In this new table, you will add the following columns:

  1. product_id, AllowDBNull = False, DataType = System.Int32, Unique = True
  2. product_name, AllowDBNull = True, DataType = System.String, Unique = False
  3. color, AllowDBNull = True, DataType = System.String, Unique = False
  4. size, AllowDBNull = True, DataType = System.String, Unique = False
  5. gender, AllowDBNull = True, DataType = System.String, Unique = False
  6. price, AllowDBNull = True, DataType = System.Single, Unique = False
  7. type_id, AllowDBNull = False, DataType = System.Int32, Unique = False
  8. product_class, AllowDBNull = True, DataType = System.String, Unique = False
  9. supplier_id, AllowDBNull = False, DataType = System.Int32, Unique = False

Creating Your Report

Now to create the report that will consume your dataset. Please note that we will have to modify the report for this all to work.

  1. In Solution Explorer, right-click the Reports folder.
  2. Select Add->Existing Item.
  3. In the Add Existing Item dialog, navigate to the location where you installed Microsoft Visual Studio and locate the Crystal Reports sample directory. For my systemm it is: C:\Program Files\Microsoft Visual Studio 8\Crystal Reports\Samples\en\Reports\General Business,
  4. Double-click the Product Catalog.rpt file,
  5. Change the report name to ProductCatalog.rpt,

Creating Crystal Reports Using the Push Method in .NET

So, with the report open for design, go to the Field Explorer and right-click the Database Fields tree item. Then, in the Database Expert dialog, delete the Products database that is currently set for the report. This will delete all of the fields on the report, but that is okay because you will recreate them in a minute.

Now, double-click the Project Data item in the left-hand pane of the Database Expert dialog:

  1. Double-click the ADO.NET Datasets tree item.
  2. Double-click the CrystalReportsFeeder.DataSets.Products tree item.
  3. Add the two data tables that appear.
  4. Click OK.

At this point, you now need to add the report fields. Below is an illustration of how I modified the report.

First, you need to add all of the groupings:

  1. Right-click a blank area of the report and select Report->Group Expert.
  2. In the Group Expert dialog, double-click product_class.
  3. In the Group Expert dialog, double-click type_name.
  4. In the Group Expert dialog, double-click product_name.
  5. Click OK.
  6. Add all of the fields as you see them in the report above.

Creating Data to Report on

Now, you need to create some XML data that will be used to feed your dataset and report. In this article, I created some sample data that you are free to add to as your heart desires. Following is a sample of the XML file that was created under the XmlData folder in your project; the file is named Products.xml:

<?xml version="1.0" encoding="utf-8" ?>
   <data>
      <product_type>
         <type_id>1</type_id>
         <type_name>Casual Shirts</type_name>
         <description>
            Something you put on a man's upper body.
         </description>
         <product>
            <product_id>1</product_id>
            <product_name>
               St. John's Bay Super Pique Polo Shirt
            </product_name>
            <color>Red</color>
            <size>Regular</size>
            <gender>Male</gender>
            <price>19.99</price>
            <type_id>1</type_id>
            <product_class<Polo Shirts</product_class>
            <supplier_id>1</supplier_id>
         </product>
         <product>
            <product_id>2</product_id>
            <product_name>
               St. John's Bay Matchpoint Pique Polo Shirt
            </product_name>
            <color>Red</color>
            <size>Regular</size>
            <gender>Male</gender>
            <price>29.99</price>
            <type_id>1</type_id>
            <product_class>Polo Shirts</product_class>
            <supplier_id>1</supplier_id>
         </product>
      </product_type>
      <product_type>
         <type_id>2</type_id>
         <type_name>Dress Shirts</type_name>
         <description>
            Something you put on a man's upper body for a special event.
         </description>
         <product>
            <product_id>3</product_id>
            <product_name>
               New! Stafford Executive Cotton 80's 2-Ply Plaid
            </product_name>
            <color>FrenchBlue Plaid</color>
            <size>Athletic</size>
            <gender>Male</gender>
            <price>39.99</price>
            <type_id>2</type_id>
            <product_class>All Cotton</product_class>
            <supplier_id>2</supplier_id>
         </product>
         <product>
            <product_id>4</product_id>
            <product_name>
               New! Stafford Executive Cotton Pinpoints -- Contra
            </product_name>
            <color>Black Stripe</color>
            <size>Regular</size>
            <gender>Male</gender>
            <price>28.99</price>
            <type_id>2</type_id>
            <product_class>All Cotton</product_class>
            <supplier_id>2</supplier_id>
         </product> 
      </product_type>
   </data>

Creating Crystal Reports Using the Push Method in .NET

Creating a Form to View the Report

In Solution Explorer, right-click the project name and select Add->Windows Form. Change the form name to ReportViewer.cs and click Add. From the Toolbox, drag a CrystalReportViewer control onto the ReportViewer form. Select the ReportViewer form and, from the Properties pane, select the Events view.

Find the Load event and add OnFormLoad as the name of the event. In the OnFormLoad method add the following code:

private void OnFormLoad(object sender, EventArgs e)
{
   Reports.ProductCatalog cat =
      new CrystalReportsFeeder.Reports.ProductCatalog();
   DataSet ds = new DataSet();
   string path;
   int index;

   // Make all of the magic happen here
   // Find out where we are so that we can load our files
   //   - Get this assemblies location
   //   - Strip away this file name and debug/Release directories
   path = Application.StartupPath;
#if DEBUG
   index = path.LastIndexOf("\\bin");
#else
   index = path.LastIndexOf("\\CrystalReportFeeder.exe");
#endif
   path = path.Substring(0, index + 1);

   // Get the database definition for this report
   ds.ReadXmlSchema(path + @"DataSets\Products.xsd");
   ds.ReadXml(path + @"XmlData\Products.xml",
              XmlReadMode.InferSchema);
   cat.Load(path + @"Reports\ProductCatalog.rpt");
   cat.SetDataSource(ds);
   crystalReportViewer1.ReportSource = cat;
}

Creating a Form to Run From

Now that you have all of the major plumbing set, it is time to create the button that sets this all in motion. In Solution Explorer, double-click the Form1 form that was created when you created the project. From the Toolbox, drag a button onto the form. Double-click the newly added button and add the following code:

private void button1_Click(object sender, EventArgs e)
{
   ReportViewer viewer = new ReportViewer();

   viewer.ShowDialog();
}

Now, run the program.

There you have it, a nice and simple way of getting a report by way of datasets, connecting to any kind of data source that you organization may have.



About the Author

Kelvin McKisic

Full-time Baptist minister who has worked in the Hardware and Software fields for over 20 years. Looking to do more in the field of technolgy for the Lord.

Downloads

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Download the Information Governance Survey Benchmark Report to gain insights that can help you further establish business value in your Records and Information Management (RIM) program and across your entire organization. Discover how your peers in the industry are dealing with this evolving information lifecycle management environment and uncover key insights such as: 87% of organizations surveyed have a RIM program in place 8% measure compliance 64% cannot get employees to "let go" of information for …

  • Live Event Date: April 22, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT Database professionals — whether developers or DBAs — can often save valuable time by learning to get the most from their new or existing productivity tools. Whether you're responsible for managing database projects, performing database health checks and reporting, analyzing code, or measuring software engineering metrics, it's likely you're not taking advantage of some of the lesser-known features of Toad from Dell. Attend this live …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds