Crystal Reports - Adding a parameter to a 'Com

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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}
   

Answer 4

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.