Ideas for calendar display on report - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Ideas for calendar display on report

Author

Topic

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-07-21 : 23:47:47

I need to create a report to show 12 calendar images, one for each month of the year. It needs to be able to set the colour of the day number in the months based on parameters eg public holiday etc. Has onyone done anything like this? I imagine html tables would be the way to go. Any other thoughts?

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-07-21 : 23:51:22

Thinking about it now...RS tables should also work

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-07-22 : 00:14:23

..maybe a single sub-report with a 7x6 RS table plus headings in 12 positions on the main report. Can cells be refererenced by row/col?

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-07-23 : 01:59:05

fyi - ended up creating the proc below to build the calendar. I'll add the non-working days to the temp table & use it for a sub-report for each month.
Preconditions: Start & end date are valid dates within a 1-year span only
CREATE PROCEDURE s_rpt_project_calendar
@date_start datetime,
@date_end datetime = NULL

AS
declare @date_counter datetime
declare @current_month tinyint

IF @date_end IS NULL
SELECT @date_end = '31-Dec-' + str(year(@date_start))

select @date_counter = @date_start, @current_month = month(@date_start)

CREATE TABLE #calendar (
MonthNum int,
Mon datetime,
Tue datetime,
Wed datetime,
Thu datetime,
Fri datetime,
Sat datetime,
Sun datetime
)
-- find the first Monday <= the current date
SELECT @date_counter = CASE WHEN datename(weekday,@date_counter) = 'Monday' THEN @date_counter
WHEN datename(weekday,@date_counter) = 'Tuesday' THEN @date_counter - 1
WHEN datename(weekday,@date_counter) = 'Wednesday' THEN @date_counter - 2
WHEN datename(weekday,@date_counter) = 'Thursday' THEN @date_counter - 3
WHEN datename(weekday,@date_counter) = 'Friday' THEN @date_counter - 4
WHEN datename(weekday,@date_counter) = 'Saturday' THEN @date_counter - 5
WHEN datename(weekday,@date_counter) = 'Sunday' THEN @date_counter - 6
END
While @date_counter <= @date_end AND @current_month <= month(@date_end)
begin
insert into #calendar (MonthNum, Mon, Tue, Wed, Thu, Fri, Sat, Sun)
Values (@current_month,
CASE WHEN month(@date_counter) = @current_month THEN @date_counter ELSE NULL END,
CASE WHEN month(@date_counter + 1) = @current_month THEN @date_counter + 1 ELSE NULL END,
CASE WHEN month(@date_counter + 2) = @current_month THEN @date_counter + 2 ELSE NULL END,
CASE WHEN month(@date_counter + 3) = @current_month THEN @date_counter + 3 ELSE NULL END,
CASE WHEN month(@date_counter + 4) = @current_month THEN @date_counter + 4 ELSE NULL END,
CASE WHEN month(@date_counter + 5) = @current_month THEN @date_counter + 5 ELSE NULL END,
CASE WHEN month(@date_counter + 6) = @current_month THEN @date_counter + 6 ELSE NULL END)
-- select @date_counter, month(@date_counter + 7),@current_month
IF month(@date_counter + 7) <= @current_month
select @date_counter = @date_counter + 7
ELSE
select @current_month = @current_month + 1

end
select * from #calendar
GO

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-07-23 : 22:44:01

FYI: Ended up not using the sub-reports as this involved 12 DB queries (~6 secs report response time run locally). I just put 12 instances of the table on the one report & filtered each(~4 secs response time). I expect the response time difference would be more significant when is is run remotely.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources