Francisco Tapia
fhtapia at gmail.com
Tue Aug 29 15:48:42 CDT 2006
For those of you who were sitting at the edge of your seat just waiting for the conclusion of the TrendLine capper, this is the solution I was happy with. 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- at sumX*@sumY)/(@n*@sumX2- at sumX_2) --y intercept SELECT @b = (@sumY- at m*@sumX)/@n --r SELECT @r = (@n*@sumXY- at sumX*@sumY)/SQRT((@n*@sumX2- at sumX_2)*(@n*@sumY2- at sumY_2)) UPDATE @TrendTable SET Trend = @m * PID + @b SELECT * FROM @TrendTable On 8/14/06, Francisco Tapia <fhtapia at gmail.com> wrote: > Robert, > I have 2 data points, one is the installs, and the other is the avg > # of fails, I wanted to trend the Avg # fails, but RS doesn't do that, > and it doesn't seem to allow you to have a 2nd Y axis. I am looking > into creating my own trendline manually, so I will just need to live > w/o the 2nd y axis. > > On 8/11/06, MacQueen, Robert <robert.macqueen at ftnfinancial.com> wrote: > > > > > > Francisco, > > Not clear on this - are you saying you want the second Y-Axis to be a trend > > line, or you would like a trendline created on the data in the second > > Y-axis. If the former, the chart control allows for multiple data values - > > in the chart properties, go to the Data tab and click on Add in the Values > > area of the form. If the latter, I did not see anything that allows a > > trend-line to be added. Excel has that feature, but I did not see an option > > to add that in Reporting Services chart properties, and Rep Svcs BOL does > > not mention it. > > > > Robert > > > > ________________________________ > > From: listbounces at sswug.org [mailto:listbounces at sswug.org] On Behalf Of > > Francisco Tapia > > Sent: Thursday, August 10, 2006 5:50 PM > > To: dba-sqlserver at databaseadvisors.com; RepSvcs; SQL Server > > 2k List > > Subject: [SQL2K] Reporting Services 2000, 2nd Y Axis Chart > > > > > > (sorry for the cross-post) > > > > I've been googling for the answer all yesterday afternoon, and today > > morning, but to no avail, > > I have a report that looks a bit like the following: > > Month, Fail, Installed > > 1/1/2000, 3, 300 > > 2/1/2000, 1.5, 256 > > 3/1/2000, 2.5, 280 > > ... > > ... > > The purpose it to graph the report with one of the series on a second y > > axis, then on this 2nd axis I'm supposed to generate a trend line. Any > > advice on this in Sql Server Reporting services (2000) would be much > > appreciated. > > > > > > > > Thanks, > > > > -- > > -Francisco > > http://pcthis.blogspot.com |PC news with out the jargon! > > http://sqlthis.blogspot.com | Tsql and More... > > > > ------ > > For archives, see http://www.sswug.org/archives > > For list assistance, contact members at sswug.org > > To unsubscribe, email lists at sswug.org with > > UNSUBSCRIBE SQL2K in the body of the message. > > > > > > ------ > > For archives, see http://www.sswug.org/archives > > For list assistance, contact members at sswug.org > > To unsubscribe, email lists at sswug.org with > > UNSUBSCRIBE SQL2K in the body of the message. > > > > > > > -- > -Francisco > http://pcthis.blogspot.com |PC news with out the jargon! > http://sqlthis.blogspot.com | Tsql and More... > > > ------ > For archives, see http://www.sswug.org/archives > For list assistance, contact members at sswug.org > To unsubscribe, email lists at sswug.org with > UNSUBSCRIBE SQL2K in the body of the message. > > > -- -Francisco http://pcthis.blogspot.com |PC news with out the jargon! http://sqlthis.blogspot.com | Tsql and More...