Subscriptions and Date Parameters
Has anyone figured out how to set up subscriptions to a report that requires filtering the data for records where a date field falls between two dates?
Especially helpful is if someone has an elegant way to allow the user to select from options like "Today, Week To Date, Month To Date, Quarter To Date, Year To Date, Yesterday, Last Week, Last Month, Last Quarter, Last Year, Tomorrow, Next Week, Next Month, Next Quarter, Next Year".
That way, each time a report runs that is subscribed to with a parameter of "Last Week", the date range changes to reflect what "Last Week" means for that report.
[606 byte] By [
Kursplat] at [2007-12-16]
Create a dropdown list with the options that you state above. Add the following code to Report... Report Properties... Code...
Public Shared Function ConvertToStartDate(ByVal relDate As String) As DateTime
Select Case relDate
Case "Today"
Return DateTime.Today
Case "Yesterday"
Return DateTime.Today.AddDays(-1)
' Additional cases
Case Else
Throw New ArgumentException("Unknown argument, " & relDate, "relDate")
End Select
End Function
Implement ConvertToEndDate in a similar manner. You can now reference the functions via Code.ConvertToStartDate(Parameters!RelativeDateDropDown.Value) in your T-SQL to build up your query.
You will probably want to use this logic on multiple reports. So I would compile this into an assembly. Then create a reference to this assembly from each report using Report... Report Properties... References... as detailed in:
http://www.c-sharpcorner.com/Code/2005/June/CustomAssemblyinRS.asp
I have created a version of these functions with a variety of date ranges and it will work great.
I have wrapped these two functions into an assembly, created the DLL, and copied it into the appropriate folder on my workstation.
If I add a text field onto a report, call the function in the text field, and then run the report, the report correctly displays the result passed back by the function in the DLL.
So, a text control on the form with the value "=SrsDates.Params.StartDate(Parameters!DateRange.Value)" correctly displays a date value based on whatever value I pick for the DateRange parameter.However, as soon as I attempt to put "=SrsDates.Params.StartDate(Parameters!DateRange.Value)" as the criteria for a field in the query builder and tab out of the field, Visual Studio crashes.
I suspected it was because the query builder was attempting to run the query behind the scenes to get a list of result fields and was tripping over the fact that a parameter value had not been selected.
So, I attempted to put "=SrsDates.Params.StartDate('Yesterday')" in the criteria column and it still crashes Visual Studio. I tried both single-quotes and double-quotes around "Yesterday" since I'm not 100% which is correct, but VS crashed either way.
Any ideas what could be causing this? Suggestions?
I'm surprised no one spotted the problem with my post. I just saw a post in the Microsoft newsgroups that showed me the error of my ways. I'll post it here in case it helps someone else in the future.
My problem is that I was trying to reference the assembly from the query, which you can't do.
I had to bring up the properties of the Dataset and referenc the assembly functions in the right side of the Parameter relationships between the query parameter and the report parameter.
