Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Trend line for Line chart in SSRS 2005

  • 24 May 2012
  • Author: karthi.kamal
  • Number of views: 9665
  • 0 Comments

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_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

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

 

 

Print
Categories: Reporting Services
Tags:
Rate this article:
No rating

karthi.kamalkarthi.kamal

Other posts by karthi.kamal

Please login or register to post comments.