Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Please help with dynamic pivot query/ CTE/ SSRS


Please help with dynamic pivot query/ CTE/ SSRS

Author
Message
alimda
alimda
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 33
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;
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search