[crystal-reports] Crystal Reports - Adding a parameter to a 'Command' query

Crystal Version - Crystal Reports 2008 Business Objects - XI

I have written a query to populate a subreport and want to pull in a parameter to that query based on input from a user. My question is, what is the correct syntax I need to put in the first line of the 'Where' clause to accept the parameter?

Here is the query I am using in Crystal Reports:

Select 
Projecttname,
ReleaseDate,
TaskName

From DB_Table

Where
(Project_Name like {?Pm-?Proj_Name})) and 
(ReleaseDate) >= currentdate

Try this:

Select Project_Name, ReleaseDate, TaskName
From DB_Table
Where Project_Name like '{?Pm-?Proj_Name}'
  And ReleaseDate >= currentdate

currentdate should be a valid database function or field to work. If you are using MS SQL Server, use GETDATE() instead.

If all you want is to filter records in a subreport based on a parameter from the main report, it might be easier to simply add the table to the subreport, and then create a Project_Name link between the main report and subreport. You can then use the Select Expert to filter the ReleaseDate as well.

Select Projecttname, ReleaseDate, TaskName From DB_Table Where Project_Name like '%{?Pm-?Proj_Name}%' and ReleaseDate >= currentdate

Note the single-quotes and wildcard characters. I just spent 30 minutes figuring out something similar.

When you are in the Command, click Create to create a new parameter; call it project_name. Once you've created it, double click its name to add it to the command's text. You query should resemble:

SELECT Projecttname, ReleaseDate, TaskName
FROM DB_Table
WHERE Project_Name LIKE {?project_name} + '*'
AND ReleaseDate >= getdate() --assumes sql server

If desired, link the main report to the subreport on this ({?project_name}) field. If you don't establish a link between the main and subreport, CR will prompt you for the subreport's parameter.

In versions prior to 2008, a command's parameter was only allowed to be a scalar value.