default date values - SQL Server Forums

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)

default date values

Author

Topic

jql
Starting Member

30 Posts

Posted - 2011-04-18 : 05:42:20

Morning,

i have been trying to work out how to add the following default date values to a SSRS report for subscription but with no joy.

each wednesday the default value should change to a new week from the saturday. eg.
weds 20th April the default dates should be week starting Sat 9th April to week ending Fri 15th April

27th April the default dates should be week starting 16th April to week ending 22nd April

can anyone help as i am stuck

Regards

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-18 : 07:07:04

Sounds like something that should be in the database and updated via a scheduled job.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2011-04-18 : 09:58:02

One possible solution is to make the stored procedure behind the report smart enough to handle this. I am going to assume that a date is one of the parameters of your report. If you allow NULL on that parameter, then inside your stored proc you could check for NULL then set a default.

I don't have the logic all worked out, but basically if it is NULL then go back a week. Set the start date to Saturday and the end date to Friday.

From here you could probably run with it to fit your needs.

DECLARE @dtiStartDate datetime
DECLARE @dtiEndDate datetime

IF @dtiStartDate IS NULL
BEGIN

--set the date to a week ago from today
SET @dtiStartDate = DATEADD(WK, -1, GETDATE())

SET @dtiStartDate = CASE WHEN (DATENAME(dw, @dtiStartDate) = 'Sunday') THEN
DATEADD(DD, -1, @dtiStartDate)
WHEN (DATENAME(dw, @dtiStartDate) = 'Monday') THEN
DATEADD(DD, -2, @dtiStartDate)
WHEN (DATENAME(dw, @dtiStartDate) = 'Tuesday') THEN
DATEADD(DD, -3, @dtiStartDate)
WHEN (DATENAME(dw, @dtiStartDate) = 'Wednesday') THEN
DATEADD(DD, -4, @dtiStartDate)
WHEN (DATENAME(dw, @dtiStartDate) = 'Thursday') THEN
DATEADD(DD, -5, @dtiStartDate)
WHEN (DATENAME(dw, @dtiStartDate) = 'Friday') THEN
DATEADD(DD, -6, @dtiStartDate)
WHEN (DATENAME(dw, @dtiStartDate) = 'Saturday') THEN
DATEADD(DD, -7, @dtiStartDate)
ELSE
@dtiStartDate
END

SET @dtiEndDate = DATEADD(DD, 6, @dtiStartDate)
END

select @dtiStartDate, @dtiEndDate

Hey, it compiles.

NeilG
Aged Yak Warrior

530 Posts

Posted - 2011-04-18 : 10:39:54

Could this not be set though an expression in the reporting services report, I.e. the parameter which your using for the start data could be set a default value of -7 days off the current date.

jql
Starting Member

30 Posts

Posted - 2011-04-20 : 02:13:25

i have something very similar to what ajthepoolman suggested which is great but what i really need is for the week start and end to change on a Wednesday to the next week and to show this as a default date in the ssrs report.
e.g. Tuesday (yesterday) showed 02/04/2011 to 08/04/2011, Wednesday (today) changes to show 09/04/2011 to 15/04/2011

i have come up with the following

select
getdate() - (datepart(w,getdate())+1)
+ case when datepart(w,getdate()) < 4 then -7 else 0 end as startdate

,getdate() - (datepart(w,getdate())+1)
+ case when datepart(w,getdate()) < 4 then -7 else 0 end +6 as enddate

Thanks for help

jql
Starting Member

30 Posts

Posted - 2011-04-20 : 04:05:33

i have now worked out a way to show as default values in a reporting services report

=iif(DatePart("w",today) < 4 ,
DateAdd("ww",-2,DateAdd("d",-Weekday(today(),firstDayofWeek.Sunday),today())),
DateAdd("ww",-1,DateAdd("d",-Weekday(today(),firstDayofWeek.Sunday),today()))
)

J

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2011-04-20 : 08:55:52

I'm sorry, I thought you were talking in the stored procedure. Now I get it, you want this to show up in the date picker control in the parameter section. Looks like you have a good solution there.

Hey, it compiles.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources