Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I - Page 2
November 20, 2006
Parameters for Analysis Services Reporting
Objective and Business Scenario
Throughout many past articles of the MSSQL Server Reporting Services series, we have leveraged parameters within the context of MDX queries. Reporting Services 2000, initially intended as a component of the MSSQL Server 2005 "BI Release", was released early, as many of us are aware, with resulting limitations in some of its capabilities, as most realistic practitioners would expect. One of the challenges that faced many of us was the absence of an MDX editor: while those of us who were comfortable with MDX syntax were not impaired significantly (although we had to deal with circumstances that accompanied parameterization in Reporting Services 2000, such as the need to string our queries for passage from Reporting Services to the Analysis Server, and the inability to "test generate" our Datasets, once we had Parameters in place within the MDX queries), those who were already challenged with MDX as a language almost certainly found no amusement in dealing with the added mechanics. Reporting Services 2005 introduced the MDX Query Builder, a tool that appeals, due to its "on-off" flexibility, to most practitioners who are comfortable writing direct MDX queries, as well as the MDX- challenged among those authors and developers who need to work with OLAP data sources. This enhancement has, unsurprisingly, changed the way that many of us had become accustomed to writing the underlying MDX queries for our Analysis Services reports.
In this article, we begin an extended examination of Parameters in Reporting Services 2005. Our initial objective will be to explore the basics surrounding Parameters, revealing several events that take place in conjunction with their creation in the design environment. Within our examination of the interaction among the various components that comprise and support run-time parameterization in Reporting Services 2005 in general, and with a specific focus upon the interaction of these components in conjunction with the use of MDX as the query language, we will establish the foundation for more sophisticated parameterization concepts that we introduce in prospective articles. We will begin by adding a simple filter / Parameter combination to a basic OLAP report, containing a Matrix data region, via the graphical interface of the MDX Query Builder. We will then, more importantly, examine the objects that Reporting Services automatically creates, in conjunction with our addition, to support parameterization at runtime. An understanding of the relationship of these objects will serve as the foundation for more elaborate parameterization techniques in sessions that follow.
Our first objective is to create a copy of the Sales Reason Comparisons sample report, within which we can implement basic Parameters. The focus of our efforts will be the addition of parameterization into an OLAP report containing a Matrix data region (the mechanics behind adding the capability, not the design of the report itself). Because of time limitations, 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 basic parameterization is the same in real world scenarios, with perhaps a more complex 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.