Featured Database Articles

Mastering OLAP Reports: Parameterizing Number of "Look Back" Periods with the MDX LastPeriods() Function, Part I - Page 3

Practice

Our
first objective is to create a copy of the Sales Reason Comparisons sample report, within which we can implement the
newly required parameter.The focus of our
efforts will be the addition of this parameter into an OLAP report containing
a matrix data region (the mechanics behind adding the capability,
not the design of the report itself). To save time, we will be working with a
simple, pre-existing sample report  in reality, the business environment will
typically require more sophistication. The process of setting up the basic
parameter is the same in real world scenarios, with perhaps a more complicated
set of underlying considerations. (I virtually
never encounter a client reporting requirement that does not involve at least
basic parameterization.)

We
will perform our practice session from inside the MSSQL Server Business
Intelligence Development Studio. For more exposure to the Business
Intelligence Development Studio itself, and the myriad design, development
and other evolutions we can perform within this powerful interface, see other
articles in this series, as well as within my Database
Journal series Introduction to MSSQL Server Analysis
Services. In
this article, we will be commenting only on the features relevant to our
immediate practice exercise, to allow us to keep to the focus of the article
more efficiently.

Preparation: Create a Clone Report within the Reporting Services Development Environment

For purposes of our
practice session, we will create a copy of the Sales Reason Comparisons report, one of several samples
that are available with (and installable separately from) the MSSQL Server
2005 integrated business intelligence suite. Making preparatory
modifications, and then making the enhancements to the report to add the
functionality that forms the subject of our lesson, can be done easily within
the Business IntelligenceStudio environment. Working with a copy
of the report will allow us the luxury of freely exploring our options, and will
leave us with a working example of the specific approach we took, to which we
can refer in our individual business environments.

Open the Sample Report Server Project

For purposes of our
practice session, we will open the AdventureWorksSample Reports
project, which contains the sample reports that ship with the Reporting
Services component of the MSSQL Server 2005 suite. We will complete
our practice session within the sample project so as to save the time required
to set up a development environment from scratch within the Business
Intelligence Development Studio.

To open the AdventureWorksSample Reports project, please see the following procedure in the References
section of my articles index:

Lets
ensure we have a working data source. Many of us will be running side-by-side
installations of MSSQL Server 2000 and MSSQL Server 2005. This
means that our installation of the latter will need to be referenced as a server
/ instance combination, versus a server name alone. (The default for
the Adventure Works DW projectsamples connection is localhost,
which will not work correctly in such a side-by-side installation, as MSSQL
Server 2000 will have assumed the identity of the local PC by default.)

If you do not know how
to ascertain or modify connectivity of the Analysis Services data
source, please perform the steps of the following procedure in the References
section of my articles index:

We will begin with a copy
of the Reporting Services 2005 Sales Reason Comparisons OLAPreport,
which we will use for our practice exercise. Creating a clone of the project means we can make
changes to select contents (perhaps as a part of later exploration with our
independent solution), while retaining the original sample in a pristine state
for other purposes, such as using it to accompany relevant sections of the Books
Online, and other documentation, as a part of learning more about Reporting
Services (particularly an OLAP report using an Analysis Services data
source),and other components of the Microsoft integrated business intelligence
solution in general.

If you do not know how
to create a copy of an existing report, please perform the steps of the
following procedure in the References section of my articles index:

We now
have a clone OLAP report file within our Reporting Services 2005 Project,
with which we can proceed in the next section to make modifications for our
subsequent practice session.

Preparation: Modify the OLAP Report for Use within Our Practice Session

We will
next make a few modifications to prepare the report for our practice session. Our
objective will be to begin the session with a simple OLAP report that contains
no parameters. Lets
open the report in Layout view (for those of us not already there) and
make the necessary settings to place it into a state upon which we can commence
our practice steps.

1.
Right-click DBJ_OLAP_Report.rdl
(or your own choice of a similar report) in the Solution Explorer.

2.
Select Open
from the context menu that appears, as shown in Illustration 1, as
necessary.

Illustration 1: Opening
the New Report ...

DBJ_OLAP_Report.rdl
opens in Layout
view.

We will
start with the Data tab.

3.
Click the
Data tab.

We
enter the Data tab, where we will remove a handful of components that we
do not need for our practice session. We will accomplish this from the
perspective of the MDX Query Builder, the main components of which (in Design
mode) are labeled in Illustration 2 below.

Illustration 2: The MDX
Query Builder  Design Mode

4.
With the Dataset
selector, select the ProductList dataset, as depicted in Illustration
3.

Illustration 3: Select
the ProductList Dataset ...

5.
Once the Dataset
loads, click the Delete button, as shown in Illustration 4.

17.
Drag the Month
level into the Data pane, dropping it to the left of the Sales Reason
column, as depicted in Illustration 9.

Illustration 9: Adding
Months to the Dataset

The
new Month column appears, as desired. Having made the necessary changes on the Data tab, we are ready
to move to the Layout tab, where we can conclude our preparatory
modifications to the report file.

18.
Click the Layout
tab, as shown in Illustration 10.

Illustration 10: Click
the Layout Tab

19.
On the Layout
tab, within the matrix data region, select the value appearing
underneath the Internet Total column heading.

20.
Right-click
the value in the text box (the value appears as =Sum(Fields!Internet_Total_Product_Cost.Value)
).

21.
Select Delete
from the context menu that appears,as depicted in Illustration 11.