[RESOLVED] Pass a parameter to a query in Crystal Reports

[MMock]

I am having trouble understanding how all the pieces of a crystal report work together. I've been searching the Internet and have slowly gotten my first report working but it's reporting on a hardcoded year, 2006, and I want to pass the year in at runtime, either being prompted or by the user entering the year in a textbox.

My environment is vb.net 2005 and the Crystal it's bundled with.

I have a Windows Form named frmCrTest2.vb. It has a CrystalReportViewer control. This is the code when the form loads:

    Private Sub frmCrTest2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Me.CrystalReportViewer1.ReportSource = cbsMain

        Dim tbCurrent As CrystalDecisions.CrystalReports.Engine.Table
        Dim tliCurrent As CrystalDecisions.Shared.TableLogOnInfo

        ' I don't know what I have that's equivalent to his cbsMain
        ' This is how he got his: If you click on the Components palette of the Toolbox window in Visual Studio .NET, you'll see an object called ReportDocument. If you drag one onto your form, you'll see the class name of your report listed in the dropdown portion of the Name combobox in the Choose a ReportDocument dialog. Select it and click OK, and you'll have a runtime-available instance of your report class. Rename the object to cbsMain (cbs is the prefix for CustomersBasic), and substitute the following line of code in the form's Load event:
        
        For Each tbCurrent In Me.cbsMain.Database.Tables
            tliCurrent = tbCurrent.LogOnInfo
            With tliCurrent.ConnectionInfo
                .ServerName = ...
                .UserID = ...
                .Password = ...
                .DatabaseName = ...
            End With
            tbCurrent.ApplyLogOnInfo(tliCurrent)
        Next tbCurrent

        '        ' I added this at 13:47 on 08/08 and this seems to be overriding the query I have defined in Command
        '        '  However, I'm getting an error 
        '        Me.cbsMain.RecordSelectionFormula = "select DATENAME(month, [Date Id Number Added]) + ' 2005' as theMonth" & _
        '    ", Count([ID Number]) AS [No of ID's]" & _
        '     ", max([ID Number]) as [Last Id Number]" & _
        '  "FROM [BE_table] " & _
        ' "WHERE DateName(year,[Date ID Number Added])='2005' " & _
        '"GROUP BY DATENAME(month, [Date Id Number Added]) " & _
        '"ORDER  BY CHARINDEX(LEFT(DATENAME(month, [Date Id Number Added]),3) " & _
        '         ", 'JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')"

        ' This was a reply to a post where someone got the same error
        ' The RecordSelectionFormula is not a sql statement. It lets you specify different criterias or "programming logic". If possible Crystal will apply the different items within the formula to the Where clause. If not possible Crystal will first get the data from the database and then pass through all the data removing records that don't fit the formula.

        ' This should be all you need.

        '      crDOC.RecordSelectionFormula = "{tblBatchDetail.rowID} = " & mRowID

        ' So if it's like a WHERE clause, where's the rest of the query?

        ' Me.cbsMain.RecordSelectionFormula = 

        ' This instruction was legal, i.e. "@paramYear" caused an exception, this didn't, but it still reported 2006
        Me.cbsMain.SetParameterValue("paramYear", "2005")

    End Sub

Please forgive my comments and commented-out code. I am learning and thus making notes to myself along the way.

I think what I am interested in is SetParameterValue. But, what is the connection between the code above, and the screen shot I've attached which shows my report design and on the left is Field Explorer. I have a query defined in the command node that's this:

select DATENAME(month, [Date Id Number Added]) + ' 2006' as theMonth
, Count([ID Number]) AS [No of ID's]
, max([ID Number]) as [Last Id Number]
FROM [BE_Table]
WHERE DateName(year,[Date ID Number Added])='2006'
GROUP
BY DATENAME(month, [Date Id Number Added])
ORDER
BY CHARINDEX(LEFT(DATENAME(month, [Date Id Number Added]),3)
, 'JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')

My questions are: How do I let '2006' be passed as a parameter? What is the relationship between Database Fields...command and Parameter Fields. What is Formula Fields? Additionally (or alternatively), I have this same query written as a stored procedure but I'm having the similar problem of how to pass the parameter. And I think when I was using the stored procedure I wasn't getting as far as I am using the query. But that ultimately is my goal.

Thanks.