Solution 2: Multidimensional Report Dynamic Columns
Next, we’re going to extend our query string code to return additional data that we’re going to use in our report headers. The trick we’re using works due to the way a query string can be defined. Instead of writing a simple SELECT statement to return data, we’re going to create a stored procedure type logic set. Recall that in our previous solution, we used the =”<OLAP and parameters>” format to pass in variables. Putting the =”” in our query string allows us to run our own code.
Consider the following stored procedure, broken into three sections for readability:
DECLARE @rec_num int, @weeknm varchar(255)
IF EXISTS
(
SELECT * FROM dbo.sysobjects
WHERE id = object_id(N'[Rpt_Header]’) AND OBJECTPROPERTY(id,
N’IsUserTable’) = 1)
DROP TABLE [Rpt_Header]
CREATE TABLE [Rpt_header](rec_num INT IDENTITY (1, 1), weeknm
VARCHAR(40)
)
—
INSERT [Rpt_Header](weeknm)
SELECT
cast([weekyear] as varchar(4))+’_’+weeknm + ‘_’+ cast(year([date]) as
varchar(4)) + ‘_’
+ case when len(datepart(mm, [date])) < 2 then ‘0’ + cast(month([date]) as
varchar(2)) else cast(month([date]) as varchar(2)) end
+ ‘_’ + case when len(datepart(dd, [date])) < 2 then ‘0’ + cast(day([date]) as
varchar(2)) else cast(day([date]) as varchar(2)) end
+ ‘_00_00_00’
FROM olapcube_time_dimension_table
WHERE [Date] BETWEEN getdate() — 100 AND getdate() AND [weekpart] = 6
order by [weeknm] desc
—
SELECT c1.weeknm AS C1, c2.weeknm AS C2, c3.weeknm AS C3, c4.weeknm
AS C4, c5.weeknm AS C5, c6.weeknm AS C6,
c7.weeknm AS C7, c8.weeknm AS C8, c9.weeknm C9, c10.weeknm C10,
c11.weeknm AS C11, c12.weeknm AS C12,
c13.weeknm AS C13
FROM rpt_header AS c1, rpt_header AS c2, rpt_header AS c3, rpt_header AS
c4, rpt_header AS c5, rpt_header AS c6,
rpt_header AS c7, rpt_header AS c8, rpt_header AS c9, rpt_header AS c10,
rpt_header AS c11, rpt_header AS c12,
rpt_header AS c13
WHERE c1.rec_num = 13 AND c2.rec_num = 12 AND c3.rec_num = 11 AND
c4.rec_num = 10 AND c5.rec_num = 9 AND
c6.rec_num = 8 AND c7.rec_num = 7 AND c8.rec_num = 6 AND
c9.rec_num = 5 AND c10.rec_num = 4 AND
c11.rec_num = 3 AND c12.rec_num = 2 AND c13.rec_num = 1
Let’s look at this procedure step by step.
The first section simply declares a couple of local variables and creates a local table. This section could be modified to create a temporary global or local table if database permissions for the Reporting Services account aren’t set to allow the creation of objects.
The second section fills our newly created table with an identity value and a date value written as a string. We’re using our WHERE clause to return the thirteen most recent date fields from the database. The string written will be similar to
2006_2006W06_2006_02_09_00_00_00
At this point, our table contains 13 rows on two columns. Each row contains an identity value and a date string. The last section of the procedure uses the self-join method (also known as a pivot) to distribute the rows across columns. This technique can cause the select statement to be quite messy and confusing, but it provides the functionality we’re looking for. After this runs, we’ll have the following table available:
|
C1 |
C2 |
C3 |
C4 |
C5 |
C6 |
C7 |
C8 |
C9 |
C10 |
C11 |
C12 |
C13 |
|
200 |
200 |
200 |
200 |
200 |
200 |
200 |
200 |
200 |
200 |
200 |
200 |
200 |
Why are we getting this table? We’re going to substitute the date string values from this table into our report definition. More specifically, we’re going to take each date string and put them in the report header that we’ll use to construct our main query. Since this procedure is dynamic in nature due to our WHERE clause using the “getdate()” function, each time the query is run, it will return the most recent thirteen weeks.