Solved

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

Posted on 2012-03-21
11
1,373 Views
Last Modified: 2012-03-27
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,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!!
Previous-Week-Period.jpg
0
Comment
Question by:HSI_guelph
  • 6
  • 5
11 Comments
 

Author Comment

by:HSI_guelph
Comment Utility
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.
0
 
LVL 37

Expert Comment

by:ValentinoV
Comment Utility
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.
0
 

Author Comment

by:HSI_guelph
Comment Utility
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?
0
 
LVL 37

Expert Comment

by:ValentinoV
Comment Utility
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.
0
 

Author Comment

by:HSI_guelph
Comment Utility
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)?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:HSI_guelph
Comment Utility
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?
0
 
LVL 37

Expert Comment

by:ValentinoV
Comment Utility
" ... 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?
0
 

Author Comment

by:HSI_guelph
Comment Utility
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.
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
Comment Utility
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  :)
0
 

Author Closing Comment

by:HSI_guelph
Comment Utility
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.
0
 
LVL 37

Expert Comment

by:ValentinoV
Comment Utility
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? :)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

After much searching across the internet I have found that you could not set the name of the file you were attaching to dynamic report subscriptons in Microsoft Reporting Services. I did manage to find one article showing you how your could make a s…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now