May 19, 2016 at 8:15 am
Very simple question here. I've used SSRS on and off over the last 4 or 5 years. Often I've had to put up 2 parameters; a begin/start date and an end date. Never have I had to explain that to users or do anything to prevent users from insisting upon making the end date before the begin date.
Until now.
So how do you make it so that if a user absolutely insists upon entering an end date some time before the start date, that the report will not run?
Kindest Regards, Rod Connect with me on LinkedIn.
May 19, 2016 at 9:13 am
Rod at work (5/19/2016)
Very simple question here. I've used SSRS on and off over the last 4 or 5 years. Often I've had to put up 2 parameters; a begin/start date and an end date. Never have I had to explain that to users or do anything to prevent users from insisting upon making the end date before the begin date.Until now.
So how do you make it so that if a user absolutely insists upon entering an end date some time before the start date, that the report will not run?
Are these dates passed into a stored procedure?
May 19, 2016 at 9:20 am
Here is a simple way to swap two values to ensure StartDate is less than EndDate:
d e c l a r e -- spaced to allow posted from current location
@StartDate datetime = '2016-05-19',
@EndDate datetime = '2016-05-01';
select @StartDate = min(ADate), @EndDate = max(ADate)
from (values (@StartDate),(@EndDate))dt(ADate);
May 19, 2016 at 9:31 am
On the SSRS side of things I handle this kind of thing like this.
At the top of my report is a text box with an error message in red text that reads something like, "Sorry, the end date must be later than the start date." The visibility of that text box is set to an expression that is true when the end data isn't greater than the start date. The visibility setting for the rest of the report is only true when when the end date is greater than the start date. They either see an error or a report depending on the start/end date being entered correctly.
The other thing to consider is that you don't want the underlying report SQL to execute if the start date is greater than the end date. To handle this I include this line in my output query's where clause:
WHERE @startdate < @enddate
If your dataset pulls from a stored proc you may want to recompile the stored proc before it runs via OPTION (RECOMPILE).
-- Itzik Ben-Gan 2001
May 19, 2016 at 10:49 am
Lynn Pettis (5/19/2016)
Here is a simple way to swap two values to ensure StartDate is less than EndDate:
d e c l a r e -- spaced to allow posted from current location
@StartDate datetime = '2016-05-19',
@EndDate datetime = '2016-05-01';
select @StartDate = min(ADate), @EndDate = max(ADate)
from (values (@StartDate),(@EndDate))dt(ADate);
Lynn,
There's some things here which I've never seen before. So here goes:
1) What is "ADate"? Does it only work with the MIN and MAX functions?
2) I've never seen VALUES used in this way. Or at least it looks different to me. I'm certainly familiar with VALUES used in INSERT statements. Is that the sort of thing that's going on here?
3) And lastly, what is the dt function, as you've used it here in the FROM clause?
Kindest Regards, Rod Connect with me on LinkedIn.
May 19, 2016 at 10:59 am
Rod at work (5/19/2016)
Lynn Pettis (5/19/2016)
Here is a simple way to swap two values to ensure StartDate is less than EndDate:
d e c l a r e -- spaced to allow posted from current location
@StartDate datetime = '2016-05-19',
@EndDate datetime = '2016-05-01';
select @StartDate = min(ADate), @EndDate = max(ADate)
from (values (@StartDate),(@EndDate))dt(ADate);
Lynn,
There's some things here which I've never seen before. So here goes:
1) What is "ADate"? Does it only work with the MIN and MAX functions?
2) I've never seen VALUES used in this way. Or at least it looks different to me. I'm certainly familiar with VALUES used in INSERT statements. Is that the sort of thing that's going on here?
3) And lastly, what is the dt function, as you've used it here in the FROM clause?
Starting in SQL 2008 you are able to use the VALUES constructer to specify a series of values separating them with a comma. This gives you the ability to insert multiple rows or, as Lynn demonstrated, create a derived table with multiple vales which he named dt.
ADate is the is the name he assigned to the one column in his derived table.
Because there's only two dates in the derived table, MIN gets you the earlier of the two dates, MAX gets you the later of the two dates.
-- Itzik Ben-Gan 2001
May 19, 2016 at 11:02 am
Rod at work (5/19/2016)
Lynn Pettis (5/19/2016)
Here is a simple way to swap two values to ensure StartDate is less than EndDate:
d e c l a r e -- spaced to allow posted from current location
@StartDate datetime = '2016-05-19',
@EndDate datetime = '2016-05-01';
select @StartDate = min(ADate), @EndDate = max(ADate)
from (values (@StartDate),(@EndDate))dt(ADate);
Lynn,
There's some things here which I've never seen before. So here goes:
1) What is "ADate"? Does it only work with the MIN and MAX functions?
2) I've never seen VALUES used in this way. Or at least it looks different to me. I'm certainly familiar with VALUES used in INSERT statements. Is that the sort of thing that's going on here?
3) And lastly, what is the dt function, as you've used it here in the FROM clause?
Let's start with this, (values (@StartDate),(@EndDate))dt(ADate) is a derived table with the alais dt and a single column named ADate. The values in the table come from the variables @StartDate and @EndDate. You could probably use other functions including user defined functions. In this case I am using min and max to be sure that @StartDate is less than or equal to @EndDate. I actually use this in several stored procedures where I work to ensure just this.
Does all this make sense or have I left anything out?
May 19, 2016 at 8:34 pm
I guess that I'd never make the assumption that the user made a reversal of the dates even by accident. What if they phat phingered something and they don't notice and then use that report as some form of gospel?
Protect the user-in-a-hurry from such an eventuality. Make the report (or what have you) error out with an informative reason. Having the user correctly enter the dates in the correct order is a form of validation that I wouldn't gloss over.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply