Cognos 8.3 - date prompt macro problem

Question

In Report Studio 8.3, I've got this raw SQL query running against a MySQL 5.1 data source:

SELECT enc.encounterID, enc.date
FROM enc
WHERE enc.date between #prompt('textPromptStartDate')# AND #prompt('textPromptEndDate', 'date')#

This produces text prompts for the user. If the user enters dates into these prompts in the format of 'YYYY-MM-DD', e.g. '2010-01-15', the query works fine. But I want to replace the text prompts with proper Date prompts. When I try replacing the above prompts with

#prompt('datePromptStartDate', 'date') AND #prompt('datePromptEndDate', 'date')#

the query runs (no errors are generated), but I get an empty result set. I have a feeling I need to adjust the date format that the date prompt macro returns, but I'm stuck after many hours of experimentation as to how to debug this.

Answer 1

You can create a prompt page with date prompt controls, and then bind them to the parameters in your prompt macros. I tested this out in 8.4 with MySQL 5.1 (ODBC) using the query you have above and it worked. I also had the same problem you experienced with no data being returned prior to creating a separate prompt page and binding the controls.

The steps you would take to do this are:

1. add a prompt page (if one doesn't exist) to your report
2. add 2 date prompt controls to the prompt page
3. in the "Parameter" property for the first date prompt, use: datePromptStartDate
4. in the "Parameter" property for the second date prompt, use: datePromptEndDate

When you execute the report, the new prompt page will replace the prompt page that is generated from the macro, and the parameters in the macros will be bound to the date controls.

Edit: I forgot to add that the macros in your SQL should look like the following:

SELECT enc.encounterID, enc.date 
FROM enc 
WHERE enc.date between #prompt('textPromptStartDate')# 
AND #prompt('textPromptEndDate')#