This prevents a gap in the line charts in SSRS. value as the trendline (instead of NULL). For the last actual value (September in this example), we want forecast to have the same ,Forecast = CASE WHEN Trend IS NOT NULL AND c.ID (SELECT MAX(ID) FROM #Temp_Regression) elaborate function to get the short month name and yearĮLSE CONCAT(LEFT(DATENAME(MONTH,CONVERT(DATE,CONCAT('2014','-',c.ID,'-','01'))),3),' 2014') SELECT TOP 12 ID = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) In this example, ID is the x-coordinate and OrderQuantity the y-coordinate. SELECT ID = 9, OrderMonth = 'Sep 2014', OrderQuantity = 63, Trend = CONVERT(DECIMAL(38, 10),NULL) SELECT ID = 8, OrderMonth = 'Aug 2014', OrderQuantity = 70, Trend = CONVERT(DECIMAL(38, 10),NULL) SELECT ID = 7, OrderMonth = 'Jul 2014', OrderQuantity = 65, Trend = CONVERT(DECIMAL(38, 10),NULL) SELECT ID = 6, OrderMonth = 'Jun 2014', OrderQuantity = 58, Trend = CONVERT(DECIMAL(38, 10),NULL) SELECT ID = 5, OrderMonth = 'May 2014', OrderQuantity = 53, Trend = CONVERT(DECIMAL(38, 10),NULL) SELECT ID = 4, OrderMonth = 'Apr 2014', OrderQuantity = 50, Trend = CONVERT(DECIMAL(38, 10),NULL) SELECT ID = 3, OrderMonth = 'Mar 2014', OrderQuantity = 60, Trend = CONVERT(DECIMAL(38, 10),NULL)
SELECT ID = 2, OrderMonth = 'Feb 2014', OrderQuantity = 41, Trend = CONVERT(DECIMAL(38, 10),NULL) SELECT ID = 1, OrderMonth = 'Jan 2014', OrderQuantity = 45, Trend = CONVERT(DECIMAL(38, 10),NULL) Trend is an empty column that will hold the calculated linear regression later on. IF OBJECT_ID('Tempdb.#Temp_Regression') IS NOT NULL
0 kommentar(er)
