Date range static prompt

[beerguy82]

Hey all -

I am having trouble with what seems like it should be a pretty minor issue.  Here is the background:

I am developing a report which will be run on several static time periods.  Currently I have a radio button prompt with static choices in the following format:

Use Value:  {2010-06-28:2010-07-02}  Display Value:  July 4th Display Period

This static prompt is running a filter with the following syntax:

cast([blah].[blah blah].[Date-Time Item],date) in_range #prompt('pPeriod1', 'range', '{:2000-01-01}')#

I have the default value in there because this is an optional prompt and I am doing some conditional render based on whether data is returned or not, and prior to 2000 there is no data.

When I run the report, and don't select a prompt value, the report returns as expected (no data).  When I choose the July 4th Display Period, I get the following error:

QE-DEF-0261 QFWP - Parsing text: cast([blah].[blah blah].[Date - Time Item],date) in_range '{2010-06-28:2010-07-02}'QE-DEF-0260 Parsing error before or near position: 105 of: "cast([blah].[blah blah].[Date - Time Item],date) in_range '{2010-06-28:2010-07-02}'"

I feel like I need to somehow escape the single quote that Cognos is wrapping the {} in, but for the life of me, I can't figure out how to do it.

Any ideas?

Thanks.

Tim

[MFGF]

Why are you using the braces {} in your Use Value?  If these are substituted into the SQL, they will be interpreted as placeholders for Native SQL.

Regards,

MF.

[beerguy82]

MF -

I was under the impression that the 'in_range' operator required the curly braces.  After removing them, I am still getting the same error, sans {}. 

Tim

[MFGF]

Ah right - I just re-read your post and realised my mistake - you are using a prompt macro in your filter, which I hadn't noticed when I glanced at the message first time.

Try changing the filter to be:

cast([blah].[blah blah].[Date-Time Item],date) in_range #prompt('pPeriod1', 'token', '{:2000-01-01}')#

Does this fix the problem?

Regards,

MF.