Crystal Reports from SQL Query String

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