Search code examples
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
Solution

  • 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}