Designing and Integrating Crystal Reports

• Download CrystalReportExample.zip - 65.6 KB

Introduction

I recently developed a reporting module for a project , where all the reports needed to be converted to crystal reports. I worked with various push / pull models and did some research. The problem I had was I wanted to remove the exclusive database dependency with crystal reports i.e. "If the application can access the database so should crystal reports". The best way i found was through a dataset schema. There are various ways of doing it , I would point out to a simple one with which you can do it.

I would adopt a step by step approach which I myself find easier to follow instead of excerpts.

Including a Crystal Report in your Project

For including a crystal report to your project , right click the project and choose add new item and then select crystal report. Name the crystal report whatever you want e.g "Accounts Detail Report" . For simplicity I would call my report "Report1" in this sample application. The process is shown in the images below.

From the pop up dialogue select crystal report as shown in the figure below:

When the report is added it immediately wants to know what kind of report you want to add in your project. For this method say "A blank report" as shown in the figure below:

Now the report is included in your project and the Solution explorer shows it in the project files. In the background Visual Studio generates a class with the same name as the report but we don't need to worry about any of it.

Designing the Report:

For designing the report , the crystal report need to know what data it has to display. Therefore we need to specify the data which we would be giving to the report as input. Normally when we talk of reports usually there is a datagrid or an existing report with some other format having datasets as their input. With dataset we can get the schema for data by simply calling the "WriteXMLSchema" this is required only once to design the report and can be commented or removed from code later or as an alternative and xsd can be added and configured.The sample application that I have included gets the dataset from a database called Books executes a query to get data and populate the data in a dataset.Then I call the "WriteXMLSchema" method to get the Schema for the dataset.

try

{

// getting the dataset

string connString = System.Configuration.ConfigurationSettings.AppSettings.Get("ConnectionString");

string query = "Select * from book";

conn = new OleDbConnection(connString);

conn.Open();

da = new OleDbDataAdapter(query, conn);

ds = new DataSet();

da.Fill(ds);

// writing schema of dataset to a file

// This should be commented once the report is designed

//ds.WriteXmlSchema(@"C:\reportSchema.xml"); 



// Crystal report code here

Report1 rpt1 = new Report1();

rpt1.SetDataSource(ds);

crystalReportViewer1.ReportSource = rpt1;



}

catch (Exception ex)

{

MessageBox.Show("Error in report" + ex.Message);

}

finally

{

conn.Close();

}

Once you have the schema simply comment the "WriteXMLSchema" and double click the report in the solution explorer. Now we need to specify crystal report the dataset that we would be passing to it .This is shown in the figure below:

For that ,Right click on the top most field on the Field Explorer "Database Expert". From the pop up expand "Create New Connection" and then expand "ADO.NET ". A dialogue box pops up asking for a file path , simply browse to the file where XML Schema was saved with WriteXMLSchema similar to whats shown in the figure below:

The Database expert reads the dataset schema and shows a dataset in the list of available tables.

Select the "table" and add it to selected tables and hit ok.The database Expert now shows all the fields that were included in the dataset.Simply drag the fields you want to show on your report in the "Details" section for recurring records and crystal reports would generate a heading for that automatically.You can rename or modify it by right clicking and select format object.

Finalize designing the report and add any other special fields by right clicking the report and selecting "Add special fields" for fields like print date , time , page numbers , headers , footers . When finished close the report and save your changes and close it.

Passing Data To Crystal Report:

To view the crystal report on a form we should have the crystal report viewer Control which is available under the crystal reports section the toolbox. If you drag it on the form this would include all the references that you want to run the crystal report. So just drag and drop a crystal report viewer control and set the properties.The dataset with which we generated the xml should now be passed to this report . this can be done by instantiating the Crystal Report object (The class which visual studio generates for you with the same name as the report) and passing in the dataset and setting the report source property as shown in the snippet below:

Report1 rpt1 = new Report1();

rpt1.SetDataSource(ds);

crystalReportViewer1.ReportSource = rpt1;

Using the Sample Code and Test Application:

I have included the database script , in addition to complete code and solution files for the project which you can run on SQL Server 2005 or 2000 to create the database. You can configure the connection string in the app.config with your setting and DB information

Summary:

There are some goods and bads with this method. Good part is it is very easy and quick to develop design and gives a clean implementation with datasets , so you can really focus on the architecture and working of components , the biggest problem is when the report has to change and you have to include more fields , this takes longer as u have to add it to your dataset , and design the report again instead of just adding a single field.If you have any other way of doing it I would love to hear from you do give me your feedback or feel free to discuss in case of any issues.