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 Sep 19, 2005

MSSQL Server Reporting Services: Mastering OLAP Reporting: Relationally-Based Picklists for OLAP Reporting - Page 2

By William Pearson

Relationally-Based Picklists for OLAP Reporting

Introduction and Business Scenario

As I explored in my articles String Functions: The .Name Function and String Functions: The .UniqueName Function, both members of the Database Journal MDX Essentials series, .Name and .UniqueName provide us with an ideal pair of identifiers to populate a Report Parameter dialog in Reporting Services for an OLAP report. It becomes evident, as we saw in String Functions: The .UniqueName Function, that, when used in an MDX query together, the two functions allow us to produce a list of Names and Unique Names that works wonderfully to populate report picklists. The dual column list provides the standard, easily understandable names from which the consumer selects, while supplying the MDX equivalent for the associated choice for passage by Reporting Services to the underlying Dataset query or elsewhere within the report structure. The result, as we have noted, is cube-based picklist support, thanks to the MDX .Name and .UniqueName functions.

We will begin our examination of this approach to meeting business needs within our usual context of a business scenario. We will assume that we have received a request from a group of information consumers in the Finance Department of the FoodMart organization. The request is for support in making an existing report, currently used for one purpose, fit varying needs that arise for information contained within the Sales sample cube. Initial discussions make it clear that we can convert the existing report to a multi-purpose report that can be shared beyond the current immediate circle of users.

The consumers begin to discuss the specifics, focusing first on the Product Family report parameter, which currently allows selection of one of the three families at run time, as depicted in Illustration 1.

Illustration 1: The Current Product Family Parameter Picklist

The consumers tell us that that existing Product Family parameter selection is too limited in a couple of ways: First, they would like to be able to select "all" Product Families in some cases, instead of being able only to choose between the families, each time they run the report. Second, they wish to extend parameterization to lower levels within the Product dimension hierarchy (Product Department, Product Category, and beyond). Within all new levels, they want to replicate the "all" selection capability that they have requested for the Product Family selection options.

As an additional nuance relating to the Product Family parameter, the consumers make one further request: They tell us that based upon a recurring need to see "Food" versus "Non-Food" items, they would like to add one more selection to the parameter picklist. They would like to call this selection "Food & Drink," and for this option to provide a combination of the Food and Drink families in the report when selected. (We might have suggested the word "Consumables," but the team seems set upon "Food & Drink," and, as we know, appearances are everything in the reporting environment...). The consumers add that, within the picklist, they want the "all" selection to appear as the default in the selection box at run time.

We listen carefully to the requirements. After confirming our understanding of the specified enhancements, we set about to provide the requested functionality in the sections that follow.

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