Dynamic Date Parameters
in [Reports]
|
Prev: How to default report language
Next: Creating a report when data exists on two levels of a data hierarchy
From: Robin9876 on 9 Jul 2008 12:07 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?
From: Bruce L-C [MVP] on 9 Jul 2008 13:36 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())) -- Bruce Loehle-Conger MVP SQL Server Reporting Services "Robin9876" <robin9876(a)hotmail.com> wrote in message news:3501e744-61b1-4661-be96-d4b0fc2b85d1(a)d45g2000hsc.googlegroups.com... > 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?
From: Robin9876 on 24 Jul 2008 09:35
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...(a)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. > 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())) > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > "Robin9876" <robin9...(a)hotmail.com> wrote in message > > news:3501e744-61b1-4661-be96-d4b0fc2b85d1(a)d45g2000hsc.googlegroups.com... > > > 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? |