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