[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.

[Crooze]

The easiest way would be to edit the report, create a parameter field for the year. Then add the parameter field into your report record selection formula. Crystal will add this to the where clause every time you run the report thus prompting you for the the year.

[mabbas110]

Create a parameter field and in record selection aread define that year to the parameter field u created. Pass that parameter field from report.

[MMock]

I created a Parameter Field for the Year, that was easy.

How do I add the parameter to the report record selection formula? Is this something you could walk me through? (I know you both have your own work to do so I understand if you can't). Do you know of a tutorial?

I found a tutorial on MSDN - "Creating a Report with Parameters" and it was helpful but it uses a table. So for example what you are doing in their sample report is selecting rows from a table with a city = "Athens". I don't know how to make the transition from a table to a query or to a stored procedure. What should I do differently than what I am doing now and what is the same? Do you know where this might be documented?

Your answers are appreciated but I won't understand how to just do something without being pointed to in-depth instructions. I apologize but there is a lot to learn.

Thanks.

[Crooze]

Once in Crystal it makes know difference if it's a table, query or store procedure.
In Crystal open the Record Selection Formula editor (I believe it's in the Report Menu) and add your filter there. {year_field} = {@year_parameter}.

Don't confuse this with a SQL query. Think of this as an additional filter to the dataset that is being presented to the report.

[MMock]

You are right, it made no difference if I used a query or a stored procedure.

What I did was in Database Expert add my stored procedure just as formerly I had defined a command. My sp wants a parameter called @sYear. Once I executed that step, in the Parameter Fields @sYear was there automatically.

Now when I run my report, I am prompted for the year.

I don't know why it took me three days to find this. The information was actually contained in a link I found which helped me to eliminate having to enter login credentials at runtime.

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

For Each tbCurrent In cbsMain.Database.Tables
    tliCurrent = tbCurrent.LogOnInfo
    With tliCurrent.ConnectionInfo
        .ServerName = "localhost"
        .UserID = "ReportUser"
        .Password = "msdn"
        .DatabaseName = "Northwind"
    End With
    tbCurrent.ApplyLogOnInfo(tliCurrent)
Next tbCurrent

This article is located at http://msdn.microsoft.com/msdnmag/issues/02/05/Crystal/. I guess I should have read further on for the remaining information I needed.

Thank you for your time. I hope this helps someone in the future.

[jre1229]

I am trying to do this but it says "Load Report Failed". Here is my code.

Dim crConnectionInfo As New CrystalDecisions.Shared.ConnectionInfo()
Dim tbCurrent As CrystalDecisions.CrystalReports.Engine.Table
Dim tliCurrent As CrystalDecisions.Shared.TableLogOnInfo

Dim cr As New test1
CrystalReportViewer1.ReportSource = cr

For Each tbCurrent In cr.Database.Tables
tliCurrent = tbCurrent.LogOnInfo
With tliCurrent.ConnectionInfo
.ServerName = "MYSERVERNAME"
.UserID = "MYUSERID"
.Password = "MYPASS"
.DatabaseName = "MYDATABASENAME"
End With
tbCurrent.ApplyLogOnInfo(tliCurrent)
        Next tbCurrent

I am using Oracle as a DB backend. Any help would be appreciated! By the way, it works if I do not use the code to pass user/pass. But then I have to enter the login info each time the report opens. Thanks!

[MMock]

Hi jre1229 -

I have not been using Crystal lately and I really don't remember any of this. I hope someone else who may be more current will take note of your issue. Good luck.