Mastering OLAP Reports: Parameterizing Number of "Top" Items with the MDX TopCount() Function, Part I

Tuesday Jul 29th 2008 by William Pearson
Share:

Writing high-performance reports against Analysis Services cubes means learning MDX to some extent. Parameterization of MDX functions is one of those things that cannot be delivered solely via graphical MDX Editor. BI Architect Bill Pearson demonstrates a way to parameterize the highly useful TopCount() function.

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.

Overview

In recent articles within this series, we have focused upon various aspects of parameterization within the Reporting Services environment. In some cases we have supported parameterization from structures completely contained within Reporting Services, and in others we have created parameter (predominantly picklist) support from within other layers of the integrated Microsoft business intelligence solution. As many of us are aware, enterprise reporting applications typically allow for parameterization (via what are sometimes known as “prompts” or “parameter prompts”) to enable information consumers to quickly find the information they need from a report. These parameters, whose values are physically passed to an axis specification or a slicer in the dataset query, often act to put filters into place “on the fly;” the “filters” are thus enacted when the consumer types or selects a value, or multiple values, at run time.

Because they allow information consumers to assume a role in guiding the delivery of information – and add a “self-serve” component to the reporting experience - parameterization in general is a popular topic in the forums and newsgroups of most enterprise reporting applications. My first exposure to the concepts of parameterization was in working with very early versions of Cognos Impromptu. Moreover, continued application of those concepts over the succeeding years within Cognos, Crystal, Business Objects, MicroStrategy, and several more specialized applications, has given me a great appreciation for the opportunities that exist in the business environment for effective parameterization. Whether the reports are to be printed, displayed on screen, or any of the other options for production / deployment, it’s easy to see the value that parameterization can add in making the selection and delivery of enterprise data more focused and consumer-friendly.

While I have extended parameterization concepts into many arenas, none have captured my attention as much as their deployment within the integrated Analysis Services / Reporting Services pairing. These applications work together to provide business intelligence in a way that is powerful and highly flexible. Indeed, I often advise clients who are attempting to locate a consultant to implement the integrated Microsoft BI solution (composed of MSSQL Server, MSSQL Server Analysis Services, and Reporting Services) to seek a “multidimensional architect” - a practitioner who has a good working knowledge of each of the components, and who can determine where, among three or more possible “logical layers,” to place which components, so as to optimize the system as a whole.

NOTE: For details surrounding hands-on approaches (as you will see, they are Legion) to the mechanics behind supporting parameterization, (including the generation of picklists) in Reporting Services, see these articles in my MSSQL Server Reporting Services series here at Database Journal:

Throughout various articles of this series, we have generated simple lists to provide virtually all we need to support parameterization within Reporting Services and other enterprise reporting applications. In this article, we will perform a more detailed examination of the mechanics behind parameterizing an MDX function – or more precisely, for parameterizing the variable argument within such a function. We will illustrate the process using the popular TopCount() function, which we introduced in Basic Set Functions: The TopCount() Function, Part I and Basic Set Functions: The TopCount() Function, Part II (both members of my MDX Essentials series at Database Journal), but the same logic can be extrapolated to many other similar MDX functions, as will become noticeable later in this article.

In this article, we will get hands-on exposure to parameterizing an MDX function, TopCount(), within a preexisting sample OLAP report. Beginning with the general concepts, we will continue into a practice session where we set up a scenario, within which we work with a basic OLAP report, to expose the steps involved. In examining the rudiments of specific function 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;
  • Perform a brief overview of the MDX TopCount() function, which we will use to support the stated reporting needs of a hypothetical client;
  • Discuss the parameterization of MDX functions in general, and the TopCount() function specifically;
  • Add the required query parameters to support date and function parameterization;
  • Ensure the adequacy of automatically created datasets to support report parameters and meet business requirements (in Part II of this article);
  • Add syntax to the TimeMonth dataset query to enforce cascading (in Part II of this article);
  • Leverage the MDX TopCount() function, containing the “number of counts” placeholder(in Part II of this article);
  • Discuss the interaction of the various components in supporting the runtime parameters that the end consumer sees (throughout Part I and Part II of this article);
  • Discuss the results obtained with the development techniques that we exploit (throughout Part I and Part II of this article).

Parameterizing the TopCount() Function

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 to market 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 grapple 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, unsurprisingly, changed the way that many of us had become accustomed to writing the underlying MDX queries for our Analysis Services reports.

Objective and Business Scenario

In this article, we will perform an extended examination of parameterization within a copy of an existing sample Reporting Services 2005 report that we will create for this purpose. Our focus will be to parameterize a specific MDX function, TopCount(), to demonstrate the process behind supporting parameterization of an index value (a numeric expression), which we can supply in this and similar functions at runtime. We will concentrate, in this article, upon the passage of our selection of a value to the function, as well as with some of the general details of setting up parameters and so forth in Reporting Services (we cover the latter in more detail in various articles I have written for that specific purpose).

Our initial objective will be to review the basics surrounding the TopCount() function, and then to perform an exercise within which we put the function to work to meet the stated business requirements of a hypothetical client.

A Quick Overview of the TopCount() Function

According to the Analysis Services Books Online, the TopCount() function returns a specified number of items from the topmost members of a specified set, optionally ordering the set first.” The TopCount() function stands out as an excellent general example of the potential power of MDX. We specify three entities, a set, a count, and a measure, in most cases, and TopCount() returns the number of top performers (or “top anything,” in effect), based upon our input.

As we shall see, TopCount() sorts the set we specify by another expression we provide within the function, thus breaking the natural hierarchy of the set. The basis of sorting by TopCount() closely resembles that used by the Order() function. We should therefore keep in mind that, in the absence of a specified sort, the function will institute a sort based upon the default member.

NOTE: For information surrounding the Order() function, see my article Basic Set Functions: The Order() Function, a member of the MDX Essentials series at Database Journal.

To restate our initial explanation of its operation, the TopCount() function sorts a set based upon a numerical expression we provide, and then picks the top (whatever number we provide in the Count parameter of the function) items in that set, based upon the rank order established by the function. The frequent requirement to examine a subset of members at the top of the general set is easily answered by the TopCount() approach of “sort, and then pick this number of members from the top down.”

Syntactically, the set upon which we seek to perform the TopCount() operation is specified within the parentheses to the right of TopCount, a common arrangement within MDX functions, as we have seen in our previous articles. The syntax is shown in the following string.

TopCount(<< Set >>, << Count >> [,<< Numeric Expression >>])

We follow <<Set>>, the set specification, with a comma, which is followed by <<Count>>, the numeric expression we provide to dictate the number of “top” members we wish for the function to return. <<Count>>» is, in turn, followed by a numeric expression whereby we specify the “sort by” criteria. As we have mentioned, TopCount() always breaks the natural hierarchy of the set – due to the fact that the set is sorted upon the third argument we provide as <<Numeric Expression>>, before returning the top (specified number of) members from that sort.

The items returned by TopCount() are limited to the << Count >> input that we provide, even in the case of “ties” in the <<Numeric Expression>> value. This may be important to consider when evaluating the data retrieved by the function, in addition to default behavior of the function when no sort criteria (in the form, again, of the <<Numeric Expression>>) is provided.

The following example expression illustrates the use of the TopCount() function, within a simple business context. Let’s say that a group of information consumers within the FoodMart (from the Analysis Services 2000 environment) Planning organization wishes to see the top ten Warehouse Cities for operating year 1997, based upon total Warehouse Profit for each city.

The basic TopCount() function specifies the “top ten Warehouse Cities” (with the number “10” as the Count specification, and [Warehouse].[City].Members as the Set specification, of the function). TopCount() assembles the top ten members from the perspective of Warehouse Profit (the Numeric Expression upon which the complete set of Warehouse Cities will first be sorted by the function). The function with arguments is represented in the ON ROWS line of the following query:

SELECT
   {[Measures].[Warehouse Profit]} ON COLUMNS,
{TOPCOUNT([Warehouse].[City].Members, 10, [Measures].[Warehouse Profit])} ON ROWS
FROM [WAREHOUSE] WHERE ([Time].[Year].[1997])

Assuming that we placed the TopCount() function within the query as constructed above, our returned dataset would resemble that shown in Table 1.

Warehouse Profit

Salem

17,041.57

Tacoma

16,725.63

Los Angeles

12,789.25

San Diego

12,536.28

Bremerton

12,063.17

Seattle

12,021.58

Spokane

5,835.46

Beverly Hills

5,674.85

Yakima

5,635.98

Portland

4,186.58


Table 1: Results Dataset, with TopCount() Defining Columns

As we would expect, the top ten Warehouse Cities are returned, having been sorted by Warehouse Profit, and having had the top ten values within that sort isolated, as we requested in the function.

As is the case with many MDX functions, TopCount() can be used to deliver sophisticated analysis datasets, particularly when we employ it along with other MDX functions. We will practice the use of TopCount() in the section that follows, building from a simple example, similar to that which we saw above, to a scenario where we apply the function, in conjunction with a calculated member, to reveal an additional analytical perspective. In our next article, we will examine even more sophisticated applications of the function, combining its use with that of other functions we have explored in articles of the series.

In the following sections, we will perform the steps required to add a parameterized “number of top items” specification capability to an OLAP report. To provide a report upon which we can practice our exercises, we will begin with the Sales Reason Comparisons sample report that, among other samples, accompanies the installation of Reporting Services 2005, and which is based upon the ubiquitous Adventure Works cube, contained within the Analysis Services database named Adventure Works DW, that comes along with an installation of Analysis Services 2005.

The Business Requirement

For purposes of our practice procedure, we will assume that information consumers within the Marketing office of the Adventure Works organization have expressed the need for modifications to the existing Sales Reason Comparisons report. The existing capabilities in the report meet some of their analysis needs, but recent requirements for a more flexible presentation of the data has resulted in our being called to assist with creating a custom report that meets an immediate, specialized need in a user-friendly manner. That is, when running the report, they wish to limit the presentation to the “top x” number of Sales Reasons, removing the others to support a specific analysis focus.

The consumers request some additional changes to the report itself, primarily that we remove the existing report parameter, as well as its underlying support mechanisms, which allows us to filter products by Product Category.

The new report will also be simpler with regard to other considerations: Instead of three measures in the column axis, we will only display two, Internet Orders and Internet Sales Amount. Moreover, as the report will apply to the Adventure Works organization as a whole, and will not break out measures by Territory Group, we will eliminate this existing grouping within the matrix data region. Finally, we will group the measures by Month, instead, so as to present both measures for a given month / year combination. We will add Year and Month report parameters, which will allow information consumers to run the report “as of” any month / year they choose at runtime.

These changes will result in a multi-purpose report, which will allow the analysts to rapidly meet differing conditions and analysis needs. As is often the case with the addition of parameterization, the organization will ultimately be able to accomplish more with fewer reports.

As part of our typical business requirements gathering process, we listen attentively to the details, formulating, in the background, an idea of the steps we need to take in modifying a copy of the report to produce the desired results. Then, having grasped the stated need, and having confirmed our understanding with the intended audience, we begin the process of modifying the Sales Reason Comparisons report to satisfy the information consumers.

Practice

Our first objective is to create a copy of the Sales Reason Comparisons sample report, within which we can implement the newly required parameter. The focus of our efforts will be the addition of this parameter into an OLAP report containing a matrix data region (the mechanics behind adding the capability, not the design of the report itself). To save time, 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 the basic parameter is the same in real world scenarios, with perhaps a more complicated 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:

Open the Sample Report Server Project

Ascertain Connectivity of the Shared Data Source

Let’s 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 sample’s 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:

Ascertain Connectivity of the Analysis Services Data Source

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:

Create a Copy of a Sample OLAP Report

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.

Preparation: Modify the OLAP Report for Use within Our Practice Session

We will next make a few modifications to prepare the report for our practice session. Our objective will be to begin the session with a simple OLAP report that contains no parameters. Let’s open the report in Layout view (for those of us not already there) and make the necessary settings to place it into a state upon which we can commence our practice steps.

1.  Right-click DBJ_OLAP_Report.rdl (or your own choice of a similar report) in the Solution Explorer.

2.  Select Open from the context menu that appears, as shown in Illustration 1, as necessary.


Illustration 1: Opening the New Report ...

DBJ_OLAP_Report.rdl opens in Layout view.

We will start with the Data tab.

3.  Click the Data tab.

We enter the Data tab, where we will remove a handful of components that we do not need for our practice session. We will accomplish this from the perspective of the MDX Query Builder, the main components of which (in Design mode) are labeled in Illustration 2 below.


Illustration 2: The MDX Query Builder – Design Mode

4.  With the Dataset selector, select the ProductList dataset, as depicted in Illustration 3.


Illustration 3: Select the ProductList Dataset ...

5.  Once the Dataset loads, click the Delete button, as shown in Illustration 4.


Illustration 4: Deleting the Unneeded Dataset ...

6.  Click Yes on the Microsoft Report Designer warning message dialog that appears next, as depicted in Illustration 5.


Illustration 5: Confirm Intention to Delete ...

The primary (and sole remaining) dataset, ProductData opens.

7.  Within the Query pane of the MDX Query Builder, right-click the column heading for Internet Total Product Cost.

8.  Click Delete Internet Total Product Cost from the context menu that appears, as shown in Illustration 6.


Illustration 6: Deleting the Unwanted Measure from the Dataset

9.  Within the Calculated Members pane (bottom left corner of the Data tab), right-click the sole Calculated Member, Profit.

10.  Click Delete on the context menu that appears, as depicted in Illustration 7.


Illustration 7: Deleting the Calculated Member

11.  Click “Yes,” when asked, “Are you sure ....”

12.  Right-click the single entry in the Filter pane (directly atop the Query pane).

13.  Click Delete to eliminate the existing Product Category Parameter from the Filter pane, as shown in Illustration 8.


Illustration 8: Deleting the Product Parameter from the Filter Pane

We will make an addition to the Dataset next.

14.  Within the Metadata pane, expand the Date dimension, by clicking the “+” sign to its immediate left.

15.  Expand the Fiscal folder that appears within the Date dimension.

16.  Expand the Fiscal hierarchy (labeled “Date.Fiscal”) within the Fiscal folder.

17.  Drag the Month level into the Data pane, dropping it to the left of the Sales Reason column, as depicted in Illustration 9.


Illustration 9: Adding Months to the Dataset

The new Month column appears, as desired. Having made the necessary changes on the Data tab, we are ready to move to the Layout tab, where we can conclude our preparatory modifications to the report file.

18.  Click the Layout tab, as shown in Illustration 10.


Illustration 10: Click the Layout Tab

19.  On the Layout tab, within the matrix data region, select the value appearing underneath the Internet Total column heading.

20.  Right-click the value in the text box (the value appears as =Sum(Fields!Internet_Total_Product_Cost.Value) ).

21.  Select Delete from the context menu that appears, as depicted in Illustration 11.


Illustration 11: Delete the Value for Internet Total Product Cost

22.  Right-click the upper left-hand corner of the Matrix Data Region (the gray square).

The gray column and row bars disappear, as a light, patterned outline forms around the matrix data region, and the context menu appears.

23.  Select Properties from the context menu, as shown in Illustration 12.


Illustration 12: Select Properties from the Context Menu ...

The Matrix Properties dialog appears, defaulted to the General tab.

24.  Click the Groups tab.

25.  In the Columns section of the Groups tab (in the lower half of the tab), select the middle group, named matrix1_Sales_Territory_Group.

26.  Click the Edit button, as depicted in Illustration 13.


Illustration 13: Editing the Matrix1_Sales_Territory_Group Column Group ...

The Grouping and Sorting Properties dialog appears, defaulted to its General tab.

27.  Atop the General tab, change the existing Name to the following:

matrix1_Month

28.  Click the row containing the Expression value (currently the expression is “Fields!Sales_Territory_Group.Value”), within the Group on list, to enable the selector.

29.  Select =Fields!Month.Value within the selector, as shown in Illustration 14.


Illustration 14: Replacing the Existing Group Expression ...

30.  Click OK, to accept our modifications, and to close the Grouping and Sorting Properties dialog.

31.  Click OK on the Groups tab, to close the Matrix Properties dialog.

32.  Once again on the Layout tab, within the Matrix Data Region, right-click the value appearing underneath the Internet Orders column heading (the leftmost of the two remaining value cells).

33.  Select Properties on the context menu that appears, as depicted in Illustration 15.


Illustration 15: Modifying Properties for the Count Value ...

34.  On the Textbox Properties dialog that next appears, click the Format tab.

35.  Replace the existing Format code setting (in the upper left corner of the Format tab) with the following string:

#,###

Here we are simply changing the existing format to one more appropriate for a count value. The Format code appears on the Format tab of the Textbox Properties dialog as shown in Illustration 16.


Illustration 16: Replacing the Existing Format Code ...

36.  Click OK to accept our modifications, and to dismiss the Textbox Properties dialog.

We next need to change the column heading associated with the Group we modified earlier, so that it reflects Months, versus the previous grouping criteria of Sales Territory Group.

37.  Right-click the text box containing the column label (immediately above the two measure values’ column labels) in the Layout tab (the text box currently contains the expression =Fields!Sales_Territory_Group.Value).

38.  Select Expression... from the context menu that appears, as depicted in Illustration 17.


Illustration 17: Modifying the Primary Column Label

The Expression Editor opens.

39.  Replace the expression in the upper portion of the Editor with the following:

=Fields!Month.Value

Recall that the intent is to label the column to reflect the nature of the Group we have put in place: Months instead of Sales Territory Group.

40.  The Expression Editor appears, with our modification, as shown in Illustration 18.


Illustration 18: The Expression Editor with Our Substitution in Place

41.  Click OK to accept our modification, and to dismiss the Expression Editor.

42.  Click the column heading for which we have just changed the expression, if necessary, simply to select the textbox.

43.  Change the Font Size, in the Format toolbar atop the design environment, from 12pt to 10pt, to make the presentation more compact, as depicted in Illustration 19.


Illustration 19: Change the Font to 10pt ...

All that remains to complete our preparatory steps is to remove the pre-existing Report Parameter. Recall that one of our earlier steps was to remove the Product entry from the Filter pane (the sample report from which our report was cloned came equipped with a parameterized Product filter). This entry had been established with the Parameter checkbox selected, a common way to create a parameterized filter, as we have seen in various other articles of the MSSQL Server Reporting Services series. Because parameterization was selected for the Product filter, it follows that a Report Parameter might well have been created at the time the entry was made. We will now remove the remaining Report Parameter.

44.  From the main menu, select Report -> Report Parameters..., as shown in Illustration 20.


Illustration 20: Select Report -> Report Parameters...

The Report Parameters dialog opens, and we see a single Report Parameter listed.

45.  Ensure that the ProductCategory Parameter is selected.

46.  Click the Remove button, as depicted in Illustration 21.


Illustration 21: Removing the Residual Report Parameter ...

47.  Click OK to accept our modifications, and to dismiss the (now empty) Report Parameters dialog.

We will execute the report to ascertain that our modifications are complete, and that we have a working report for the practice session that follows.

48.  Click the Preview tab, as shown in Illustration 22.


Illustration 22: Click the Preview Tab to Execute the Report

The Report is being generated message briefly appears, and then the report displays. The modified report appears as partially depicted in Illustration 23.


Illustration 23: The Modified Report (Partial View)

We note that the fiscal months July 2001 through July 2004 appear (representing a total of 37 months, the earliest of which are relatively sparse with regard to data).

Our report is now ready for the practice exercise, which we will begin in the next section.

Procedure: Adding and Parameterizing the Date Dimension Elements and the TopCount() Function in Reporting Services 2005

As we noted earlier, our intent within this article is to examine the detailed mechanics involved in adding – and parameterizing – a single MDX function, TopCount() within our report. Moreover, we will parameterize the Date dimension itself (both Fiscal Year and Month) so as to allow our information consumers to declare an “as of” date within the report. We will accomplish the initial steps from the Data tab, to which we will return in the following steps.

1.  Click the Data tab, once again

We return to the Data tab, where this time we will ultimately replace some of the syntax that currently exists within the MDX query - syntax that has, up until now, been assembled graphically via the MDX query designer in Design mode.

As most of us know, when we create a dataset from an Analysis Services data source, Report Designer initially offers us the MDX query designer in Design mode. We can work within Design mode to interactively build an MDX query using graphical elements. Design mode affords us the capability to select a cube and to drag numerous objects to the Data pane, including:

  • Dimensions
  • Dimension Attributes
  • Levels
  • Hierarchies
  • Measures
  • Key Performance Indicators (KPIs).

We can also add calculated members, set default values for variables (we will expose a specific instance of this in our practice exercise, as part of the focus of our article), and automatically see result sets returned for the queries we build (particularly useful as we make changes within the Query pane) to the Data pane. We can set filters to limit the data retrieved from the data source by the query, and define parameters.

As many of us have no doubt already learned, the need for dealing with “direct MDX” has not disappeared. This can be particularly true when the need arises to tailor an MDX query to meet more sophisticated business requirements within Reporting Services. One example will be illustrated within the focus of our article – the use of any but the most basic (those that are implicit within the arrangement of objects within the Data pane of the MDX functions within the MDX query designer in Design mode) MDX functions. (We illustrate other examples where we must use the MDX query designer in Query mode in numerous other articles of my MSSQL Server Reporting Services series.)

To view or edit MDX query syntax directly, we must switch to Query mode by clicking the Design Mode toggle button on the toolbar. The Design Mode toggle button appears within the Report Designer as shown in Illustration 24.


Illustration 24: Design Mode Toggle Button in Report Designer

2.  Click the Design Mode toggle button on the toolbar.

The syntax for the underlying query appears, as depicted in Illustration 25.


Illustration 25: MDX Syntax Appears within the Query Pane of the Query Mode

What we need to do, to meet the requirements of our client colleagues, includes the following (in the order specified):

  • Add a query parameter for each of Year, Month, and Span of TopCount;
  • Ensure that the datasets automatically created for each of the newly added query parameters are appropriate, adjusting as necessary to support report parameters in meeting the business requirement;
  • Add the syntax to the query to leverage the MDX TopCount() function, which will work in conjunction with the other two query parameters, to achieve the retrieval / presentation dictated by the selections of information consumers at runtime.

We will accomplish these steps within the following subsections.

Add the Required Query Parameters to Support Date and Function Parameterization

1.  Click the Query Parameters button on the toolbar, as shown in Illustration 26.


Illustration 26: Click the Query Parameters Button atop the Query Pane ...

The Query Parameters dialog appears as depicted in Illustration 27.


Illustration 27: The Query Parameters Dialog Appears ...

Here we add the three “variables” we will be associating, via the query syntax, with the report.

NOTE: Simply typing the placeholders into the query syntax (preceded by an “@” sign in Reporting Services 2005) first does not automatically create the query parameters or the associated report parameters – contrary to some of the documentation (and myriad other sources) I have seen on the Web. Simply typing in the placeholder(s) first in the query, and following this with an attempt to run the query via the Execute Query button, results in a message informing us of “query preparation failure.” The message relates that “the query contains the [term following the “@” sign in our placeholder] parameter, which is not declared.” An example of this message (for a parameter placeholder added to the query syntax within an earlier article), for which no entry had been made in the Query Parameters dialog, appears as shown in Illustration 28.


Illustration 28: Message Noting Query Preparation Failure, Due to an Undeclared Sample Parameter ...

2.  In the single row of the Query Parameters dialog, in the table underneath the caption “Specify details of the parameters defined in the query,” click the leftmost cell (containing the placeholder “<Enter Parameter>”) to select it, as shown in Illustration 29.


Illustration 29: Select the Cell Marked “<Enter Parameter>” ...

3.  Type the following into the selected cell:

TimeYear

4.  Within the cell to the immediate right of the Parameter cell just populated, labeled Dimension, select Date, as depicted in Illustration 30.


Illustration 30: Select the Date Dimension ...

5.  Within the cell to the immediate right of the Dimension cell just populated, labeled Hierarchy, select Date.Fiscal, as shown in Illustration 31.


Illustration 31: Select the Date.Fiscal Hierarchy ...

6.  Leave the Multiple values checkbox, appearing in the cell to the immediate right of the Hierarchy cell just populated, unchecked.

7.  Using the selector in the Default cell (the rightmost cell in the row within which we have been working), expand the All Periods entry by clicking the “+” sign to its immediate left.

8.  Select the FY 2005 member, as depicted in Illustration 32.


Illustration 32: Select FY 2005 as the Default ...

NOTE: Assigning a Default is a requirement to allow us to test the dataset query anytime we add query parameter placeholders to the query syntax. Doing so also initially populates the default of a subsequently created report parameter, as we shall see.

9.  Click OK to accept our selection, and to dismiss the Default selector.

The row within which we have been working in the Query Parameters dialog appears as shown in Illustration 33.


Illustration 33: The Query Parameters Dialog with Our Input ...

Next, let’s add a query parameter for Month.

10.  Once again within the Query Parameters dialog, in the newly appearing second row of the table underneath the caption “Specify details of the parameters defined in the query,” click the leftmost cell (containing the placeholder “<Enter Parameter>”) to select it, as we did in the first row of the table for the TimeYear parameter.

11.  Type the following into the selected cell:

TimeMonth

12.  Within the Dimension cell, again to the immediate right of the Parameter cell, select Date, as we did in the first row.

13.  Within the Hierarchy cell, once more to the immediate right of the Dimension cell, select Date.Fiscal, as we did in the row above.

14.  Leave the Multiple values checkbox, appearing in the cell to the immediate right of the Hierarchy cell just populated, unchecked, once again.

15.  Using the selector in the Default cell (the rightmost cell in the row within which we have been working), expand the All Periods entry by clicking the “+” sign to its immediate left.

16.  Expand the FY 2005 member.

17.  Expand the H1 FY 2005 member that appears underneath the expanded FY 2005 member.

18.  Expand the Q1 FY 2005 member that appears underneath the expanded H1 FY 2005 member.

19.  Select the July 2004 member, as depicted in Illustration 34.


Illustration 34: Select July 2004 as the Default ...

20.  Click OK to accept our selection, and to dismiss the Default selector.

Next, we will add a query parameter for the span of desired top count (for purposes of our illustration, we will select three items – keeping in mind that the default here, in effect, serves only to provide a value to substitute for the associated query parameter value at run time (as well as for the associated report parameter default). This parameter will have some different settings than the more “typical” settings above – including blanks in a couple of places (a fact that has not, at the time of this writing, been well documented ...).

21.  Once again within the Query Parameters dialog, in the newly appearing third row of the table underneath the caption “Specify details of the parameters defined in the query,” click the leftmost cell (containing the placeholder “<Enter Parameter>”) to select it, as we did in the rows of the table for the two parameters we have already added above.

22.  Type the following into the selected cell:

TopCountSpan

23.  Leave the Dimension cell (again to the immediate right of the Parameter cell) blank.

24.  Leave the Hierarchy cell, (again, to the immediate right of the Dimension cell) blank.

25.  Leave the Multiple values checkbox, appearing in the cell to the immediate right of the Hierarchy cell just populated, unchecked, as we have done in the TimeYear and TimeMonth rows above.

26.  Click within the Default cell (the rightmost cell in the row, once again), to place the cursor there.

27.  Type the following into the selected cell:

3

as shown in Illustration 35.


Illustration 35: Input “3” as the Default Span for Look Back ...

28.  Click the Parameter cell on the same row (where we typed “LookBackSpan” earlier), once again, to “set” the “3” within the Default cell.

The third row, within which we have been working in the Query Parameters dialog, appears as depicted in Illustration 36.


Illustration 36: The Query Parameters Dialog with Our Input ...

29.  Click OK to accept our selection, and to dismiss the Query Parameters dialog.

We are returned to the Data tab, where we will make some modifications to the query syntax in the section that follows. Before doing so, let’s quickly confirm some events that have transpired behind the scenes, as a part of our creating the three query parameters in the preceding steps of this section.

30.  Select Report -> Report Parameters ..., from the main menu, as shown in Illustration 37.


Illustration 37: Select Report --> Report Parameters ...

We note the appearance of the three new query parameters within the Parameters pane of the Report Parameters dialog, which appears next, as depicted in Illustration 38.


Illustration 38: The Report Parameters Dialog with New Entries ...

31.  Click OK to dismiss the Report Parameters dialog for now.

32.  Click the downward pointing arrow on the Dataset selector (currently displaying the ProductData selection) within the Data tab.

We note that only the ProductData selection appears within the selector, as shown in Illustration 39.


Illustration 39: The Single Existing Dataset within the Selector ...

33.  Click the Layout tab in the Report Designer.

34.  Click the Data tab immediately to return to our former position.

35.  Click the downward pointing arrow on the Dataset selector, once again.

We note that the TimeYear and TimeMonth datasets (automatically created when we moved to the Layout tab) join the ProductData selection within the selector, as depicted in Illustration 40.


Illustration 40: The Newly Created Datasets Also Appear within the Selector ...

The datasets we see have been automatically created to support parameters that we have defined with simple datasets that we may or may not choose to use “out of the box.” (See the various articles I have published surrounding parameters in Reporting Services 2005, within my MSSQL Server Reporting Services series at Database Journal.)

NOTE: it is important to remember that, although the report parameter and its underlying Dataset are created automatically when we create the underlying query parameters within the Query Parameters dialog of the MDX query designer (Query mode), removal of a corresponding, supporting row from the Query Parameters dialog will not produce an opposite effect. The Report Parameter and the Dataset will remain until they are manually removed. Moreover, the disablement of parameterization in this manner from within the Query Parameters dialog, followed by re-creation of the affected row will, unless we intervene before we recreate the Parameter, result in the creation of two Report Parameters.

One might wonder why the third query parameter we created, TopCountSpan, did not trigger the creation of a third dataset. Because the simple parameter is based upon a mere number, in this case, and not upon a dimensional structure, no basis exists within the “auto create” capability upon which to build the dataset. We will, of course, be able to manage parameter picklist support via another avenue, as we shall see in a later section in Part II. Our first step in Part II, will be to overview the two datasets that were created, and ensure their adequacy to support the associated date-related report parameters in meeting the business requirements of our client colleagues. We will then conclude by working with underlying dataset queries to enforce cascading, and to support the parameterized “number of top items” selection capability that is the primary focus of this article.

NOTE: Please consider saving the .rdl file we have created to this point for use in the article that follows, so as to avoid the need to repeat the preparation process we have undertaken above.

36.  Experiment further with the report, if desired.

37.  When finished with the report, click the Layout tab.

38.  Select File -> Save DBJ_OLAP_Report.rdl As ... to save our work, up to this point, to a location where it can be easily accessed for the next article within our series.

39.  Select File -> Exit to leave the design environment, when ready.

Conclusion ...

In this article, we began another extended examination of Parameters in Reporting Services 2005. This time, we set out to obtain some hands-on exposure to parameterizing an MDX function, TopCount(), within a preexisting sample OLAP report. Beginning with the general concepts, we continued into a practice session where we set up a scenario within which we would work with a basic OLAP report, to expose the steps involved. In examining the rudiments of specific function parameterization within an OLAP report containing a matrix data region, we performed a brief overview of the MDX TopCount() function, which we stated that we would use to support a stated reporting need of a hypothetical client. We discussed the parameterization of MDX functions in general, and the TopCount() function specifically.

We next prepared a copy of a sample report sourced from Analysis Services, containing a Matrix data region, with which to perform our practice exercise. We then added the required query parameters to support date and function parameterization, which we stated we would complete in Part II of this article. Throughout our various preparation and other steps, we discussed the interaction of the various components in supporting the runtime parameters that the end consumer sees, as well as touching upon the results obtained within the development techniques that we exploited.

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

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

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved