Saturday, February 26, 2011

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...
  2. AS
  3. DECLARE @weeks_ordered TABLE (num VARCHAR(3))
  4. DECLARE @weeks TABLE (wk INT)
  6. DECLARE @cols NVARCHAR(3000)
  8. DECLARE @col_pv VARCHAR(2000)
  9. DECLARE @query VARCHAR(3000)
  12. SET @INT = 1
  15. WHILE @INT <= 52
  16. BEGIN
  17.     INSERT INTO @weeks VALUES (@INT)
  18.     SET @INT = @INT + 1
  19. END
  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
  32. SELECT @col_pv = STUFF(( SELECT  
  33.                                 '],[' + w.num
  34.                         FROM  @weeks_ordered AS w
  35.                         FOR XML PATH('')
  36.                       )12'') + ']'
  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.                       )12'') + '],0) as W' + CAST(DATEPART(wk,GETDATE()) AS VARCHAR(2))
  46. IF OBJECT_ID('tempdb.dbo.#detail') IS not null
  47.         DROP TABLE #detail
  49. CREATE TABLE #detail
  50. (
  51. AccountNumber VARCHAR(10)
  52. ,PruductNumber VARCHAR(25)
  53. ,OrderQty INT
  54. ,WeekNumber SMALLINT
  55. )
  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
  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
  6.         CurrentWeek = DatePart(DateInterval.WeekOfYear, System.DateTime.Now)
  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.

No comments:

Post a Comment