asked on

Removing saturday and sunday from ssrs 2008 calendar report

I used the following tutorial to create a calendar based report in SSRS. It is working but not exactly to what my requirements are,

My DBMS for scheduling ensures no tasks are added to Saturday's and Sunday's but I am not sure how to reflect that via this report the way it is currently built

Because of the way the query is built, right now tasks that aren't actually scheduled on a Saturday or Sunday are displaying as if they are because I haven't found a way to filter out those days. I am hoping someone can tell me how to accomplish this.

http://rduclos.wordpress.com/2010/02/13/ssrs-2008-generate-calendars-based-on-a-date-range/

Thanks for any help!

ValentinoV

Ideally you'd have a Date dimension that contains a column to indicate which day of the week the record represents. In that case you could just use a WHERE clause to filter out the weekend days.

Another option is to use either the DATEPART or DATENAME function. Note that the result of DATEPART is influenced by the DATEFIRST setting, as following example indicates:

select DATEPART(dw, getdate())
select DATENAME(dw, getdate())

set datefirst 3;

select DATEPART(dw, getdate())
select DATENAME(dw, getdate())

Open in new window

Easwaran Paramasivam

Include below highlighted where clause to filter saturday and sunday. HTH.

SELECT
DisplayOnCalendar = DENSE_RANK() OVER (ORDER BY d.Year, d.Month),
d.Month,
[Day] = DATEPART(DAY,d.[Date]),
d.Year,
[WeekDay] = DATEPART(WEEKDAY, d.[Date]),
[Order] = DENSE_RANK() OVER (PARTITION BY d.Year, d.Month ORDER BY d.Date),
d.Date,
ebd.FullName
FROM
Dates d
LEFT JOIN EmployeeBirthDay ebd ON ebd.Month = DATEPART(MONTH,d.[Date]) AND ebd.Day = DATEPART(DAY,d.[Date])
WHERE ((DATEPART(dw, d.[Date]) + @@DATEFIRST) % 7) NOT IN (0, 1)

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial