Set a Default Value to a Prompt Based on a Database Value?

I have a report that first displays a prompt page with a value prompt. The prompt is linked to a query that selects all dates that are valid for the report i.e. dates for which report data is available. The user selects a date, clicks finish and the report is generated with data for the selected date.

One of the requirements of the report is that it can also be submitted as a batch report using the data for the latest date (MAX) available. I could easily do this by creating a second version of the report without the prompt page that picks the correct date in the query, but I would like to avoid having (supporting) two versions of the same report.

I tried the following. The date query returns two columns; the actual date and a display date. The display date is a formatted version of the actual date with the exception of the latest date. For the latest date the display date is the text 'Current Date'. In the Properties of the Value Prompt the Display Value is the display date and the Use Value is the actual date. As mentioned above in interactive mode if 'Current Date' is selected the actual date is used as a parameter in the rest of the queries, as expected. For batch mode I created a view of the report, set up a schedule based on a trigger and under Prompt Values selected a default value for the prompt of 'Current Date'. The expectation was that month after month (it is a monthly report) it would fire the query to identify the new 'Current Date' and use it for the date of the report. It seems that this worked the first month that the trigger was set up, but did not work for subsequent months.

That is a long description of what I am trying to accomplish. I appreciate all suggestions but I would prefer to avoid a JavaScript / HTML solution.

Based on the documentation that I can find, #prompt is used within a user defined sql and appears to want a specific value in the string. How would I express a data item or other column value within that syntax?
prompt ( prompt_name , datatype , defaultText , text, queryItem , trailing_text )

Is there a way to use it from within a Cognos filter object?

if the current filter in Cognos is [DBSource].[Order Date] = ?End_Date?

What would the resulting filter look like in Cognos using your "#prompt ..." syntax?

Copyright 1998-2015 Ziff Davis, LLC (Toolbox.com). All rights reserved. All product names are trademarks of their respective companies. Toolbox.com is not
affiliated with or endorsed by any company listed at this site.