1 Previous Next 

Crystal report "Report sample"


This example, shows how to create a reusable form for showing a crystal reports report. With this form it's easy to pass parameters to the report and change the datasource location. The code for this example and the sample report were provided to us by Peter Proost, set this sample report in the Bin folder
Download Report sample by PeterProost

'Drag a CrystalReportViewer on a newproject in a windowform.
'Use the Zipped report.rpt file in the bin directory and than this code.
Used is here the Northwind database, with the table Employees. You have to change this and replace the 'Server' to your servername
'you need to add a reference to these 3 dll's and also CrystalDecisions.windows.forms

Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Imports CrystalDecisions.ReportSource
'This you have to add in the designer created part after the InitializeComponent

Me.WindowState = FormWindowState.Maximized
'Because of this beneath, you cannot set it in the form load event
Me.InitialiseReport(Application.StartupPath & "\myreport.rpt", "My report")
strDataBase = "Northwind" 'The Database that you use
strServer = "Server" 'The Server that you use
blnIntegrated = True 'or False
'These are used if integrated security = false
'Me.UserId = ""
'Me.Password =""
'After the InitialiseReport you can start adding your parameters
Me.AddParameter("table", "Employees") 'or any other table that you want
'Before showing the report and after the last parameter is added
'you need to call ActionReport which adds the parameters to the report
Me.ActionReport()
'This part is placed before the end Class  
'The variable containing all ParameterFields
Private crParameterFields As ParameterFields
'The variable containing the report
Private crReportDocument As ReportDocument
'The boolean to specify if the the report should
'use integrated security or not
Private blnIntegrated As Boolean = True 'or false
Private strUserId As String
Private strPassword As String
'Variables containing the server, database, userId and password info
Private strServer As String 
Private strDataBase As String 

'This sub is used to add a string parameter,
'you have to pass the parameter name and value
'the parameter name is case sensitive
Public Overloads Sub AddParameter(ByVal pName As String, ByVal pValue As String)
        Dim crParameterField As New ParameterField
        Dim crParameterDiscreteValue As New ParameterDiscreteValue
        Dim crParameterValue As ParameterValue
        For Each crParameterField In crParameterFields
            If crParameterField.ParameterFieldName = pName Then
                crParameterField.CurrentValues.Clear()
                crParameterDiscreteValue = New ParameterDiscreteValue
                crParameterDiscreteValue.Value = pValue
                crParameterField.CurrentValues.Add(CType(crParameterDiscreteValue, ParameterValue))
            End If
        Next
End Sub

'This sub is used to add a date parameter,
'you have to pass the parameter name and value
'the parameter name is case sensitive
Public Overloads Sub AddParameter(ByVal pName As String, ByVal pValue As Date)
   Dim crParameterField As New ParameterField
   Dim crParameterDiscreteValue As New ParameterDiscreteValue
   Dim crParameterValue As ParameterValue
      For Each crParameterField In crParameterFields
          If crParameterField.ParameterFieldName = pName Then
             crParameterField.CurrentValues.Clear()
             crParameterDiscreteValue = New ParameterDiscreteValue
             crParameterDiscreteValue.Value = pValue
             crParameterField.CurrentValues.Add(CType(crParameterDiscreteValue, ParameterValue))
          End If
      Next
End Sub

'This sub is used to add a number parameter,
'you have to pass the parameter name and value (done in this sample)
'the parameter name is case sensitive

Public Overloads Sub AddParameter(ByVal pName As String, ByVal pValue As Integer)
   Dim crParameterField As New ParameterField
   Dim crParameterDiscreteValue As New ParameterDiscreteValue
   Dim crParameterValue As ParameterValue
        For Each crParameterField In crParameterFields
            If crParameterField.ParameterFieldName = pName Then
                crParameterField.CurrentValues.Clear()
                crParameterDiscreteValue = New ParameterDiscreteValue
                crParameterDiscreteValue.Value = pValue
                crParameterField.CurrentValues.Add(CType(crParameterDiscreteValue, ParameterValue))
            End If
        Next
End Sub

'This sub is used to Initialise the Report
'You need to pass the path to the report for example "c:\myreport.rpt" (done in this sample)
Public Sub InitialiseReport(ByVal pReportName As String, ByVal pTitle As String)
        crReportDocument = New ReportDocument
        crParameterFields = New ParameterFields
        Dim tblCurrent As Table
        Dim crSubreportObject As SubreportObject
        Dim subRepDoc As New ReportDocument
        Dim crDatabase As CrystalDecisions.CrystalReports.Engine.Database
        Dim crTables As Tables
        Dim crTable As Table
        Dim crSection As Section
        Dim crSections As Sections
        Dim crReportObjects As ReportObjects
        Dim crReportObject As ReportObject
        Dim crLogOnInfo As TableLogOnInfo
        Dim crpConnectionInfo As New CrystalDecisions.Shared.ConnectionInfo
        Dim crpTableLogOnInfo As New CrystalDecisions.Shared.TableLogOnInfo
        crReportDocument.Load(pReportName)
        CrystalReportViewer1.ReportSource = crReportDocument
        CrystalReportViewer1.Zoom(4)
        'Here you can specify the server and database to use
        'for example if your report is developed on server1 and the development
        'database was called develop and now you want to run the report on server2
        'and the database is called production. Then you would pass server2 and production
        'to the variables.
        'Caution: The table structure has to be the same in the two databases
        With crpConnectionInfo
            .ServerName = strServer
            .DatabaseName = strDataBase
        End With
        'If you don't use integrated security
        'provide the username and password used to logon to the database
        If blnIntegrated = False Then
            crpConnectionInfo.UserID = strUserId
            crpConnectionInfo.Password = strPassword
        End If

        'Apply the ConnectionloginInfo to every table that's used inside the report
        For Each tblCurrent In crReportDocument.Database.Tables
            crpTableLogOnInfo = tblCurrent.LogOnInfo
            crpTableLogOnInfo.ConnectionInfo = crpConnectionInfo
            tblCurrent.ApplyLogOnInfo(crpTableLogOnInfo)
        Next

        'Set the sections collection with report sections
        crSections = crReportDocument.ReportDefinition.Sections
        For Each crSection In crSections
            crReportObjects = crSection.ReportObjects
            For Each crReportObject In crReportObjects
                If crReportObject.Kind = ReportObjectKind.SubreportObject Then
                    'If there's a subreport, typecast the reportobject to a subreport object
                    crSubreportObject = CType(crReportObject, SubreportObject)

                    'Open the subreport
                    subRepDoc = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName)

                    crDatabase = subRepDoc.Database
                    crTables = crDatabase.Tables

                    'Loop through all the tables and set the connection info,
                    'Pass on the connection info to the logoninfo object then add the
                    'logoninfo to the subreport
                    For Each crTable In crTables
                        With crpConnectionInfo
                            .ServerName = strServer
                            .DatabaseName = strDataBase
                        End With
                        crLogOnInfo = crTable.LogOnInfo
                        crLogOnInfo.ConnectionInfo = crpConnectionInfo
                        crTable.ApplyLogOnInfo(crLogOnInfo)
                    Next
                End If
            Next
        Next
        crParameterFields = CrystalReportViewer1.ParameterFieldInfo
        Me.Text = pTitle
End Sub

Public Sub ActionReport()
        'Add all the parameters to the crystalreportviewer/reportdocument
        CrystalReportViewer1.ParameterFieldInfo = crParameterFields
End Sub

Private Sub frmReport_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
        'This is necessary with CR9
        'Because if you don't use this, everytime you open a reportform
        'and close it again one sleeping connection is added to SQL Server
        'and they remain there until the application closes.
        'Thanks to Bernie Yaeger who helped me a year ago with this
        crReportDocument.Dispose()
End Sub



1 Previous Next