Cascading prompt that will pass year and then populate the months

[shelbsassy]

Hello,
I have created a prompt page with 2 prompts - one for Agent Name and then one for Year. I would like to create a cascading prompt from Year that when Year is selected, a Month prompt will populate with the months in that year (that would be multi-select). I have tried this a few times but keep getting errors saying objects aren't found. I don't think I am applying filters correctly.
In Query 1, I have [Year] as extract(year,[OPEN_DATE]) and I also have [Year as char] which is cast([Year]as char(4)).
For the Year prompt, I am using [Year] as the value and [Year as cast] for the display (when I had [Year] as the display, I would get 2,009.
The filter I have in the Query 1 is [Year] IN ?Year?
Everything works as it should, I have this as multi-select right now and reports are correct.
If I create a Month prompt, the value I have for month is:
[Month] data item from Query 1 is

CASE extract(month,[OPEN_DATE])
WHEN 1
THEN 'Jan'
WHEN 2
THEN 'Feb'
WHEN 3
THEN 'Mar'
WHEN 4
THEN 'Apr'
WHEN 5
THEN 'May'
WHEN 6
THEN 'Jun'
WHEN 7
THEN 'Jul'
WHEN 8
THEN 'Aug'
WHEN 9
THEN 'Sep'
WHEN 10
THEN 'Oct'
WHEN 11
THEN 'Nov'
WHEN 12
THEN 'Dec'
ELSE 'Unknown'
END

I know the cascading source on the Month prompt would be ?year? but how do I write the filters in the Query 1, Year prompt query and Month prompt query?

What am I missing?
Thank you,
Shel

 

[blom0344]

Perhaps a more suitable solution is to base the month prompt on a fixed selection with 12 values instead of using a prompt query.
Define a value prompt and set UI to radio button group. Now go to the static choices and add 12 static values:

Use Display
1 'Jan'
2 'Feb'
3 ..
4 ..
5
6
7
8
9
10
11
12 'Dec'

Add parameter 'month'
Set multi-select to 'yes'

The final data query filter will then be:

extract(year,[OPEN_DATE]) = ?Year? and extract(month,[OPEN_DATE])
in ?month?


Ties Blom