Crystal Reports - Adding a parameter to a 'Command' query

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.

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.

The solution I came up with was as follows:

1. Create the SQL query in your favorite query dev tool
2. In Crystal Reports, within the main report, create parameter to pass to the subreport
3. Create sub report, using the 'Add Command' option in the 'Data' portion of the 'Report Creation Wizard' and the SQL query from #1.

4. Once the subreport is added to the main report, right click on the subreport, choose 'Change Subreport Links...', select the link field, and uncheck 'Select data in subreport based on field:'

   NOTE: You may have to initially add the parameter with the 'Select data in subreport based on field:' checked, then go back to 'Change Subreport Links ' and uncheck it after the subreport has been created.

5. In the subreport, click the 'Report' menu, 'Select Expert', use the 'Formula Editor', set the SQL column from #1 either equal to or like the parameter(s) selected in #4.

                   (Subreport SQL Column)  (Parameter from Main Report)
           Example:  {Command.Project} like {?Pm-?Proj_Name}