VB.NET - Crystal Reports: create reports with dynamic columns

Asked By kibika on 31-Mar-13 06:09 AM
I have a form on which I have checkboxes - on checking these boxes, the names appear in the crystal reports columns while unchecked ones don't appear. I want assistance to change these code such that instead of using the checkboxes on form, the crystal reports displays the columns which have data, while those ones without data are not displayed automatically.

I will appreciate assistance. Thanks.

Imports System
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data
Imports System.Drawing
Imports System.Text
Imports System.Windows.Forms
Imports System.Data.OleDb

Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.ReportSource
Imports CrystalDecisions.Shared
Imports CrystalDecisions.Windows.Forms

Public Class Form1
    Inherits Form
    Private objRpt As CrystalReport1

    Public Sub New()
      InitializeComponent()
    End Sub
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    
    End Sub
    Private Function CreateSelectQueryAndParameters() As String
      Dim reportDocument As ReportDocument
      Dim paramFields As ParameterFields

      Dim paramField As ParameterField
      Dim paramDiscreteValue As ParameterDiscreteValue

      reportDocument = New ReportDocument()
      paramFields = New ParameterFields()

      Dim query As String = "SELECT "
      Dim columnNo As Integer = 0

      If chbCode.Checked Then
        columnNo += 1
        query = query.Insert(query.Length, "CustomerCode as Column" + columnNo.ToString())

        paramField = New ParameterField()
        paramField.Name = "col" + columnNo.ToString()
        paramDiscreteValue = New ParameterDiscreteValue()
        paramDiscreteValue.Value = "Customer Code"
        paramField.CurrentValues.Add(paramDiscreteValue)
        'Add the paramField to paramFields
        paramFields.Add(paramField)
      End If
      If chbFirstName.Checked Then
        columnNo += 1
        If query.Contains("Column") Then
          query = query.Insert(query.Length, ", ")
        End If
        query = query.Insert(query.Length, "FirstName as Column" + columnNo.ToString())

        paramField = New ParameterField()
        paramField.Name = "col" + columnNo.ToString()
        paramDiscreteValue = New ParameterDiscreteValue()
        paramDiscreteValue.Value = "First Name"
        paramField.CurrentValues.Add(paramDiscreteValue)
        'Add the paramField to paramFields
        paramFields.Add(paramField)
      End If
      If chbLastName.Checked Then
        columnNo += 1
        'To determine Column number
        If query.Contains("Column") Then
          query = query.Insert(query.Length, ", ")
        End If
        query = query.Insert(query.Length, "LastName as Column" + columnNo.ToString())

        paramField = New ParameterField()
        paramField.Name = "col" + columnNo.ToString()
        paramDiscreteValue = New ParameterDiscreteValue()
        paramDiscreteValue.Value = "Last Name"
        paramField.CurrentValues.Add(paramDiscreteValue)
        'Add the paramField to paramFields
        paramFields.Add(paramField)
      End If
      If chbAddress.Checked Then
        columnNo += 1
        If query.Contains("Column") Then
          query = query.Insert(query.Length, ", ")
        End If
        query = query.Insert(query.Length, "Address as Column" + columnNo.ToString())

        paramField = New ParameterField()
        paramField.Name = "col" + columnNo.ToString()
        paramDiscreteValue = New ParameterDiscreteValue()
        paramDiscreteValue.Value = "Address"
        paramField.CurrentValues.Add(paramDiscreteValue)
        'Add the paramField to paramFields
        paramFields.Add(paramField)
      End If
      If chbPhone.Checked Then
        columnNo += 1
        If query.Contains("Column") Then
          query = query.Insert(query.Length, ", ")
        End If
        query = query.Insert(query.Length, "Phone as Column" + columnNo.ToString())

        paramField = New ParameterField()
        paramField.Name = "col" + columnNo.ToString()
        paramDiscreteValue = New ParameterDiscreteValue()
        paramDiscreteValue.Value = "Phone"
        paramField.CurrentValues.Add(paramDiscreteValue)
        'Add the paramField to paramFields
        paramFields.Add(paramField)
      End If

      'if there is any remaining parameter, assign empty value for that
      'parameter.
      For i As Integer = columnNo To 4
        columnNo += 1
        paramField = New ParameterField()
        paramField.Name = "col" + columnNo.ToString()
        paramDiscreteValue = New ParameterDiscreteValue()
        paramDiscreteValue.Value = ""
        paramField.CurrentValues.Add(paramDiscreteValue)
        'Add the paramField to paramFields
        paramFields.Add(paramField)
      Next

      CrystalReportViewer1.ParameterFieldInfo = paramFields

      query += " FROM Customer"
      Return query
    End Function

    Private Sub btnViewReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnViewReport.Click
      objRpt = New CrystalReport1()

      Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\db1.mdb"

      'Get Select query String and add parameters to the
      'Crystal report.
      Dim query As String = CreateSelectQueryAndParameters()

      'if there is no item select, then exit from the method.
      If Not query.Contains("Column") Then
        MessageBox.Show("No selection to display!")
        Return
      End If

      Try
        Dim Conn As New OleDbConnection(connString)

        Dim adapter As New OleDbDataAdapter(query, connString)
        Dim Ds As New DataSet1()

        adapter.Fill(Ds, "Customer")

        objRpt.SetDataSource(Ds)
        CrystalReportViewer1.ReportSource = objRpt
      Catch oleEx As OleDbException
        MessageBox.Show(oleEx.Message)
      Catch Ex As Exception
        MessageBox.Show(Ex.Message)
      End Try
    End Sub
End Class