May 30, 2017 at 8:38 am
Hello Everyone!
I hope what I need can be achieved...
I want to have three parameters in my report. 1 pull down and 2 dates. The pull down will be populated with a data set from a table with the following values:
value name
1 This Week
2 Previous Week
3 This Month
4 Previous Month
What I want to do is parse the value over to a function that will calculate the date range based on the value parsed and automatically populate the date range parameters. Also, if the pull down selection changes, I need the date range parameters to update as well.
Now, I would need help in creating the function and help with the configuration of the parameters, so any and all help would be greatly appreciated!
Thank You!
Matt
May 30, 2017 at 11:53 am
Can the user select a date range after they've selected one of the four options? If not, just have one dropdown with your four values:
1 This Week
2 Previous Week
3 This Month
4 Previous Month
Then just calculate @BeginDate and @EndDate variables in your T-SQL query
DECLARE @BeginDate datetime, @EndDate datetime;
IF @RptParam = 'This Month'
BEGIN
SET @BeginDate = CAST(MONTH(GETDATE()) as char(2)) + '/01/' + CAST(YEAR(GETDATE()) as char(4));
SET @EndDate = DATEADD(day, -1, DATEADD(month, 1, @BeginDate));
END
IF @RptParam = 'Previous Month'
BEGIN
SET @BeginDate = DATEADD(month, -1, CAST(MONTH(GETDATE()) as char(2)) + '/01/' + CAST(YEAR(GETDATE()) as char(4)));
SET @EndDate = DATEADD(day, -1, DATEADD(month, 1, @BeginDate));
END
SELECT *
FROM dbo.MyTable
WHERE ImportantDate BETWEEN @BeginDate AND @EndDate;
I hope this helps,
Rob
May 30, 2017 at 12:05 pm
Rob,
While user input might be nice for the dates, I would rather have them just select one of the values, and the date parameters would automatically be calculated for them.
May 30, 2017 at 12:33 pm
meichmann - Tuesday, May 30, 2017 12:05 PMRob,
While user input might be nice for the dates, I would rather have them just select one of the values, and the date parameters would automatically be calculated for them.
Good; then what I outlined above should work for you.
Thanks,
Rob
May 30, 2017 at 12:46 pm
Rob,
I apologize but i read your message wrong. Yes, in fact they will be able to enter dates after selecting on of the values if they wish to change it.
May 31, 2017 at 9:46 am
I did something similar on some of our reports and what I have found with SSRS is that only the first selection of a date range will set the date values, then if you change the date range selection, the dates are not updated. After doing some research, Microsoft deemed this as "by design" and never fixed it for whatever reason. Let us know if you have better luck when trying this. I have been informing users that if they choose the wrong range, to close the report and re-open it to be able to select again.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply