Crystal Report Won't Use Updated Parameter Value/Data Source When Created In Loop

1
votes

I've created a library in an attempt to handle all of the "quirks" of using Crystal Reports in a Visual Studio (VB.NET) project. I've pulled together all the elements that have presented challenges to me in the past - setting/updating parameter and formula values, printing (including page ranges), and setting logon credentials - and put them into reusable methods that all seem to work well when I generate the reports individually.

However, I've run into a scenario where I want to reuse the same report object in a loop to print multiple variations with different data sets/parameter(s) so that I can "easily" reuse the same printer settings and other options without re-prompting the user for each iteration. In this case, I'm working with an internally built DataSet object (built by someone other than me) and my Crystal Report file's Data Source is pointing to the .xsd file for structure.

EDIT: Forgot to mention that the report was created in CR Developer v11.5.12.1838 and the VB.NET library project is targetting the 4.7.2 .NET framework and using the v13.0.3500.0 (runtime v2.0.50727) of the Crystal libraries.

My intent is/was to instantiate a new report object outside the loop, then just re-set and refresh the report's data source and parameter values on each iteration of the loop. Unfortunately, it seems that if I do it this way, the report won't correctly pick up either the parameter values, the updated data source, or both. I've been trying several variations of code placement (because I know that the order in which things are done is very important to the Crystal Reports engine), but none of it seems to work the way I believe it should.

If I instantiate a new report object inside the loop, it will correctly generate the reports for each iteration with the correct data source and parameter values. Of course, it resets all of the internal properties of my class to "default", which kinda defeats the purpose. (Yes, I know I could pass additional/other parameters to a constructor to achieve the effect, but that seems an extremely "brute-force" solution, and I'd much rather get it to work the way I have in mind).

AND NOW FOR SOME CODE

Here is a pared-down/obfuscated version of the most recent iteration of the calling method (currently a part of a button click event handler). Every attempt I've made to instantiate a reusable object seems to result in some sort of failure. In this version, it loads the report and correctly passes along the parameter value, but the data source is completely empty resulting in a blank report. In other variations (I've discarded that code now), when I actually try to print/export/show the report, it fails with a COM exception: Missing parameter values.

I've tried using the .Refresh and .ReportClientDocument.VerifyDatabase methods separately, but those don't make a difference. When I check the parameters at runtime, it appears that the CR parameter/value and query results have been populated, but any method that makes any changes after the initialization just seems to "break" the report.

        Dim ReportName As String = "\\SERVERNAME\Applications\Reports\ClientActiveCustomerSummary.rpt"
        Dim Report As Common.CRReport = Nothing

        Try
            ReportData = ClientDataSet.Tables("ActiveSummary").Copy
            ReportData = GetClientActiveSummaryData
        Catch ex As Exception
            MessageBox.Show(ex.Message & vbCrLf & vbCrLf &
                "Error while retrieving client customer summary report data.",
                "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

       If ReportData.Rows.Count > 0 Then
            Report = New Common.CRReport(Common.CRReport.ReportSourceType.ADODataSet, New IO.FileInfo(ReportName), ClientDataSet)

            For Each LVItem As ListViewItem In checkItemsMP
                Dim ClientQuery = From ap In ReportData.AsEnumerable
                               Where ap.Field(Of Object)("mp") = LVItem.SubItems("mp").Text
                               Order By ap.Field(Of Object)("customername")
                               Select ap

                ClientDataSet.Tables("ActiveSummary").Merge(ClientQuery.CopyToDataTable)
                Report.ReportParameters.Clear()
                Report.AddReportParameter("ClientName", LVItem.SubItems("clientname").Text)
                Report.GenerateReport()
                ClientDataSet.Tables("ActiveSummary").Clear()
                ClientQuery = Nothing
            Next LVItem

            For Each LVItem As ListViewItem In checkItemsBN
                Dim BranchName As String = LVItem.SubItems("clientname").Text & " " & LVItem.SubItems("branchname").Text
                Dim BranchQuery = From ap In ReportData.AsEnumerable
                               Where (ap.Field(Of Object)("clientname") & " " & ap.Field(Of Object)("branchname")) = BranchName
                               Order By ap.Field(Of Object)("customername")
                               Select ap

                ClientDataSet.Tables("ActiveSummary").Merge(BranchQuery.CopyToDataTable)
                Report.ReportParameters.Clear()
                Report.AddReportParameter("ClientName", BranchName)
                Report.GenerateReport()
                ClientDataSet.Tables("ActiveSummary").Clear()
                BranchQuery = Nothing
            Next LVItem
        Else
            MessageBox.Show("NO RECORDS FOUND", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)
            ReportData.Dispose()
            ReportData = Nothing
        End If

Obviously, in this case, I'm passing in an ADO.NET DataSet object and using a value retrieved from a ListView on the form itself for the value of the report's single parameter. Again, if I instantiate a new CRReport object on each iteration of the loop, the report will create normally with the correct data and parameter value, but it prompts the user each time for the report creation options (print/show/export, then - if "print" is selected - again for which printer to use).

And here is the reporting class. (Please understand that this is a work in progress and is far from "production quality" code):

REPORT OBJECT (CRReport)

Imports System.IO
Imports CrystalDecisions.Shared
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.ReportAppServer.DataDefModel
Imports System.Drawing
Imports System.Windows.Forms

    Public Class CRReport
        Inherits ReportDocument

        Public Enum ReportSourceType
            PostgreSQL = 1
            MySQL = 2
            ADODataSet = 3
            XML = 4
            CSV = 5
            Access = 6
        End Enum

        Public Enum GenerateReportOption
            None = 0
            DisplayOnScreen = 1
            SendToPrinter = 2
            ExportToFile = 3
            MailToRecipient = 4
        End Enum

        Public Property ReportFile As FileInfo
        Public Property ExportPath As String
        Public Property ReportParameters As List(Of CRParameter)
        Public Property ReportFormulas As List(Of CRFormula)
        Public Property SourceType As ReportSourceType

        Private Property XMLDataSource As FileInfo
        Private Property ADODataSet As System.Data.DataSet
        Private Property ReportOption As GenerateReportOption
        Private WithEvents DocumentToPrint As Printing.PrintDocument

#Region "PUBLIC METHODS"
#Region "CONSTRUCTORS"
        Public Sub New(ByVal SourceType As ReportSourceType, ByVal CurrentReportFile As FileInfo)
            Me.Initialize()
            Me.SourceType = SourceType
            Me.ReportFile = CurrentReportFile
            PrepareReport()
        End Sub

        Public Sub New(ByVal SourceType As ReportSourceType, ByVal CurrentReportFile As FileInfo, ByVal XMLFile As FileInfo)
            Me.Initialize()
            Me.SourceType = SourceType
            Me.ReportFile = CurrentReportFile
            Me.XMLDataSource = XMLFile
            PrepareReport()
        End Sub

        Public Sub New(ByVal SourceType As ReportSourceType, ByVal CurrentReportFile As FileInfo, ByVal ADODataSource As System.Data.DataSet)
            Me.Initialize()
            Me.SourceType = SourceType
            Me.ReportFile = CurrentReportFile
            Me.ADODataSet = ADODataSource
            PrepareReport()
        End Sub

        Public Sub New(ByVal SourceType As ReportSourceType, ByVal CurrentReportFile As FileInfo, ByVal CurrentExportPath As String)
            Me.Initialize()
            Me.SourceType = SourceType
            Me.ReportFile = CurrentReportFile
            Me.ExportPath = CurrentExportPath

            If Not Me.ExportPath Is Nothing AndAlso Not String.IsNullOrEmpty(Me.ExportPath) Then
                Dim ExportFile As New IO.FileInfo(Me.ExportPath)

                If Not IO.Directory.Exists(ExportFile.DirectoryName) Then
                    IO.Directory.CreateDirectory(ExportFile.DirectoryName)
                End If
            End If

            PrepareReport()
        End Sub
#End Region

        Public Sub AddReportParameter(ByVal CurrentParameterName As String, ByVal CurrentParameterValue As Object)
            If Not String.IsNullOrEmpty(CurrentParameterName) Then
                Dim NewParameter As New CRParameter(Me, CurrentParameterName, CurrentParameterValue)

                Me.ReportParameters.Add(NewParameter)
            End If
        End Sub

        Public Sub AddReportFormula(ByVal CurrentFormulaName As String, ByVal CurrentFormulaValue As Object)
            If Not String.IsNullOrEmpty(CurrentFormulaName) Then
                Dim NewFormula As New CRFormula(Me, CurrentFormulaName, CurrentFormulaValue)

                Me.ReportFormulas.Add(NewFormula)
            End If
        End Sub

        Public Sub GenerateReport(ByVal ReportOption As GenerateReportOption)
            If Me.ReportOption = GenerateReportOption.None Then
                ' THIS DIALOG IS SOLELY FOR PROMPTING THE USER FOR HOW TO GENERATE THE REPORT
                Dim ReportDialog As New dlgGenerateReport

                Me.ReportOption = ReportDialog.GetReportGenerationOption()
            End If

            If Not Me.ReportOption = GenerateReportOption.None Then
                Select Case ReportOption
                    Case GenerateReportOption.DisplayOnScreen
                        Me.ShowReport()
                    Case GenerateReportOption.SendToPrinter
                        Me.PrintReport()
                    Case GenerateReportOption.ExportToFile
                        Me.ExportReport()
                End Select
            End If
        End Sub
#End Region

#Region "PRIVATE METHODS"
        Private Sub Initialize()
            Me.ReportFile = Nothing
            Me.ExportPath = String.Empty
            Me.ADODataSet = Nothing
            Me.XMLDataSource = Nothing
            Me.ReportParameters = New List(Of CRParameter)
            Me.ReportFormulas = New List(Of CRFormula)
            Me.SourceType = ReportSourceType.XML
            Me.ReportOption = GenerateReportOption.None
        End Sub

        Private Sub PrepareReport()
            If Not Me.ReportFile Is Nothing Then
                Me.Load(Me.ReportFile.FullName)
                Me.DataSourceConnections.Clear()
                SetReportConnectionInfo()

                If Me.ReportFormulas.Count > 0 Then
                    For Each Formula As CRFormula In Me.ReportFormulas
                        Formula.UpdateFormulaField()
                    Next Formula
                End If

                If Me.ReportParameters.Count > 0 Then
                    For Each Parameter As CRParameter In Me.ReportParameters
                        Parameter.UpdateReportParameter()
                    Next Parameter
                End If

                Me.Refresh()
                Me.ReportClientDocument.VerifyDatabase()
            End If
        End Sub

        Private Sub SetReportConnectionInfo()
            If Me.SourceType = ReportSourceType.PostgreSQL Then
                Dim CRDatabase As CrystalDecisions.CrystalReports.Engine.Database = Me.Database
                Dim CRTables As CrystalDecisions.CrystalReports.Engine.Tables = CRDatabase.Tables
                Dim CRConnectionInfo As New CrystalDecisions.Shared.ConnectionInfo
                Dim DBUsername As String = Utility.GetUsername
                Dim DBPassword As String = Utility.GetPassword

                With CRConnectionInfo
                    .DatabaseName = <DATABASENAME>
                    .ServerName = <HOSTNAME>
                    .UserID = DBUsername
                    .Password = DBPassword
                End With

                For Each CRTable As CrystalDecisions.CrystalReports.Engine.Table In CRTables
                    Dim CRTableLogonInfo As CrystalDecisions.Shared.TableLogOnInfo = CRTable.LogOnInfo

                    CRTableLogonInfo.ConnectionInfo = CRConnectionInfo
                    CRTable.ApplyLogOnInfo(CRTableLogonInfo)
                Next CRTable
            ElseIf Me.SourceType = ReportSourceType.ADODataSet Then
                Dim CRDatabase As CrystalDecisions.CrystalReports.Engine.Database = Me.Database
                Dim CRTables As CrystalDecisions.CrystalReports.Engine.Tables = CRDatabase.Tables

                For Each CRTable As CrystalDecisions.CrystalReports.Engine.Table In CRTables
                    For Each ADOTable As DataTable In ADODataSet.Tables
                        If CRTable.Name.ToUpper.Trim = ADOTable.TableName.ToUpper.Trim Then
                            CRTable.SetDataSource(ADOTable)
                            Exit For
                        End If
                    Next ADOTable
                Next CRTable

                Me.ReportClientDocument.VerifyDatabase()
            ElseIf Me.SourceType = ReportSourceType.XML Then
                If Not Me.XMLDataSource Is Nothing AndAlso Me.XMLDataSource.Exists Then
                    Dim CRDatabaseAttributes As New CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag
                    Dim CRLogonProperties As New CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag
                    Dim CRConnectionDetails As New CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo
                    Dim CRTable As CrystalDecisions.ReportAppServer.DataDefModel.Table
                    Dim CRTables As CrystalDecisions.ReportAppServer.DataDefModel.Tables = Me.ReportClientDocument.DatabaseController.Database.Tables
                    Dim XMLData As New System.Data.DataSet

                    XMLData.ReadXml(Me.XMLDataSource.FullName)

                    With CRLogonProperties
                        .Add("File Path ", Me.XMLDataSource.FullName)
                        .Add("Internal Connection ID", "{be7cdac3-6a64-4923-8177-898ab55d0fa0}")
                    End With

                    With CRDatabaseAttributes
                        .Add("Database DLL", "crdb_adoplus.dll")
                        .Add("QE_DatabaseName", "")
                        .Add("QE_DatabaseType", "")
                        .Add("QE_LogonProperties", CRLogonProperties)
                        .Add("QE_ServerDescription", Me.XMLDataSource.Name.Substring(0, Me.XMLDataSource.Name.Length - Me.XMLDataSource.Extension.Length))
                        .Add("QE_SQLDB", "False")
                        .Add("SSO Enabled", "False")
                    End With

                    With CRConnectionDetails
                        .Attributes = CRDatabaseAttributes
                        .Kind = CrystalDecisions.ReportAppServer.DataDefModel.CrConnectionInfoKindEnum.crConnectionInfoKindCRQE
                        .UserName = ""
                        .Password = ""
                    End With

                    For I As Integer = 0 To XMLData.Tables.Count - 1
                        CRTable = New CrystalDecisions.ReportAppServer.DataDefModel.Table

                        With CRTable
                            .ConnectionInfo = CRConnectionDetails
                            .Name = XMLData.Tables(I).TableName
                            .QualifiedName = XMLData.Tables(I).TableName
                            .[Alias] = XMLData.Tables(I).TableName
                        End With

                        Me.ReportClientDocument.DatabaseController.SetTableLocation(CRTables(I), CRTable)
                    Next I

                    Me.ReportClientDocument.VerifyDatabase()
                End If
            End If
        End Sub

        Private Sub PrintReport()
            If Me.DocumentToPrint Is Nothing Then
                ' THIS IS WHY I WANT TO REUSE THE REPORTING OBJECT
                ' IF I CAN SET/SAVE THE PRINT DOCUMENT/SETTINGS WITHIN THE OBJECT,
                ' THE USER SHOULD ONLY HAVE TO RESPOND ONCE FOR ANY ITERATIONS
                ' USING THE SAME REPORT OBJECT
                Dim SelectPrinter As New PrintDialog
                Dim PrinterSelected As DialogResult = DialogResult.Cancel

                Me.DocumentToPrint = New Printing.PrintDocument

                With SelectPrinter
                    .Document = DocumentToPrint
                    .AllowPrintToFile = False
                    .AllowSelection = False
                    .AllowCurrentPage = False
                    .AllowSomePages = False
                    .PrintToFile = False
                    .UseEXDialog = True
                End With

                PrinterSelected = SelectPrinter.ShowDialog()

                If PrinterSelected = DialogResult.OK Then
                    SendToPrinter()
                End If
            Else
                SendToPrinter()
            End If
        End Sub

        Private Sub SendToPrinter()
            Dim Copies As Integer = DocumentToPrint.PrinterSettings.Copies
            Dim PrinterName As String = DocumentToPrint.PrinterSettings.PrinterName
            Dim LastPageNumber As Integer = 1

            ' IF THE PARAMETER VALUE DOESN'T GET PASSED/UPDATED PROPERLY
            ' THIS LINE WILL THROW A COM EXCEPTION 'MISSING PARAMETER VALUE'
            LastPageNumber = Me.FormatEngine.GetLastPageNumber(New CrystalDecisions.Shared.ReportPageRequestContext())
            Me.PrintOptions.CopyTo(DocumentToPrint.PrinterSettings, DocumentToPrint.DefaultPageSettings)

            If DocumentToPrint.PrinterSettings.SupportsColor Then
                DocumentToPrint.DefaultPageSettings.Color = True
            End If

            Me.PrintOptions.CopyFrom(DocumentToPrint.PrinterSettings, DocumentToPrint.DefaultPageSettings)
            Me.PrintOptions.PrinterName = PrinterName
            Me.PrintOptions.PrinterDuplex = CType(DocumentToPrint.PrinterSettings.Duplex, PrinterDuplex)
            Me.PrintToPrinter(Copies, True, 1, LastPageNumber)
        End Sub

        Private Function ExportReport() As IO.FileInfo
            Dim ExportFile As IO.FileInfo = Nothing

            If Not Me.ExportPath Is Nothing AndAlso Not String.IsNullOrEmpty(Me.ExportPath) Then
                ExportFile = New IO.FileInfo(Me.ExportPath)

                If Not ExportFile.Exists Then
                    Me.ExportToDisk(ExportFormatType.PortableDocFormat, ExportFile.FullName)
                Else
                    Dim Response As DialogResult = DialogResult.Cancel

                    Response = MessageBox.Show(ExportFile.Name & " already exists in this location." & vbCrLf & vbCrLf &
                                               "Do you want to overwrite the existing file?" & vbCrLf & vbCrLf &
                                               "Click [Y]ES to overwrite the existing file" & vbCrLf &
                                               "Click [N]O to create a new file" & vbCrLf &
                                               "Click [C]ANCEL to cancel the export process",
                                               "PDF ALREADY EXISTS",
                                               MessageBoxButtons.YesNoCancel, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button2)

                    If Response = DialogResult.Yes Then
                        ExportFile.Delete()
                    ElseIf Response = DialogResult.No Then
                        ExportFile = New IO.FileInfo(Common.Utility.IncrementExistingFileName(Me.ExportPath))
                    Else
                        ExportFile = Nothing
                    End If

                    If Not ExportFile Is Nothing Then
                        Me.ExportToDisk(ExportFormatType.PortableDocFormat, ExportFile.FullName)
                    End If
                End If
            End If

            Return ExportFile
        End Function

        Private Sub ShowReport()
            Dim ReportViewer As New frmReportPreview

            With ReportViewer
                .rptViewer.ReportSource = Nothing
                .rptViewer.ReportSource = Me
                .WindowState = FormWindowState.Maximized
                .rptViewer.RefreshReport()

                ' Set zoom level: 1 = Page Width, 2 = Whole Page, 25-100 = zoom %
                .rptViewer.Zoom(1)
                .rptViewer.Show()
                .Show()
            End With
        End Sub

        Private Sub EmailReport(ByRef ReportMail As System.Net.Mail.MailMessage)
            Dim ReportAttachment As IO.FileInfo = ExportReport()

            If Not ReportAttachment Is Nothing AndAlso ReportAttachment.Exists Then
                ReportMail.Attachments.Add(New System.Net.Mail.Attachment(ReportAttachment.FullName))

                If Utility.SendEmailMessage(ReportMail) Then

                End If
            End If
        End Sub
#End Region

I've tried adding calls to the PrepareReport method (again) in the GenerateReport method of CRReport class so that it would reset the data source and parameter values, but it seems that it still doesn't get everything properly set up in the actual Crystal Report object for report generation. My experience so far has been that I have to set all of this on instantiation for some reason or it just fails completely.

For reference purposes, the parameters and formulae for Crystal are encapsulated in their own classes:

PARAMETER OBJECT (CRParameter)

#Region "CRYSTAL REPORTS PARAMETER CLASS"
    Public Class CRParameter
        Public Property CurrentReport As CRReport
        Public Property ParameterName As String
        Public Property ParameterValue As Object

        Public Sub New(ByVal Report As CRReport)
            Me.CurrentReport = Report
            Me.ParameterName = String.Empty
            Me.ParameterValue = Nothing
        End Sub

        Public Sub New(ByVal Report As CRReport, ByVal CurrentParameterName As String, ByVal CurrentParameterValue As Object)
            Me.CurrentReport = Report
            Me.ParameterName = CurrentParameterName
            Me.ParameterValue = CurrentParameterValue
            UpdateReportParameter()
        End Sub

        Friend Sub UpdateReportParameter()
            If Not Me.CurrentReport Is Nothing Then
                If Not String.IsNullOrEmpty(Me.ParameterName) Then
                    Dim CRFieldDefinitions As ParameterFieldDefinitions = Nothing
                    Dim CRFieldDefinition As ParameterFieldDefinition = Nothing
                    Dim CRValues As ParameterValues = Nothing
                    Dim CRDiscreteValue As ParameterDiscreteValue = Nothing

                    Try
                        CRFieldDefinitions = Me.CurrentReport.DataDefinition.ParameterFields
                        CRFieldDefinition = CRFieldDefinitions.Item(Me.ParameterName)
                        CRValues = CRFieldDefinition.CurrentValues

                        CRValues.Clear()
                        CRDiscreteValue = New ParameterDiscreteValue
                        CRDiscreteValue.Description = Me.ParameterName
                        CRDiscreteValue.Value = Me.ParameterValue
                        CRValues.Add(CRDiscreteValue)
                        CRFieldDefinition.ApplyCurrentValues(CRValues)
                        CRFieldDefinition.ApplyDefaultValues(CRValues)
                    Catch ex As Exception
                        Throw
                    Finally
                        If Not CRFieldDefinitions Is Nothing Then
                            CRFieldDefinitions.Dispose()
                        End If

                        If Not CRFieldDefinition Is Nothing Then
                            CRFieldDefinition.Dispose()
                        End If

                        If Not CRValues Is Nothing Then
                            CRValues = Nothing
                        End If

                        If Not CRDiscreteValue Is Nothing Then
                            CRDiscreteValue = Nothing
                        End If
                    End Try
                End If
            End If
        End Sub
    End Class
#End Region

FORMULA OBJECT (CRFormula)

I realize this falls outside the scope of the original question, but for the sake of completeness, I wanted to include it in case someone else might be looking for code to use.

#Region "CRYSTAL REPORTS FORMULA VALUE CLASS"
    Public Class CRFormula
        Public Property CurrentReport As CRReport
        Public Property FormulaName As String
        Public Property FormulaValue As Object

        Public Sub New(ByVal Report As CRReport)
            Me.CurrentReport = Report
            Me.FormulaName = String.Empty
            Me.FormulaValue = Nothing
        End Sub

        Public Sub New(ByVal Report As CRReport, ByVal NewFormulaName As String, ByVal NewFormulaValue As Object)
            Me.CurrentReport = Report
            Me.FormulaName = NewFormulaName
            Me.FormulaValue = NewFormulaValue
            'UpdateFormulaField()
        End Sub

        Friend Sub UpdateFormulaField()
            If Not Me.CurrentReport Is Nothing Then
                If Not String.IsNullOrEmpty(Me.FormulaName) Then
                    Try
                        If Me.FormulaValue Is Nothing Then
                            Me.FormulaValue = ""
                            Me.CurrentReport.DataDefinition.FormulaFields(Me.FormulaName).Text = Me.FormulaValue.ToString
                        ElseIf TypeOf Me.FormulaValue Is String AndAlso String.IsNullOrEmpty(Convert.ToString(Me.FormulaValue)) Then
                            Me.FormulaValue = ""
                            Me.CurrentReport.DataDefinition.FormulaFields(Me.FormulaName).Text = Me.FormulaValue.ToString
                        ElseIf TypeOf Me.FormulaValue Is String AndAlso Not String.IsNullOrEmpty(Convert.ToString(Me.FormulaValue)) Then
                            Me.FormulaValue = "'" & Me.FormulaValue.ToString & "'"
                            Me.CurrentReport.DataDefinition.FormulaFields(Me.FormulaName).Text = Me.FormulaValue.ToString
                        ElseIf TypeOf Me.FormulaValue Is Date Then
                            Me.FormulaValue = "'" & Convert.ToDateTime(Me.FormulaValue).ToString("yyyy-MM-dd") & "'"
                            Me.CurrentReport.DataDefinition.FormulaFields(Me.FormulaName).Text = Me.FormulaValue.ToString
                        Else
                            Me.CurrentReport.DataDefinition.FormulaFields(Me.FormulaName).Text = Me.FormulaValue.ToString
                        End If
                    Catch ex As Exception

                    End Try
                End If
            End If
        End Sub
    End Class
#End Region

I've tried to include as much detail and information about the challenge I'm facing as possible, but please feel free to ask any questions about any of it if you require clarification.

1
vb.netcrystal-reports
which version of crystal report? – Ajay2707
@Ajay2707 - The report was created in CR Developer v11.5.12.1838 and the VB.NET application is using the v13.0.3500.0 (runtime v2.0.50727) libraries. The project is targetting the 4.7.2 .NET framework. I've updated the question with these details. – G_Hosa_Phat

1 Answers

0
votes

Okay, I believe I've found the cause of the problem, and it's one of those "quirks" I mentioned at the top of my question. The Crystal Reports engine is very particular about the order of certain events, and this is one of those cases. In my original PrepareReport() method, I had the calls to the .Refresh() and .VerifyDatabase() methods executing last. This (apparently) effectively "resets" the parameters/data source, so everything I had above it was basically nullified.

So, I went back through some older code to look at how I've worked with individual Crystal Reports in the past and found that calling the .Refresh() and .VerifyDatabase() methods prior to attempting to set parameter and/or formula values seems to work as expected, so I moved those two lines up in the PrepareReport() code and tried again. It all seemed to work correctly. Several tests later, and the order of execution appears to be the culprit here. Now my PrepareReport() method looks like this:

Private Sub PrepareReport()
    If Not Me.CRReportFile Is Nothing Then
        Me.CrystalReport = New CrystalDecisions.CrystalReports.Engine.ReportDocument
        Me.CrystalReport.Load(Me.CRReportFile.FullName)
        Me.CrystalReport.DataSourceConnections.Clear()
        SetReportConnectionInfo()

        'MOVED THIS UP IN THE EXECUTION ORDER
        Me.CrystalReport.Refresh()
        Me.CrystalReport.ReportClientDocument.VerifyDatabase()

        If Me.ReportFormulas.Count > 0 Then
            For Each Formula As CRFormula In Me.ReportFormulas
                Formula.UpdateFormulaField(Me.CrystalReport)
            Next Formula
        End If

        If Me.ReportParameters.Count > 0 Then
            For Each Parameter As CRParameter In Me.ReportParameters
                Parameter.UpdateReportParameter(Me.CrystalReport)
            Next Parameter
        End If

        ' THE REFRESH() & VERIFYDATABASE() METHOD CALLS USED TO BE DOWN HERE
    End If
End Sub

TL;DR VERSION FOR THOSE INTERESTED IN ADDITIONAL INFO

A couple of other things I tried while troubleshooting, none of which resulted in complete success, although some produced varying degrees:

  • I tried disposing of the base object with MyBase.Dispose() (Obviously this was a bad idea). Of course, I couldn't instantiate a new base object without entirely recreating the CRReport object, which was what I was trying to avoid in the first place.
  • I removed the inheritance from the class and created a private variable for a CrystalDecisions.CrystalReports.Engine.ReportDocument object that could be instantiated independently of my class. Even though it may seem unnecessary, this actually wasn't a horrible idea as I'll explain later.
  • I tried several other variations of code placement that failed in one way or another.

Since everything seems to be working now with the revised PrepareReport() code, I've done a bit of refactoring. Instead of calling this method multiple times (once at instantiation and once at report generation), I removed the calls from the constructors and put a single call to it in the GenerateReport() method.

A SLIGHT "HICCUP"

I did some additional testing using the ShowReport() method (display it on the screen instead of printing it on paper), and there was some "weirdness", so I had to make adjustments. In my calling method (the button click event), I tried to dispose of the CRReport object after generating all of the reports, but that caused me not to be able to switch pages after the reports were generated/displayed (I got a NullReferenceException - Object reference not set to an instance of an object). A minor tweak later, and I could get the reports to stay instantiated but, due to the data set being overridden by later iterations, it wasn't always showing the correct data in each window.

This is where my removing of the inheritance comes into play. I created a private CrystalDecisions.CrystalReports.Engine.ReportDocument object for the class that could be reinstantiated and passed around a bit that would retain only the data associated with that particular instance of the report. I refactored the code for the CRReport, CRParameter, and CRFormula objects to use that new private variable instead, and everything looks like it's behaving exactly as expected.

HERE'S THE FULL REVISED CODE

Please remember, not all of this has been fully tested. I've yet to test the ExportReport() method b/c I need to clean up a couple of things there, and the EmailReport() method has a long way to go. I've only tested it with the ADO.NET DataSet during this run, although the code used for XML and PostgreSQL has worked in the past.

REPORT OBJECT (CRReport)

Public Class CRReport
    Public Property CRReportFile As FileInfo
    Public Property ReportParameters As List(Of CRParameter)
    Public Property ReportFormulas As List(Of CRFormula)
    Public Property SourceType As ReportSourceType

    Public Property ExportPath As String
        Get
            Return ExportReportToPath
        End Get

        Set(value As String)
            If Not value Is Nothing AndAlso Not String.IsNullOrEmpty(value) Then
                Dim ExportFile As New IO.FileInfo(value)

                If Not IO.Directory.Exists(ExportFile.DirectoryName) Then
                    IO.Directory.CreateDirectory(ExportFile.DirectoryName)
                End If

                ExportReportToPath = ExportFile.FullName
            End If
        End Set
    End Property

    Private Property XMLDataSource As FileInfo
    Private Property ADODataSet As System.Data.DataSet
    Private Property ReportOption As GenerateReportOption

    Private CrystalReport As CrystalDecisions.CrystalReports.Engine.ReportDocument
    Private ExportReportToPath As String

    Public Enum ReportSourceType
        PostgreSQL = 1
        MySQL = 2
        ADODataSet = 3
        XML = 4
        CSV = 5
        Access = 6
    End Enum

    Public Enum GenerateReportOption
        None = 0
        DisplayOnScreen = 1
        SendToPrinter = 2
        ExportToFile = 3
        MailToRecipient = 4
    End Enum

    Private WithEvents DocumentToPrint As Printing.PrintDocument

#Region "PUBLIC METHODS"
    Public Sub New(ByVal CurrentReportFile As FileInfo, ByVal XMLFile As FileInfo)
        Me.Initialize()
        Me.SourceType = ReportSourceType.XML
        Me.CRReportFile = CurrentReportFile
        Me.XMLDataSource = XMLFile
    End Sub

    Public Sub New(ByVal CurrentReportFile As FileInfo, ByVal ADODataSource As System.Data.DataSet)
        Me.Initialize()
        Me.SourceType = ReportSourceType.ADODataSet
        Me.CRReportFile = CurrentReportFile
        Me.ADODataSet = ADODataSource
    End Sub

    Public Sub AddReportParameter(ByVal CurrentParameterName As String, ByVal CurrentParameterValue As Object)
        If Not String.IsNullOrEmpty(CurrentParameterName) Then
            Dim NewParameter As New CRParameter(CurrentParameterName, CurrentParameterValue)

            Me.ReportParameters.Add(NewParameter)
        End If
    End Sub

    Public Sub AddReportFormula(ByVal CurrentFormulaName As String, ByVal CurrentFormulaValue As Object)
        If Not String.IsNullOrEmpty(CurrentFormulaName) Then
            Dim NewFormula As New CRFormula(CurrentFormulaName, CurrentFormulaValue)

            Me.ReportFormulas.Add(NewFormula)
        End If
    End Sub

    Public Sub GenerateReport()
        If Me.ReportOption = GenerateReportOption.None Then
            Dim ReportDialog As New dlgGenerateReport

            Me.ReportOption = ReportDialog.GetReportGenerationOption()
        End If

        If Not Me.ReportOption = GenerateReportOption.None Then
            GenerateReport(Me.ReportOption)
        End If
    End Sub

    Public Sub GenerateReport(ByVal ReportOption As GenerateReportOption)
        If Me.ReportOption = GenerateReportOption.None Then
            Dim ReportDialog As New dlgGenerateReport

            Me.ReportOption = ReportDialog.GetReportGenerationOption()
        End If

        If Not Me.ReportOption = GenerateReportOption.None Then
            PrepareReport()

            Select Case ReportOption
                Case GenerateReportOption.DisplayOnScreen
                    Me.ShowReport()
                Case GenerateReportOption.SendToPrinter
                    Me.PrintReport()
                Case GenerateReportOption.ExportToFile
                    Me.ExportReport()
            End Select
        End If
    End Sub

    Private Sub PrintReport()
        If Me.DocumentToPrint Is Nothing Then
            Dim SelectPrinter As New PrintDialog
            Dim PrinterSelected As DialogResult = DialogResult.Cancel

            Me.DocumentToPrint = New Printing.PrintDocument
            Me.CrystalReport.PrintOptions.CopyTo(Me.DocumentToPrint.PrinterSettings, Me.DocumentToPrint.DefaultPageSettings)

            With SelectPrinter
                .Document = DocumentToPrint
                .AllowPrintToFile = False
                .AllowSelection = False
                .AllowCurrentPage = False
                .AllowSomePages = False
                .PrintToFile = False
                .UseEXDialog = True
            End With

            PrinterSelected = SelectPrinter.ShowDialog()

            If PrinterSelected = DialogResult.OK Then
                SendToPrinter()
            End If
        Else
            SendToPrinter()
        End If
    End Sub

    Private Sub SendToPrinter()
        If Not Me.DocumentToPrint Is Nothing Then
            Dim Copies As Integer = Me.DocumentToPrint.PrinterSettings.Copies
            Dim PrinterName As String = Me.DocumentToPrint.PrinterSettings.PrinterName
            Dim LastPageNumber As Integer = 1

            LastPageNumber = Me.CrystalReport.FormatEngine.GetLastPageNumber(New CrystalDecisions.Shared.ReportPageRequestContext())
            Me.CrystalReport.PrintOptions.CopyFrom(Me.DocumentToPrint.PrinterSettings, Me.DocumentToPrint.DefaultPageSettings)
            Me.CrystalReport.PrintOptions.PrinterName = PrinterName
            Me.CrystalReport.PrintOptions.PrinterDuplex = CType(Me.DocumentToPrint.PrinterSettings.Duplex, PrinterDuplex)
            Me.CrystalReport.PrintToPrinter(Copies, True, 1, LastPageNumber)
        End If
    End Sub

    Private Function ExportReport() As IO.FileInfo
        Dim ExportFile As IO.FileInfo = Nothing

        If Not Me.ExportPath Is Nothing AndAlso Not String.IsNullOrEmpty(Me.ExportPath) Then
            ExportFile = New IO.FileInfo(Me.ExportPath)

            If Not ExportFile.Exists Then
                Me.CrystalReport.ExportToDisk(ExportFormatType.PortableDocFormat, ExportFile.FullName)
            Else
                Dim Response As DialogResult = DialogResult.Cancel

                Response = MessageBox.Show(ExportFile.Name & " already exists in this location." & vbCrLf & vbCrLf &
                                           "Do you want to overwrite the existing file?" & vbCrLf & vbCrLf &
                                           "Click [Y]ES to overwrite the existing file" & vbCrLf &
                                           "Click [N]O to create a new file" & vbCrLf &
                                           "Click [C]ANCEL to cancel the export process",
                                           "PDF ALREADY EXISTS",
                                           MessageBoxButtons.YesNoCancel, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button2)

                If Response = DialogResult.Yes Then
                    ExportFile.Delete()
                ElseIf Response = DialogResult.No Then
                    ExportFile = New IO.FileInfo(Common.Utility.IncrementExistingFileName(Me.ExportPath))
                Else
                    ExportFile = Nothing
                End If

                If Not ExportFile Is Nothing Then
                    Me.CrystalReport.ExportToDisk(ExportFormatType.PortableDocFormat, ExportFile.FullName)
                End If
            End If
        End If

        Return ExportFile
    End Function

    Private Sub ShowReport()
        Dim ReportViewer As New frmReportPreview

        With ReportViewer
            .rptViewer.ReportSource = Nothing
            .rptViewer.ReportSource = Me.CrystalReport
            .WindowState = FormWindowState.Maximized
            .rptViewer.RefreshReport()

            ' Set zoom level: 1 = Page Width, 2 = Whole Page, 25-100 = zoom %
            .rptViewer.Zoom(1)
            .rptViewer.Show()
            .Show()
        End With
    End Sub

    Private Sub EmailReport(ByRef ReportMail As System.Net.Mail.MailMessage)
        Dim ReportAttachment As IO.FileInfo = ExportReport()

        If Not ReportAttachment Is Nothing AndAlso ReportAttachment.Exists Then
            ReportMail.Attachments.Add(New System.Net.Mail.Attachment(ReportAttachment.FullName))

            If Utility.SendEmailMessage(ReportMail) Then

            End If
        End If
    End Sub

    Public Overloads Sub Dispose()
        Me.CrystalReport.Dispose()

        If Not Me.DocumentToPrint Is Nothing Then
            Me.DocumentToPrint.Dispose()
        End If
    End Sub
#End Region

#Region "PRIVATE METHODS"
    Private Sub Initialize()
        Me.CrystalReport = Nothing
        Me.CRReportFile = Nothing
        Me.ExportPath = String.Empty
        Me.ADODataSet = Nothing
        Me.XMLDataSource = Nothing
        Me.ReportParameters = New List(Of CRParameter)
        Me.ReportFormulas = New List(Of CRFormula)
        Me.SourceType = ReportSourceType.XML
        Me.ReportOption = GenerateReportOption.None
    End Sub

    Private Sub PrepareReport()
        If Not Me.CRReportFile Is Nothing Then
            Me.CrystalReport = New CrystalDecisions.CrystalReports.Engine.ReportDocument
            Me.CrystalReport.Load(Me.CRReportFile.FullName)
            Me.CrystalReport.DataSourceConnections.Clear()
            SetReportConnectionInfo()

            Me.CrystalReport.Refresh()
            Me.CrystalReport.ReportClientDocument.VerifyDatabase()

            If Me.ReportFormulas.Count > 0 Then
                For Each Formula As CRFormula In Me.ReportFormulas
                    Formula.UpdateFormulaField(Me.CrystalReport)
                Next Formula
            End If

            If Me.ReportParameters.Count > 0 Then
                For Each Parameter As CRParameter In Me.ReportParameters
                    Parameter.UpdateReportParameter(Me.CrystalReport)
                Next Parameter
            End If
        End If
    End Sub

    Private Sub SetReportConnectionInfo()
        If Me.SourceType = ReportSourceType.PostgreSQL Then
            Dim CRDatabase As CrystalDecisions.CrystalReports.Engine.Database = Me.CrystalReport.Database
            Dim CRTables As CrystalDecisions.CrystalReports.Engine.Tables = CRDatabase.Tables
            Dim CRConnectionInfo As New CrystalDecisions.Shared.ConnectionInfo

            With CRConnectionInfo
                .DatabaseName = <DBNAME>
                .ServerName = <HOSTNAME>
                .UserID = Utility.GetDBUsername
                .Password = Utility.GetDBPassword
            End With

            For Each CRTable As CrystalDecisions.CrystalReports.Engine.Table In CRTables
                Dim CRTableLogonInfo As CrystalDecisions.Shared.TableLogOnInfo = CRTable.LogOnInfo

                CRTableLogonInfo.ConnectionInfo = CRConnectionInfo
                CRTable.ApplyLogOnInfo(CRTableLogonInfo)
            Next CRTable
        ElseIf Me.SourceType = ReportSourceType.ADODataSet Then
            Dim CRDatabase As CrystalDecisions.CrystalReports.Engine.Database = Me.CrystalReport.Database
            Dim CRTables As CrystalDecisions.CrystalReports.Engine.Tables = CRDatabase.Tables

            For Each CRTable As CrystalDecisions.CrystalReports.Engine.Table In CRTables
                For Each ADOTable As DataTable In ADODataSet.Tables
                    If CRTable.Name.ToUpper.Trim = ADOTable.TableName.ToUpper.Trim Then
                        CRTable.SetDataSource(ADOTable)
                        Exit For
                    End If
                Next ADOTable
            Next CRTable
        ElseIf Me.SourceType = ReportSourceType.XML Then
            If Not Me.XMLDataSource Is Nothing AndAlso Me.XMLDataSource.Exists Then
                Dim CRDatabaseAttributes As New CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag
                Dim CRLogonProperties As New CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag
                Dim CRConnectionDetails As New CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo
                Dim CRTable As CrystalDecisions.ReportAppServer.DataDefModel.Table
                Dim CRTables As CrystalDecisions.ReportAppServer.DataDefModel.Tables = Me.CrystalReport.ReportClientDocument.DatabaseController.Database.Tables
                Dim XMLData As New System.Data.DataSet

                XMLData.ReadXml(Me.XMLDataSource.FullName)

                With CRLogonProperties
                    .Add("File Path ", Me.XMLDataSource.FullName)
                    .Add("Internal Connection ID", "{be7cdac3-6a64-4923-8177-898ab55d0fa0}")
                End With

                With CRDatabaseAttributes
                    .Add("Database DLL", "crdb_adoplus.dll")
                    .Add("QE_DatabaseName", "")
                    .Add("QE_DatabaseType", "")
                    .Add("QE_LogonProperties", CRLogonProperties)
                    .Add("QE_ServerDescription", Me.XMLDataSource.Name.Substring(0, Me.XMLDataSource.Name.Length - Me.XMLDataSource.Extension.Length))
                    .Add("QE_SQLDB", "False")
                    .Add("SSO Enabled", "False")
                End With

                With CRConnectionDetails
                    .Attributes = CRDatabaseAttributes
                    .Kind = CrystalDecisions.ReportAppServer.DataDefModel.CrConnectionInfoKindEnum.crConnectionInfoKindCRQE
                    .UserName = ""
                    .Password = ""
                End With

                For I As Integer = 0 To XMLData.Tables.Count - 1
                    CRTable = New CrystalDecisions.ReportAppServer.DataDefModel.Table

                    With CRTable
                        .ConnectionInfo = CRConnectionDetails
                        .Name = XMLData.Tables(I).TableName
                        .QualifiedName = XMLData.Tables(I).TableName
                        .[Alias] = XMLData.Tables(I).TableName
                    End With

                    Me.CrystalReport.ReportClientDocument.DatabaseController.SetTableLocation(CRTables(I), CRTable)
                Next I
            End If
        End If
    End Sub
#End Region
End Class

PARAMETER OBJECT (CRParameter)

#Region "CRYSTAL REPORTS PARAMETER CLASS"
Public Class CRParameter
    Public Property ParameterName As String
    Public Property ParameterValue As Object

    Public Sub New(ByVal CurrentParameterName As String, ByVal CurrentParameterValue As Object)
        Me.ParameterName = CurrentParameterName
        Me.ParameterValue = CurrentParameterValue
    End Sub

    Friend Sub UpdateReportParameter(ByRef CurrentReport As CrystalDecisions.CrystalReports.Engine.ReportDocument)
        If Not CurrentReport Is Nothing Then
            If Not String.IsNullOrEmpty(Me.ParameterName) Then
                Using ReportFieldDefinitions As ParameterFieldDefinitions = CurrentReport.DataDefinition.ParameterFields
                    Using ReportParameter As ParameterFieldDefinition = ReportFieldDefinitions.Item(Me.ParameterName)
                        Dim ReportValues As ParameterValues = ReportParameter.CurrentValues
                        Dim NewValue As New ParameterDiscreteValue

                        ReportValues.Clear()
                        NewValue.Description = Me.ParameterName
                        NewValue.Value = Me.ParameterValue
                        ReportValues.Add(NewValue)
                        ReportParameter.ApplyCurrentValues(ReportValues)
                        ReportParameter.ApplyDefaultValues(ReportValues)
                    End Using
                End Using
            End If
        End If
    End Sub
End Class
#End Region

FORMULA OBJECT (CRFormula)

#Region "CRYSTAL REPORTS FORMULA VALUE CLASS"
Public Class CRFormula
    Public Property FormulaName As String
    Public Property FormulaValue As Object

    Public Sub New(ByVal NewFormulaName As String, ByVal NewFormulaValue As Object)
        Me.FormulaName = NewFormulaName
        Me.FormulaValue = NewFormulaValue
    End Sub

    Friend Sub UpdateFormulaField(ByRef CurrentReport As CrystalDecisions.CrystalReports.Engine.ReportDocument)
        If Not CurrentReport Is Nothing Then
            If Not String.IsNullOrEmpty(Me.FormulaName) Then
                Try
                    If Me.FormulaValue Is Nothing Then
                        Me.FormulaValue = ""
                        CurrentReport.DataDefinition.FormulaFields(Me.FormulaName).Text = Me.FormulaValue.ToString
                    ElseIf TypeOf Me.FormulaValue Is String AndAlso String.IsNullOrEmpty(Convert.ToString(Me.FormulaValue)) Then
                        Me.FormulaValue = ""
                        CurrentReport.DataDefinition.FormulaFields(Me.FormulaName).Text = Me.FormulaValue.ToString
                    ElseIf TypeOf Me.FormulaValue Is String AndAlso Not String.IsNullOrEmpty(Convert.ToString(Me.FormulaValue)) Then
                        Me.FormulaValue = "'" & Me.FormulaValue.ToString & "'"
                        CurrentReport.DataDefinition.FormulaFields(Me.FormulaName).Text = Me.FormulaValue.ToString
                    ElseIf TypeOf Me.FormulaValue Is Date Then
                        Me.FormulaValue = "'" & Convert.ToDateTime(Me.FormulaValue).ToString("yyyy-MM-dd") & "'"
                        CurrentReport.DataDefinition.FormulaFields(Me.FormulaName).Text = Me.FormulaValue.ToString
                    Else
                        CurrentReport.DataDefinition.FormulaFields(Me.FormulaName).Text = Me.FormulaValue.ToString
                    End If
                Catch ex As Exception

                End Try
            End If
        End If
    End Sub
End Class
#End Region