Great Outdoors stopped selling Trail Star in 2011, and 2011 is now the default value in the prompt. Notice using a double quote to escape the single quote.

We can add filters to the query like this: Cognos is actually making a query that runs maximum(), and returns that value to the macro – exactly like a parameter map would. This behaves exactly like the parameter map, and the SQL is identical. Instead, let’s take a look at queryValue() What if we want the last date of data for a specific product line? Or product? It requires access to the framework model, and a new parameter map for each and every scenario that we need. This is much better but still way too clunky. Create a model query that has the value you want, add a data item with a static 1, and create a parameter map based on that. One way around would be to create a parameter map in the framework model. It’s creating a window function, and actually hits every row in that table. 'TQ0_q_SelectedMonth1'.'Year0' AS 'Year0' 'GOSALESDW'.'gosalesdw'.'GO_TIME_DIM' 'GO_TIME_DIM'

For example, my report might need to let the user select a year, but last date with data is unknown. There are many times where I want a way of pulling a default value from a database in a filter. On the surface it looks fairly innocuous, but the result of this is a lot of effort saved when building reports. There’s a new function called “queryValue”. A very pleasant surprise when building reports.

Cognos sql prompt