In this article I will explain how to create Crystal Report with dynamic columns in ASP.Net using C# and VB.Net.
The idea is to connect Crystal Report with a Generic Typed DataSet and then programmatically show hide dynamic columns in Crystal Report in ASP.Net.
Database
Here I am making use of Microsoft’s Northwind Database. You can download it from here
1. Add Typed DataSet to the ASP.Net Website
Since I am using disconnected Crystal Reports we will make use of Typed DataSet to populate the Crystal Reports with data from database.
The use of Typed DataSet helps us avoid direct connection of Crystal Report with database, which is very necessary for building a Crystal Report with dynamic columns.
![Create Crystal Report with dynamic columns in ASP.Net using C# and VB.Net]()
2. Adding DataTable to the Typed DataSet
Our next step would be to add a DataTable to the Type DataSet.
![Create Crystal Report with dynamic columns in ASP.Net using C# and VB.Net]()
3. Adding Generic Columns or fields to DataTable
In the DataTable you will need to add as many columns as you need. For this article I am adding 5 columns DataColumn1 to DataColumn5. These generic columns will be used to fetch data from any column of any table.
Note: Since we are adding generic columns, the data type of all columns has to be of String type so that it works for all columns of different data types.
![Create Crystal Report with dynamic columns in ASP.Net using C# and VB.Net]()
4. Add Crystal Report to the ASP.Net Website
Now you will need to add a Crystal Report to the ASP.Net Application. You can give it name as per your choice.
![Create Crystal Report with dynamic columns in ASP.Net using C# and VB.Net]()
As soon as you click OK you get the following dialog. You must select Using the Report Wizard option.
![Create Crystal Report with dynamic columns in ASP.Net using C# and VB.Net]()
Once you press OK in the above dialog, the Report Wizard starts and you get the following dialog where you need to choose the type of Database connection for your Crystal Report. Since we are using DataSet we will choose the Customers DataSet.
![Create Crystal Report with dynamic columns in ASP.Net using C# and VB.Net]()
Next the Wizard will ask for the Columns or Fields from the DataSet you need to display on the Crystal Reports.
Note: You need to choose all columns as we will show and hide Columns in Crystal Report dynamically from the code.
![Create Crystal Report with dynamic columns in ASP.Net using C# and VB.Net]()
Note: There are more steps in the Wizards but those are Optional hence are not included in this article.
Once you click Finish your Crystal Report should look as below. Here the Section 2 acts as the Header and Section 3 will records from the DataTable.
![Create Crystal Report with dynamic columns in ASP.Net using C# and VB.Net]()
All the elements in the Section 2 are Crystal Report TextObject. The Text of these TextObjects will be replaced with the actual names of the Columns present in the Recordset returned by the SQL Query.
![Create Crystal Report with dynamic columns in ASP.Net using C# and VB.Net]()
HTML Markup
The HTML Markup consists of an ASP.Net CheckBoxList containing names of 5 columns of the Customers Table and a CrystalReportViewer control.
Columns:
<asp:CheckBoxList ID="chkColumns" runat="server" RepeatDirection="Horizontal" AutoPostBack="true"
OnSelectedIndexChanged="Columns_Changed">
<asp:ListItem Text="CustomerId" Value="CustomerId" Selected="True"></asp:ListItem>
<asp:ListItem Text="ContactName" Value="ContactName" Selected="True"></asp:ListItem>
<asp:ListItem Text="City" Value="City" Selected="True"></asp:ListItem>
<asp:ListItem Text="Country" Value="Country" Selected="True"></asp:ListItem>
<asp:ListItem Text="PostalCode" Value="PostalCode" Selected="True"></asp:ListItem>
</asp:CheckBoxList>
<hr />
<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true"
Height="400" Width="580" BestFitPage="False" />
Namespaces
You will need to import the following namespaces.
C#
using System.Data;
using System.Linq;
using System.Configuration;
using System.Data.SqlClient;
using CrystalDecisions.CrystalReports.Engine;
VB.Net
Imports System.Data
Imports System.Linq
Imports System.Configuration
Imports System.Data.SqlClient
Imports CrystalDecisions.CrystalReports.Engine
Create Crystal Report with dynamic columns in ASP.Net
I am populating Crystal Reports from database in the Page Load event of the page using the BindReport function.
This function build a dynamic query based on the selections of the CheckBoxList and then passes the SQL Query to the GetData function along with one more parameter i.e. Crystal Report Document.
Inside the GetData function, the records are fetched from the database using SqlDataReader. Once the results are available first a loop is executed over the fields returned and then the names of the Columns are set in the corresponding TextObject in Section 2 discussed earlier.
The above part completes the dynamic header generation of Crystal Report. Now the next task is to populate data from DataReader into the DataTable of the Typed DataSet.
Finally the Typed DataSet is returned by the function and it is supplied as ReportSource property of the Crystal Report.
This BindReport function is also called inside the SelectedIndexChanged event handler of the CheckBoxList.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
this.BindReport();
}
}
private void BindReport()
{
string query = "SELECT TOP 5 ";
bool isSelected = chkColumns.Items.Cast<ListItem>().Count(i => i.Selected == true) > 0;
if (!isSelected)
{
chkColumns.Items[0].Selected = true;
}
foreach (ListItem item in chkColumns.Items)
{
if (item.Selected)
{
query += item.Value + ",";
isSelected = true;
}
}
query = query.Substring(0, query.Length - 1);
query += " FROM Customers";
ReportDocument crystalReport = new ReportDocument();
CrystalReportViewer1.DisplayGroupTree = false;
crystalReport.Load(Server.MapPath("~/CustomerReport.rpt"));
Customers dsCustomers = GetData(query, crystalReport);
crystalReport.SetDataSource(dsCustomers);
CrystalReportViewer1.ReportSource = crystalReport;
}
private Customers GetData(string query, ReportDocument crystalReport)
{
string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
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;
}
}
protected void Columns_Changed(object sender, EventArgs e)
{
this.BindReport();
}
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Me.BindReport()
End If
End Sub
Private Sub BindReport()
Dim query As String = "SELECT TOP 5 "
Dim isSelected As Boolean = chkColumns.Items.Cast(Of ListItem)().Count(Function(i) i.Selected = True) > 0
If Not isSelected Then
chkColumns.Items(0).Selected = True
End If
For Each item As ListItem In chkColumns.Items
If item.Selected Then
query += item.Value + ","
isSelected = True
End If
Next
query = query.Substring(0, query.Length - 1)
query += " FROM Customers"
Dim crystalReport As New ReportDocument()
CrystalReportViewer1.DisplayGroupTree = False
crystalReport.Load(Server.MapPath("~/CustomerReport.rpt"))
Dim dsCustomers As Customers = GetData(query, crystalReport)
crystalReport.SetDataSource(dsCustomers)
CrystalReportViewer1.ReportSource = crystalReport
End Sub
Private Function GetData(query As String, crystalReport As ReportDocument) As Customers
Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
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
Protected Sub Columns_Changed(sender As Object, e As EventArgs)
Me.BindReport()
End Sub
Screenshot
The following Screenshot describes how the Crystal Report is being populated with dynamic columns in ASP.Net.
![Create Crystal Report with dynamic columns in ASP.Net using C# and VB.Net]()
Demo
Downloads
