HELP... setting variable in RS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

HELP... setting variable in RS

Author

Topic

2revup
Posting Yak Master

112 Posts

Posted - 2013-04-19 : 02:19:40

To explain this one its best I show you a working SQL query:

DECLARE @startdate datetime2 = '2013-01-01 00:00:00.0000000';
DECLARE @enddate datetime2 = GETDATE();
DECLARE @interval int = 30;

--Set @enddate = DATEADD(d, 1, @enddate) --comment this out if you do not want to include the end date
Set @startdate = DATEADD(d, -@interval, @enddate)

--print @startdate

Select Distinct UserLogin, cast(count(UserLogin) as decimal(38,0)) / @interval as 'DailyAvg'
From Correspondences_All
Where creation_date BETWEEN @startdate AND @enddate
group by UserLogin

So this takes the start date and minus 30 days and outputs a new date, and then with the NEW date range will calculate an AVG.

My problem is that I cant do this in report builder / RS query
Set @startdate = DATEADD(d, -@interval, @enddate)

because the startdate is defined by the user via the calendar drop down. How on EARTH can I get this DATEADD(d, -@interval, @enddate)in there to do my calculation.

I hope this makes sense, its a hard one to try an explain.

2revup
Posting Yak Master

112 Posts

Posted - 2013-04-19 : 02:23:23

I think what I might be trying to do might cancel out the users selected date out... THis could be pointless. Can someone validate that please.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-19 : 02:48:12

why not add it as parameter in report and use corresponding SSRS expression? like

DateAdd("d",-1 * Parameters!interval.value,Parameters!EndDate.value)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

2revup
Posting Yak Master

112 Posts

Posted - 2013-04-19 : 20:06:29

You gave me my answer once again - you are brilliant

Select UserLogin,Location, supervisor, count(UserLogin), cast(count(UserLogin) as decimal(38,0)) / @interval as 'DailyAvg'
From cases
where creation_date between
DateAdd("d",-1 * @interval ,@endDate) and @endDate
group by UserLogin, Location, supervisor

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources