asked
SSRS default date 20th of month
Hello I've converted a spreadsheet into a SSRS report which is run on the 20th of month and I need to pass the 20th of the current month as a default value (start date). If the 20th falls on a Saturday, default to friday 19th. If the 20th falls on Sunday default to Friday 18th. How do I do this and what is the best way of doing it?
Personally, I would handle this within the stored procedure and not try and work this out in SSRS. This code snippet shows how to determine a startdate according to your rules. Note that in my environment the week starts on a Sunday (i.e.@@DATEFIRST = 7) - if you have a different start to the week you will need to adjust the values. declare @d datetime --set @d = '20 august 2011' -- saturday set @d = '20 November 2011' -- sunday select @startdate = case when DATEPART(dw,@d) = 7 --Saturday then @d-1 when DATEPART(dw,@d) = 1 -- Sunday then @d -2 else @d end select @startdate
I presumed there was user input specifying dates, with an optional default
Can you not change the report SQL at all? It sounds like you can't as the report exists, but you have a specific requirement to call it with specific parameters - is this right?
