SSRS date range set default today but allow user to choose another date


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,Today()) and @EndDate=Today() but we'd like to change the default date range to the last pay period.  So if today is March 21, then the last pay period would be Monday March 12 to Sunday March 18 (at midnight).  Employees would enter their hours on Monday March 19th and Tuesday March 20th (and every Tuesday) they would be sent the report through scheduled events we set up.  

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!!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HSI_guelphAuthor Commented:
I got the date range working with default values set to:

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.
ValentinoVBI ConsultantCommented:
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.
HSI_guelphAuthor Commented:
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?
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

ValentinoVBI ConsultantCommented:
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 DD

Open in new window

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

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.
HSI_guelphAuthor Commented:
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)?
HSI_guelphAuthor Commented:
I deleted the end parameter and modified the Available values for the @StartDate to:
=FormatDateTime(DateAdd("d", -7 -(WeekDay(Today(),2))+1, Today()), DateFormat.LongDate) + " - " +
FormatDateTime(DateAdd("d", -7 -(WeekDay(Today(),2))+7, Today()), DateFormat.LongDate)

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?
ValentinoVBI ConsultantCommented:
" ... 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?
HSI_guelphAuthor Commented:
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.
ValentinoVBI ConsultantCommented:
I wouldn't try to get that working in one report.  It sounds like you've got two different usage scenarios, and trying to get both in the same report will confuse the users.

Anyway, suppose you'd want to give it a try, you could set your parameters to some default value which you can then test on to see if it was modified.  For instance, a Date/Time parameter can be set to "1900-01-01" by default, or some other value which will never count as a valid value selected by the user.  Then in your dataset you need to check which parameter is not the default value and you'll know which one was modified.

If going into that path, you're probably better of if you'd use a stored procedure.  See my links in your other question  :)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HSI_guelphAuthor Commented:
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.
ValentinoVBI ConsultantCommented:
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? :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.