Dynamic column names and fields in SSRS (Custom Matrix)

I had no choice but to do work with creating dynamic column headings and dynamically determine what field in my dataset should go where in a report today. Since this is the second time I’ve gone through this exercise and knowing the lack of information out there on really how to do it, I thought it deserves a blog entry.

So here is the basis of the requirements. You have a query that uses PIVOT but thrown into the mix is the need for dynamic columns in the PIVOT. This is usually a task when you are going after things like current week plus the last 52 weeks. That was the case in this situation. I needed to bring in a dynamic set of columns to be used in PIVOT. The matrix in 2005 did not give me what I needed in the end result so this is the path I took.

First task is to write the procedure to use PIVOT with dynamic column headers. I’m not going to go into that method since it’s well documented out there and out of scope. I will point you to Pivots with Dynamic Columns in SQL Server 2005 as it explains the way to accomplish this well.

I wrote something in AdventureWorks to for this example so if you have AdventureWorks floating around you should be able to read this and run through step for step with success.

Here is our procedure. I’m sure my methods will take great notice from my local TSQL friends  The dynamic SQL more so than anything...

1. CREATE PROCEDURE GetSalesPerWeek
2. AS
3. DECLARE @weeks_ordered TABLE (num VARCHAR(3))
4. DECLARE @weeks TABLE (wk INT)
5. DECLARE @DATE DATETIME
6. DECLARE @cols NVARCHAR(3000)
7. DECLARE @INT INT
8. DECLARE @col_pv VARCHAR(2000)
9. DECLARE @query VARCHAR(3000)
10.  
11.  
12. SET @INT = 1
13. SET @DATE = GETDATE()
14.  
15. WHILE @INT <= 52
16. BEGIN
17.     INSERT INTO @weeks VALUES (@INT)
18.     SET @INT = @INT + 1
19. END
20.  
21.  
22. INSERT INTO @weeks_ordered
23. SELECT
24. wk
25. FROM @weeks
26. ORDER BY
27. CASE WHEN DATEPART(wk,@DATE) - wk < 0
28. THEN DATEPART(wk,@DATE) - wk + 53
29. ELSE DATEPART(wk,@DATE) - wk
30. END DESC
31.  
32. SELECT @col_pv = STUFF(( SELECT  
33.                                 '],[' + w.num
34.                         FROM  @weeks_ordered AS w
35.                         FOR XML PATH('')
36.                       ), 1, 2, '') + ']'
37.  
38. SELECT  @cols = STUFF(( SELECT  
39.                                 '],0) as W' + CASE WHEN CAST(w.num - 1 AS VARCHAR(2)) = 0 THEN '52' ELSE
40.                                                         CAST(w.num - 1 AS VARCHAR(2)) END + ',isnull([' + w.num
41.                         FROM  @weeks_ordered AS w
42.                         FOR XML PATH('')
43.                       ), 1, 2, '') + '],0) as W' + CAST(DATEPART(wk,GETDATE()) AS VARCHAR(2))
44.  
45.  
46. IF OBJECT_ID('tempdb.dbo.#detail') IS not null
47.         DROP TABLE #detail
48.  
49. CREATE TABLE #detail
50. (
51. AccountNumber VARCHAR(10)
52. ,PruductNumber VARCHAR(25)
53. ,OrderQty INT
54. ,WeekNumber SMALLINT
55. )
56.  
57.  
58. INSERT INTO #detail
59. SELECT
60.     cust.AccountNumber
61.     ,items.ProductNumber
62.     ,det.OrderQty
63.     ,DATEPART(wk,hdr.ShipDate) WeekNumber
64. FROM
65. Sales.SalesOrderHeader hdr
66. INNER Join Sales.SalesOrderDetail det ON hdr.SalesOrderID = det.SalesOrderID
67. INNER Join Production.Product items ON det.ProductID = items.ProductID
68. INNER Join Sales.Customer cust ON hdr.CustomerID = cust.CustomerID
69. INNER Join @weeks ord ON DATEPART(wk,ShipDate) = wk
70. WHERE ShipDate >= DATEADD(wk,-52,'2004-06-01')
71. GROUP BY
72.     cust.AccountNumber
73.     ,items.ProductNumber
74.     ,det.OrderQty
75.     ,DATEPART(wk,hdr.ShipDate)
76.     ,wk
77. ORDER BY
78. CASE WHEN DATEPART(wk,ShipDate) - wk < 0
79. THEN DATEPART(wk,ShipDate) - wk + 53
80. ELSE DATEPART(wk,ShipDate) - wk
81. END
82.  
83.  
84. SET @query =
85. '
86. Select 
87.     AccountNumber
88.     ,PruductNumber
89.     , '     + RIGHT(@cols,LEN(@cols)-10) + '
90. From
91.     #detail as sales
92. PIVOT (sum(OrderQty) FOR WeekNumber IN (' + @col_pv + ')) as pv
93. Order By AccountNumber
94. '
95. EXEC(@query)

The results shows us the PIVOT results of each account number and the sales for the week of the year

Image reduced in size to fit your browser, click for original size

The problem with all of this is the dynamic nature of the column names. In reporting services we’re used to handling column names as static entities. So here is how we’ll build our report given the fact these column names can and will change over time.

So create a new report in your solution and add a new DataSet. Make it a text call with the following statement

1. EXEC GetSalesPerWeek

Run the DataSet to verify everything comes in ok.

Image reduced in size to fit your browser, click for original size

Now add a new table to your empty report. Add the account number and product number as you would normally. Next we need to figure out what week is actually first. To do this we’re going to write a function in the code section of SSRS

In the layout tab go to Report and select Report Properties. This will give you the properties for the entire report. Select the Code tab. Copy and paste the following code into the window

1. Public Function GetColumnHeading(ByVal x As Integer)
2.        Dim WeeksArr As New System.Collections.ArrayList()
3.         Dim i As Long
4.         Dim CurrentWeek As Long
5.  
6.         CurrentWeek = DatePart(DateInterval.WeekOfYear, System.DateTime.Now)
7.  
8.         For i = 1 To 52
9.             WeeksArr.Add(1 + (i + CurrentWeek - 1) Mod 52)
10.         Next
11.         Return WeeksArr(x)
12.     End Function

This code was written by our own gmmastros. Thanks to him for this and the help it gave me when I needed it. Gets the job done and it does it quickly.

Final results should look like this

Image reduced in size to fit your browser, click for original size

Hit OK to save.

Now in the field next to the Product Number go ahead and enter an expression for the heading like this

="W" & Code.GetColumnHeading(0)

Recall in our procedure we return each week as Wnn for the week number. So in our code we created an ArrayList filled up with the order we want. The same order we based the procedure off of. Now by using the index of the ArrayList we can simply call for the heading that should be all the way to the left (-51 weeks from the current) by means of index of 0. In the details textbox we can then simply do the following as well given the same guidelines

=Fields("W" & Code.GetColumnHeading(0)).Value

Most developers don’t know they can reference the fields by name in this manner. Usually it just isn’t required and that is the case. It can be useful to note that you can dynamically fill the object name in though and get the same results as Fields!name.Value

I went ahead and put a few more columns and increased the index requested from the ArrayList. Running that results in the following.

Image reduced in size to fit your browser, click for original size

Now you have your customer matrix by means of dynamic column and field referencing. You also a nice example of PIVOT by means of dynamic column names.