Crystal Reports - Adding a parameter to a Command query

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   gt   currentdate

User · Answer

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  gt   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

User · Answer

The solution I came up with was as follows    Create the SQL query in your favorite query dev tool In Crystal Reports  within the main report  create parameter to pass to the subreport Create sub report  using the  Add Command  option in the  Data  portion of the  Report Creation Wizard  and the SQL query from  1  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  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

User · Answer

Select Projecttname  ReleaseDate  TaskName From DB Table Where Project Name like     Pm- Proj Name    and ReleaseDate  gt   currentdate   Note the single-quotes and wildcard characters  I just spent 30 minutes figuring out something similar

User · Answer

Try this   Select Project Name  ReleaseDate  TaskName From DB Table Where Project Name like    Pm- Proj Name     And ReleaseDate  gt   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

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

Examples related to crystal-reports