Learn how to use datasets to power Crystal Reports, without the need for a server-based database.
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.
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.
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
- Open Visual Studio and create a C# Windows Application.
- In the New Project dialog: Change the Name, Location, and Solution Name to that of your liking. For this article:
Create directory for solution is checked.
Add to Source Control is unchecked.
Click the Ok button.
In Solution Explorer, right-click the project name and choose the Add->New Folder.
Change the folder name to Reports.
Create two more folders and call one XmlData, and the other Datasets.
- Name: CrystalReportFeeder
- Location: Remains unchanged
- Solution Name: CrystalReportDemo
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.
- Right-click the Datasets folder in Solution Explorer.
- Select Add->New Item.
- In the Add New Item dialog, click the DataSet object.
- Change the dataset name to Products.xsd.
- 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:
- Right-click product_type dataset and select Add->Column a. Change the column name to type_id.
- In the Properties pane: AllowDBNull = False, DataType = System.Int32, Unique = True.
- Repeat Steps 1 and 2, adding two more columns, named type_name and description.
- 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:
- product_id, AllowDBNull = False, DataType = System.Int32, Unique = True
- product_name, AllowDBNull = True, DataType = System.String, Unique = False
- color, AllowDBNull = True, DataType = System.String, Unique = False
- size, AllowDBNull = True, DataType = System.String, Unique = False
- gender, AllowDBNull = True, DataType = System.String, Unique = False
- price, AllowDBNull = True, DataType = System.Single, Unique = False
- type_id, AllowDBNull = False, DataType = System.Int32, Unique = False
- product_class, AllowDBNull = True, DataType = System.String, Unique = False
- 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.
- In Solution Explorer, right-click the Reports folder.
- Select Add->Existing Item.
- 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,
- Double-click the Product Catalog.rpt file,
- Change the report name to ProductCatalog.rpt,
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:
- Double-click the ADO.NET Datasets tree item.
- Double-click the CrystalReportsFeeder.DataSets.Products tree item.
- Add the two data tables that appear.
- 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:
- Right-click a blank area of the report and select Report->Group Expert.
- In the Group Expert dialog, double-click product_class.
- In the Group Expert dialog, double-click type_name.
- In the Group Expert dialog, double-click product_name.
- Click OK.
- 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" ?>
Something you put on a man's upper body.
St. John's Bay Super Pique Polo Shirt
St. John's Bay Matchpoint Pique Polo Shirt
Something you put on a man's upper body for a special event.
New! Stafford Executive Cotton 80's 2-Ply Plaid
New! Stafford Executive Cotton Pinpoints -- Contra
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 =
DataSet ds = new DataSet();
// 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;
index = path.LastIndexOf("\\bin");
index = path.LastIndexOf("\\CrystalReportFeeder.exe");
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",
cat.Load(path + @"Reports\ProductCatalog.rpt");
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();
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.