Dynamic Crystal Reports from C# Application

Usually Crystal Reports generating with pre-defined columns . That is, the fields showing in Crystal Reports are selecting through a wizard at the time of Crystal Report design.

The following program describes how to generate a Crystal Report in C# with dynamic columns at the time of report generation . That is, we are not selecting any column from database table at the time of Crystal Report design. Instead of that we are passing an SQl query string and get the Crystal Report dynamically at runtime in C# . From the following picture you can understand how it works.

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.

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.

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

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;

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.