(RS - using Powercube) - sort month level in date hierarchy dimension

[tyody]

Hi all -

I have a report studio report that prompts for the month level of the date hierarchy dimension.  The month level has all values from 2004/Jan through 2007/Dec.  I need to 1. Filter this to just show values for the last 3 rolling years (ie 2004/April - 2007/April). and 2. Sort the values descending (so 2007/April is the first value in the drop down list. 

Anyone know how or if this can be done?

Thanks!

[workdan]

You don't say what type of prompt your using, but I wonder if the following might work:

Define the query that populates the prompt with a filter that uses the Business Date/Time functions to check if the year of the field is within three years of the current date and, if it's in the earliest year, if the month of the field is greater than or equal to the current month.

As far as sorting the prompt, I believe you should be able to just apply a Pre-Sort property to the prompt query.

Sorry I can't be more specific, but this is just off the top of my head. Hope it works out!

Dan

[tyody]

Thanks Dan - I'm using a value prompt.  The date time function don't work with dimensional data.  (At least I can't seem to get them to work).  I've tried to presort and its not working either.