Crystal Reports from SQL Query String

Typically, Crystal Reports are generated based on pre-defined columns. However, it is indeed possible to create Crystal Reports with dynamic columns. In this context, we will demonstrate the process of generating dynamic Crystal Reports using SQL statements. By entering a SQL statement in a designated textbox, the Crystal Reports will be generated based on the provided SQL query.

This approach allows for flexibility and adaptability in generating Crystal Reports, as the user has the ability to customize the report output based on their specific SQL requirements. By using SQL statements, the Crystal Reports can dynamically retrieve and present data from the underlying database, providing a tailored reporting experience.

Implementing dynamic Crystal Reports through SQL statements empowers users to generate reports that align precisely with their data analysis needs, enhancing the versatility and effectiveness of Crystal Reports in capturing and presenting information.

vb.net_crystal_report_dynamic_column_0.GIF

Please note that all the programming samples provided in these tutorials for Crystal Reports are based on the crystaldb database. Prior to starting this tutorial, I highly recommend reviewing the database structure to familiarize yourself with its organization. You can access the database structure by clicking on the following link: Click here to view the Database Structure .

To begin, create a new VB.NET project. Within this project, it is necessary to add a Strongly Typed Dataset. Before proceeding with the creation of the Strongly Typed Dataset, I suggest referring to the detailed tutorial that outlines the process step by step. This tutorial will guide you in create a strongly typed datset and provide valuable insights into its implementation. Additionally, it is recommended to include five columns in the DataTable. However, please note that you have the flexibility to add any number of columns based on your specific requirements.

vb.net_crystal_report_dynamic_column_1.GIF

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

vb.net_crystal_report_dynamic_column_2.GIF

Select all the column from dataset.

vb.net_crystal_report_dynamic_column_3.GIF

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 .

Full Source VB.NET
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
NOTES:

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






Related Topics
  1. Sample Database and tables for Crystal Reports tutorials
  2. Step by Step help for creating a simple Crystal Reports in VB.NET
  3. VB.NET Crystal Reports from multiple tables
  4. VB.NET Crystal Reports String parameter
  1. VB.NET Crystal Reports Integer parameter
  2. VB.NET Crystal Reports Date parameter
  3. VB.NET Crystal Report Load Dynamically
  4. VB.NET Crystal Reports Formula Fields
  5. VB.NET Crystal Reports Summary Fields
  6. VB.NET Crystal Reports Export to PDF
  7. VB.NET Crystal Reports Export to Excel
  8. Email a Crystal Reports from VB.NET
  9. Crystal Reports Without Database
  10. Crystal Report from SQL Query String
  11. Crystal Reports from XML File
  12. Create a Subreport in Crystal Reports
  13. Create a Subreport in Crystal Reports with Link
  14. How to deploy Crystal Reports on Clinet Machine
  15. How to create Crystal Reports installer using Merge Modules