Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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

Posted on 2012-03-21
Medium Priority
Last Modified: 2012-03-27

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!!
Question by:HSI_guelph
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5

Author Comment

ID: 37749304
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.
LVL 37

Expert Comment

ID: 37751188
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.

Author Comment

ID: 37752669
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?
Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

LVL 37

Expert Comment

ID: 37752823
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.

Author Comment

ID: 37753519
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)?

Author Comment

ID: 37753979
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?
LVL 37

Expert Comment

ID: 37765084
" ... 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?

Author Comment

ID: 37766140
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.
LVL 37

Accepted Solution

ValentinoV earned 2000 total points
ID: 37770059
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  :)

Author Closing Comment

ID: 37771860
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.
LVL 37

Expert Comment

ID: 37771965
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? :)

Featured Post

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
Time Corrections for Reports Working with a report, we made some interesting discoveries about the time corrections/updates We are using the following Parameters: Starting Entered Date (Date) formatted as Data type: "Date/Time" Ending Entered …
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

596 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question