May 7, 2015 at 12:45 pm
Here's what i have so far. I want it to say if the quarterly parameter is selected, then display StartDate (DateAdd("m", -4, DateSerial(Year(Now()), Month(Now()), 1)))
+ 3 months
- 1 day.
Which should give me the end date of the quarter.
=IIF(
Parameters!ReportTimeFrame.Label = "QUARTERLY"
,DateAdd("m", -4, DateSerial(Year(Now()), Month(Now()), 1)) + DateAdd("m", 3, DateSerial(Year(Now()), Month(Now()), 1)) - DateAdd("d", -1, Today)
,DateAdd("d", -1, DateSerial(Year(Now()), Month(Now()), 1))
)
May 7, 2015 at 3:25 pm
SSRS has a DateInterval.Quarterly field which could make for more readable code:
= DATEADD(
DateInterval.Day
, -1
, DATEADD(
DateInterval.Quarter
, DATEPART(DateInterval.Quarter, DateTime.Now )
, DATESERIAL(YEAR(Now), 1, 1)
)
)
You could also replace DateInterval.Quarter to DateInterval.Month to get month end dates.
May 8, 2015 at 6:50 am
I'm not sure I understand your response...
So If I want it to DEFAULT to the last day of the quarter (March 31st) What would be best to use?
May 10, 2015 at 4:56 pm
To set the default value for a parameter, in the properties window for a parameter, switch to the "Default Values" tab, select "Specify values", click "Add", click the "fx" button, and replace "(Null)" with the given code.
Your suggestion that the code should return March 31st - the end of the first quarter is March 31st. The end of the current (2nd) quarter is July 30th, which is what the above code would give. Perhaps I'm misunderstanding your intention.
The code below, with 1st February being an arbitrary date in the first quarter, will give the result of March 31st.
= DATEADD(
DateInterval.Day
, -1
, DATEADD(
DateInterval.Quarter
, DATEPART(DateInterval.Quarter, DateTime.Parse("2015-02-01") )
, DATESERIAL(YEAR(Now), 1, 1)
)
)
May 11, 2015 at 12:01 am
You can also try getting the default value directly from the result of a dataset query. This is a fully dynamic way to get what you want :
SELECTDATEADD(d, -1,
DATEADD(q,DATEPART(q,getdate())-1,CAST(year(getdate()) AS varchar(10)))
) AS myDefaultDate
Of course you next set the default value of the report parameter to source from a query, using this dataset.
----------------------------------------------------
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply