Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Please help with dynamic pivot query/ CTE/ SSRS Expand / Collapse
Author
Message
Posted Wednesday, November 13, 2013 9:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 20, 2013 10:54 AM
Points: 2, Visits: 28
Hi there,

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.

Thank you,

Alimda




====================================================================


Current report query is similar to this:

CTE_Records
(
...
),

CTE_Record_Details
(
...
),

CTE_Pivot --Static Pivot for the most recent 10 years of data
(
...
)

SELECT
...
FROM CTE_Records
JOINS... CTE_Record_Details
JOINS... CTE_Pivot


=====================================================================


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);

DECLARE
@cols AS NVARCHAR(MAX),
@y AS INT,
@sql AS NVARCHAR(MAX);

-- Construct the column list for the IN clause
-- e.g., [2002],[2003],[2004]
SET @cols = STUFF(
(SELECT N',' + QUOTENAME(y) AS [text()]
FROM
(
SELECT ...
FROM ...

) AS Y
ORDER BY y
FOR XML PATH('')),
1, 1, N'');

-- Construct the full T-SQL statement
-- and execute dynamically
SET @sql = N'SELECT *
FROM
(
SELECT ...
FROM ...

) AS D
PIVOT(SUM(Amount) FOR [year] IN(' + @cols + N')) AS P;';

EXEC sp_executesql @sql;
Post #1513968
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse