May 5, 2011 at 5:06 pm
Hi
I wanted to know if anyone is aware of creating a dynamic date parameter in a SQL Reporting Services subscription.
The thing that I want to do is as follows
I have a SQL reporting services report that takes date parameter (I have two parameters as StartDate and EndDate. These dates are picked from the Datepicker). I want to create a subscription to the report that will run on 25th of every month and show the data for the prior month.
A simple solution for a "report that will run early each morning and show the data for the day prior" is to add a hidden boolean parameter to the report, called for example 'IsSubscription' with a default value of false and add the following expression to the StartDate and EndDate default values:
=IIf(Parameters!IsSubscription.Value, DateAdd("d",-1,Today()), Today())
When running the report normally, the IsSubscription parameter is defaulted to false and the default date is today.
When creating the subscription, the IsSubscription parameter must is set to true, thus setting the default value to today-1 = yesterday.
But I have no Idea how I can change the else part in the above IFF() condition so that it allows the user to select the date (using the datepicker) instead of running the report automatically(for the date entered in else part i.e. in the above case, Today()) when a report is clicked.
I hope I have made my point and is clear to anyone who reads it. Any help is appreciated
Thanks in Advance
Hardik
August 31, 2011 at 7:19 am
Hi,
I know this is rather late however I've seen a lot of these posts and noone really comes up with the best options.
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 as Parameters!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,
Paul.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply