I'm developing a SSRS report in VS2010 and trying to come up with the following outcome.
Data1 Data2 Data3 Data4 2004 2005 … 2013
.. .. .. .. .. .. ... ..
Data 1 through 4 are coming from several CTE joins. The years data (2004 through 2013) supposed to be the most recent 10 years sum of data. I built the years sum data with a static pivot query and joined to my other CTEs via the unique record ID and this report calls one big view that consist of several CTEs and it works fine. As a stated before the years data should have been the most recent 10 years and it is static currently in my report. In other words, I need to change my report next year to reflect the most recent 10 years. I tried to build a dynamic pivot query to replace the static pivot query, however, I couldn't join my dynamic pivot query to my main CTE because the dynamic pivot query declares temp table etc. and I couldn't find a way to join it my main data source. What would be the right approach to build this report?
Any help is appreciated.
Current report query is similar to this:
CTE_Pivot --Static Pivot for the most recent 10 years of data
I was able to create a dynamic pivot table via this query, but I can't join this to my main query above.
DECLARE @T AS TABLE(y INT NOT NULL PRIMARY KEY);
@cols AS NVARCHAR(MAX),
@y AS INT,
@sql AS NVARCHAR(MAX);
-- Construct the column list for the IN clause
-- e.g., ,,
SET @cols = STUFF(
(SELECT N',' + QUOTENAME(y) AS [text()]
) AS Y
ORDER BY y
FOR XML PATH('')),
1, 1, N'');
-- Construct the full T-SQL statement
-- and execute dynamically
SET @sql = N'SELECT *
) AS D
PIVOT(SUM(Amount) FOR [year] IN(' + @cols + N')) AS P;';
EXEC sp_executesql @sql;