XML Feeds
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. Sense 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 sense 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...
- CREATE PROCEDURE GetSalesPerWeek
- AS
- DECLARE @weeks_ordered TABLE (num VARCHAR(3))
- DECLARE @weeks TABLE (wk INT)
- DECLARE @DATE DATETIME
- DECLARE @cols NVARCHAR(3000)
- DECLARE @INT INT
- DECLARE @col_pv VARCHAR(2000)
- DECLARE @query VARCHAR(3000)
- SET @INT = 1
- SET @DATE = GETDATE()
- WHILE @INT <= 52
- BEGIN
- INSERT INTO @weeks VALUES (@INT)
- SET @INT = @INT + 1
- END
- INSERT INTO @weeks_ordered
- SELECT
- wk
- FROM @weeks
- ORDER BY
- CASE WHEN DATEPART(wk,@DATE) - wk < 0
- THEN DATEPART(wk,@DATE) - wk + 53
- ELSE DATEPART(wk,@DATE) - wk
- END DESC
- SELECT @col_pv = STUFF(( SELECT
- '],[' + w.num
- FROM @weeks_ordered AS w
- FOR XML PATH('')
- ), 1, 2, '') + ']'
- SELECT @cols = STUFF(( SELECT
- '],0) as W' + CASE WHEN CAST(w.num - 1 AS VARCHAR(2)) = 0 THEN '52' ELSE
- CAST(w.num - 1 AS VARCHAR(2)) END + ',isnull([' + w.num
- FROM @weeks_ordered AS w
- FOR XML PATH('')
- ), 1, 2, '') + '],0) as W' + CAST(DATEPART(wk,GETDATE()) AS VARCHAR(2))
- IF OBJECT_ID('tempdb.dbo.#detail') IS not null
- DROP TABLE #detail
- CREATE TABLE #detail
- (
- AccountNumber VARCHAR(10)
- ,PruductNumber VARCHAR(25)
- ,OrderQty INT
- ,WeekNumber SMALLINT
- )
- INSERT INTO #detail
- SELECT
- cust.AccountNumber
- ,items.ProductNumber
- ,det.OrderQty
- ,DATEPART(wk,hdr.ShipDate) WeekNumber
- FROM
- Sales.SalesOrderHeader hdr
- INNER Join Sales.SalesOrderDetail det ON hdr.SalesOrderID = det.SalesOrderID
- INNER Join Production.Product items ON det.ProductID = items.ProductID
- INNER Join Sales.Customer cust ON hdr.CustomerID = cust.CustomerID
- INNER Join @weeks ord ON DATEPART(wk,ShipDate) = wk
- WHERE ShipDate >= DATEADD(wk,-52,'2004-06-01')
- GROUP BY
- cust.AccountNumber
- ,items.ProductNumber
- ,det.OrderQty
- ,DATEPART(wk,hdr.ShipDate)
- ,wk
- ORDER BY
- CASE WHEN DATEPART(wk,ShipDate) - wk < 0
- THEN DATEPART(wk,ShipDate) - wk + 53
- ELSE DATEPART(wk,ShipDate) - wk
- END
- SET @query =
- '
- Select
- AccountNumber
- ,PruductNumber
- , ' + RIGHT(@cols,LEN(@cols)-10) + '
- From
- #detail as sales
- PIVOT (sum(OrderQty) FOR WeekNumber IN (' + @col_pv + ')) as pv
- Order By AccountNumber
- '
- EXEC(@query)
The results shows us the PIVOT results of each account number and the sales for the week of the year
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
- EXEC GetSalesPerWeek
Run the DataSet to verify everything comes in ok.
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
- Public Function GetColumnHeading(ByVal x As Integer)
- Dim WeeksArr As New System.Collections.ArrayList()
- Dim i As Long
- Dim CurrentWeek As Long
- CurrentWeek = DatePart(DateInterval.WeekOfYear, System.DateTime.Now)
- For i = 1 To 52
- WeeksArr.Add(1 + (i + CurrentWeek - 1) Mod 52)
- Next
- Return WeeksArr(x)
- 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
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.
Now you have your customer matrix in a sense by means of dynamic column and field referencing. You also a nice example of PIVOT by means of dynamic column names.
This blog probably has about 12 pages worth of explanations but I’d like to leave those to the forums so please follow the directions below if you want to discuss this method further. Otherwise, have fun with it!
*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.