How to have a Value Prompt of YEAR display all months in that year

[shelbsassy]

I successfully created a crosstab with Month/Year as the column and the row is the Agents name. There are no filters for dates so it shows all months and years in the database (2/09 - 2/11). I am trying to add a prompt page where the user can select the Agent Name from a value prompt and then I have a value prompt for the year. I have the Agent part done, having trouble with the year, and how to pass the parameter into my report.
What I have for the year value prompt is: I used the dataitem [Year] which is extract(year,[OPEN_DATE]) and named the parameter ?year? In the value prompt it displays 2009, 2010, 2011 but when I click one of the years, i get a blank page. I just don't know how to write a filter or expression to say that if 2009 is chosen, display all the months in 2009 (and the others) separately - which is what I finally achieved in my crosstab above. The crosstab column (crosstab node member) contains <#COMBINE#> which is [Month as char]||' - '||[Year as char]. I am just now trying to figure out how to break them up by year but still display all the months in that year. The only reference in my main query that I have to the value prompt is [YEAR]=?year?, which obviously isnt working. What kind of filter do I need to create to get those date ranges and have them disp
lay correctly? Do I need to do something with the <COMBINE> field that is the column header/top node? Do I need to create a higher column that shows the year and then the month/year would be a subset of that?

Thank you for any help, I cannot find anything close to what I need on any of the boards or User Manual and I am pretty new to Cognos.

Shel

 

[blom0344]

The definition of the #COMBINE# dataitem is irrelevant. Best practice is to define seperate prompt queries and use the prompt parameter as a filter in the main data query.

So, If you want to select only whole years from your value prompt - with multiple select enabled - then define a prompt query that simply fetches extract(year,[somenamespace].[some_date]). Make the value prompt a multiselect. Define parameter p_Year for the prompt.

Now add a filter to the main query :

extract(year,[open_date]) in ?p_Year? where [open_date] is the name of the data_item in the main query

Ties Blom