Dynamic Columns AND Rows ?

[RiddleMeThis]

Hi Guys,
I'm hoping someone can help here because this has caused a lot of head scratching over the past few weeks.
I know of the PIVOT function to switch rows into columns, but this means the columns also become rows, therefore the row number is fixed, I would like to create a report that will grow in both the X and the Y axis each month (creating a stairstep report).
For example, the month an account was created would be down the left side, and the month a payment was made would be across the top.
The best I can come up with at the moment is a monthly column manually added to a seperate Excel spreadsheet by using the following code to report on the count of all payments the previous month, grouped by account creation month:

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-1,getdate())), 0)
SET @EndDate = DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

SELECT CONVERT(VARCHAR(3), tblAccount.CreateDate,100)
+ ' '
+ CONVERT(VARCHAR(4), tblAccount.CreateDate,111)
, COUNT(tblPayment.PaymentID)

FROM tblAccount
INNER JOIN tblPayment ON tblAccount.AccountID = tblPayment.AccountID

WHERE tblPayment.PayDate BETWEEN @StartDate AND @EndDate

GROUP BY CONVERT(VARCHAR(3), tblAccount.CreateDate,100)
+ ' '
+ CONVERT(VARCHAR(4), tblAccount.CreateDate,111)




Any Ideas? I'll be away over the weekend but thanks in advance to anyone who can provide input.

 

[markros]

I'm not sure I understand the whole question, but when you don't know your columns in advance, you use Dynamic Pivot.

Here is one link of the topic

http://www.sqlusa.com/bestpractices/training/scripts/dynamicsql/

(see 5th example)

PluralSight Learning Library

 

[RiddleMeThis]

Hi Markros,
Thanks for the tip, you're right, I hadn't used dynamic pivots before, and had only really brushed on basic pivots, so I have it functional now, producing the correct results. I had to recode the column headers string a couple of times to get it how it has been requested, but it works!

I now have another related problem though.
When I put the query into a SProc, and try to load it in SSRS, SSRS only recognises the first 2 columns, even though there is much more than 2!
I've tried to add the other columns manually but I get the following error message:
"An error occured during local report processing.
An error has occured during report processing.
Index was outside the bounds of the array."

Any ideas why this would happen?

My source is listed below, if i remove the "create procedure" and just run the query it works, and when i run the query in the "data" tab of SSRS its fine, but SSRS wont acknowledge the other columns in the layout/preview tabs:



CREATE PROCEDURE StairStepTest3
AS




DECLARE @TotalMonthsX VARCHAR(2)
SET @TotalMonthsX = '12'

DECLARE @TotalMonthsY VARCHAR(2)
SET @TotalMonthsY = '12'

DECLARE @MonthsCounter INT
SET @MonthsCounter = 1

DECLARE @PivotColumnHeaders VARCHAR(MAX)
SET @PivotColumnHeaders = '[Month '

WHILE @MonthsCounter <= CAST(@TotalMonthsX AS INT)
BEGIN
SET @PivotColumnHeaders = @PivotColumnHeaders + CAST(@Monthscounter AS VARCHAR) + '], [Month '
SET @MonthsCounter = @MonthsCounter + 1
END

SET @PivotColumnHeaders = SUBSTRING(@PivotColumnHeaders,1,LEN(@PivotColumnHeaders)-8)




DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
SELECT *
FROM (
/* Creates a subquery containing all the raw data for use in the pivot, but not yet aggregated or grouped */
SELECT CONVERT(VARCHAR(3), tblAccount.CreateDate, 107) + '' '' + CONVERT(VARCHAR(4), tblAccount.CreateDate, 111) AS [Y]
, ''MONTH '' + CAST(DATEDIFF(mm, tblAccount.CreateDate, tblPayment.PayDate) +1 AS VARCHAR) AS [X]
, tblPayment.PayAmount [AggregatedValue]

FROM tblAccount
INNER JOIN tblPayment ON tblAccount.AccID = tblPayment.AccID

WHERE DATEDIFF(mm, tblAccount.CreateDate, tblPayment.PayDate) BETWEEN 0 AND ' + @TotalMonthsX + '
AND DATEDIFF(mm, tblAccount.CreateDate, GETDATE()) BETWEEN 0 AND ' + @TotalMonthsY + '
) AS PivotData

PIVOT (
/* Aggregate the data on the following [AggregatedValue] field */
SUM(AggregatedValue)

/* Group the results by @PivotColumnHeaders */
FOR [X] IN (' + @PivotColumnHeaders + ')

) AS PivotTable

/* Order the rows by Y date */
ORDER BY YEAR([Y]) ,MONTH([Y])
'

EXECUTE(@PivotTableSQL)

 

[RiddleMeThis]

Got it !

I just had to create a temp table and fill it with data, then drop it, SSRS looks at this temp table and takes the columns from it:

CREATE TABLE #Temp1
(
CreateDate VARCHAR(8)
)

SET @MonthsCounter = 1

DECLARE @ColumnCreator NVARCHAR(MAX)

WHILE @MonthsCounter <= CAST(@TotalMonthsX AS INT)
BEGIN
SET @ColumnCreator = N'

ALTER TABLE #Temp1
ADD M'+ @MonthsCounter + ' DECIMAL
'
EXEC (@ColumnCreator)
SET @MonthsCounter = @MonthsCounter + 1
END



INSERT INTO #Temp1
EXEC(@PivotTableSQL)
SELECT * FROM #Temp1
DROP TABLE #Temp1