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

Monday Aug 18th 2008 by William Pearson
Share:

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.

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 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.

NOTE: To gain the most from completing the steps of our continuing practice session below, you will have needed to complete Part I of this article. We will continue where we left off in the first half, and continue to work with objects that we created in Part I, as we complete the steps of our practice session. You must complete the preparation and initial practice session in Part I before continuing with the practice session below.

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.

1.  Re-open the DBJ_OLAP_Report with which we worked in the practice session in Part I.

2.  From the Layout tab, select Report -> Report Parameters from the main menu, once again.

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.

3.  Ensure that the TimeYear parameter is selected within the Parameters pane on the left side of the Report Parameters dialog.

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.)

4.  Click OK to dismiss the Report Parameters dialog for the present time.

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.)

5.  Click the Data tab once again.

6.  Click the downward selector arrow on the right side of the Dataset selector.

7.  Select the new TimeYear dataset that appears, as shown in Illustration 1.


Illustration 1: Select the New Dataset

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.

8.  Click the Execute Query button in the toolbar, as depicted in Illustration 2.


Illustration 2: Execute the Query ...

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


Illustration 3: The Dataset Created for Year Parameter Picklist Support (Partial View)

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.

NOTE: While we will discuss MDX functions, expressions, queries, and related subject matter throughout the MSSQL Server Reporting Services series, please refer to the articles of my MDX Essentials series, whose member articles are published monthly here at Database Journal, for detailed information about MDX, as well as MDX keywords, functions, and other considerations.

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:

9.  Click the Edit Selected Dataset (“...”) button to the right of the Dataset selector (currently containing TimeYear), as depicted in Illustration 4.


Illustration 4: Click the Edit Selected Dataset (“...”) Button ...

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


Illustration 5: The Dataset Dialog Appears ...

10.  Click the Filters tab on the Dataset dialog.

11.  Click the Expression box in the top row of the table labeled Filters on the Filters tab, to activate its downward pointing selector arrow.

12.  Using the selector, select the “=Fields!ParameterLevel.Value” option (at the bottom of the list), as depicted in Illustration 6.


Illustration 6: Select the “=Fields!ParameterLevel.Value” Option as the Expression ...

13.  Ensure that an “equals” (“ = ”) sign appears in the Operator column, to the right of the Expression column, within the same row of the Filters table.

14.  Type the following into the Value column, to the right of the Operator column, within the same row:

=1

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


Illustration 7: The Dataset Dialog, with Our Input

15.  Click OK to accept our input, and to close this dialog.

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”.

16.  Select TimeMonth, within the Dataset selector on the Data tab.

17.  Click the Edit Selected Dataset (“...”) button to the right of the Dataset selector, as we did for the TimeYear dataset earlier.

The Dataset dialog appears, once again.

18.  Click the Filters tab on the Dataset dialog.

19.  Click the Expression box in the top row of the table labeled Filters on the Filters tab, to activate its downward pointing selector arrow, as we did for the TimeYear dataset earlier.

20.  Using the selector, select the “=Fields!ParameterLevel.Value” option, exactly as we did for the TimeYear dataset.

21.  Ensure, once again, that an “equals” (“ = ”) sign appears in the Operator column, to the right of the Expression column, within the same row of the Filters table.

22.  Type the following into the Value column, to the right of the Operator column, within the same row:

=4

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


Illustration 8: The Dataset Dialog, with Our Input

23.  Click OK to accept our input, and to dismiss the Dataset dialog.

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.

24.  Click the Preview tab next, to execute the report, once again.

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

25.  Click the downward pointing arrow to the right of the Month parameter selector, to expose the parameter picklist.

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


Illustration 9: The Month Parameter Picklist Appears...

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.

26.  Click the Data tab to return to the Data view.

27.  Click Report --> Report Parameters to re-open the Report Parameters dialog, as before.

28.  Ensure selection of the TimeYear parameter in the Parameters pane of the dialog.

29.  Within the Prompt box, found within the Properties section (the third input box from the top) of the Report Parameters dialog, replace the existing prompt name (“TimeYear”) with the following:

Year:

30.  Within the Label field selector, the third setting from the top within the Available values section of the dialog (about midway down and underneath the Properties section), select Parameter Caption as presented in Illustration 10.


Illustration 10: Select Parameter Caption for the Label Field Reference ...

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


Illustration 11: Modified Settings for the First Automatically Created Report Parameter

31.  Select the TimeMonth parameter within the Parameters pane on the left side of the Report Parameters dialog.

32.  Within the Prompt box, found within the Properties section of the Report Parameters dialog, once again, replace the existing prompt name (“TimeMonth”) with the following:

Month:

33.  Within the Label field selector, the third setting from the top within the Available values section of the dialog, select Parameter Caption, as we did for the TimeYear report parameter.

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


Illustration 12: Settings for the Second Automatically Created Report Parameter

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.)

34.  Select the TopCountSpan parameter within the Parameters pane on the left side of the Report Parameters dialog.

35.  Within the Prompt box, again within the Properties section of the Report Parameters dialog, replace the existing prompt name (“TopCountSpan”) with the following:

No. Top Items:

36.  Within the Available values section of the dialog, ensure that the Non-queried radio button is selected.

37.  Populate the table that appears to the right of the Non-queried radio button as specified in Table 1 below.

Label

Value

1

1

2

2

3

3

4

4

5

5

6

6

7

7

8

8

9

9

10

10


Table 1: Label and Value Input for the Non-queried Available Values Table

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


Illustration 13: Settings for the Third Automatically Created Report Parameter (Partial View)

38.  Click OK to accept our changes and to dismiss the Report Parameters dialog.

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 parameterizedtop 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.

1.  From the Data tab, once again, click the downward selector arrow on the right side of the Dataset selector.

2.  Select the TimeMonth dataset, if necessary.

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.

3.  Click the Query Parameters button on the toolbar, as we did 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.

4.  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.

5.  Type the following into the selected cell:

TimeYear

6.  Within the cell to the immediate right of the Parameter cell just populated, labeled Dimension, select Date, just as we did within the same position within the primary dataset query earlier.

7.  Within the cell to the immediate right of the Dimension cell just populated, labeled Hierarchy, select Date.Fiscal, as we did within the same position within the primary dataset query earlier.

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

9.  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.

10.  Select the FY 2005 member, as we did within the same position within the primary dataset query earlier.

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

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


Illustration 14: The Query Parameters Dialog, with Our Input ...

12.  Click OK to accept our new parameter addition, and to close the Query Parameters dialog.

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.


Illustration 15: The TimeMonth Dataset Query

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:

13.  Remove the following from the existing dataset query:

FROM [Adventure Works]

14.  Replace the newly removed clause with the following syntax (simply add it to the end of the query):

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).


Illustration 16: Modified TimeMonth Dataset Query

15.  Click the Execute Query (“!”) button in the toolbar.

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


Illustration 17: The Dataset with Our Modifications (Partial View)

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.

1.  From our present position on the Data tab, click the downward selector arrow on the right side of the Dataset selector, once again.

2.  Select the ProductData dataset, at the top of the list of datasets.

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


Illustration 18: The Primary Dataset (ProductData) Query

3.  Carefully remove the following syntax from the query:

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

shown circled in the portion of the query depicted in Illustration 19.


Illustration 19: Remove the Date–related Portion of the (Partially Shown) Query

4.  Leaving the cursor in place, type the following substitute syntax into the query:

(STRTOSET(@TimeMonth, CONSTRAINED)

5.  Carefully remove the following syntax from the query:

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

shown circled in the query presented in Illustration 20.


Illustration 20: Remove the Sales Reason–related Portion of the Query

6.  Leaving the cursor in place, type the following substitute syntax into the query:

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

So that the complete query is as follows (cut and paste it into the place of the existing query, if that is easier):

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

Note: The “replacement” syntax is in red in the code above.

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.


Illustration 21: The Dataset Query with Our Modifications

7.  Click the Execute Query button on the toolbar, once again, to run the newly modified query.

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


Illustration 22: The Modified ProductData Dataset (Partial View)

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.

1.  Click the Preview tab.

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.


Illustration 23: Adjusting the Label Field, which has Reset to Default ... (Optional)

2.  Click the Preview tab, once again, if you have made the change noted above.

3.  Click the downward pointing arrow on the right side of the Month parameter selector.

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:

4.  Select FY 2004 within the Year parameter picklist.

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

5.  Click the downward selector button that appears on the right of the Month parameter, as we did before, to expose the parameter picklist, as partially depicted in Illustration 24.


Illustration 24: The Parameter Picklist Cascades to Display the Member Months of FY 2004 (Partial View)

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.

6.  Select June 2004 within the Month parameter picklist (the bottom entry appearing within the selector).

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.

7.  Leaving the No. Top Items parameter selection at default (“3”), click the View Report button in the upper right corner of the Preview tab, to execute the report with current settings.

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.


Illustration 25: The Report Displays a “Top Count” Span of Three Sales Reason Items

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

8.  Click the downward pointing arrow to the immediate right of the No. Months to Report parameter selector.

9.  Select the number “5” within the No. Top Items parameter picklist.

10.  Click the View Report button in the upper right corner of the Preview tab, to execute the report with current settings.

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


Illustration 26: The Report Displays the Five Largest (Based upon Internet Sales)

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.

11.  Experiment further with the report, if desired.

12.  Select File -> Save All to save our work to this point.

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

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.

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