Create Crystal Report with dynamic columns in C# and VB.Net

Hi nirmal90,

Using these articles i have created the example.

Basic Crystal Report Tutorial with example in Windows Forms (WinForms) Application using C# and VB.Net

Create Crystal Report with dynamic columns in ASP.Net using C# and VB.Net

Namespaces

C#

using System.Data;
using System.Data.SqlClient;
using System.Linq;
using CrystalDecisions.CrystalReports.Engine;

VB.Net

Imports System.Data
Imports System.Data.SqlClient
Imports System.Linq
Imports CrystalDecisions.CrystalReports.Engine

Code

C#

private void Form1_Load(object sender, EventArgs e)
{
    chkColumns.Items.Add("CustomerId", true);
    chkColumns.Items.Add("ContactName", true);
    chkColumns.Items.Add("City", true);
    chkColumns.Items.Add("Country", true);
    this.BindReport();
}

private Customers GetData(string query, ReportDocument crystalReport)
{
    string conString = @"Data Source=.\Sql2022;Initial Catalog=Northwind;uid=sa;pwd=pass@123";
    SqlCommand cmd = new SqlCommand(query);
    using (SqlConnection con = new SqlConnection(conString))
    {
        Customers dsCustomers = new Customers();
        cmd.Connection = con;
        con.Open();
        using (SqlDataReader sdr = cmd.ExecuteReader())
        {
            //Get the List of all TextObjects in Section2.
            List<TextObject> textObjects = crystalReport.ReportDefinition.Sections["Section2"].ReportObjects.OfType<TextObject>().ToList();
            for (int i = 0; i < textObjects.Count; i++)
            {
                //Set the name of Column in TextObject.
                textObjects[i].Text = string.Empty;
                if (sdr.FieldCount > i)
                {
                    textObjects[i].Text = sdr.GetName(i);
                }
            }
            //Load all the data rows in the Dataset.
            while (sdr.Read())
            {
                DataRow dr = dsCustomers.Tables[0].Rows.Add();
                for (int i = 0; i < sdr.FieldCount; i++)
                {
                    dr[i] = sdr[i];
                }
            }
        }
        con.Close();
        return dsCustomers;
    }
}

private void BindReport(string columnNames = "")
{
    string columns = !string.IsNullOrEmpty(columnNames) ? columnNames : "CustomerId, ContactName, City, Country";

    string query = string.Format("SELECT TOP 5 {0} FROM Customers", columns);
    ReportDocument crystalReport = new ReportDocument();
    crystalReport.Load(@"D:\CustomerReport.rpt");
    Customers dsCustomers = GetData(query, crystalReport);
    crystalReport.SetDataSource(dsCustomers);
    crystalReportViewer1.DisplayGroupTree = false;
    crystalReportViewer1.ReportSource = crystalReport;
    crystalReportViewer1.RefreshReport();
}

private void chkColumns_ItemCheck(object sender, ItemCheckEventArgs e)
{
    List<string> items = new List<string>();
    foreach (object column in chkColumns.CheckedItems)
    {
        items.Add(column.ToString());
    }

    if (e.NewValue == CheckState.Checked)
    {
        items.Add(chkColumns.GetItemText(chkColumns.Items[e.Index]));
    }
    else
    {
        items.Remove(chkColumns.GetItemText(chkColumns.Items[e.Index]));
    }
    this.BindReport(string.Join(", ", items));
}

VB.Net

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    chkColumns.Items.Add("CustomerId", True)
    chkColumns.Items.Add("ContactName", True)
    chkColumns.Items.Add("City", True)
    chkColumns.Items.Add("Country", True)
    Me.BindReport()
End Sub

Private Function GetData(query As String, crystalReport As ReportDocument) As Customers
    Dim conString As String = "Data Source=.\Sql2022;Initial Catalog=Northwind;uid=sa;pwd=pass@123"
    Dim cmd As New SqlCommand(query)
    Using con As New SqlConnection(conString)
        Dim dsCustomers As New Customers()
        cmd.Connection = con
        con.Open()
        Using sdr As SqlDataReader = cmd.ExecuteReader()
            'Get the List of all TextObjects in Section2.
            Dim textObjects As List(Of TextObject) = crystalReport.ReportDefinition.Sections("Section2").ReportObjects.OfType(Of TextObject)().ToList()
            For i As Integer = 0 To textObjects.Count - 1
                'Set the name of Column in TextObject.
                textObjects(i).Text = String.Empty
                If sdr.FieldCount > i Then
                    textObjects(i).Text = sdr.GetName(i)
                End If
            Next
            'Load all the data rows in the Dataset.
            While sdr.Read()
                Dim dr As DataRow = dsCustomers.Tables(0).Rows.Add()
                For i As Integer = 0 To sdr.FieldCount - 1
                    dr(i) = sdr(i)
                Next
            End While
        End Using
        con.Close()
        Return dsCustomers
    End Using
End Function

Private Sub BindReport(Optional columnNames As String = "")
    Dim columns As String = If(Not String.IsNullOrEmpty(columnNames), columnNames, "CustomerId, ContactName, City, Country")
    Dim query As String = String.Format("SELECT TOP 5 {0} FROM Customers", columns)
    Dim crystalReport As ReportDocument = New ReportDocument()
    crystalReport.Load("D:\CustomerReport.rpt")
    Dim dsCustomers As Customers = GetData(query, crystalReport)
    crystalReport.SetDataSource(dsCustomers)
    crystalReportViewer1.DisplayGroupTree = False
    crystalReportViewer1.ReportSource = crystalReport
    crystalReportViewer1.RefreshReport()
End Sub

Private Sub chkColumns_ItemCheck(sender As Object, e As ItemCheckEventArgs) Handles chkColumns.ItemCheck
    Dim items As List(Of String) = New List(Of String)()
    For Each column As Object In chkColumns.CheckedItems
        items.Add(column.ToString())
    Next
    If e.NewValue = CheckState.Checked Then
        items.Add(chkColumns.GetItemText(chkColumns.Items(e.Index)))
    Else
        items.Remove(chkColumns.GetItemText(chkColumns.Items(e.Index)))
    End If

    Me.BindReport(String.Join(", ", items))
End Sub

Screenshot