Setting automatic date calculations in Report Parameters

Question

Setting automatic date calculations in Report Parameters

Shannon-763872

SSC Enthusiast

Points: 176
More actions
May 29, 2008 at 3:06 pm

#187197

I need to use an expression in the start date of the report parameter default value that uses today's date and calculates the last Sunday for the start date.
I used this in a excel report but now need to build the report in SSRS. In excel I used:
**F8 is =today()**
=(F8+CHOOSE(WEEKDAY(F8),0,-1,-2,-3,-4,-5,-6))
This would return the last Sunday. example: if it's 5/29/2008 it would return 5/25/2008.
I have something to start out with but can't get it to work:
=IIF(WeekDay(Globals!ExecutionTime)
but don't know where to go from there.
Any help on this would be great.
Thanks
Shannon

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 1

you can do the calculation either in your SQL or in a ReportBuilder expression.

SQL always returns the current date and time. if you only want the date, the expression is bit more complicated. if you want to select data based on the sunday date, the SQL calculation is better.

declare @sunday smalldatetime

-- prior sunday, current time

set @sunday = dateadd( day, 1 - datepart(weekday, getdate()), getdate())

-- prior sunday, midnight

set @sunday = dateadd( day, 1 - datepart(weekday, getdate()) + datediff(day,0,getdate()), 0)

select ... from ... where ... and [some_date] >= @sunday

ReportBuilder uses VB functions. Now() is the current date and time; Today() is the current date only.

dateadd( day, 1 - dayofweek(Now()), Now())

dateadd( day, 1 - dayofweek(Today()), Today())

checkai Hall of Fame Points: 3041 More actions · Answer 2

This should work within the report itself. I set a parameter's default to this and the results looked good.

=Switch(DatePart("w", now())=1,now(),

DatePart("w", now())=2,DateAdd("d",-1,now()),

DatePart("w", now())=3,DateAdd("d",-2,now()),

DatePart("w", now())=4,DateAdd("d",-3,now()),

DatePart("w", now())=5,DateAdd("d",-4,now()),

DatePart("w", now())=6,DateAdd("d",-5,now()),

DatePart("w", now())=7,DateAdd("d",-6,now()))

Shannon-763872 SSC Enthusiast Points: 176 More actions · Answer 3

I used this in the parameter settings and it works great. Thanks so much for your help.

Shannon

:):):):)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

checkai (5/30/2008)
This should work within the report itself. I set a parameter's default to this and the results looked good.
=Switch(DatePart("w", now())=1,now(),
DatePart("w", now())=2,DateAdd("d",-1,now()),
DatePart("w", now())=3,DateAdd("d",-2,now()),
DatePart("w", now())=4,DateAdd("d",-3,now()),
DatePart("w", now())=5,DateAdd("d",-4,now()),
DatePart("w", now())=6,DateAdd("d",-5,now()),
DatePart("w", now())=7,DateAdd("d",-6,now()))

Or slightly simpler....

=DateAdd("d",1-weekday(now(),vbSunday), now())

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Shannon-763872 SSC Enthusiast Points: 176 More actions · Answer 5

Now they want me to also go out 4 weeks and find the Sunday of that forecast week. I thought I could just add the +28 but either that doesn't work or I'm not putting it in correctly. Is it possible? It doesn't seem to like the + symbol.

It works when I go stright out 28 days with this:

=DateAdd("d",+28,today())

Thanks again,

Shannon

Shannon-763872 SSC Enthusiast Points: 176 More actions · Answer 6

I figured it out after a little research:

=DateAdd(DateInterval.Day, 28,Parameters!StartDate.Value)

Thanks for all your help with the Start Date!

Shannon