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 May 16, 2005

MSSQL Server Reporting Services : Mastering OLAP Reporting: Ad Hoc TopCount and BottomCount Parameters - Page 13

By William Pearson

Execute the Report and Verify Results

We are now ready to review the results of our handiwork within the Preview tab of the Report Designer.

1.  Click the Preview tab, atop the design environment.

This begins the chain of events surrounding parameterization. The parameter selector box, labeled Period, appears in the upper left corner of the Preview tab, indicating initialization of the parameter and the execution of the supporting picklist Dataset.

2.  Scroll down the Period selector, under 1997, and click / select Q3, as depicted in Illustration 56.

Illustration 56: Select 1997 - Q3 Level within the Time Hierarchy

Note the indentation of the various members of the hierarchy, and how it makes selection at various levels easy.

3.  Select Top in the Top or Bottom? selector.

4.  Type the number six (6) into the Number type-in picklist.

5.  Click View Report to activate the primary Dataset query.

The parameters we have chosen (in their respective "qualified" formats) are, in turn, passed to MSAS. The results display quickly, and appear similar to those shown in Illustration 57.

Illustration 57: Report Preview, Based upon Our Parameter Selection

6.  Explore using the parameters further, selecting various combinations of picklist selections to return the corresponding results, as desired.

Conclusion ...

In this article, we continued our exploration of OLAP reporting with Reporting Services, focusing generally upon multiple approaches to parameterization, and specifically upon the parameterization of a pair of MDX functions, TopCount() and BottomCount(). After discussing our objectives and providing an overview of the TopCount() and BottomCount() functions, we presented a set of business requirements of a hypothetical group of information consumers, upon which we based the practice examples that followed. After undertaking several preparatory steps, we set out to create two types of parameters (type-in and selector picklists), with both cube- and report-based support, discussing the differences along the way.

Using a copy of a sample OLAP report that accompanies an installation of Reporting Services, and a copy of a sample cube that accompanies an installation of Analysis Services, we set about meeting the requirements of the information consumers. We modified the clone report to more closely meet the new presentation specifications, and then began parameterization, some of which we supported by structures we added to the clone cube upon which the report was ultimately based. We explained the MDX we used at both cube and report layers to support our new parameters, as a part of the steps we took to put them in place. Finally, after connecting all the pieces within Reporting Services, we verified the effectiveness of the solution by previewing the report in Report Designer, supplying ad hoc parameters, at runtime.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Reporting Services Forum.

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