Mastering OLAP Reports: Parameterizing Number of "Look Back" Periods with the MDX LastPeriods() Function, Part I - Page 3
June 13, 2008
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 Intelligence Studio 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 AdventureWorks Sample 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 AdventureWorks Sample Reports project, please see the following procedure in the References section of my articles index:
Ascertain Connectivity of the Shared Data Source
Lets 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 project samples 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:
Create a Copy of the Sales Reason Comparisons Report
We will begin with a copy of the Reporting Services 2005 Sales Reason Comparisons OLAP report, 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. Lets 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.
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.
4. With the Dataset selector, select the ProductList dataset, as depicted in Illustration 3.
5. Once the Dataset loads, click the Delete button, as shown in Illustration 4.
6. Click Yes on the Microsoft Report Designer warning message dialog that appears next, as depicted in Illustration 5.
The primary (and sole remaining) dataset, ProductData opens.
7. Within the Query pane of the MDX Query Builder, right-click the column heading for Internet Total Product Cost.
8. Click Delete Internet Total Product Cost from the context menu that appears, as shown in Illustration 6.
9. Within the Calculated Members pane (bottom left corner of the Data tab), right-click the sole Calculated Member, Profit.
10. Click Delete on the context menu that appears, as depicted in Illustration 7.
11. Click Yes, when asked, Are you sure ....
12. Right-click the single entry in the Filter pane (directly atop the Query pane).
13. Click Delete to eliminate the existing Product Category Parameter from the Filter pane, as shown in Illustration 8.
We will make an addition to the Dataset next.
14. Within the Metadata pane, expand the Date dimension, by clicking the + sign to its immediate left.
15. Expand the Fiscal folder that appears within the Date dimension.
16. Expand the Fiscal hierarchy (labeled Date.Fiscal) within the Fiscal folder.
17. Drag the Month level into the Data pane, dropping it to the left of the Sales Reason column, as depicted in Illustration 9.
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.
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.
22. Right-click the upper left-hand corner of the Matrix Data Region (the gray square).
The gray column and row bars disappear, as a light, patterned outline forms around the matrix data region, and the context menu appears.