Trend line for Line chart in SSRS 2005
This article will help to draw the Trend Line for Line chart in SSRS 2005 .this was done through by calculating the data points for the specific data .the mathematical formula for find the trend line was Y=Mx+C.i was searched more links to solve this problem but i didnt get any solution. i tried this and now i got a clear trend for the line chart graph. i hope this will be useful.if any queries ask me ..
Trend line for Line chart in SSRS 2005
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_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_trend ,
Xaxis,Yaxis
from Trend_Line_chart_Table1_Temp
SET NOCOUNT OFF
End
--------------------------------------------------------------------------------
Attachments
Is x and xaxis the same? Or are you selecting another value as your x?