Creating a dynamic date parameter in a SQL Reporting Services 2005 subscription

Hi, I came acroos several queries regarding dynamic date parameter to ssrs subscriptions,

here i came up with a solution for this. hope this helps Basically Subscriptions store the actual value set at the time of creation, so if you choose 2001-01-01 for a date parameter it will always run the report with that date, what basically happens is the value is passed to the dateset used by your main report asParameters!HostDate.Value what you need to do is translate that value into something more workable. First I would turn your date parameters into a list from a dataset using a procedure like below, we limit people accessing archive material beyond a year but you can alter however you want (more detail after procedure).

---

create procedure [dbo].[p_RS_DateParameters]

as begin set nocount on

set datefirst 1

declare @MaxCount int,

@Count int, @Date datetime,

@Error int, @Rows int

select @MaxCount = 365, -- number of days available as a date parameter

@Count = 1,

@Date = convert(datetime, convert(varchar, getDate(), 112)),

@Error = 0

create table #DateRange ( Title varchar (50), Date varchar (50), ID int identity (1,1))

insert into #DateRange (Title, Date)

select 'Today', 'Today'

union all select 'Yesterday', 'Yesterday'

union all select 'Two Days Ago', 'Two Days Ago'

union all select 'Last Week', 'Last Week'

union all select 'Last Week Start', 'Last Week Start'

union all select 'Last Week End', 'Last Week End'

union all select 'Last Month', 'Last Month'

union all select 'Last Month Start', 'Last Month Start'

union all select 'Last Month End', 'Last Month End'

union all select 'Last Year', 'Last Year'

union all select 'Last Year Start', 'Last Year Start'

union all select 'Last Year End', 'Last Year

End'

while (@Count < @MaxCount)

begin

set @Date = dateadd(dd, -1, @Date)

insert into #DateRange (Title, Date) values (convert(varchar, @Date, 106), @Date)

set @Count = @Count + 1

end

select DateRange.Title, DateRange.Date from dbo.#DateRange DateRange

order by DateRange.ID asc

select @Error = @@error, @Rows = @@rowcount if ((@Error = 0) and (@Rows = 0))

set @Error = -2 if (@Error <> 0)

goto cleanup cleanup:

if object_id(N'[dbo].[#DateRange]') is not null

drop table #DateRange

return @Error

end

---

Basically you will set the Value of parameter to the date column and the title of the parameter to title column from the dataset. Next you need to use some code to translate the date, you will notice that a few values in the procedure use a text value like "Yesterday", these will be used for subscription parameters. To translate those values go to the code page on the report by going to properties (right click on main report area) and choosing the code tab. Past into that code page the following (More detail to follow).

---

Public Function TranslateDate(TheValue As Object) As String

if (CType(TheValue, String) = "Today") then return

Format(Now,"yyyy-MM-dd") end if

if (CType(TheValue, String) = "Yesterday") then

return Format(dateadd("d", -1, Now),"yyyy-MM-dd") end if

if (CType(TheValue, String) ="Last Week") then

return Format(dateadd("d", -7, Now),"yyyy-MM-dd") end if

if (CType(TheValue, String) = "Last Week Start") then

return Format(dateadd("d", -1 * (datepart("w", Now) - 1), dateadd("ww", -1, Now)),"yyyy-MM-dd") end if

if (CType(TheValue, String) = "Last Week End") then return Format(dateadd("d", -1 * (datepart("w", Now)), Now),"yyyy-MM-dd") end if

if (CType(TheValue, String) = "Last Month") then return Format(dateadd("m", -1, Now),"yyyy-MM-dd") end if

if (CType(TheValue, String) = "Last Month Start") then return Format(dateadd("m", -1, dateadd("d", - (day(Now) - 1), Now)),"yyyy-MM-dd") end if

if (CType(TheValue, String) = "Last Month End") then return Format(dateadd("d", - (day(Now)), Now),"yyyy-MM-dd") end if

if (CType(TheValue, String) = "Last Year") then return Format(dateadd("yyyy", -1, Now),"yyyy-MM-dd") end if

if (CType(TheValue, String) = "Last Year Start") then return Format(dateadd("yyyy", -1, dateadd("m", -1 * (datepart("m", Now) - 1), dateadd("d", - (day(Now) - 1), Now))),"yyyy-MM-dd") end if

if (CType(TheValue, String) = "Last Year End") then return CType(datepart("yyyy", Now), String) + "-12-31" end if

return Format(CDate(TheValue),"yyyy-MM-dd")

End Function

---

Next you will need to alter your main data set parameters by altering the expression, go to your main dataset and click on the parameters tab, where you have date parameters click on the expression table and enter in there the following: =Code.TranslateDate(Parameters!Date.Value) Where Parameters!Date.Value is your parameter. What will happen is when you select the parameter value "Yesterday" the value "Yesterday" will be stored in the subscription when the report executes Yesterday will be translated into Now minus 1 day. I'm glad I got all that out. Kind Regards,