July 10, 2012 at 6:57 am
Hi,
I have a @start parameter in my report.
I am trying to default the @start to mar 1 of current year unless current date is less than mar 1, then set default to March 1 of last year.
How can I achieve that in report itself.
I have started making a new dataset it month(getdate()) <=3 then...but I am not sure how do I go about the year part and making it dynamic.
Thanks
July 10, 2012 at 7:26 am
You can use VB expressions in SSRS Report Builder. Samples here: http://technet.microsoft.com/en-us/library/ms157328.aspx#VisualBasicFunctions
I don't remember VB well enough to build the expression myself, but what you need to do is:
1. Get today's date. In VB, that's the Today() function.
2. Find the number of years from 0 (1/1/1900) to Today(). That's the DateDiff() function, with Years as the unit. I think that would look like
DateDiff("y", 0, Today())
but I'm not certain. Look that one up and confirm it.
3. Add the number from that to 0 if it's after March, or subtract 1 from it then add it to 0 if it's before March. In VB, that's the DateAdd() function. Might look like:
DateAdd(DateInterval.year, DateDiff("y", 0, Today()), 0)
Again, you'll need to confirm the syntax on that. Also you'll need to write your IF statement or CASE statement to make it subtract one from the year if it's before March.
4. Add 2 months to that, because the above will give you 1 Jan of this year, or the prior year if you have that math in there for pre-March dates.
The T-SQL version looks like:
SELECT CASE WHEN DATEPART(MONTH, GETDATE()) < 3
THEN DATEADD(month, 2, DATEADD(year, DATEDIFF(year, 0, GETDATE()) - 1, 0)) -- prior year
ELSE DATEADD(month, 2, DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0)) -- current year
END ;
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 10, 2012 at 9:44 am
Thank you for your help. Saved a lot of time. very much appreciated.
July 12, 2012 at 6:33 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply