SSRS date range set default today but allow user to choose another date
PLEASE SKIP TO MY LAST POST UNLESS YOU WANT BACKGROUND INFO
I've created a report that displays a date range of
BETWEEN @StartDate AND @EndDate
with @StartDate and @EndDate as parameters.
The defaults are @StartDate=DateAdd("d",-7,
This would be the default set up. If they want to see hours for another period I want them to be able to choose a period rather than choosing a start date and end date as I have now. I was thinking a drop-down list of date ranges would be the easiest way though a calendar would be visually more engaging.
Is there a way to dynamically set this up? I know there are functions for date that lets me return things like the integer value of the week and I've seen code where people use a date and figure out the first day of that month so I was wondering if there is a way to take todays date and find the previous week's range? Would it be best to put something like this into a stored procedure? A condition where if today's date is Tuesday then the pay period is last week monday to this past sunday?
Or if I'm making things more complicated than they can be does anyone know a way to implement this functionality into a report?
Thank you!!
Previous-Week-Period.jpg
I've created a report that displays a date range of
BETWEEN @StartDate AND @EndDate
with @StartDate and @EndDate as parameters.
The defaults are @StartDate=DateAdd("d",-7,
This would be the default set up. If they want to see hours for another period I want them to be able to choose a period rather than choosing a start date and end date as I have now. I was thinking a drop-down list of date ranges would be the easiest way though a calendar would be visually more engaging.
Is there a way to dynamically set this up? I know there are functions for date that lets me return things like the integer value of the week and I've seen code where people use a date and figure out the first day of that month so I was wondering if there is a way to take todays date and find the previous week's range? Would it be best to put something like this into a stored procedure? A condition where if today's date is Tuesday then the pay period is last week monday to this past sunday?
Or if I'm making things more complicated than they can be does anyone know a way to implement this functionality into a report?
Thank you!!
Previous-Week-Period.jpg
I wouldn't go for a second parameter. It will only annoy the users.
If you'd like to have the date picker (calendar), then the notion "date range" will not be visible to the user. But you could make it work though. Just let the user select any day he wants, and apply your logic to get to the date range.
Another option is, as you've mentioned, a dropdown list. In that case it would be clear to the user that he's selecting a range, but there won't be any visual calendar.
The dropdown label would be something like "2012/03/12 - 2012/03/18". For the values behind the label, I would use a date such as the start date of your range. You can then use that date to get you data. This way your logic will become easier because the user is actually selecting the start date without realizing it, and your end date will always be "SelectedDate + 6".
Of course, that means you'll need to produce the data for the dropdown somehow. Easiest would be to set up a database table that contains this data. In data warehousing terms that would be a date dimension.
So, it all depends on how you'd like the user experience to be, but both methods should technically be possible.
If you'd like to have the date picker (calendar), then the notion "date range" will not be visible to the user. But you could make it work though. Just let the user select any day he wants, and apply your logic to get to the date range.
Another option is, as you've mentioned, a dropdown list. In that case it would be clear to the user that he's selecting a range, but there won't be any visual calendar.
The dropdown label would be something like "2012/03/12 - 2012/03/18". For the values behind the label, I would use a date such as the start date of your range. You can then use that date to get you data. This way your logic will become easier because the user is actually selecting the start date without realizing it, and your end date will always be "SelectedDate + 6".
Of course, that means you'll need to produce the data for the dropdown somehow. Easiest would be to set up a database table that contains this data. In data warehousing terms that would be a date dimension.
So, it all depends on how you'd like the user experience to be, but both methods should technically be possible.
ASKER
Is it possible to have a date parameter tied to a drop down list? I've hidden the parameters for now but I could make @startdate visible and the user only selects there but can it be a drop down list? Our database is relational and I'd like to avoid making a table if I have to because then the table would need to be manually updated (I could fill it with years worth of data but it feels like a Y2K thing and I want something that 10 yrs from now if I'm in the Caribbean then no one would have to do anything with my code).
I'm wondering if I could have the @StartDate parameter default to the current week and be set by user but then set the @EndDate within the query since the query will be run when the user makes a selection. But then I want to display the date range from @StartDate to @EndDate in the report. I could set an expression for the textbox but is that sloppy coding?
I'm wondering if I could have the @StartDate parameter default to the current week and be set by user but then set the @EndDate within the query since the query will be run when the user makes a selection. But then I want to display the date range from @StartDate to @EndDate in the report. I could set an expression for the textbox but is that sloppy coding?
Is it possible to have a date parameter tied to a drop down list?
Sure, just set the type to Date/Time and specify the dataset for the Default Values. Of course, that means you have a dataset that returns the list of values. Here's an example of such a query on the AdventureWorksDW2008R2 sample database:
select DD.FullDateAlternateKey
, DD.EnglishDayNameOfWeek + ' ' + convert(varchar(50), DD.DateKey) as DateLabel
from dbo.DimDate DDI want something that 10 yrs from now if I'm in the Caribbean then no one would have to do anything with my code
Make it 20 years and that's no longer a concern. Or do you think your report will still be used as is, twenty years from now? :)
I'm wondering ... is that sloppy coding?
No, that makes sense and that way your users can just use the calendar date picker. That is actually the first option that I mentioned in my earlier comment.
ASKER
I haven't played around much with the AW database (my experience is JSP, Tomcat, SQL) but I find that sometimes they have columns/data set specifically so you can do the examples easily. The database I'm working with was set up by the company that provided the accounting software and the database is a relational nightmare. So I'm trying to wrap my brain around what's going on in your code and how it could be used in my report.
I'm calculating hours for employees from WIP table which lists invoice details. So would the date/time dataset be based on that? I would want the drop down to list a range but hold only the monday value and display only weeks in the last fiscal year (so it doesn't go all the way back to 2006 and make the list huge)?
I'm calculating hours for employees from WIP table which lists invoice details. So would the date/time dataset be based on that? I would want the drop down to list a range but hold only the monday value and display only weeks in the last fiscal year (so it doesn't go all the way back to 2006 and make the list huge)?
ASKER
I deleted the end parameter and modified the Available values for the @StartDate to:
=FormatDateTime(DateAdd("d
FormatDateTime(DateAdd("d"
Changing the #s in additional specified values so that they would show 6 weeks of ranges starting Monday-Sunday.
Then I changed the query to include (WIP.Wdate BETWEEN @StartDate AND @StartDate + 6) in the WHERE clause.
It works but could someone look at the code and tell me if that will possibly cause issues down the road or when today() changes? Have I butchered the code when there is be an easier way?
=FormatDateTime(DateAdd("d
FormatDateTime(DateAdd("d"
Changing the #s in additional specified values so that they would show 6 weeks of ranges starting Monday-Sunday.
Then I changed the query to include (WIP.Wdate BETWEEN @StartDate AND @StartDate + 6) in the WHERE clause.
It works but could someone look at the code and tell me if that will possibly cause issues down the road or when today() changes? Have I butchered the code when there is be an easier way?
" ... I find that sometimes they have columns/data set specifically so you can do the examples easily ... So I'm trying to wrap my brain around what's going on in your code ..."
The DimDate table is a regular date dimension (or time dimension, both refer to the same concept), to be found in any regular data warehouse.
All that the query does is produce a list of dates (datetime data type) with their corresponding "range" label (a string to be displayed in the report parameter).
That way the user can select from a dropdown list showing the range string while actually selecting just a datetime value. The selected value from the parameter can then be used in the dataset that retrieves the data, just as you've indicated in your last comment actually.
"It works but could someone look at the code and tell me if that will possibly cause issues down the road or when today() changes? "
I don't see any problems: if it works today, it should work tomorrow as well. So if what you've created works for both you and your users, I guess that's mission accomplished then, right?
The DimDate table is a regular date dimension (or time dimension, both refer to the same concept), to be found in any regular data warehouse.
All that the query does is produce a list of dates (datetime data type) with their corresponding "range" label (a string to be displayed in the report parameter).
That way the user can select from a dropdown list showing the range string while actually selecting just a datetime value. The selected value from the parameter can then be used in the dataset that retrieves the data, just as you've indicated in your last comment actually.
"It works but could someone look at the code and tell me if that will possibly cause issues down the road or when today() changes? "
I don't see any problems: if it works today, it should work tomorrow as well. So if what you've created works for both you and your users, I guess that's mission accomplished then, right?
ASKER
Lol yes its working but now my boss wants to have another option that lets him pick the startdate and enddate from calendars. I have that working on one report and the dropdown on another report but am not sure how to combine them into one report with three parameters (2 working together). I will have to test to see which one the user selected last and make sure that, if it used the other option to last display the report, that it will ignore that one.
I'm not sure if putting them both on the same report is a good idea. Maybe I can make an identical report and provide a link "to choose by start and end dates" and have it look like the report updated with calendars in place of the dropdown but in fact they would be two seperate reports.
I'm not sure if putting them both on the same report is a good idea. Maybe I can make an identical report and provide a link "to choose by start and end dates" and have it look like the report updated with calendars in place of the dropdown but in fact they would be two seperate reports.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
TYVM for your help! I agree that at this point that if our boss wants more customization then we should have multiple reports and my supervisor agrees. I've created two identical reports one that has a week-based dropdown and one that lets the user pick the start and end dates from a calendar with links between the two. My supervisor has no problem with multiple reports, he's understanding about trying to make 1 report that does 12 different things, lol.
If it's any consolation: I once developed a report with 23 parameters (if I recall well). And some of those where customizable through a separate application. Users would create a "template", which represented a certain combination of parameters/values and that template could then be selected through one of the parameters in the report. What can you do, when the requirements are complex, the report will be complex too I guess?
Needless to say that we didn't use the standard report manager interface to render the reports, right? :)
Needless to say that we didn't use the standard report manager interface to render the reports, right? :)
ASKER
StartDate =DateAdd("d", -7 -(WeekDay(Today(),2))+1, Today())
EndDate =DateAdd("d", -7 -(WeekDay(Today(),2)) +7, Today())
But I'm unsure now how to let the employee choose a date range. Should I create another parameter with a calendar and then set the start/end dates as above but with Today() being the date the user chooses? Then I'd need a condition where if the report is run for the first time it uses Today() and if the user chooses a date then it uses that in place of today.