Hi all

I generate the Trend Line for SSRS 2005 for Line chart by creating the Stored procedure by using the Mathematical calculations

This is the mathematical formula

**By using this formula we are generate the trend line for given data for Line chart in SSRS 2005 **

M for slope

C for Intercept

Y for Trend

** **

**Y=Mx+C**

M= n∑(xy)- ∑x∑y

________________

n∑(x)^2 -(∑x)^2

C= ∑y-m∑x

_________

n

key_numorator is nothing but the slope numorator Part

key_denominator is nothing but the Slope Denominator part

key _slope is nothing but the slope of the given data

key__intercept is nothing but the Intercept for the given data

key_trend is nothing but the Trend for the given data

-------------------------------------------------------------------------------------------------------------------------------------------------

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[Trend_Line_Chart]

AS

SET NOCOUNT ON

BEGIN

truncate table Trend_Line_chart_Table1_Temp

insert into Trend_Line_chart_Table1_Temp

SELECT * INTO Trend_Line_chart_Table1_Temp

From Original_Value_Table

--Now the Trend_Line_chart_Table1_Temp had the both x axix and Y axis --value

truncate table Trend_Line_chart_Table2_Temp

insert into Trend_Line_chart_Table2_Temp

select count(*) as nvalue,xvalue as xaxis ,yvalue as yaxis, sum(xaxis*yaxis) as sum_xy,sum(yaxis) as sum_y,sum(x) as sum_x,

sum(x*x) as total_xaxis,sum(sum(y)*sum(x)) as Sum_xy,(sum(x)*sum(x)) as sum_x_squ from Trend_Line_chart_Table1_Temp

--This table is used to fetch the calculated field from temp_table1 and --its increse the performance

select((select nvalue from Trend_Line_chart_Table2_Temp)*(select sum_xy from Trend_Line_chart_Table2_Temp))-

((select sum_y from Trend_Line_chart_Table2_Temp)*(select sum_x from Trend_Line_chart_Table2_Temp)) as **key_numorator** ,

((select nvalue from Trend_Line_chart_Table2_Temp)*(select total_xaxis from Trend_Line_chart_Table2_Temp))-

(select sum_x from Trend_Line_chart_Table2_Temp)*(select sum_x from Trend_Line_chart_Table2_Temp) as **key_denominator**,

(((select nvalue from Trend_Line_chart_Table2_Temp)*(select sum_xy from Trend_Line_chart_Table2_Temp))-

((select sum_y from Trend_Line_chart_Table2_Temp)*(select sum_x from Trend_Line_chart_Table2_Temp)))*1.0/

(((select nvalue from Trend_Line_chart_Table2_Temp)*(select total_xaxis from Trend_Line_chart_Table2_Temp))-

(select sum_x from Trend_Line_chart_Table2_Temp)*(select sum_x from Trend_Line_chart_Table2_Temp)) as **key _slope**,

((select sum_y from Trend_Line_chart_Table2_Temp)-

((select sum_x from Trend_Line_chart_Table2_Temp)*(((select nvalue from Trend_Line_chart_Table2_Temp)*(select sum_xy from Trend_Line_chart_Table2_Temp))-

((select sum_y from Trend_Line_chart_Table2_Temp)*(select sum_x from Trend_Line_chart_Table2_Temp)))*1.0/

(((select nvalue from Trend_Line_chart_Table2_Temp)*(select total_xaxis from Trend_Line_chart_Table2_Temp))-

(select sum_x from Trend_Line_chart_Table2_Temp)*(select sum_x from Trend_Line_chart_Table2_Temp))))

/(select nvalue from Trend_Line_chart_Table2_Temp) as **key__intercept**,

((((select nvalue from Trend_Line_chart_Table2_Temp)*(select sum_xy from Trend_Line_chart_Table2_Temp))-

((select sum_y from Trend_Line_chart_Table2_Temp)*(select sum_x from Trend_Line_chart_Table2_Temp)))*1.0/

(((select nvalue from Trend_Line_chart_Table2_Temp)*(select total_xaxis from Trend_Line_chart_Table2_Temp))-

(select sum_x from Trend_Line_chart_Table2_Temp)*(select sum_x from Trend_Line_chart_Table2_Temp))*rowcnt)+

((select sum_y from Trend_Line_chart_Table2_Temp)-

((select sum_x from Trend_Line_chart_Table2_Temp)*(((select nvalue from Trend_Line_chart_Table2_Temp)*(select sum_xy from Trend_Line_chart_Table2_Temp))-

(select sum_x from Trend_Line_chart_Table2_Temp)*(select sum_x from Trend_Line_chart_Table2_Temp))))

/(select nvalue from Trend_Line_chart_Table2_Temp) as **key_trend** ,

Xaxis,Yaxis

from Trend_Line_chart_Table1_Temp

SET NOCOUNT OFF

End

------------------------------------------------------------------------------------------------------------------------------------------------------