Dynamically generate a Crystal Report using C# 2.0 (.NET 2005)

Introduction

This program shows how to dynamically load data from a database and application into the Crystal Report. By using this program, we can customize a Crystal Report to some limit at run time of the application like specifying which field (Columns of a particular table) should be displayed in the report.

Background

I tried to find a solution for dynamically generate a Crystal Report using C# 2.0 (.NET 2005) by searching many forums and sites, but unfortunately I couldn't find any solution for that. Some forums said that there is no way to create dynamic Crystal Reports using .NET 2005. Finally, I found a way to do that. Using the Code

1. Create a C# project or add a Form to your existing project. Now you can add Checkboxes that correspond to columns of a particular table that should be displayed in the Crystal Report and CrystalReportViewer control to the form.

For this demonstration, I have created a database called db1.mdb (in bin\Debug) using Access and created a table called Customer.


2. Add a DataSet (*.xsd file) to your project using add -> New Items in solution explorer. After that, add a DataTable to the DataSet. Add columns to DataTable and name them Column1, Column2, and so on. The number of columns depends on how many columns should be displayed in the Crystal report.
3. Add a Crystal Report into the project and using the Report Wizard, choose ADO.NET DataSets of the Project data source as the data source of the Crystal Report and select Customer data table of DataSet1 as the selected table of the Crystal Report. Then select fields to be displayed in your report. Then remove Column1…, Column5 objects in Section 2 of the Crystal Report.

4. Now add parameters called col1, col2… col5 (the number of parameters should be equal to the number of columns displayed in the Crystal Report.) using Field Explorer.

5. Add the following method to your Form for Create SQL SELECT query and assign values to parameters of the Crystal Report according to user selected columns that should be displayed on your report.

6. ///

7. /// This method is used to
8. /// 1. create SELECT query according to the selected column names and
9. /// 2. create parameters and assign values for that parameter
10. /// that correspond to the crystal report.
11. /// NOTE: This parameter is used to display Column names of the
12. /// Crystal Report according to the user selection.
13. ///

14. ///
15. private string CreateSelectQueryAndParameters()
16. {
17. ReportDocument reportDocument;
18. ParameterFields paramFields;
19.
20. ParameterField paramField;
21. ParameterDiscreteValue paramDiscreteValue;
22.
23. reportDocument = new ReportDocument();
24. paramFields = new ParameterFields();
25.
26. string query = "SELECT ";
27. int columnNo = 0;
28.
29. if (chbCode.Checked)
30. {
31. columnNo++;
32. query = query.Insert(query.Length, "Code as Column" +
33. columnNo.ToString());
34.
35. paramField = new ParameterField();
36. paramField.Name = "col" + columnNo.ToString();
37. paramDiscreteValue = new ParameterDiscreteValue();
38. paramDiscreteValue.Value = "Customer Code";
39. paramField.CurrentValues.Add(paramDiscreteValue);
40. //Add the paramField to paramFields
41. paramFields.Add(paramField);
42. }
43. if (chbFirstName.Checked)
44. {
45. columnNo++;
46. if (query.Contains("Column"))
47. {
48. query = query.Insert(query.Length, ", ");
49. }
50. query = query.Insert(query.Length, "FirstName as Column" +
51. columnNo.ToString());
52.
53. paramField = new ParameterField();
54. paramField.Name = "col" + columnNo.ToString();
55. paramDiscreteValue = new ParameterDiscreteValue();
56. paramDiscreteValue.Value = "First Name";
57. paramField.CurrentValues.Add(paramDiscreteValue);
58. //Add the paramField to paramFields
59. paramFields.Add(paramField);
60. }
61. if (chbLastName.Checked)
62. {
63. columnNo++; //To determine Column number
64. if (query.Contains("Column"))
65. {
66. query = query.Insert(query.Length, ", ");
67. }
68. query = query.Insert(query.Length, "LastName as Column" +
69. columnNo.ToString());
70.
71. paramField = new ParameterField();
72. paramField.Name = "col" + columnNo.ToString();
73. paramDiscreteValue = new ParameterDiscreteValue();
74. paramDiscreteValue.Value = "Last Name";
75. paramField.CurrentValues.Add(paramDiscreteValue);
76. //Add the paramField to paramFields
77. paramFields.Add(paramField);
78. }
79. if (chbAddress.Checked)
80. {
81. columnNo++;
82. if (query.Contains("Column"))
83. {
84. query = query.Insert(query.Length, ", ");
85. }
86. query = query.Insert(query.Length, "Address as Column" +
87. columnNo.ToString());
88.
89. paramField = new ParameterField();
90. paramField.Name = "col" + columnNo.ToString();
91. paramDiscreteValue = new ParameterDiscreteValue();
92. paramDiscreteValue.Value = "Address";
93. paramField.CurrentValues.Add(paramDiscreteValue);
94. //Add the paramField to paramFields
95. paramFields.Add(paramField);
96. }
97. if (chbPhone.Checked)
98. {
99. columnNo++;
100. if (query.Contains("Column"))
101. {
102. query = query.Insert(query.Length, ", ");
103. }
104. query = query.Insert(query.Length, "Phone as Column" +
105. columnNo.ToString());
106.
107. paramField = new ParameterField();
108. paramField.Name = "col" + columnNo.ToString();
109. paramDiscreteValue = new ParameterDiscreteValue();
110. paramDiscreteValue.Value = "Phone";
111. paramField.CurrentValues.Add(paramDiscreteValue);
112. //Add the paramField to paramFields
113. paramFields.Add(paramField);
114. }
115.
116. //if there is any remaining parameter, assign empty value for that
117. //parameter.
118. for (int i = columnNo; i < 5; i++)
119. {
120. columnNo++;
121. paramField = new ParameterField();
122. paramField.Name = "col" + columnNo.ToString();
123. paramDiscreteValue = new ParameterDiscreteValue();
124. paramDiscreteValue.Value = "";
125. paramField.CurrentValues.Add(paramDiscreteValue);
126. //Add the paramField to paramFields
127. paramFields.Add(paramField);
128. }
129.
130. crystalReportViewer1.ParameterFieldInfo = paramFields; .
132. query += " FROM Customer" ;
133. return query;
134. } //
135. Add the following method to the button click event to display a report when the user presses the button: 136. using System;
137. using System.Collections.Generic;
138. using System.ComponentModel;
139. using System.Data;
140. using System.Drawing;
141. using System.Text;
142. using System.Windows.Forms;
143. using System.Data.OleDb;
144.
145. using CrystalDecisions.CrystalReports.Engine;
146. using CrystalDecisions.ReportSource;
147. using CrystalDecisions.Shared;
148. using CrystalDecisions.Windows.Forms;
149.
150. namespace app5
151. {
152. public partial class Form1 : Form
153. {
154. CrystalReport1 objRpt;
155.
156. public Form1()
157. { 158. InitializeComponent();
159. }
160.
161. private void button1_Click(object sender, EventArgs e)
162. {
163. objRpt = new CrystalReport1();
164.
165. string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
166. "Data Source=|DataDirectory|\\db1.mdb";
167.
168. //Get Select query String and add parameters to the
169. //Crystal report.
170. string query = CreateSelectQueryAndParameters();
171.
172. //if there is no item select, then exit from the method.
173. if (!query.Contains("Column"))
174. {
175. MessageBox.Show("No selection to display!");
176. return;
177. }
178.
179. try . {
181. OleDbConnection Conn = new OleDbConnection(connString);
182.
183. OleDbDataAdapter adepter =
184. new OleDbDataAdapter(query, connString);
185. DataSet1 Ds = new DataSet1();
186.
187. adepter.Fill(Ds, "Customer");
188.
189. objRpt.SetDataSource(Ds);
190. crystalReportViewer1.ReportSource = objRpt;
191. }
192. catch (OleDbException oleEx)
193. {
194. MessageBox.Show(oleEx.Message);
195. }
196. catch (Exception Ex)
197. {
198. MessageBox.Show(Ex.Message);
199. } }

Crystal Reports Features

Crystal Reports

Crystal Reports includes several new features for greater productivity and ease of use across all elements of the reporting process. For current customers, this release includes hundreds of improvements, enhancements, and hot fixes based on your feedback. Tighter integration with Crystal Enterprise is also a key focus area for version 10.

Data Access

Crystal Reports provides broad data connectivity options making it easy to access enterprise data and satisfy end user information requirements. Whether you require access to databases, files, logs, application systems (CRM, ERP, etc.), or program elements, you can use Crystal Reports.

More than 35 data drivers. Access virtually any data with native, ODBC, OLE DB, and JDBC connectivity to relational, OLAP, XML, legacy, and enterprise data sources including, Oracle, IBM DB2, Sybase, Microsoft SQL Server, and Informix.

Custom data. Access user-defined (in-memory) application data by connecting to JavaBeans, ADO.NET,and COM Data Providers.

Tight control over database connectivity. Write your own SQL commands or use the Crystal Reports proven query generation capabilities for flexible control over database connectivity.

Multi-data source access. Connect to a unique data source or combine multiple data sources in one report.

XML support. Integrate Crystal Reports with your B2B or B2C applications via XML data access or export.

Unicode support. Use unicode strings to display data stored in almost any language and present multiple languages in a single report.

Formatting and Design

Crystal Reports offers a high degree of flexibility and control over how data is presented and formatted.

Visual report designer. Quickly design interactive reports using an intuitive, drag-and-drop interface and object-oriented explorers.

Experts and wizards. Use optional experts and wizards to simplify common reporting tasks such as connecting to a data source, selecting, grouping, sorting, and summarizing data.

Any report type. Create virtually any report you need including cross-tab, conditional, top N/bottom N, summary/drill-down, form, mailing label, OLAP, and subreport.

Charting and mapping. Increase the graphical impact of your reports by choosing from a wide variety of map and chart types including bar/3D bar, pie/doughnut, line, gantt, gauge, funnel, XY scatter, ranged map, dot density map, and more.

Reusable report objects. Expedite the report design process by storing key report objects -- including text objects, SQL commands, bitmaps, and custom functions (formulas) -- in a central managed library (provided in Crystal Enterprise) for sharing, reuse, and single-point updating across multiple reports.

Customizable templates. Spend less time formatting individual reports. Design and apply customized templates that specify standards in formatting and logic -- including data access operations -- to ensure design consistency across reports.You can even use existing reports as templates.

Powerful formula language. Use the extensive formula language in Crystal Reports with over 160 built-in and user-defined functions and operators to tightly control report formatting, complex logic, and data selection. A call-stack makes debugging data-level errors easy. A formula extractor and formula workshop are also included.

Custom functions. Eliminate redundant formula creation. Extract business logic from formulas to create custom functions so you can use them across multiple reports.

Asymmetric reporting. Provide a customized view of OLAP grids. With asymmetric reporting, you can hide specific dimensions of data so that end users only see relevant and valuable data.

Access and Excel add-ins. Use an intuitive wizard to easily design a Crystal report right from within Microsoft Access or Excel.

Report Viewing and Interaction

Crystal Reports provides flexible options that let end users view and interact with information in familiar formats and via familiar environments.

Multiple export formats. Provide end users with reports in the format they prefer. Export reports to popular formats, including Excel, PDF, XML, HTML, RTF, and more.

Customizable report viewers. Choose from a variety of server-side and thin-client report viewer controls, including DHTML page (WebForms) viewer, .NET WinForms viewer, Java viewer, ActiveX, and Report Part viewer (mobile viewer), to render reports into various output formats without additional coding. End user report interactivity options -- including page forward/back, drill up/down, export, and print -- are automatically included and can be customized based on your requirements.

Parameters. Let users view the same data in different ways without creating multiple reports. End users can select predefined parameters in a single report to receive a subset of customized data.

Alerting. Highlight specific information for your end users with report alerts -- user-defined messages that appear when certain conditions are met by the data in a report. Use this feature with Crystal Enterprise so that alerts can be sent out to end users with direct links back to the original report.
Guided navigation. Define specific navigation paths between report objects within the same or different reports for easier end user navigation.

Hyperlinks. Use hyperlinks to turn reports into interactive web documents that connect to related information, including web sites and other reports.

Drill-down. Without additional coding, create reports that allow end users to drill down on relational and OLAP data to uncover details that might otherwise go unnoticed.

Report parts. Repurpose existing reports for use with wireless devices and corporate portals. Provide end users with access to charts, summary tables, specific records, and other key report information via portals, WML phones, RIM Blackberry, and Compaq iPAQ devices.

Support for Microsoft Office XP Smart Tags. Insert key report objects -- including charts and tables -- into Microsoft Outlook, Word, or Excel for instant viewing and refreshing from within an Office document.