Disable the "Weekend" on SSRS Date Type Parameter

0
votes

I have a report and the report has parameter (@date) for user to pick a date.

Currently, my parameter (@date) use Parameter Properties > Default Values > =Today() (For set today date as default paramter).

But I also want the parameter can disable the weekend days. So the user only can select the weekday.

How can i do it ? Please help.

2
reporting-servicesssrs-2012
No, this is not possible in SSRS. If you do a list of available values for the dates, then you loose the date picker functionality. – Harry
Ohh, i just know it. Thankyou very much. – harwin vigo
For your parameter (@date) what range do you expect the user to be limited to other than no weekend dates? for example + or - 60 days from current date? – SuperSimmer 44
Nothing else. I just don't want the user choose the weekend date. Hmm... maybe i can use the Max() from date of my fields... maybe ? – harwin vigo

2 Answers

1
votes

You cannot disable weekends on the date picker. If the user selects a weekend date and that will not result in error. May I suggest using visibility to hide your tables/matrices based on the date they select?

In your table(s) properties, go to the table visibility option and use this expression:

=IIF(weekday(Parameters!YourParameterName.Value) = 1, TRUE,IIF(weekday(Parameters!YourParameterName.Value) = 7, TRUE, FALSE))

Add a textbox after your table(s), include an "error message" in the textbox to display for users if they select a weekend date. Go to the textbox property, visibility option and use this expression:

=IIF(weekday(Parameters!YourParameterName.Value) = 1, FALSE, IIF(weekday(Parameters!YourParameterName.Value) = 7, FALSE, TRUE))

I'm using nested IIF function in here but you can use SWITCH if desired.

The result is when a weekend date is selected, your textbox will be displayed with your message. Otherwise your table(s) will show.

0
votes

If you don't mind losing the functionality of the date picker you could set up the @date parameter to use the following query, currently set to +/- 60 days from current date, which you could amend to suit your requirements:

;with dates ([startDate]) as (
Select convert(date,DateAdd("d",-60,(getdate()))) as [startDate] 
union all 
Select dateadd(day, 1, [startDate])
from dates
where [startDate] <= DateAdd("d",60,(getdate())))
select [startDate]
from dates
where datepart(dw,[startDate]) in (2,3,4,5,6)   
option (maxrecursion 32767)