How to Pass Parameters to Crystal Reports at Runtime

Introduction

In this article I will show you how to pass parameters to Crystal Reports from Windows Form at runtime. I have shown an example from one of my projects. You can take a look at the following picture. It is a Vehicles Maintenance Work Order Form. In this case if you want to print the Work Order Form, you have to pass this work order # (071208-0104-AVA230/2) to the Crystal Report so that the report will show only this work order that has been requested.

First of all, create a Crystal Report with the wizard. Now here, I am assuming that you know how to create a Crystal Report though a wizard in VS 2005. Let's suppose we have created the report that is linked to our table WorkOrders in the database.

Now from the Field Explorer, drag and drop the Unbound String Field to Crystal Report. And then from Field Explorer, explore the Formula Fields and right click on that unbound string field and rename that to UBWONo. Then right click on that field in Crystal report and format the object. Then set its font color to white so that it will not display at runtime. So now you have a field on the report that will get the parameter at runtime. But you must pass this parameter field value to the actual database WorkOrderNo field so that we could get the record of that work order number from the database.

Now right click on the database field WorkOrders.BOWONo and click on Select Expert…

And in the Select Expert Dialog Box, give the following parameters:

Now your Crystal Report is ready to get the parameters. Behind the Windows Form from where you are calling the report, in our case you will see the first picture as shown above.

Imports System.Data.SqlClient
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Public Class FrmWOVehicleMaint
Public logOnInfo As New CrystalDecisions.Shared.TableLogOnInfo()
Private Sub btnPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
        Handles btnPrint.Click
    Me.Cursor = Cursors.WaitCursor
    If SqlConn.State = 1 Then SqlConn.Close()
    Dim frmRept As New FrmReportsDisplay
    Dim oCR As New rptBOWorkOrder
    oCR.DataDefinition.FormulaFields.Item("UBWONo").Text = "'" & Me.lblWONo.Text & "'"

strSQL = "SELECT VehiclesWorkOrders.BOWONo, VehiclesWorkOrders.DateGiven,  _
        VehiclesWorkOrders.TimeGiven, VehiclesWorkOrders.VehicleNo, _
        VehiclesWorkOrders.Mileage,VehiclesWorkOrders.DateComplete, _
        VehiclesWorkOrders.TimeComplete, VehiclesWorkOrders.TotalDownTime, _
        Employees.FirstName, Employees.MiddleName, Employees.LastName, _
        employees_1.FirstName AS Expr1, employees_1.MiddleName AS Expr2, _
        employees_1.LastName AS Expr3,VehicleCard.VehicleType, _
        VehicleCard.Manufacturer, VehicleCard.VinNo, _
        VehiclesWorkOrders.Problem, VehiclesWorkOrders.Diagnose, _
        VehiclesWorkOrders.PartsUsed, VehiclesWorkOrders.Remarks  _
        FROM VehiclesWorkOrders  LEFT OUTER JOIN Employees _
        AS Employees ON VehiclesWorkOrders.IssuedBy = _
        Employees.EmployeeID LEFT OUTER JOIN Employees AS employees_1 _
        ON VehiclesWorkOrders.HandoverTo = _
        employees_1.EmployeeID LEFT OUTER JOIN VehicleCard _
        AS VehicleCard ON VehiclesWorkOrders.VehicleNo = _
        VehicleCard.VehicleNo  WHERE VehiclesWorkOrders.BOWONo =_
        " & oCR.DataDefinition.FormulaFields.Item("UBWONo").Text
        Dim cmd As New SqlCommand(strSQL, SqlConn)
        Dim DA As New SqlDataAdapter(cmd)
        Dim DS As New DataSet
        DA.Fill(DS, "VehiclesWorkOrders,Employees,Employees_1,VehicleCard")
        DT = DS.Tables(0)
        SqlConn.Open()
        oCR.SetDataSource(DS)
        frmRept.CRViewer.ReportSource = (oCR)
        logOnInfo = oCR.Database.Tables(0).LogOnInfo
        logOnInfo.ConnectionInfo.ServerName = mServerName
        logOnInfo.ConnectionInfo.DatabaseName = mInitialCatalog
        logOnInfo.ConnectionInfo.UserID = mUser
        logOnInfo.ConnectionInfo.Password = mPassword
        oCR.Database.Tables(0).ApplyLogOnInfo(logOnInfo)
        frmRept.Show()
        SqlConn.Close()
        Me.Cursor = Cursors.Default
    End Sub