In usual practice , Crystal Reports we are getting from pre defined columns. But we can make Crystal Reports from Dynamic column . Here we are going to do the dynamic Crystal Reports from SQL statements . That is we enter SQL in textbox and get the Crystal Reports according to the SQL statement.

All Crystal Reports programming samples in this tutorials is based on the following database (crystaldb) . Please take a look at the database structure before you start this tutorial - Click here to see Database Structure .

Create a new VB.NET project and add a Strongly Typed Dataset . Before creating a Strongly Typed take a look at the detailed tutorial of create a strongly typed datset and add five column in the Datatable. Here we are limiting as five column , but you can add any number of column according to your requirements.

Next step is to create a Crystal Reports design from the Strongly Typed dataset.

Select all the column from dataset.

Select the default form(Form1.vb) and add a TextBox , Button and Crystal Reports Viewer .

Here we are going to pass the SQl statements to Crystal Reports at runtime . For that we parsing the SQL statement before we passing it to Crystal Reports. So we create a function for parsing SQL statements.

Public Function procesSQL() As String

Put the following vb.net source code in your form and run the program .

Imports System.Data.SqlClient Imports CrystalDecisions.CrystalReports.Engine Imports CrystalDecisions.Shared Imports System.Data Public Class Form1     Dim objRpt As New CrystalReport1     Private Sub Button1_Click(ByVal sender As System.Object,   ByVal e As System.EventArgs) Handles Button1.Click         Dim cnn As SqlConnection         Dim connectionString As String         Dim sql As String          connectionString = "data source=SERVERNAME; _   initial catalog=crystaldb;user id=sa;password=PASSWORD;"         cnn = New SqlConnection(connectionString)         cnn.Open()         sql = procesSQL()         Dim dscmd As New SqlDataAdapter(sql, cnn)         Dim ds As New DataSet1         dscmd.Fill(ds, "Product")         objRpt.SetDataSource(ds.Tables(1))         CrystalReportViewer1.ReportSource = objRpt         CrystalReportViewer1.Refresh()     End Sub     Public Function procesSQL() As String         Dim sql As String         Dim inSql As String         Dim firstPart As String         Dim lastPart As String         Dim selectStart As Integer         Dim fromStart As Integer         Dim fields As String()         Dim i As Integer         Dim MyText As TextObject          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 To fields.Length - 1             If i > 0 Then                 firstPart = firstPart  &  " , "  _     & fields(i).ToString() & "  AS COLUMN" & i + 1                 MyText = CType(objRpt.ReportDefinition.ReportObjects("Text"  _     & i + 1), TextObject)                 MyText.Text = fields(i).ToString()             Else                 firstPart = firstPart & fields(i).ToString() & _     "  AS COLUMN" & i + 1                 MyText = CType(objRpt.ReportDefinition.ReportObjects("Text" & _      i + 1), TextObject)                 MyText.Text = fields(i).ToString()             End If         Next         sql = "SELECT " & firstPart & " " & lastPart         Return sql     End Function  End Class

NOTE :

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