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

BI Architect Bill Pearson continues his hands-on demonstration of an approach to the parameterization of 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

As I noted in Mastering OLAP Reports: Parameterizing Number of “Top” Items with the MDX TopCount() Function, Part I, we have focused, in several recent articles of this series, 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 a series of values, at run time.

We emphasized, in Part I, that, 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 continued application of the underlying concepts over many years within Cognos, Crystal, Business Objects, MicroStrategy, and a host of other, 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 is challenging to overstate 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. As I have noted in Part I, 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 consultant 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.

Throughout these articles, as well as elsewhere, 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 continue to pursue the objective we stated in Mastering OLAP Reports: Parameterizing Number of “Top” Items with the MDX TopCount() Function, Part I: 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, Parts I and II (a member of my MDX Essentials series at Database Journal), but the same logic can be extrapolated to many other similar MDX functions, as I have noted, and will continue to note, in my articles.

In this article, we will continue to get hands-on exposure to parameterizing TopCount() within the sample OLAP report that we cloned in Part I. After introducing the general concepts, in Part I, we began our practice session by setting up a scenario within which to work with a basic OLAP report in exposing the steps involved in parameterizing the TopCount() function specifically. We began by opening the sample Report Server project, AdventureWorks Sample Reports, and ascertaining connectivity of its shared Analysis Services data source. Next, we created a clone of an existing sample report, containing a matrix data region, with which to perform our practice exercise.

Once we had made structural modifications to the clone report, to further prepare for our practice exercise session, we performed a brief overview of the MDX TopCount() function, discussing details of the use we intended for the function to perform in support of the stated reporting needs of a hypothetical client, as well as touching upon general concepts surrounding the parameterization of MDX functions in general, and the TopCount() function specifically.

In this, the second part of our article, we will:

• Ensure the adequacy of the datasets, created automatically when we added query parameters as a last step in Part I, to support report parameters and meet business requirements;
• Add syntax to the Month dataset query to enforce cascading, based upon the selection made for the Year parameter by an information consumer at runtime;
• Leverage the MDX TopCount() function, containing count and Month parameter placeholders;
• 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.

Parameterizing the TopCount() Function (continued ...)

Throughout many past articles of the MSSQL Server Reporting Services series, we have leveraged parameters within the context of MDX queries. To reiterate my introduction in Mastering OLAP Reports: Parameterizing Number of “Top” Items with the MDX TopCount() Function, Part I, 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. One of the challenges that faced many of us was the absence of an MDX Editor. Those of us who were comfortable with MDX syntax were not impaired significantly (although we had to deal 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 however, who were already challenged with MDX as a language almost certainly found no amusement in dealing with the added mechanics.

As we discussed in Part I, Reporting Services 2005 introduced the MDX Query Builder, a tool that satisfies, due to its “on-off” flexibility, 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 already become accustomed to writing the underlying MDX queries for our Analysis Services reports.

Objective and Business Scenario

In Part I, we stated our objective to perform an extended examination of parameterization within a copy of an existing sample Reporting Services 2005 report that we created for this purpose. Our focus, we stated, was and is to parameterize a specific MDX function, TopCount(), to demonstrate the process behind supporting parameterization of an index value we can supply in this and similar functions at runtime. We stated that we would concentrate, in this two-part article, upon the passage of our selection of a value to the function, more than the details of setting up parameters and so forth in Reporting Services (we cover the latter in detail in various articles I have written for that specific purpose), although we do touch upon most of the typical aspects of setting up parameters.

In Part I, we reviewed the basics surrounding the TopCount() function, and then we began to prepare to perform an exercise within which we would put the function to work to meet the stated business requirements of a hypothetical client.

As we noted in Basic Set Functions: The TopCount() Function, Parts I and II, both members of my Database Journal MDX Essentials series, the TopCount() function allows us to meet numerous common business needs, including (and especially) those that require that we return the “top n” members (such as cities or stores), usually for a given time frame (which might actually be “all time”), based upon a valid measure (such as total profits, total margin, etc.).

An example might be the top ten Warehouse Cities for operating year 1997, based upon total Warehouse Profit for each city.

I emphasized in Part I of this article that I have found the TopCount() function to be highly useful in both statistical and financial reporting. The capability to specify “the top [number of values we request],” for example, in patient readings, can become highly popular among, say, doctors who are tracking the effectiveness of new treatments and the like, allowing them to further explore (via drilldown, drillthrough, etc.) why certain clinics, for example, are experiencing better patient results than others, once these “top” clinics are identified. For more information, together with an illustration that further clarifies the operation of TopCount(), please see Part I.

As we noted in Part I of this article, and as we shall see in our continuing practice session, parameterization of the count variable within the TopCount() function lies at the heart of the procedure we shall undertake within our procedural example below. We will detail the steps within a sample report, which we have cloned to save time, focusing largely upon setup of the parameter mechanism within the primary report dataset, together with its linkage to the corresponding Report Parameter and elsewhere.

Parameterizing TopCount()

I often parameterize the TopCount() function within a Reporting Services application I am developing as part of a larger implementation of the integrated Microsoft Business Intelligence solution for a given client. While this is only a tiny part of the overall structure typically assembled for a combined OLAP and relational reporting system, it will provide an interesting glimpse of the much larger population of opportunities that I find daily in working with these powerful analysis and reporting tools.

In the following sections, we will perform the steps required to add parameterized “number of top items” within a given scenario to an OLAP report. To provide a report upon which to practice our exercises, we cloned and modified, in Part I, 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.

To restate the scenario we posed in Part I, we will assume that information consumers within the Marketing office of the Adventure Works organization have expressed the need for modifications of the existing Sales Reason Comparisons report. The existing capabilities in the report meet most 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.

In discussing their requirements in detail, representatives of the Marketing department have stated that ongoing analysis operations would be dramatically enhanced if they could simply limit, at report run time, the data returned and presented in the report to the “top x” number of Sales Reasons, removing the others to support a specific analysis focus.

Our colleagues have informed us, again as we summarized in Part I, that the original report needed some other basic changes (primarily the removal of the existing report parameter, as well as its underlying support mechanisms, which originally allowed us to filter products by Product Category, among other minor changes), which we accomplished within the preparation section of Part I. We also streamlined and regrouped the report with regard to other considerations: Instead of three measures in the column axis, we modified the report to display only two, Internet Orders and Internet Sales Amount. Moreover, as the client representatives told us that the report would apply to the Adventure Works organization as a whole, and would not need to break out measures by Territory Group, we eliminated this existing grouping within the matrix data region. Finally, we grouped the measures by Month, instead, so as to present both measures for a given month / year combination. We then added Year and Month report parameters, which would allow information consumers to run the report “as of” any month / year they choose at runtime.

The modifications we made in Part I, and that we will continue to make in this half of the article, will result in a multi-purpose report, the month / year presented by which can be dictated on the fly by the easy, ad hoc selection from associated picklists. Moreover, and more along the lines of our specific focus , the analysts will be able to limit the presentation to a “top” number (determined at runtime) of Sales Reasons, removing the others to allow these information consumers 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.

For more information on the TopCount() function from the perspective of our reporting requirements, as well as for the original specification of the needs as expressed by our client colleagues, see Mastering OLAP Reports: Parameterizing Number of “Top” Items with the MDX TopCount() Function, Part I.

Practice – continued ...

Recall that we left off working, in Part I, with the copy we had created of the Sales Reason Comparisons sample report, after we had accomplished the preparatory changes noted above. We had progressed the report through the earliest procedures for adding – and parameterizing – a single MDX function, TopCount(), in combination with taking the initial steps to parameterize the Date dimension itself (both Year and Month), so as to allow our information consumers to declare an “as of” period within the report. We concluded Part I with the addition of the required query parameters to support both date and function parameterization.

We stated that we would resume our practice session by reviewing the two datasets that were created, primarily to ensure their adequacy to support the associated date-related report parameters in meeting the business requirements of our client colleagues. We will resume our practice session in the sections that follow.

Ensure Adequacy of Automatically Created Datasets to Support Report Parameters and Meet Business Requirements

Let’s take a brief look at the two datasets whose automatic creation by Reporting Services was triggered by our addition of the TimeYear and TimeMonth query parameters earlier. Our focus here will be to understand their construction and to ascertain their fitness to support the Year and Month report parameters.

When we looked at the Report Parameters that were created within our last section, we may have noted that each referenced its respectively named, underlying dataset. Let’s return to the Report Parameters dialog to inspect the resulting relationship further.

We recall that we had previously removed all Report Parameters, as part of our preparation for the practice exercise. Yet we note, within the Report Parameters dialog that has appeared (as we did in Mastering OLAP Reports: Parameterizing Number of “Top” Items with the MDX TopCount() Function, Part I), the presence of three new Report Parameters, called TimeYear, TimeMonth, and TopCountSpan. These Report Parameters, as we have learned, were created automatically when we created the associated rows within the Query Parameter dialog on the Data tab.

Another important observation we might make for the TimeYear (and all observations we make here for TimeYear apply to the TimeMonth parameter, within the context of its own supporting dataset, as well) parameter is that the From query setting within the Available values section of the Report Parameters dialog is active (the radio button is selected), and that the setting references a Dataset (and the associated default Value and Label fields) with the same name as the new Report Parameter - a Dataset that has also been automatically created. The purpose of this Dataset is to support the selection picklist for the new Report Parameter.

Finally, we note that the MDX qualified name for Fiscal Year 2005 ([Date].[Fiscal].[Fiscal Year].&[2005] ), is referenced as the Default Value for the Report Parameter. (Even though we use the MDX qualified name within the Default Value here, as at any other time in the Report Parameter dialog when we set a default, the “label” name - a “consumer-friendly” name, generally - is what actually appears within the parameter selector at runtime.)

We will now return to the Data tab, to examine one of the datasets which have been automatically created (to populate the parameter picklist at report run time) – the dataset (and dataset query, of course) specifically created to support the new TimeYear report parameter. (The columns of the TimeYear dataset are identical to those of the TimeMonth dataset, and so we will examine only the former at present.)

The dataset query appears, presenting the MDX syntax in Query view. This is the query half of the query / dataset pair that have been automatically created to generate the parameter picklist (as well as the MDX qualified name that is passed to Reporting Services, based upon the selection made at runtime, among other items) to support the new TimeYear report parameter.

The query executes, and the data grid becomes populated, as partially shown in Illustration 3.

An examination of the query and the data it returns reveals that, in addition to retrieving data directly from the cube, the automatically generated query creates additional derived columns whose sole purposes are the support of the report parameter, from whose picklist information consumers make the selections at runtime, and from which MDX-qualified values are passed to Reporting Services to generate the associated report. The derived columns are:

• ParameterCaption
• ParameterValue
• ParameterLevel

All three fields are products of calculated members created via MDX syntax (using the WITH MEMBER keyword combination), within the dataset query. As we discover within other articles of the series, there are multiple ways to approach picklist support (For example, I very often construct members such as the above at the Analysis Services level, to afford central maintenance and reusability, among other, perhaps less obvious, advantages). This is simply the mechanism through which Reporting Services accomplishes support of the automatically generated report parameter. As we saw earlier, the Value and Label settings within the report parameter dialog reference the ParameterValue and ParameterCaption fields, respectively, within this Dataset.

As I note repeatedly throughout the extended examination of parameters that I perform within this and numerous other articles throughout the MSSQL Server Reporting Services series, it is important to realize how Reporting Services automatically creates both the report parameter and the dataset that supports the parameter at runtime. As we venture into custom parameterization within various practice sessions elsewhere, I urge my readers to keep in mind how the parts knit together within this basic context, so as to understand the relationships that we need to establish manually.

Having discussed the components of the two automatically created datasets, TimeYear and TimeMonth, we are ready to make a single modification to each. If we examine the TimeYear dataset at this point, we can see that both the ParameterCaption and ParameterValue derived columns include a great deal more than year-level members: for example, we see not only a caption for FY 2002 itself, but captions for FY 2002 semesters (such as H1 FY 2002), quarters (such as Q1 FY 2002), months (such as July 2001), and individual dates (such as July 1, 2001). These occurrences are to be expected, based upon the MDX syntax used within the system-generated dataset query, but the varying granularity of the captions and values poses a problem when we consider that these columns exist to support single-level parameter picklists within the report at runtime. This means, for example, our TimeYear dataset needs to provide only the Year (or, more specifically in our case, Fiscal Year) level members to the ultimate picklist.

We can filter out all except the Year level members by using the ParameterLevel column for this purpose. As we can see, the Year level members of the dataset comprise ParameterLevel 1. All we have to do is filter by this level, as we shall do in the following steps:

The Dataset dialog appears, defaulted to the Query tab, as shown in Illustration 5.

=1

The Dataset dialog, along with our settings, appears as shown in Illustration 7.

Next, we will add a similar filter to the TimeMonth dataset. The steps are almost identical as those we took for the TimeYear dataset; the only material difference is that the ParameterLevel for the Month level within the Date dimension is identified by the number “4”.

The Dataset dialog appears, once again.

=4

The Dataset dialog, along with our settings, appears this time as depicted in Illustration 8.

Let's take a quick look at the runtime picklists for the date-related parameters, to ascertain the effectiveness of our dataset filters, as well as to verify their satisfactory operation.

The report runs, and then displays the drop-down parameters as specified in the preceding steps, including the defaults requested.

The parameter picklists appears, displaying months as partially shown in Illustration 9.

As we can see, our filter has been effective in populating the parameter picklist with months only. (If we click the downward pointing arrow to the right of the Year parameter selector, we can see that there, too, the correct parameter level only is displayed.) But while our dataset filters appear to be effective, we note in the Month parameter picklist that the months displayed are not restricted to FY 2005. This indicates that the Month parameter picklist is not cascading appropriately.

As is probably obvious, we must perform some additional steps before the new Month parameter picklist cascades correctly. Moreover, even though the parameter picklists in general appear to function at the Preview tab level, we will need to perform the steps to make them actually work (to make the month / date selections and the “top count” capability actually restrict the presentation to the number of “top” values specified, etc.). These steps lie within the realm of the underlying dataset query, within which we will be working in the next section.

Before we do that, let’s return to the report parameters dialog to conclude this section with a few cosmetic changes.

Year:

The Report Parameters dialog, including our setting changes, appears as depicted in Illustration 11.

Month:

The Report Parameters dialog, including our setting changes, appears as shown in Illustration 12.

Finally, let’s make some settings for the new LookBackSpan parameter. As we noted in Mastering OLAP Reports: Parameterizing Number of “Top” Items with the MDX TopCount() Function, Part I, the TopCountSpan parameter will differ from the other two automatically created parameters, particularly in the fact that it will not reference a corresponding, automatically created dataset – a situation we both described and explained in Part I. Because the parameter picklist will simply offer a range of numerals as selection options, we will reference those numerals through direct specification within the Report Parameters dialog (although other approaches exist to support such a parameter, such as using a manually created dataset, etc.)

No. Top Items:

The Report Parameters dialog, including our setting changes, appears as partially depicted in Illustration 13.

As we mentioned earlier, we must perform some additional steps before the new parameters actually work. These steps will need to be accomplished within the syntax of a couple of underlying dataset queries, within which we will be working in the next section.

Work with Underlying Dataset Queries to Enforce Cascading and to Support the Parameterized “Top Count” Capability

Add Syntax to the TimeMonth Dataset Query to Enforce Cascading

We will return to the Data tab, at this point, to make modifications to the primary dataset query to both properly enable the date-related parameters for Year and Month, as well as to add and enable the MDX function that will support the parameterized “top count” capability within the report, as our client colleagues have requested.

Before we work specifically with the primary dataset query, however, we will modify our TimeMonth dataset query to support the cascading parameter picklist for the Month parameter, the current absence of which we discussed earlier. As we mentioned before, we seek to enforce cascading so that the appropriate child members (Months) appear within the Month parameter picklist, based upon the selection made in the Year parameter parent level to which they belong.

At this stage we face a couple of modifications to the TimeMonth dataset. First we must add a Query Parameter within the dataset for TimeYear, and then we must embed the parameter, via a placeholder, into the syntax. We shall make these modifications within the steps that follow. We will then need to make a relatively minor final adjustment to the TimeYear report parameter, as a reaction to a reset that it performs based upon our addition of the Query Parameter within the TimeMonth dataset, as we shall see.

The dataset query appears, presenting the MDX syntax in Query view, as we saw in earlier steps. Because we are establishing a dependency, based upon the TimeYear dataset, within the TimeMonth dataset, we must establish this dependency within a Query Parameter within the dataset, just as we did on a larger scale within the primary dataset query earlier.

The Query Parameters dialog (currently empty) appears.

Here we add the new “variable” we are associating, via the query syntax, with the TimeMonth parameter picklist, which appears at runtime. It is in this manner that we build in support for cascading of (only) the appropriate child-months.

TimeYear

The Query Parameters dialog appears, with our addition, as shown in Illustration 14.

We are returned to the Data tab, where we can see the original MDX syntax in the Query pane. The MDX appears as depicted in Illustration 15.

All that remains to enforce a cascading TimeMonth parameter is to insert the appropriate syntax into the TimeMonth dataset query. Our overall objective is simply to force a dynamic “filter” based upon the value of the TimeYear parameter (which we have already “declared” within the Query Parameters dialog). We will do this in the steps that follow:

FROM [Adventure Works]

FROM 
   ( 
      SELECT 
         ( 
            STRTOSET(@TimeYear, CONSTRAINED) ) ON AXIS(0) 
         FROM  
           [Adventure Works]
         ) 

The entire query should appear as shown in Illustration 16 (I have expanded the additional syntax to promote easier understanding).

The query executes, and a data grid becomes populated, as partially depicted in Illustration 17.

We can scroll through the results dataset and easily see that only those Months that are the children of FY2005 (the default TimeYear parameter value) appear. This will support our desired retrieval objective, selections within the Month picklist at runtime that are associated with (and only those that are associated with) the year that is selected at runtime within the parent TimeYear parameter. We will ascertain the effectiveness of our handiwork via the performance of a simple Preview later, once we have completed the syntax modification procedures in the next subsection, where we make final enhancements - this time to the primary dataset - to embed the “top count” capabilities requested by our client colleagues, based upon the selections they make at runtime for year, month, and “number of ‘top’ items to retrieve.”

Add Syntax to Enforce the Month Parameter Selection and Leverage the MDX TopCount() Function, Containing the TopCountSpan Parameter Placeholder

Our last procedure will comprise “turning it all on” by another syntax modification – this time within the query supporting the primary dataset, ProductData. Recall that we have already added query parameters for TimeYear, TimeMonth and TopCountSpan into the ProductData dataset. At this point, we have only to embed the parameters into the query syntax, which we will do in the steps that follow.

The dataset query appears, presenting the MDX syntax in Query view, as shown in Illustration 18.

 ([Date].[Fiscal].[Month].ALLMEMBERS

(STRTOSET(@TimeMonth, CONSTRAINED)

 [Sales Reason].[Sales Reason].[Sales Reason].ALLMEMBERS

TOPCOUNT([Sales Reason].[Sales Reasons].[Sales Reason].MEMBERS, @TopCountSpan, [Measures].[Internet Sales Amount])

SELECT NON EMPTY { [Measures].[Internet Order Quantity], 
 [Measures].[Internet Sales Amount] } ON COLUMNS, 
 NON EMPTY { 
(STRTOSET(@TimeMonth, CONSTRAINED)   * TOPCOUNT([Sales Reason].[Sales Reasons].[Sales Reason].MEMBERS, @TopCountSpan, 
 [Measures].[Internet Sales Amount])
 * [Sales Territory].[Sales Territory Group].[Sales Territory Group].ALLMEMBERS)  } DIMENSION PROPERTIES MEMBER_CAPTION, 
 MEMBER_UNIQUE_NAME, [Sales Reason].[Sales Reason].[Sales Reason].KEY, 
 [Sales Territory].[Sales Territory Group].[Sales Territory Group].KEY ON ROWS 
  FROM ( SELECT ( { [Date].[Fiscal].[Fiscal Year].&[2004], 
 [Date].[Fiscal].[Fiscal Year].&[2005] } ) ON COLUMNS 
 FROM [Adventure Works]) CELL PROPERTIES VALUE, 
 BACK_COLOR, 
 FORE_COLOR, 
 FORMATTED_VALUE, 
 FORMAT_STRING, 
 FONT_NAME, 
 FONT_SIZE, 
 FONT_FLAGS

The dataset query appears (the replacements have been buffered with extra space for easier viewing), with our substitutions (boxed in red), within the Query pane as shown in Illustration 21.

The query executes, and the data grid again becomes populated, as partially depicted in Illustration 22.

An examination of the query and the data it returns reveals that, from the perspective of the Data tab, therefore, our query appears to be performing as expected. We will verify performance of this and other items with which we have worked in the next section, where we will conclude our practice exercise.

Verification: Preview the Report and Inspect the Effectiveness of Our Modifications and Enhancements

Let’s preview the report to inspect the results of our handiwork.

DBJ_OLAP_Report.rdl initializes, and the Year prompt becomes enabled.

We note that the Year parameter is once again displaying the ParameterCaptionIndented Label field - something we had already changed to the standard ParameterCaption Label field within the Report Parameters dialog for the respective parameter TimeYear earlier. It is, perhaps, useful to note, at this stage, that the Label field has reset itself to this, the Reporting Services default, when we “declared” the TimeYear query parameter within the TimeMonth dataset, as a part of making the latter cascade properly, based upon the selection we made within the TimeYear picklist. To restore the standard ParameterCaption Label field, we can simply return to the Report Parameters dialog for the TimeYear parameter, and reselect ParameterCaption via the Label field selector, as shown in Illustration 23.

We note that only two months appear within the Month parameter picklist - the only populated member months of FY 2005 within the Adventure Works sample cube.

NOTE: Ignore the results that appear if the report auto-executes at this point. The default year / month parameter selections are for a period that experienced limited (less than three) Sales Reasons associated with its activity.

Let's try changing the Year at this point to demonstrate further the fact the cascading is working effectively:

Once we make a selection within the Year dropdown selector, the next parameter within the cascading chain, Month, becomes enabled, once again.

We note (scrolling down, as necessary) that twelve months appear within the Month parameter picklist - the populated member months of FY 2004 within the Adventure Works sample cube. We can easily see that the Month parameter picklist cascades appropriately, based upon the selection we make in the Year parameter picklist.

Let's verify the operation of the “top count” capability we have added via the No. Top Items parameter. As we can see the default of “3” appears in the selector.

The report executes, and displays its output, including a “top count” of three Sales Reasons (based upon Internet Sales Amount), as shown in Illustration 25.

Let's perform another quick test of the parameterized “top count” capability.

The report executes, displaying its output, this time presenting the Sales Reasons associated with five largest Internet Sales Amounts, as depicted in Illustration 26.

Sales Reason Items

We see, therefore, that the “top sales” parameter we have put into place, No. Top Items, accomplishes the intended ends, and enables us to meet the business need expressed by the information consumers. More generally, we can easily see that Reporting Services 2005, with its graphical design environment, supports easy and flexible design of innovative parameters, which can be based upon a sizable inventory of MDX functions and operators, among other options. Further, we have seen how we can design parameters to cascade, and to otherwise interact in an intuitive, user-friendly fashion. While we might have gone significantly further in designing function-based defaults for cascading parameter picklists, or even more elaborate, dynamically adjusting “top count” spans, based upon other criteria we selected, and a host of other possibilities, we assure our client colleagues that, once they understand the basics, we can certainly help them to further enjoy the myriad more advanced capabilities that are exposed within the current version of Reporting Services.

Conclusion ...

In this article, we concluded another extended examination, which we began in Mastering OLAP Reports: Parameterizing Number of “Top” Items with the MDX TopCount() Function, Part I, of parameterization in Reporting Services 2005. We continued toward our primary objective of getting hands-on exposure to parameterizing TopCount() within the sample OLAP report that we cloned in the first half of the article, where we began our practice session by setting up a scenario within which to work with a basic OLAP report in exposing the steps involved.

We picked up, in this article, with the clone of an existing sample report, containing a matrix data region, which we had created, and structurally modified further for our practice session, in Part I. We also briefly recounted an overview of the MDX TopCount() function we had performed in the first half of this article, discussing details of the use we intended for the function to support the stated reporting needs of a hypothetical client. In so doing, we touched upon general concepts surrounding the parameterization of MDX functions in general, and the TopCount() function specifically.

In this, the second half of our article, we continued our practice session by reviewing and ensuring the adequacy of the datasets (automatically created when we added the required query parameters to support date and function parameterization in Part I) to support report parameters and meet the stated business requirements. We next added syntax to the Month dataset query to enforce cascading, based upon the selection made for the Year parameter by an information consumer at runtime. Finally, we added syntax to enforce the Month parameter selection and leveraged the MDX TopCount() function, containing the set from which we wished to present the “top” items, the count of items desired) and the numeric expression (the measure Internet Sales Amount, in our example) upon which we wished to base our “top” ranking, to provide the “top count” capability requested by our client. At appropriate junctures throughout both Part I and Part II of this article, we discussed the interaction between the various components in supporting the runtime parameter that the end consumer sees, as well as discussing the results obtained with 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.