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 11

By William Pearson

"Parameterizing" the Core Dataset

We began the design of our report with a simple, unparameterized core Dataset. We can now logically approach the parameterization of the core Dataset, with everything in place that will be required to make it work. While, as we have mentioned in previous articles, the handling of parameterization for an MDX query takes some getting used to, the important thing to remember is that the objective is simply to supply a precise MDX string, to pass to MSAS from Reporting Services.

Reporting from an OLAP cube, with anything but the simplest, non-parameterized MDX, requires a bit more effort than simply creating an MDX query to create a Dataset, as we did with the original in our example. The clumsiness comes with the introduction of the string by which we pass the MDX / parameter combination. In essence, we implant the parameter, and then turn the existing MDX into a string. While it works fine in passing the code to MSAS, converting the query to a string disables Dataset refreshment on the Data tab - hence the unwieldiness that some developers find challenging.

Let's revisit our initial query, through which we will make the soon-to-be-handicapped Report Designer aware of all the fields we expect to require within the Dataset. Precision is important in the next steps, particularly with regard to the absence of carriage returns (pressing the Enter key to "push down" a line), and other stray actions.

1.  On the Data tab, select the ProductData Dataset in the Dataset selector, as depicted in Illustration 48.

Illustration 48: Select the ProductData Dataset Again ...

2.  Replace the original ProductData Dataset query with the following (cut and paste, if helpful):


   { [Measures].[Store Sales], [Measures].[Store Cost]  } ON AXIS(0), 

   {TopCount([Product].[Product Name].Members, 10, ([Measures].[Store Sales]))} ON    

   {[Time].[1997].[Q1]} ON AXIS(2) 



3.  Click the Run button in the toolbar to execute the query,

4.  Click the Refresh Fields button to refresh the data fields with the data retrieved within the new Dataset, as shown in Illustration 49.

Illustration 49: Execute the Query, and then Refresh Data Fields

The new Dataset appears in the Results pane, below the query, as depicted in Illustration 50.

Illustration 50: Results Dataset Appears (Compressed View)

Running the query with "placeholder", hardcoded TopCount() and Time specifications, as above, allows us to populate the data set with a representative of each of the data fields, as well as giving us an opportunity to see the function work within a simple, non-parameterized query. Having refreshed the data fields to match the new query results, we will now insert parameterization. Once this is done, the query must be combined into a single string, as we shall see.

5.  Modify exactly the existing Dataset query by substituting parameter references, shown in the "With" column of Table 4, for the portion of the query designated under "Replace"



TopCount (in 3rd Row/ Axis(1))

" +Parameters!pX_OutlierType.Value +"

10 (in 3rd Row / Axis(1))

" +Parameters!pX_TopBotNum.Value +"

[Time].[1997].[Q1] (in 4th Row / Axis(2))

" +Parameters!pX_Date_Hier.Value +"

Table 4: Expression Components Summary

The Query pane, containing our modified input, appears as shown in Illustration 51.

Illustration 51: MDX Query in the Query Pane

6.  Combine the query into a single string, enclosing it in quotes, with an equals (=) sign in front of the query, precisely as shown in the following string (cut and paste, if helpful):

="SELECT { [Measures].[Store Sales], [Measures].[Store Cost]  } ON AXIS(0), {"
+Parameters!pX_OutlierType.Value +"([Product].[Product Name].Members, " 
+", ([Measures].[Store Sales], " +Parameters!pX_Date_Hier.Value +"))} ON AXIS(1), {"
+Parameters!pX_Date_Hier.Value +"} ON AXIS(2) FROM [SALES]"

NOTE: The query must exist as a single line, without "artificially" creating line breaks via the Enter key, etc. It often helps to do so within Notepad or another editor. Although line breaks may appear to exist in the query above, this is simply the way the text is presented in this document; the query is a single string.

The Run icon is disabled once the query is enclosed in double quotes. The importance of constructing the query correctly becomes obvious, in that there is no way to execute it within the Data tab at this stage.

An explanation of the components of the above expression appears in Table 5.




The = sign alerts Reporting Services that an expression is to follow. The expression must be enclosed in double quotes.

{[Measures].[Store Sales], 
[Measures].[Store Cost]  } ON AXIS(0)

The measures we wish to include in the report, specifying the Column Axis of the query (Axis(0))

{" +Parameters!pX_OutlierType.Value 
+"([Product].[Product Name].Members, " 
+Parameters!pX_TopBotNum.Value +",
([Measures].[Store Sales], " 
+Parameters!pX_Date_Hier.Value +"))} ON 

The Row Axis of the query (Axis(1)), whereby we are specifying - just as we did in our first, simpler MDX expression, that we wish Product Names to appear in the rows of the returned Dataset.

The following syntax comprises parameterization of the TopCount() / BottomCount() functions:

+Parameters!pX_OutlierType.Value + 


+Parameters!pX_TopBotNum.Value +  

The first string above enables selection of "Top" or "Bottom", passing the associated keyword to be concatenated with the rest of the syntax required to build the function for the members of the Product Name level of the Product dimension. The second string above passes in the << Count >> portion of the function, and the resulting construction is concatenated to the Numeric Value, Store Sales, upon which the "top" or "bottom" selection is based. The result is a complete TopCount() / BottomCount() function.

NOTE: For more on the functions themselves, see the section entitled A Quick Overview of the TopCount and BottomCount Functions above.

{" +Parameters!pX_Date_Hier.Value +"} ON

The AXIS(2) specification is populated by the unique name from the cube's Time dimension, enclosing the reference to the value of the parameter within the brackets ({}) - in the present case, the value of the date parameter selected from the hierarchical picklist, stored in the "qualified" format of the respective Time dimension specification.


The FROM clause, a staple of any MDX query, merely specifies the cube (Sales) we are designating as the source of our data.

The final set of double quotes (") is vital, as it signals "end of the expression" to MSAS.

Table 5: Parameterized Expression Components Summary

Having completed the parameterization of the report, we have a final pair of modifications to make to the report's properties to render the report fully functional from the perspective of the new business requirements with which we began our practice exercise. We will perform this alteration in the following section.

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