CODEWITHSUNDEEP
sql-server-2008t-sqlreporting-servicesparametersssrs-2008
MISNole
MISNole

Reputation: 1062

SSRS Parameter that selects on days of the week within the date range

I have a report that runs embedded SQL that uses a simple @startDate and @endDate parameter and the variables in a WHERE clause:

And DM.FromDateTime Between @startDate AND @endDate

What I would like to accomplish is to have an additional parameter that would return only the day of the week the user selected.

For example, if the user chose 3/1/2015 to 3/31/2015 as their date range and selected 'Monday', the report would only populate with data from 3/2, 3/9, 3/16, 3/23, and 3/30.

I know SQL has datepart (dw) function, but any advice on how to handle this parameter?

Upvotes: 0

Views: 396

Answers (1)

Alan Cortez
Alan Cortez

Reputation: 86

Create a new integer report parameter @DayOfWeek. It's available values are:
'Sunday' = 1
'Monday' = 2
'Tuesday' = 3
'Wednesday' = 4
'Thursday' = 5
'Friday' = 6
'Saturday' = 7

Then your where clause is simply:

And DM.FromDateTime Between @startDate AND @endDate
AND DATEPART(WEEKDAY, DM.FromDateTime) = @DayOfWeek

Upvotes: 1

Related Questions