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