Issues (I Think) With Crystal Report Parameters

[The_Grudge]

Please forgive me for the lack of information I'm about to provide, but I'll do my best. I'm going to do my best to make a very long story a bit shorter.

At my workplace there was an immediate need to build a quarterly reporting system that would a) capture data points for a list of measures and b) print the data in an easy to read package. Through some luck, we obtained a piece of custom-built software from a gentleman at another company similar to us. The developer has even provided us with his source code.

The project that they built was written in VB (Visual Studio 2008) and connects to an SQL Server database. Users can enter data on the front-end and once everything has been entered, they can click on a button called "Generate Report" that executes a Crystal Report to show them everything they've just entered, complete with graphs and so on. (The Crystal Report was built in Visual Studio using the add-on).

I have installed this software on my local PC which has Visual Studio 2010 and it works perfectly for the most part. The part of his program that passes parameters to Crystal Reports is a bit wonky though. The first time you run the report, it ignores the two parameters you would normally provide for fiscal quarter (e.g. Q1 - Q3), so you end up with a report that is not limited in any way. What ends up happening is I get a huge report that contains all quarters that are in the database.

Here's the strange part though. If I toggle a checkbox that's on the form on and then off, the paramaters work just fine and the report prints as expected. The checkbox has no code behind it whatsoever. There is just a small snippet of code that checks to see if the checkbox is selected or not before it passes the parameters to Crystal Reports and executes the report.

I've entered debug mode several times to try and figure out what's wrong and the parameters VB passes to Crystal are EXACTLY the same, both when the report craps out (printing all quarters) and when it works (printing data for desired Q's only).

Now I must confess that I'm just getting out of VB 6.0, so I'm not entirely up to speed on parameters going into Crystal, but from my end it looks fine. The developer does not have this issue on his computer.

The only differences are I have VS2010 while he has 2008 and a conversion did occur when I opened his project.

We're both at a loss for what to try next. To summarize, the problem is that parameters being passed to the program into Crystal are being ignored - unless a checkbox on the screen is activated/deactivated....then it works fine.

[jggtz]

Probably there is a LostFocus event in another control when checkbox is clicked

It would be good if you post the relevant code

[The_Grudge]

I thought I had the problem nailed down this morning but no dice. Any help is appreciated.

Imports CrystalDecisions.Shared
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Windows.Forms
Public Class FrmReports
    Dim crParameterDiscreteValue As ParameterDiscreteValue
    Dim crParameterFieldDefinitions As ParameterFieldDefinitions
    Dim crParameterFieldLocation As ParameterFieldDefinition
    Dim crParameterValues As ParameterValues
    Dim varPrintOptions As String
    Private Sub FrmReports_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'DSDirection.Direction' table. You can move, or remove it, as needed.
        Me.IndTerritoryTableAdapter.Fill(Me.DsIndTerritory.IndTerritory)
        Me.QuarterMasterTableAdapter1.Fill(Me.DSQuarterMaster.QuarterMaster)
        Me.FiscalMasterTableAdapter.Fill(Me.DSFiscalMaster.FiscalMaster)

        frmload()

    End Sub
    Private Sub FillToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FillToolStripButton.Click
        Try
            '  Me.ReportsTableAdapter.Fill(Me.Dsreports.Reports, New System.Nullable(Of Integer)(CType(SiteIDToolStripTextBox.Text, Integer)), IndTypeToolStripTextBox.Text, ReportingTypeToolStripTextBox.Text)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click


        Dim fiscal1, fiscal2 As String

        fiscal1 = ComboBox6.Text & "-" & ComboBox3.Text
        fiscal2 = ComboBox5.Text & "-" & ComboBox4.Text
        Me.Cursor = Cursors.WaitCursor

        Me.ReportsTableAdapter.Fill4Index(Me.Dsreports.Reports, comboSite.Text, Trim(ComboBox2.Text))

        Dim rpt As New QMIndex()
        'Dim rpt As New SDReport()

        rpt.SetDataSource(Dsreports)
        ' ' ' ' rpt.OpenSubreport("acro").SetDataSource(DSAcro)
        ' rpt.OpenSubreport("Status").SetDataSource(DSStatusTrend)


        rpt.SetParameterValue("FiscalQtrFrom", fiscal1)
        rpt.SetParameterValue("FiscalQtrto", fiscal2)
        rpt.SetParameterValue("Site", Trim(comboSite.Text))

        'rpt.SetParameterValue("IndicatorType", Trim(ComboBox1.Text))
        rpt.SetParameterValue("ReportTYpe", Trim(ComboBox2.Text))

        Dim CompleteFileName As String = "S:\OP PERF\LHSC-SJHC\Indicator Reporting\ExportPDF\Appendix1.pdf"

        'rpt.ExportToDisk(ExportFormatType.PortableDocFormat, CompleteFileName)
        CrystalReportViewer1.Show()
        CrystalReportViewer1.Zoom(75)
        CrystalReportViewer1.ReportSource = rpt

        TabControl1.SelectedIndex = 2
        Me.Cursor = Cursors.Default
        '  MsgBox("PDF has generated in S:\OP PERF\LHSC-SJHC\Indicator Reporting\ExportPDF\Appendix1.pdf")


    End Sub

    Private Sub frmload()
        'TODO: This line of code loads data into the 'DSStatusTrend.Status' table. You can move, or remove it, as needed.
        'TODO: This line of code loads data into the 'DSAcro.Acronyms' table. You can move, or remove it, as needed.
        Me.AcronymsTableAdapter.Fill(Me.DSAcro.Acronyms, Val(ComboBox8.SelectedValue))
        'TODO: This line of code loads data into the 'DSQuarterMaster.QuarterMaster' table. You can move, or remove it, as needed.
        Me.SiteTableAdapter.FillBy(Me.DSSites.Site, Val(ComboBox8.SelectedValue))
        'TODO: This line of code loads data into the 'DSIndRepType.IndicatorReportingType' table. You can move, or remove it, as needed.
        Me.IndicatorReportingTypeTableAdapter.FillByTerID(Me.DSIndRepType.IndicatorReportingType, Val(ComboBox8.SelectedValue))
        'TODO: This line of code loads data into the 'DSIndicaotorType.IndicatorType' table. You can move, or remove it, as needed.
        Me.IndicatorTypeTableAdapter.Fill(Me.DSIndicaotorType.IndicatorType)
        Me.DirectionTableAdapter.FillByTer4Rep(Me.DSDirection.Direction, Val(ComboBox8.SelectedValue))

        popList()
        CheckBox1.Checked = False
    End Sub

    Private Sub ComboBox8_SelectedIndexChanged_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox8.SelectedIndexChanged
        frmload()
        popList()

    End Sub

    Private Sub ComboBox2_SelectedIndexChanged_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox2.SelectedIndexChanged
        If Trim(ComboBox2.Text) = "Strategic Plan Report" Then
            popList2()
        Else
            popList2()
        End If
    End Sub

    Private Sub ComboBox2_SelectedValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox2.SelectedValueChanged
        popList()
    End Sub

    Private Sub ComboBox2_TextChanged1(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox2.TextChanged
        If Trim(ComboBox2.Text) = "Strategic Plan Report" Then
            popList2()
        Else
            popList2()
        End If
    End Sub

    Public Sub popList()

        '  If Trim(ComboBox11.Text) = "ALL" Then
        Me.ReportsTableAdapter.Fill4IndFilter(Me.Dsreports.Reports, comboSite.Text, Trim(ComboBox2.Text))
        '     If ComboBox11.Text <> "All" Then
        'Me.ReportsTableAdapter.Fill4IndFilterDir(Me.Dsreports.Reports, comboSite.Text, Trim(ComboBox2.Text), Trim(ComboBox11.Text))
        'End If



        lstIndicators.Items.Clear()
        Dim drNew2 As DataRow
        Dim varind As String

        For Each drNew2 In Me.Dsreports.Reports.Rows
            varind = drNew2("MainIndicator") & " - " & Trim(drNew2("TypeDescription")) & " -  " & drNew2("MainID").ToString


            lstIndicators.Items.Add(varind.ToString)
        Next

    End Sub


    Private Sub ComboBox8_SelectedValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox8.SelectedValueChanged
        popList()
    End Sub

    Private Sub ComboBox11_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox11.SelectedIndexChanged
        popList2()
    End Sub

    Private Sub ComboBox11_SelectedValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox11.SelectedValueChanged
        popList2()
    End Sub
    Public Sub popList2()
        Dim fiscal1, fiscal2, varSelect As String
        Me.StatusTableAdapter.Fill(Me.DSStatusTrend.Status)


        If Trim(ComboBox11.Text) <> "ALL" Then
            Me.ReportsTableAdapter.Fill4IndFilterDir(Me.Dsreports.Reports, comboSite.Text, Trim(ComboBox2.Text), Trim(ComboBox11.Text))
        ElseIf Trim(ComboBox11.Text) = "ALL" Then
            Me.ReportsTableAdapter.Fill4IndFilter(Me.Dsreports.Reports, comboSite.Text, Trim(ComboBox2.Text))
        End If


        lstIndicators.Items.Clear()
        Dim drNew2 As DataRow
        Dim varind As String

        For Each drNew2 In Me.Dsreports.Reports.Rows
            varind = drNew2("MainIndicator") & " - " & Trim(drNew2("TypeDescription")) & "-    " & drNew2("MainID").ToString


            lstIndicators.Items.Add(varind.ToString)
        Next

    End Sub

    
    
    Private Sub Button4_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

        Dim fiscal1, fiscal2, varSelect As String
        Me.StatusTableAdapter.Fill(Me.DSStatusTrend.Status)
        If RadioButton1.Checked = True Then
            varPrintOptions = "Both"
        End If
        If RadioButton2.Checked = True Then
            varPrintOptions = "Summary"
        End If
        If RadioButton3.Checked = True Then
            varPrintOptions = "Detail"
        End If

        If CheckBox1.Checked = True Then
            varSelect = "Yes"
        Else
            varSelect = "No"
        End If

        If ComboBox11.Text = "Select Direction Please" Then
            MsgBox("Select Direction Please ", MsgBoxStyle.Information)
            ComboBox11.Focus()
            Exit Sub
        End If

        Me.Cursor = Cursors.WaitCursor
        fiscal1 = ComboBox6.Text & "-" & ComboBox3.Text
        fiscal2 = ComboBox5.Text & "-" & ComboBox4.Text

        Me.ReportsTableAdapter.Fill(Me.Dsreports.Reports, comboSite.Text, Trim(ComboBox2.Text))
        Me.RptSummTableAdapter.Fill(Me.DSReports2.rptSumm, comboSite.Text, Trim(ComboBox2.Text))

        Dim rpt As New QMIndicatorReporting25q()

        rpt.SetDataSource(Dsreports)
        rpt.OpenSubreport("acro").SetDataSource(DSAcro)
        rpt.OpenSubreport("Status").SetDataSource(DSStatusTrend)
        rpt.OpenSubreport("RptSummary").SetDataSource(DSReports2)


        rpt.SetParameterValue("FiscalQtrFrom", fiscal1)
        rpt.SetParameterValue("FiscalQtrto", fiscal2)
        rpt.SetParameterValue("Site", Trim(comboSite.Text))


        rpt.SetParameterValue("ReportTYpe", Trim(ComboBox2.Text))
        rpt.SetParameterValue("prmDirection", Trim(ComboBox11.Text))
        rpt.SetParameterValue("PrintOptions", varPrintOptions)
        rpt.SetParameterValue("prmSelect", varSelect)
        rpt.SetParameterValue("prmIndId", 1)


        crParameterFieldDefinitions = rpt.DataDefinition.ParameterFields
        crParameterFieldLocation = crParameterFieldDefinitions.Item("prmIndId")
        crParameterValues = crParameterFieldLocation.CurrentValues
        For ctr = 0 To lstIndicators.CheckedItems.Count - 1
            Dim tt As String = lstIndicators.CheckedItems(ctr).ToString
            Dim PrmInd As Integer
            PrmInd = Val(Mid(Trim(tt), Len(Trim(tt)) - 3, 5))
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = PrmInd
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)
        Next


        Dim CompleteFileName As String = "S:\OP\Indicator Reporting\ExportPDF\Report2.pdf"

        CrystalReportViewer1.Show()

        CrystalReportViewer1.ReportSource = rpt
        CrystalReportViewer1.Zoom(50)
        Me.Cursor = Cursors.Default
        TabControl1.SelectedIndex = 2
    End Sub
End Class

[dbasnett]

See http://www.vbforums.com/showpost.php...01&postcount=7

[The_Grudge]

Thanks - I threw a post in there as well, linking to this thread.

[Hack]

Moved To Reporting

Try this: Download the free Express version of VS 2008 and run his original code against that. If it works fine, then it could be a VB.NET problem between the two versions. If that is the case, I'll move it back to the VB.NET section.

[wes4dbt]

Try setting the reportsource property before you execute the "crystalreportviewr1.show" method

[The_Grudge]

Try setting the reportsource property before you execute the "crystalreportviewr1.show" method

Thanks for the idea - unfortunately no dice.

I've discovered something else though. It's NOT the checkbox in particular that causes the report to work or not. The first time I run the report it always prints way too much data. If I return to the "generate report" screen and touch nothing, but run the report again - it works.

[wes4dbt]

You have Button2 and Button4 that produce reports. Which report are you talking about?

[The_Grudge]

You have Button2 and Button4 that produce reports. Which report are you talking about?

It's button4 that fires this particular report.

I spent some more time with this today and I think it's got something to do with the code snippets dealing with ComboBox11 and checking for "ALL". There are two similar instances of this code in the form. The first time the program runs (when it doesn't work), the value of the Combobox is "". The second time through it's "ALL" and everything works.

   If Trim(ComboBox11.Text) <> "ALL" Then
            Me.ReportsTableAdapter.Fill4IndFilterDir(Me.Dsreports.Reports, comboSite.Text, Trim(ComboBox2.Text), Trim(ComboBox11.Text))
        ElseIf Trim(ComboBox11.Text) = "ALL" Then
            Me.ReportsTableAdapter.Fill4IndFilter(Me.Dsreports.Reports, comboSite.Text, Trim(ComboBox2.Text))
        End If

Again, I didn't write this code so I may re-write this portion and the other one like it. I'm thinking it's in here somewhere.

[wes4dbt]

Yeah, it's a little to complicated for me to track down the problem by just looking at the code. I would suggest putting Break Points and tracking the values. You could also try printing there values in the immediate window like

   
Debug.Print "CB11 = " & comboBox11.Text
If Trim(ComboBox11.Text) <> "ALL" Then
            Debug.Print "combosite = " & comboSite.Text & "  combobox2 = " & combobox2.text
            Me.ReportsTableAdapter.Fill4IndFilterDir(Me.Dsreports.Reports, comboSite.Text, Trim(ComboBox2.Text), Trim(ComboBox11.Text))
        ElseIf Trim(ComboBox11.Text) = "ALL" Then
Debug.Print "combosite = " & comboSite.Text & "  combobox2 = " & combobox2.text
            Me.ReportsTableAdapter.Fill4IndFilter(Me.Dsreports.Reports, comboSite.Text, Trim(ComboBox2.Text))
        End If

You said the developer doesn't have this problem. If your using the same program and the same database then that's not likely. My guess would be there is something he does different then you when he creates the report. Maybe try following him keystroke by keystroke and see what happens.

Good luck