Dynamic Crystal Reports from C# Application

Typically, Crystal Reports are generated with predetermined columns. In other words, during the Crystal Report design phase, the fields to be displayed are selected using a wizard.

The subsequent program elucidates the process of generating a dynamic Crystal Report in C# with variable columns during the report generation stage. Instead of selecting specific columns from a database table during the Crystal Report design phase, we employ an SQL query string. This approach allows us to obtain the Crystal Report dynamically at runtime in C#. To gain a better understanding of this process, please refer to the accompanying visual representation provided below.

csharp-crystal-report-dynamic-column

All C# Crystal Reports Tutorial in this website is based on the following database - crystaldb. So before you begin this section , please take a look at the database structure of crystaldb - Click Here C# crystaldb

If you are new to Crystal Reports and do not know how to create Crystal Reports from C# , please take a look at the section step by step tutorial for creating a Crystal Reports from C#.

Here we are going to create a Dynamic Crystal Report with the help of Strongly Typed Dataset. Create a new CSharp project and add a Strongly Typed Dataset in the C# Project . Before creating a Strongly Typed Dataset take a look at the detailed tutorial of how to create a C# Crystal Report from Strongly Typed Dataset of previous section .

Hope you understand how to create a C# Crystal Report from Strongly Typed Dataset. Create a new C# project and add a Strongly Typed Datset . Add five columns in the DataTable of Strongly Typed Dataset. Here we are limiting as five column , but you can add any number of column according to your reports requirements.

csharp-crystal-dynamic-column

Next step is to create a Crystal Reports design from the Strongly Typed dataset. You have to add a Crystal Report in your project and select data source as Strongly Typed Dataset. If you don't know how to do this section , refer the previous section of How to create a C# Crystal Report from Strongly Typed Dataset.

csharp-crystal-report-dynamic-table

Select all the column (five) from Strongly Typed Dataset .

csharp-crystal-report-dynamic-column-selection

Click finish button . Then you can see the selected fields in the Crystal Repots . Arrange the fields according to your requirement . Now the designing part is over and the next step is to call the Crystal Reports in CSharp and view it in Crystal Reports Viewer control .

Select the default form (Form1.cs) you created in CSharp and drag a Textbox , button and CrystalReportViewer control to your form (like in the first picture ).

Here we are going to pass the SQl statements to Crystal Reports at runtime from C# program . For that we have to parse the SQL statement before we passing it to Crystal Reports. So we create a function for parsing SQL statements in the C# program.

Public Function procesSQL() As String

You have to include CrystalDecisions.CrystalReports.Engine in your C# Source Code.

using CrystalDecisions.CrystalReports.Engine; using CrystalDecisions.Shared;

Copy and paste the following source code and run your C# project.

Full Source C#
using System; using System.Windows.Forms; using CrystalDecisions.CrystalReports.Engine; using CrystalDecisions.Shared; using System.Data; using System.Data.SqlClient ; namespace WindowsApplication1 { public partial class Form1 : Form { CrystalReport1 objRpt = new CrystalReport1(); public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { SqlConnection cnn ; string connectionString = null; string sql = null; connectionString = "data source=SERVER NAME;initial catalog=crystaldb;user id=USER NAME;password=PASSWORD;"; cnn = new SqlConnection(connectionString); cnn.Open(); sql = procesSQL(); SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn); DataSet1 ds = new DataSet1(); dscmd.Fill(ds, "Product"); objRpt.SetDataSource(ds.Tables[1]); crystalReportViewer1.ReportSource = objRpt; crystalReportViewer1.Refresh(); } public string procesSQL() { string sql = null; string inSql = null; string firstPart = null; string lastPart = null; int selectStart = 0; int fromStart = 0; string[] fields = null; string[] sep = { "," }; int i = 0; TextObject MyText ; inSql = textBox1.Text; inSql = inSql.ToUpper(); selectStart = inSql.IndexOf("SELECT"); fromStart = inSql.IndexOf("FROM"); selectStart = selectStart + 6; firstPart = inSql.Substring(selectStart, (fromStart - selectStart)); lastPart = inSql.Substring(fromStart, inSql.Length - fromStart); fields = firstPart.Split(','); firstPart = ""; for (i = 0; i <= fields.Length - 1; i++) { if (i > 0) { firstPart = firstPart + ", " + fields[i].ToString() + " AS COLUMN" + (i + 1); firstPart.Trim(); MyText = (TextObject) objRpt.ReportDefinition.ReportObjects[i+1]; MyText.Text = fields[i].ToString(); } else { firstPart = firstPart + fields[i].ToString() + " AS COLUMN" + (i + 1); firstPart.Trim(); MyText = (TextObject)objRpt.ReportDefinition.ReportObjects[i+1]; MyText.Text = fields[i].ToString(); } } sql = "SELECT " + firstPart + " " + lastPart; return sql; } } }

connectionString = "data source=SERVER NAME;initial catalog=crystaldb;user id=USER NAME;password=PASSWORD;";

You have to provide the necessary database information to Connection String.