SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Development Tools
 Reporting Services Development
 HELP... setting variable in RS
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

2revup
Posting Yak Master

109 Posts

Posted - 04/19/2013 :  02:19:40  Show Profile  Reply with Quote
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.


Edited by - 2revup on 04/19/2013 02:29:15

2revup
Posting Yak Master

109 Posts

Posted - 04/19/2013 :  02:23:23  Show Profile  Reply with Quote
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.

Edited by - 2revup on 04/19/2013 02:47:15
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 04/19/2013 :  02:48:12  Show Profile  Reply with Quote
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
Go to Top of Page

2revup
Posting Yak Master

109 Posts

Posted - 04/19/2013 :  20:06:29  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000