CODEWITHSUNDEEP
sqlreporting-servicesreporting
Mukesh
Mukesh

Reputation: 55

Declaring date in SSRS via logic

Can anyone let me know how to declare a date in SSRS via some logic?

if today = Monday then start date = today - 3days

if today != (not equal to ) Monday then start date = today - 1day

This should be given in SSRS so that if I click today's date, yesterday's data should come in my report.

Upvotes: 0

Views: 308

Answers (2)

Naveen Kumar
Naveen Kumar

Reputation: 1541

Write an expression in SSRS to set Date

=IIF(Weekday(Today()) = 2,DateAdd(DateInterval.Day,-3, Today()),DateAdd(DateInterval.Day,-1, Today()))

Upvotes: 0

GarethD
GarethD

Reputation: 69749

Assuming that on Sunday this should also default to Friday, you can set your parameter default to:

=IIf(WeekdayName(Weekday(Today)) = "Sunday", DateAdd("d", -2, today), 
 iif(WeekdayName(Weekday(Today)) = "Monday", DateAdd("d", -3, today), 
 DateAdd("d", -1, today)))

Or For SQL:

DECLARE @Date DATE = '2016-04-18'; -- Monday
SET @Date = DATEADD(DAY, CASE (DATEPART(WEEKDAY, @Date) + @@DATEFIRST) % 7 
                            WHEN 1 THEN -2 
                            WHEN 2 THEN -3 
                            ELSE -1 
                        END, DATEDIFF(DAY, 0, @Date));

SELECT  @Date;

Which gives 2016-04-15 (Friday)

Upvotes: 1

Related Questions