Overcoming Software Limitations in Multidimensional Reports



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])) &lt; 2 then ’0′ + cast(month([date]) as
          varchar(2)) else cast(month([date]) as varchar(2)) end
+ ‘_’ + case when len(datepart(dd, [date])) &lt; 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
6_2
006
W0
6_2
006
_02
_09
_00
_00
_00

200
6_2
006
W0
5_2
006
_02
_02
_00
_00
_00

200
6_2
006
W0
4_2
006
_01
_29
_00
_00
_00

200
6_2
006
W0
3_2
006
_01
_22
_00
_00
_00

200
6_2
006
W0
2_2
006
_01
_14
_00
_00
_00

200
6_2
006
W0
1_2
006
_01
_07
_00
_00
_00

200
5_2
005
W5
2_2
005
_12
_31
_00
_00
_00

200
5_2
005
W5
1_2
005
_12
_24
_00
_00
_00

200
5_2
005
W5
0_2
005
_12
_15
_00
_00
_00

200
5_2
005
W4
9_2
005
_12
_07
_00
_00
_00

200
5_2
005
W4
8_2
005
_12
_01
_00
_00
_00

200
5_2
005
W4
7_2
005
_11
_22
_00
_00
_00

200
5_2
005
W4
6_2
005
_11
_15
_00
_00
_00

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.

Continues…

Pages: 1 2 3 4




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |