Dynamic Date Parameters

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Robin9876 - 09 Jul 2008 17:07 GMT

For a SSRS 2005 report is it possible to have a parameter which has
text values of "last month", last two months" and "last year".
Is it possible to have date range values for the ranges such as
equivalent code for Date - 1 Month, 2 Months which are passed to the
dataset?

Reply to this Message

Bruce L-C [MVP] - 09 Jul 2008 18:36 GMT

What I do is have a list for the options. The label will say the phrase Last
Month, Last Two Months etc. The value would be an integer.
This is working T-SQL with the change to the field and table name would work
for you in Query analyzer.
declare @MonthRange as int

set @MonthRange = 1

declare @ENDDATE as datetime

set @ENDDATE = convert(varchar(2),datepart(month,getdate())) + '/1/' +
convert(varchar(4),datepart(year,getdate()))

select min(datereceived), max(datereceived), count(*) from active where
datereceived >= dateadd(month,-1*@MonthRange,@ENDDATE) and datereceived <
@ENDDATE

In the generic query tab you can do this:

declare @ENDDATE as datetime

set @ENDDATE = convert(varchar(2),datepart(month,getdate())) + '/1/' +
convert(varchar(4),datepart(year,getdate()))

select min(datereceived), max(datereceived), count(*) from active where
datereceived >= dateadd(month,-1*@MonthRange,@ENDDATE) and datereceived <
@ENDDATE

RS will create a report parameter for @MonthRange.

Or the query can be re-written as this:

select min(datereceived), max(datereceived), count(*) from active where
datereceived >=
dateadd(month,-1*@MonthRange,convert(varchar(2),datepart(month,getdate())) +
'/1/' + convert(varchar(4),datepart(year,getdate()))) and datereceived <
convert(varchar(2),datepart(month,getdate())) + '/1/' +
convert(varchar(4),datepart(year,getdate()))

Signature

Bruce Loehle-Conger
MVP SQL Server Reporting Services

> For a SSRS 2005 report is it possible to have a parameter which has
> text values of "last month", last two months" and "last year".
> Is it possible to have date range values for the ranges such as
> equivalent code for Date - 1 Month, 2 Months which are passed to the
> dataset?

Reply to this Message

Robin9876 - 24 Jul 2008 14:35 GMT

How would you be able to allow a custom date range to be used instead
of the predefined dates?

On 9 Jul, 18:36, "Bruce L-C [MVP]" <bruce_lcNOS...@hotmail.com>
wrote:

> What I do is have a list for the options. The label will say the phrase Last
> Month, Last Two Months etc. The value would be an integer.
[quoted text clipped - 44 lines]
> > equivalent code for Date - 1 Month, 2 Months which are passed to the
> > dataset?

Reply to this Message