Going against the Trend

Sql Server 2000 Reporting Services is an awesome reporting tool, but what they forgot to pack in is something simple like a 2nd Y axis (wich I still haven't figured out how to do), and a stinking Trendline. I mean, c'mon, even EXCEL does trendlines! So my quickie solution is the following script, All you have to do is populate the @TrendTable and you're on your way!!!



DECLARE @TrendTable TABLE (PID INT IDENTITY (1,1),
XColumn smalldatetime,
YColumn1 REAL,
YColumn2 INT,
Trend REAL)

---------
/*
At this point populate the @TrendTable with your data, the Xcolumn is of course plotted data on the x axis, On this example I've put in a Y column and a 2nd Y column for your charting needs, YColumn1 is the column that will be trended.
*/
--------

DECLARE @n REAL
DECLARE @sumX REAL
DECLARE @sumY REAL
DECLARE @sumXY REAL
DECLARE @sumX2 REAL
DECLARE @sumY2 REAL
DECLARE @sumX_2 REAL
DECLARE @sumY_2 REAL

DECLARE @m REAL
DECLARE @b REAL
DECLARE @r REAL

SELECT @n = COUNT(*), @sumX=SUM(PID),
@sumY=SUM(YColumn1),
@sumXY=SUM(PID*YColumn1),
@sumX2=SUM(POWER(PID,2)),
@sumY2=SUM(POWER(YColumn1,2))
FROM @TrendTable

SELECT @sumX_2 = POWER(@sumX,2), @sumY_2 = POWER(@sumY,2)

--slope
SELECT @m = (@n*@sumXY-@sumX*@sumY)/(@n*@sumX2-@sumX_2)
--y intercept
SELECT @b = (@sumY-@m*@sumX)/@n
--r
SELECT @r = (@n*@sumXY-@sumX*@sumY)/SQRT((@n*@sumX2-@sumX_2)*(@n*@sumY2-@sumY_2))

UPDATE @TrendTable
SET Trend = @m * PID + @b


SELECT * FROM @TrendTable