RE: Linear Regression

[knarf]

I would also like to use trendlines in simple line charts

[knarf]

After some research and and some testing ox the trendline in Excel I've come up with a method of displaying trendline in Reporting Services charts.

It involves using SQL to workout the trendline and displaying it as a second column of data. Copy the code below into Quary Analyzer and run it. It can then be adpated to your SP or UDF as required.

Regards

Frank

--#########################################

/*

BASED on code from Noah Ganter: many thanks

Altered by: Frank Kerrigan, Stryker UK

Note that a the trend feature in EXCEL is a simple regression of the
form Y=bx + c
where Y is the predicted value, b is the beta coefficient or slope, and c is
the contstant. This is linear regression. You may also fit curvelinear
regression, say, with a quadratic root Y=bx2 + bx + c or even a cubed trend
Y=bx3 + bx2 + bx + c. I have included code for a simple, linear regression.

For the purposes of this demonstration, just cut and paste this code into
sql server and run it:
the X values are arrayed in a column, x and
y values are to be predicted. The new x to fit to once the trend has been
made are in the x_predicted column.

*/

drop TABLE #data_table

CREATE TABLE #data_table (x FLOAT, y FLOAT, y_predicted FLOAT)
INSERT #data_table (x,y) values (1,232)
INSERT #data_table (x,y) values (2,334)
INSERT #data_table (x,y) values (3,567)
INSERT #data_table (x,y) values (4,765)
INSERT #data_table (x,y) values (5,324)
INSERT #data_table (x,y) values (6,924)

--correlation
declare @r FLOAT

--Standard Deviation of X
declare @sd_x FLOAT

--Standard Deviation of Y
declare @sd_y FLOAT

--Slope
declare @b FLOAT

--Constant
declare @c FLOAT

--Mean X
declare @mean_x FLOAT

--Mean Y
declare @mean_y FLOAT

--r Correlation
select @r = (select
(count(*)*sum(x*y)-sum(x)*sum(y))/(
sqrt(count(*)*sum(square(x))-square(sum(x)))*
sqrt(count(*)*sum(square(y))-square(sum(y))))
correlation
from #data_table)

--sd Standard Deviation of X
select @sd_x = (select stdev(x) from #data_table)
select @sd_y = (select stdev(y) from #data_table)

--mean X
select @mean_x = (select avg(x) from #data_table)
select @mean_y = (select avg(y) from #data_table)

--b SLOPE
select @b = @r*(@sd_y/@sd_x)

--c Constant
select @c = @mean_y - (@b*@mean_x)

--Add trend to y_predicted to the column

update #data_table set y_predicted=a.value from (
select x, (@b*x + @c) as value
from #data_table) a
where #data_table.x = a.x

-- DISPLAY VALUES

select * from #data_table

--################################################