How to Load and Display Crystal Reports in VB.NET






4.49/5 (42 votes)
Mar 6, 2005
1 min read

1199192

15075
An article about how to load and display Crystal Reports and handle the parameters and logon information.

Introduction
The following code shows how to load Crystal Reports in VB.NET, solving all the issues of logon, including sub reports and parameter passing. You can view your reports by simply calling the required functions with its parameters.
Using the Code
Using this code in your application is very simple. In the first step you need to add a form, and name it frmViewReport
, then place the Crystal Report Viewer control on the form and name it rptViewer
. In the code section, simply paste the following function. You can call this function from anywhere in your application with reference to the frmViewReport
form. The sample code for calling the function is given below:
Dim objForm As New frmViewReport
objForm.ViewReport("C:\test.rtp", , "@parameter1=test¶mter2=10")
objForm.show()
Now let me explain you in detail what is going on in the code and what the format of the parameter string is. If there are parameters in the Crystal Reports then they should be passed with their values to the param
of the function. The parameter string should be in the following format:
<First Parameter Name>=<First Paramter Value>&
<Second Parameter Name>=<Second Paramter Value>..
Note the parameter name and its value pairs are separated by an '&'. The report name with its full path should be passed to sReportName
function.
Following is the function code with comments. I hope there will be no problem in understanding the code. Even if there is anything bothering you then drop me a message I'll explain that:
Friend Function ViewReport(ByVal sReportName As String, _
Optional ByVal sSelectionFormula As String = "", _
Optional ByVal param As String = "") As Boolean
'Declaring variablesables
Dim intCounter As Integer
Dim intCounter1 As Integer
'Crystal Report's report document object
Dim objReport As New _
CrystalDecisions.CrystalReports.Engine.ReportDocument
'object of table Log on info of Crystal report
Dim ConInfo As New CrystalDecisions.Shared.TableLogOnInfo
'Parameter value object of crystal report
' parameters used for adding the value to parameter.
Dim paraValue As New CrystalDecisions.Shared.ParameterDiscreteValue
'Current parameter value object(collection) of crystal report parameters.
Dim currValue As CrystalDecisions.Shared.ParameterValues
'Sub report object of crystal report.
Dim mySubReportObject As _
CrystalDecisions.CrystalReports.Engine.SubreportObject
'Sub report document of crystal report.
Dim mySubRepDoc As New _
CrystalDecisions.CrystalReports.Engine.ReportDocument
Dim strParValPair() As String
Dim strVal() As String
Dim index As Integer
Try
'Load the report
objReport.Load(sReportName)
'Check if there are parameters or not in report.
intCounter = objReport.DataDefinition.ParameterFields.Count
'As parameter fields collection also picks the selection
' formula which is not the parameter
' so if total parameter count is 1 then we check whether
' its a parameter or selection formula.
If intCounter = 1 Then
If InStr(objReport.DataDefinition.ParameterFields(_
0).ParameterFieldName, ".", CompareMethod.Text) > 0 Then
intCounter = 0
End If
End If
'If there are parameters in report and
'user has passed them then split the
'parameter string and Apply the values
'to their concurrent parameters.
If intCounter > 0 And Trim(param) <> "" Then
strParValPair = param.Split("&")
For index = 0 To UBound(strParValPair)
If InStr(strParValPair(index), "=") > 0 Then
strVal = strParValPair(index).Split("=")
paraValue.Value = strVal(1)
currValue = _
objReport.DataDefinition.ParameterFields(_
strVal(0)).CurrentValues
currValue.Add(paraValue)
objReport.DataDefinition.ParameterFields(_
strVal(0)).ApplyCurrentValues(currValue)
End If
Next
End If
'Set the connection information to ConInfo
'object so that we can apply the
'connection information on each table in the report
ConInfo.ConnectionInfo.UserID = <User Name>
ConInfo.ConnectionInfo.Password = <Password>
ConInfo.ConnectionInfo.ServerName = <Server Name>
ConInfo.ConnectionInfo.DatabaseName = <Database Name>
For intCounter = 0 To objReport.Database.Tables.Count - 1
objReport.Database.Tables(intCounter).ApplyLogOnInfo(ConInfo)
Next
' Loop through each section on the report then look
' through each object in the section
' if the object is a subreport, then apply logon info
' on each table of that sub report
For index = 0 To objReport.ReportDefinition.Sections.Count - 1
For intCounter = 0 To _
objReport.ReportDefinition.Sections(_
index).ReportObjects.Count - 1
With objReport.ReportDefinition.Sections(index)
If .ReportObjects(intCounter).Kind = _
CrystalDecisions.Shared.ReportObjectKind.SubreportObject Then
mySubReportObject = CType(.ReportObjects(intCounter), _
CrystalDecisions.CrystalReports.Engine.SubreportObject)
mySubRepDoc = _
mySubReportObject.OpenSubreport(mySubReportObject.SubreportName)
For intCounter1 = 0 To mySubRepDoc.Database.Tables.Count - 1
mySubRepDoc.Database.Tables(_
intCounter1).ApplyLogOnInfo(_
ConInfo)sp;
mySubRepDoc.Database.Tables(_
intCounter1).ApplyLogOnInfo(ConInfo)
Next
End If
End With
Next
Next
'If there is a selection formula passed to this function then use that
If sSelectionFormula.Length > 0 Then
objReport.RecordSelectionFormula = sSelectionFormula
End If
'Re setting control
rptViewer.ReportSource = Nothing
'Set the current report object to report.
rptViewer.ReportSource = objReport
'Show the report
rptViewer.Show()
Return True
Catch ex As System.Exception
MsgBox(ex.Message)
End Try
End Function
Points of Interest
The main problems while developing the function were the sub reports and the selection formula. When there were no parameters and only the selection formula, the function failed because the parameters collection was picking the selection formula. Initially it was hard to detect, but finally I solved that problem.