Parameters and Stored Procedures
A common hurdle that developers have is figuring out how to print reports connected to stored procedures using parameters. But it really isn’t hard once you understand how it works. Reports that use stored procedures as their data source are no different than reports that use any other data source. When you open the report, it automatically calls the stored procedure, retrieves the data, and populates the report with this data. The difference between using a stored procedure and using a table is that stored procedures accept parameters as input.
When a report is designed with a stored procedure, Crystal Reports examines the stored procedure to see if it uses parameters. If so, the designer automatically creates a report parameter for each parameter in the stored procedure. There is a one-to-one mapping of report parameters to stored procedure parameters. When the report runs, the report engine takes the value of each of these parameters and automatically passes them to the stored procedure.
As you saw in Chapter 16, the user is always prompted to enter the parameters before the report can execute. Of course, you probably don’t want to prompt the user for this information because your application has already done so via the user interface. To prevent this from happening, manually populate the parameter(s) via code with the information the user has already provided. After the parameters are filled, the report connects to the database, passes the parameters to the stored procedure and previews the report.
Listing 17-13 is the complete code for using the viewer control to connect to a stored procedure that uses a parameter.
Private Sub SpWithViewer(ByVal UserId As String, ByVal Password As String, _
ByVal SpParameter As String)
'Logon to the server
Dim crReport As New CrystalReport1()
Dim crLogonInfo As New CrystalDecisions.Shared.TableLogOnInfo()
CrystalReportViewer1.LogOnInfo = _
New CrystalDecisions.Shared.TableLogOnInfos()
crLogonInfo.TableName = "spCustomers;1"
With crLogonInfo.ConnectionInfo
.UserID = UserId
.Password = Password
End With
CrystalReportViewer1.LogOnInfo.Add(crLogonInfo)
'Create the parameter
Dim ParameterFields As CrystalDecisions.Shared.ParameterFields
Dim ParameterField As CrystalDecisions.Shared.ParameterField
Dim spValue As CrystalDecisions.Shared.ParameterDiscreteValue
ParameterFields = New CrystalDecisions.Shared.ParameterFields()
ParameterField = New CrystalDecisions.Shared.ParameterField()
ParameterField.ParameterFieldName = "@CustPattern"
spValue = New CrystalDecisions.Shared.ParameterDiscreteValue()
spValue.Value = SpParameter
ParameterField.CurrentValues.Add(spValue)
ParameterFields.Add(ParameterField)
CrystalReportViewer1.ParameterFieldInfo = ParameterFields
'Show the report
CrystalReportViewer1.ReportSource = crReport
End Sub
This code is a compilation of code you’ve already seen. The first half logs onto the data source with the appropriate server name, database name, and login credentials. Notice that the TableName property is the name of the stored procedure with ;1 shown at the end of it. Crystal Reports always puts ;1 at the end of the procedure name and if you forget to include it then it won’t work. The second half of the code creates a new parameter field, adds is to the parameter field collection and assigns this collection to the report viewer. If you want to pass parameters using the ReportDocument object instead, then replace the parameter code with the code from Listing 16-1.
