Any ideas on how to set the default value of a checkbox prompt dynamically to maximum value in the prompt?
Prompt: Year (contains values such as 2014-15, 2015-16, 2016-17 etc.)
Data source: Relational
Cognos version: 11.0.13
Thanks!
In the prompt query, create data item c_UseYear: if ( [Year] = maximum ( [Year] for report ) then ( '111111' ) else ( [Year] )
Make sure the default value (111111 in this case) is something your prompt will never have otherwise.
Create prompt set to prompt query:
- Use Value: c_UseYear
- Display Value: Year
- Default Selections: 111111
In the query to be filtered, make sure the filter handles the new default value:
if ( ?p_Year? = '111111' ) then ( [Year] = maximum ( [Year] for report ) ) else ( [Year] = ?p_Year? )
You can create maximum ( [Year] for report ) as a separate data item if you prefer, but it should work either way.
My approach is based off this post: http://cognosknowhow.blogspot.com/2013/04/how-to-dynamically-set-up-default-value.html (http://cognosknowhow.blogspot.com/2013/04/how-to-dynamically-set-up-default-value.html)
Thank you very much! Your solution worked for me! I took the same approach before but missed modifying the filter n the query to handle default value. Thanks again
How to make this work for a multi select checkbox prompt? It worked for my single select prompt.