Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Nov 20, 2006

Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I

By William Pearson

About the Series...

This article is a member of the series MSSQL Server Reporting Services. The series is designed to introduce MSSQL Server Reporting Services ("Reporting Services"), presenting an overview of its features, with tips and techniques for real-world use. For more information on the series in general, please see my initial Database Journal article, A New Paradigm for Enterprise Reporting. For the software components, samples and tools needed to complete the hands-on portion of this article, see BlackBelt Administration: Linked Reports in Report Manager, another article within this series.

About the Mastering OLAP Reporting Articles...

One of the first things that become clear to "early adopters" of Reporting Services is that the "knowledgebase" for Analysis Services reporting with this tool is, to say the least, sparse. As I stated in my article, Mastering OLAP Reporting: Cascading Prompts (where I treated the subject of cascading parameters for Reporting Services 2000), the purpose of the Mastering OLAP Reporting subset of my Reporting Services series is to focus on techniques for using Reporting Services for OLAP reporting. In many cases, which I try to outline in my articles at appropriate junctures, the functionality of well-established, but expensive, solutions, such as Cognos PowerPlay, can be met in most respects by Reporting Services – at a tiny fraction of the cost.

The vacuum of documentation in this arena, even taking into consideration the release of several books surrounding Reporting Services 2005 in recent months, continues to represent a serious "undersell" of Reporting Services, from an OLAP reporting perspective. I hope to contribute to making this arena more accessible for everyone, and to share my implementation and conversion experiences as the series evolves. In the meantime, we can rest assured that the OLAP potential in Reporting Services will contribute significantly to the inevitable commoditization of business intelligence, via the integrated Microsoft BI solution.

For more information about the Mastering OLAP Reporting articles, see the section entitled "About the Mastering OLAP Reporting Articles" in my article Ad Hoc TopCount and BottomCount Parameters.


In this article, we will get hands-on exposure to providing the most basic Parameter support, from the perspective of reports based upon an Analysis Services 2005 cube, for the information consumers we support. Our hands-on practice will specifically focus upon how to create a Parameter (or, more precisely, a "parameterized filter") through the use of the graphical user interface, the MDX Query Builder. As a part of our examination we will take a look at some of the processes that transpire "out of sight" within Reporting Services, primarily to form a foundation for subsequent articles, where we build upon this knowledge to create parameterization capabilities which we can finely customize to the needs of our local environment. While some of the practical concepts we cover in this article are quite basic, a good understanding of the nature and sequencing of object creation, based upon settings we make within the MDX Query Builder, is essential to the more extended procedures we undertake elsewhere. In this article, we will discuss the general concepts, and then set up a scenario within which we work with a basic OLAP report, to expose the steps involved. In examining the rudiments of parameterization within an OLAP report containing a Matrix data region, we will:

  • Open the sample Report Server project, AdventureWorks Sample Reports, and ascertain connectivity of its shared Analysis Services data source;
  • Create a clone of an existing sample report, containing a Matrix data region, with which to perform our practice exercise;
  • Make structural modifications to the clone report, to prepare for our practice exercise session with Parameters within a Matrix data region;
  • Create, within the graphical Design Mode of the MDX Query Builder, a filter for which parameterization is enabled via the Filter pane setting;
  • Preview the report to observe the runtime Parameter in action;
  • Inspect the automatically created Report Parameter and its settings;
  • Examine the automatically created Dataset underlying the new Report Parameter;
  • Discuss the interaction of the various components in supporting the runtime Parameter that the end consumer sees;
  • Discuss the results obtained with the development techniques that we exploit.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM