Hi,
I have a dropdown value prompt on prompt page which is populated using 'Calendar Date' field from 'Date' Dimension.
Use values of value prompt is _last_of_month ([Date].[Calendar Date]) for showing only last date of month.
Display value of values prompt is cast (extract(day, [Calendar Date]),varchar(2)) + '-' +
case
when (extract(month, [Calendar Date])=1)
then 'JAN'
when (extract(month, [Calendar Date])=2)
then 'FEB'
when (extract(month, [Calendar Date])=3)
then 'MAR'
when (extract(month, [Calendar Date])=4)
then 'APR'
when (extract(month, [Calendar Date])=5)
then 'MAY'
when (extract(month, [Calendar Date])=6)
then 'JUN'
when (extract(month, [Calendar Date])=7)
then 'JUL'
when (extract(month, [Calendar Date])=8)
then 'AUG'
when (extract(month, [Calendar Date])=9)
then 'SEP'
when (extract(month, [Calendar Date])=10)
then 'OCT'
when (extract(month, [Calendar Date])=11)
then 'NOV'
when (extract(month, [Calendar Date])=12)
then 'DEC'
end + '-' +
cast (extract(year, [Calendar Date]),varchar(4)) for showing date in 'DD-MMM-YYYY' format.
I have also applied filter in query of value prompt query to display dates greater than '31-12-2008' which works fine.
Till this point everything is functioning as expected but now Users want to see Current Month's last date should be displayed when they run the report .
Could anyone please suggest how should I achieve this functionality?
You can sort the values in value prompt in desc order and apply one more filter in value prompt query like calendar_Date<=current_Month.
Now you can add javascript to remove 1 st 2 line and making the 1stvalue as default value..