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

Monday May 16th 2005 by William Pearson
Share:

Parameterize a pair of MDX functions, and more, from a simple OLAP report. MSAS Architect Bill Pearson provides hands-on practice in providing ad hoc TopCount() and BottomCount() functions, as well as demonstrating the support of parameterization from both report and cube layers within an integrated BI solution.

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"), with the objective of presenting an overview of its features, together with many tips and techniques for real-world use. For more information on the series, as well as the hardware / software requirements to prepare for the exercises we will undertake, please see my initial Database Journal article, A New Paradigm for Enterprise Reporting.

As I have stated since the charter article of the series, published about the time Reporting Services was first publicly released, my conviction is that Reporting Services will commoditize business intelligence, particularly in its role as a component in an integrated Microsoft BI solution. Having been impressed from my first exposure to this exciting application, when it was in early beta, my certainty in its destiny grows stronger by the day, as I convert formerly dominant enterprise Business Intelligence systems, such as Cognos, Business Objects, Crystal, and others, to the Reporting Services architecture. I receive constant requests to conduct strategy sessions about these conversions with large organizations in a diverse range of industries - the interest grows daily as awareness of the solution becomes pervasive. Indeed, the six-plus figures that many can shave from their annual IT budgets represent a compelling sweetener to examining this incredible toolset.

Basic assumptions underlying the series are that you have correctly installed Reporting Services, including Service Pack 1, along with the applications upon which it relies, and that you have access and the other rights / privileges required to complete the steps we undertake in my articles. For details on the specifics of the adjustments necessary to quickly allow full freedom to complete the exercises in this and subsequent articles, as well as important assumptions regarding rights and privileges in general, please see earlier articles in the series, as well as the Reporting Services Books Online.

About the Mastering OLAP Reporting Articles...

As I have noted in many articles and presentations, one of the first things that becomes clear to "early adopters" of Reporting Services is that the "knowledgebase" for OLAP reporting with this tool is, to say the least, sparse. (I recently heard an internal "reporting guru" say, during a BI strategy session with a major soft drink manufacturer in Atlanta, that "we didn't evaluate Reporting Services because it doesn't do cubes ...") As most of us are aware, minimal, if any, attention is given to using Analysis Services cubes as data sources for reports in the handful of books that have been published on Reporting Services to date. All are written from the perspective of relational reporting, as if with existing popular tools for that purpose. One Reporting Services book discusses OLAP reporting with Reporting Services, and then performs illustrative exercises with Office Web Components (OWC), instead. Another depicts an MDX snippet at the end of the book, as if as an afterthought. All of the early books focus entirely on relational reporting, and most make heavy use, typically enough, of the Books Online and other scraps of documentation that we already have anyway. (I could go on, but my overall opinion of the technical book industry is already well known.)

As I stated in my article, Mastering OLAP Reporting: Cascading Prompts, 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 to date, represents a serious "undersell" of Reporting Services, from an OLAP reporting perspective. I hope to contribute to making this arena more accessible to everyone, and to share my implementation and conversion experiences as the series evolves. In the meantime, rest assured that the OLAP potential in Reporting Services will be yet another reason that the application "commoditizes" Business Intelligence.

Overview

As we have discussed elsewhere in the series, parameters (sometimes known as "prompts" or "parameter prompts") are a staple of enterprise reporting, because they enable information consumers to quickly find the information they need from a data source. These filters / members can be put in place "on the fly," and are typically enacted when the consumer types or selects a value, or a series of values, at run time.

We discussed the two primary types of parameters, type-in and picklist, in Mastering OLAP Reporting: Cascading Prompts, where we concluded that a well-constructed picklist is often the tool of choice, because of its inherent elimination of typing errors. Because it is important to always anticipate information consumer needs, I maintain an "inventory" of successful approaches (I come across such nuances frequently as a BI architect and consultant) to meeting the "need for user friendliness." In working with MSAS, I have found countless opportunities to "embed" support for such instrumentality at the MSAS level.

In this article, I will present a "combined approach" for the support of a top- or bottom- count picklist in the reporting environment. This option will enable an information consumer to decide, on the fly, whether a "top" or "bottom" query is more appropriate, as well as to input the number of top / bottom members they wish to retrieve. After constructing its foundation within the context of the underlying Dataset within Reporting Services, I will show the use of a picklist in conjunction with a type-in parameter to achieve the illustrative needs of a hypothetical client in Reporting Services.

Some of the general concepts in this article extend to any enterprise reporting package designed to report from Analysis Services OLAP cubes, assuming that they provide a means for stringing and passing MDX in a manner similar to that we are about to undertake. It is especially applicable in the cases of tools like Crystal Analysis Pro, ProClarity and other advanced, yet relatively "open," OLAP reporting applications. Suffice it to say that if you can successfully designate an Analysis Services cube as a data source, and can pass direct MDX to Analysis Services from the reporting application, you can probably employ the concepts we will be discussing here.

In this article, we will:

  • Create copies of both an existing sample OLAP report and an Analysis Services sample cube, to save time while preserving the respective original samples;
  • Provide an overview of the TopCount() and BottomCount() MDX functions, prior to introducing a hypothetical need for their use in an OLAP report;
  • Create calculated members and a named set within our clone cube, to support a hierarchical picklist for Time dimension member specification in Reporting Services;
  • Modify the clone report to more closely meet the new presentation specifications of a hypothetical group of information consumers;
  • Explain the MDX that we employ at both the cube (to support hierarchical date parameterization), and at the report (to support TopCount() and BottomCount() parameterization), levels;
  • Create parameters within our new OLAP report to support the selection of a hierarchical Time dimension specification; as well as "top" or "bottom", along with the number of the top / bottom members, as report retrieval options;
  • Preview the report in Report Designer, supplying run-time parameters, to verify its operation.

Ad HocTopCount and BottomCount Parameters

Introduction and Business Scenario

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. My continued application of those concepts over the succeeding 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 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 seeking 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.

An excellent example of parameterization within this context can be had in the exploitation of the popular MDX TopCount() and BottomCount() functions.

A Quick Overview of the TopCount and BottomCount Functions

As many of us who routinely use MDX in our business environments are aware, the TopCount() and BottomCount() functions are highly valuable in enabling us to isolate best performers from among hundreds, perhaps thousands, of fellow members. This ranking capability is critical in data analysis and decision support scenarios: In many business situations, we seek to report upon "best performers" for various reasons. TopCount() facilitates our doing so, allowing us to sort on a numeric value expression that we can provide. We can tell the function how many "top" members we wish to retrieve (say, the "top ten," or the "top twenty"), for a "custom-fit" approach, that matches our needs precisely. BottomCount() allows us to perform the opposite, but in a procedurally similar manner: we can thus force a sort on a numeric value expression for the number of "bottom" members we wish to retrieve. Particularly useful in the analysis of "underperformers" within many contexts, BottomCount() helps us to isolate candidates for elimination (as with underperforming locations, products - even people), or other appropriate action.

According to the Analysis Services Books Online, the TopCount() function returns a specified number of items from the topmost members of a specified set, ordering the set first." We specify three parameters, 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. BottomCount(), by contrast, provides the specified number bottommost members, and works, in all considerations except "direction" and starting point, in a manner similar to TopCount().

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, and is identical, except for the keyword that begins it, for BottomCount().

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

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 with whom we are working within the FoodMart organization wishes to see the top ten Product Names for operating year 1997, based upon total Store Sales for each product.

The basic TopCount() function specifies the "top ten Product Names" (with the number "10" as the Count specification, and [Product].[Product Name].Members as the Set specification, of the function). TopCount() assembles the top ten members from the perspective of Store Sales (the Numeric Expression upon which the complete set of Product Names will first be sorted by the function). The function with arguments is represented in the ON ROWS specification of the following query (enclosed in the dashed-line box):

SELECT
   {[Measures].[Store Sales]} ON COLUMNS,
{TOPCOUNT([Product].[Product Name].Members, 10, [Measures].[Store Sales])} ON ROWS
FROM [SALES] 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.

Store Sales

Hermanos Green Pepper

$922.54

Just Right Rice Soup

853.52

Urban Small Eggs

845.24

Washington Apple Drink

835.38

Carlson Head Cheese

821.79

Hermanos Lemons

815.58

Tell Tale Cantelope

813.66

Imagine Waffles

808.83

Hilltop Mint Mouthwash

792.78

Moms Foot-Long Hot Dogs

785.40


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

NOTE: For more information on the TopCount(), and, indirectly, the Bottomcount(), functions, see the introductory articles, Basic Set Functions: The TopCount() Function, Part I and Part II in my MDX Essentials series at Database Journal. For use of the function in more elaborate practice scenarios, see Set and String Functions: The GENERATE() Function, within the same series. Finally, for optimization considerations surrounding TopCount() and BottomCount(), see Optimizing MDX: More on Location, and the Importance of Arrangement, found in my MDX in Analysis Services series. All series can be accessed from my Database Journal Index Page.

Parameterizing TopCount() and BottomCount()

I often parameterize the TopCount() and BottomCount() functions 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 we typically assemble 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 top and bottom counts to an OLAP report. To provide a report upon which we can practice our exercises, we will begin with the Foodmart Sales sample report that, among other samples, accompanies the installation of Reporting Services, and which is based upon the ubiquitous Sales sample cube that comes along with an installation of Analysis Services.

For purposes of our practice procedure, we will assume that information consumers within the Marketing office of the FoodMart organization have expressed the need for modifications of the existing Foodmart Sales report. The drilldown capabilities in the report meet most of their analysis needs, but recent requirements to perform "outlier analysis" with regard to the products that the organization sells, have 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 state that a particular analysis operation would be dramatically enhanced if they could simply specify, at report run time, that they wished to see a variable number of "top products" with regard to sales. Ideally, they would like to be prompted for the number of products each time they wished to run the report, as the number might vary with each information request. "Top ten" (of whatever number) analysis of this sort is quite common within OLAP, and we express confidence that we can meet the described need.

The consumers request some additional changes to the report body itself, primarily that we remove the existing parameter, which allows us to filter products by Product Family. The new report will also be simpler with regard to dimensional levels: The consumers state that the row axis (currently occupied by several levels of the Product dimension, which, in turn, drill down to Product Brand Name as the lowest level) needs only to display the Product Name, the lowest level of the Product dimension in the Sales cube. Because this will be a fixed report, designed for a limited use by analysts, we have no need of drilldown features in the row axis. Finally, the consumers ask that the report be modified to allow date selection via a cascading picklist - a feature that will replace the current date drilldown in the column headings.

As an added embellishment, we propose the extension of the requirement to include the capability to dictate, also at run time, our selection of "top" or "bottom" performers, it being our experience that analysis of bottom performers can be useful in many cases. The capability to focus on bottom performers is a natural extension of the need to analyze top performers, if not, perhaps, to support different actions by the organization once "bottom" status is ascertained. The information consumers receive our suggestion with enthusiasm, and agree that they can envision several uses for such a capability. We thus plan from the outset to provide a multi-purpose tool in a compact, efficient "package." These capabilities will also convert the existing Foodmart Sales report to a multi-purpose report whose presentation is dictated on the fly by the easy, ad hoc input of varying criteria, allowing the analysts to meet differing conditions and analysis needs rapidly. 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 FoodMart Sales report to satisfy the information consumers.

Considerations and Comments

Before we alter the FoodMart Sales cube to support our hierarchical date prompts, we will create a copy of the cube. Creating a clone of the sample Sales cube means we can make changes to our cube while retaining the original sample in a pristine state - perhaps for other purposes, such as using it to accompany relevant sections of the Books Online, and other documentation, in learning more about Analysis Services in general. (Once we finish with our structural changes to the clone cube, we will create a copy of the Foodmart Sales report, for the same reasons.)

Before we can work with a clone of the Foodmart Sales report, we need to create a Reporting Services project in the Visual Studio.Net 2003 Report Designer environment. Making the enhancements to the report to add the requested functionality can be done easily within the Reporting Services Report Designer environment. Working with a copy of the report will allow us the luxury of freely exploring our options, and leave us a working example of the specific approach we took, to which we can refer in our individual business environments. This approach also preserves the original sample in a pristine state - for the same reasons we do so for the Sales sample cube. If you already have a project within which you like to work with training or development objects, you can simply skip the Create a Reporting Services Project section.

If the sample FoodMart 2000 Analysis Services database or the Foodmart Sales report was not created / installed as part of the initial installation of the associated application, if either was removed prior to your beginning this article, or if either or both applications have yet to be installed, etc., see the respective Books Online or other documentation for the necessary procedures to prepare for the exercises that follow.

Hands-On Procedure

Preparation

Create a Clone of the Foodmart Sales Cube

Let's first create a copy of the existing FoodMart Sales cube to provide a "disposable" work environment.

1.  Open Analysis Manager.

2.  Expand the Analysis Server folder in the management console.

3.  Expand the Analysis Server with which you are working by clicking the "+" sign to its left.

4.  Expand the FoodMart 2000 database.

5.  Expand the Cubes folder inside the FoodMart 2000 database.

6.  Right-click the Sales cube.

7.  Click Copy from the context menu that appears, as shown in Illustration 1.


Illustration 1: Select Copy from the Context Menu

8.  Right-click the Cubes folder.

9.  Select Paste from the context menu that appears, as depicted in Illustration 2.


Illustration 2: Select Paste, after Right-clicking the Cubes Folder

The Duplicate Name dialog appears.

10.  Rename the new cube as follows:

Param_Support_Sales

TIP:

This is also a good way to rename MSAS objects for which a "Rename" option does not exist. We simply create the new object in the manner shown above, give it the desired name, and discard the original object, as appropriate.

Keep in mind that, in the case of cubes and other structural objects, this will mean reprocessing before the clone will be fully usable.

The Duplicate Name dialog appears as shown in Illustration 3.


Illustration 3: Duplicate Name Dialog with Our Input

11.  Click OK to create the clone cube.

The new Param_Support_Sales cube appears in the tree as depicted in Illustration 4.


Illustration 4: The New Cube Appears

Enhance the Foodmart Sales Cube to Support a Hierarchical Date Picklist in Reporting Services

1.  Right-click the new Param_Support_Sales cube.

2.  Select Edit ... from the context menu that appears, as shown in Illustration 5.

Click for larger image

Illustration 5: Select Edit from the Context Menu

The Cube Editor opens.

3.  Right-click the Calculated Members folder within Cube Editor.

4.  Select New Calculated Member ... from the context menu that appears, as depicted in Illustration 6.


Illustration 6: Select New Calculated Member from the Context Menu

The Calculated Member Builder opens.

5.  Type the following into the Member name box:

RS_pX_TimePLDispName_Hier

While the name of the calculated member can obviously be anything useful in the environment in which we are creating it, I typically use a name similar to the above, in order to make it apparent, both inside Analysis Services and in the reporting application (Reporting Services in this article), that the calculated member exists for use in the reporting ("RS") application. In addition, it makes it apparent that it has been created to support picklists ("pX"), and that this particular calculated member exists to generate a picklist display name ("PLDispName") in our report(s) for the various levels of the Time dimension. The specific convention we use in our own business environments should conform to the needs and conventions of the respective environment, of course.

6.  Type the following MDX into the Value Expression section of the Calculated Member Builder:

Space([Time].Currentmember.Level.Ordinal * 3) + [Time].CurrentMember.Name

The MDX expression above returns, via our calculated member, a display name. We will use this expression to generate the picklist that the information consumers see, as selection options upon being prompted, each time they attempt to generate a report in which we insert the picklist to support a parameter. An explanation of the components of the above expression appears in Table 2.

Expression

Meaning

Space([Time].Currentmember.Level.Ordinal * 3)

The Space() VBA function is used together with the level ordinal property of the current member to generate a "space size." We then multiply that size value by three (3).

Our objective here is cosmetic - we are simply making the levels of the Time dimension appear more intuitively hierarchical for the consumers, by "indenting" (by the assigned space size) each of the various levels of the Time dimension in accordance with its levels in the hierarchy.

+ [Time].CurrentMember.Name

This section of our expression actually generates the name of the current member ...


Table 2: Expression Components Summary

NOTE: For an explanation of the .CurrentMember function, see my article MDX Essentials: MDX Member Functions: "Relative" Member Functions in the MDX Essentials series at Database Journal.

The Calculated Member Builder appears with our input, as partially shown in Illustration 7.


Illustration 7: Calculated Member Builder (Partial View) with Complete MDX Expression

7.  Click OK to close the Calculated Member Builder.

The new Calculated Member appears in the tree within the Calculated Members folder.

8.  Right-click the Calculated Members folder within Cube Editor, once again.

9.  Select New Calculated Member ... again, from the context menu that appears.

The Calculated Member Builder opens.

10.  Type the following into the Member name box:

RS_pX_TimeMSASName_Hier

We are again using a naming convention for the member to make it apparent, both inside Analysis Services and in the reporting application, that the calculated member exists for use in the reporting ("RS") application, that it has been created to support picklists ("pX"), and that this particular calculated member exists to generate a "qualified name" (the Unique Name) within MDX.

11.  Type the following MDX into the Value Expression section of the Calculated Member Builder:

[Time].CurrentMember.UniqueName

The MDX expression will return, via our calculated member, the unique name (a member property) that we will use as the value field in a Dataset query, upon which we base our ultimate report. In other words, it generates the qualified / full name that corresponds to the "user-friendlier" picklist name that the user selects at runtime.

The Calculated Member Builder appears, with our input, as partially depicted in Illustration 8.


Illustration 8: Calculated Member Builder (Partial View) with Complete MDX Expression

12.  Click OK to close the Calculated Member Builder.

The new Calculated Members appear in the tree within the Calculated Members folder as shown in Illustration 9.


Illustration 9: Calculated Members in Place for Picklist Name and MSAS Name

(Compressed View)

13.  Select File -> Save to save our additions to the cube structure.

Now that we have the two calculated members in place, let's create a named set to contain them conveniently for use in the reporting application.

14.  Right-click the Named Sets folder within Cube Editor.

15.  Select New Named Set ... from the context menu that appears, as depicted in Illustration 10.


Illustration 10: Select New Named Set ... from the Context Menu

The Named Set Builder opens.

16.  Type the following into the Set name box:

RS_pX_Time_Hier

As we saw to be the case with the calculated members earlier, the name of the set can obviously be anything useful in the environment in which we are creating it. I typically use a name similar to the above, in order to make it apparent, both inside Analysis Services and from the reporting application (Reporting Services in this article), that the named set exists for use in the reporting ("RS") application. IN addition, it makes it apparent that it has been created to support picklists ("pX"), and that this particular named set exists to support picklists in our reports for the various hierarchical levels of the Time dimension.

17.  Type the following MDX (using the graphical design tools, if useful) into the Set Expression section of the Named Set Builder:

{[Measures].[RS_pX_TimePLDispName_Hier],[Measures].[RS_pX_TimeMSASName_Hier]}

The set we specify above will return, via our named set, both the picklist name (which the consumers will see) and the "qualified" MSAS name. The former name provides the list from which the intended audience can select the Time levels they wish to appear in the report; the latter name supplies the MDX- "qualified" name required to filter the report appropriately.

The Named Set Builder appears, with our input, as partially shown in Illustration 11.


Illustration 11: Named Set Builder (Partial View) with Complete MDX Expression

18.  Click the Check button to ascertain the correctness of the syntax.

A confirmation dialog should appear, indicating that Syntax is OK, as depicted in Illustration 12.


Illustration 12: Confirmation Dialog Indicates Syntax is Correct

19.  Click OK to close the confirmation dialog.

20.  Click OK to close the Named Set Builder, and to save our new named set.

The new named set appears in the tree as shown in Illustration 13.


Illustration 13: The New Named Set Appears

21.  Select File -> Save once again to save the cube with the new addition.

22.  Select Tools -> Process Cube to process the clone cube.

NOTE: The cube must be processed before we can designate it as a data source in the next section.

The Process a Cube dialog appears, as depicted in Illustration 14, with the processing method defaulted to Full Process. Full processing is the only option, as this is the first processing cycle of our cloned cube.


Illustration 14: Full Process Selected in the Process a Cube Dialog

23.  Click OK to begin processing.

Processing begins. The Process viewer displays various logged events, then presents a green Processing completed successfully message, as shown in Illustration 15.


Illustration 15: Indication of Successful Processing Appears

24.  Click Close to dismiss the viewer.

25.  Select File -> Exit to close the Cube Editor.

26.  Exit Analysis Services, as desired.

Create a Reporting Services Project

To begin, we will launch Reporting Services' Report Designer, found in Microsoft Visual Studio .NET 2003.

1.  Click Start.

2.  Navigate to, and click, the Microsoft Visual Studio .NET 2003 shortcut in the Programs group, as appropriate.

The equivalent on my PC appears as depicted in Illustration 16.

Click for larger image

Illustration 16: Beginning in Microsoft Visual Studio .NET 2003 ...

Visual Studio .NET 2003 opens at the Start page.

3.  Select File -> New from the main menu.

4.  Click Project from the cascading menu, as shown in Illustration 17.

Click for larger image

Illustration 17: Selecting a New Project

The New Project dialog appears. Business Intelligence Projects appears in the Project Types tree, indicating an installation of Reporting Services.

5.  Click Business Intelligence Projects in the Project Types tree, if necessary.

6.  Click Report Project in the Templates list.

7.  Type the following into the Name box, leaving other settings at default:

RS017

8.  Navigate to a location in which to place the Report Project files.

The New Project dialog appears, with our input, as depicted in Illustration 18.


Illustration 18: The New Projects Dialog, with Addition

9.  Click OK.

Our new project appears in the Solution Explorer (upper right corner of the Visual Studio .NET interface), as we see in Illustration 19.


Illustration 19: The New Project Appears in the Solution Explorer

Having created a Report Project, we are ready to proceed with creating the new report clone.

Enhancing the Foodmart Sales Report to Include Ad Hoc Sorting

In this section, we will copy the existing Foodmart Sales report, and then open it in Reporting Services' Report Designer, where we will begin the modifications that the information consumers have requested.

Create a Copy of the Foodmart Sales Report

As we have noted, we will be working with a copy of the Foodmart Sales report, to keep the original sample intact for easy access and use at another time.

1.  Right-click the Reports folder underneath the newly created shared data source, in the Solution Explorer.

2.  Select Add -> Add Existing Item from the cascading context menus that appear, as depicted in Illustration 20.

Click for larger image

Illustration 20: Adding the Report to the Project ...

The Add Existing Item - RS017 dialog appears.

When we installed Reporting Services, the default installation point for the sample report files was the Samples folder within the Reporting Services program folder. A common example of this default path is as follows:

C:\Microsoft SQL Server\MSSQL\Reporting Services\Samples\Reports

3.  Using the Add Existing Item - RS017 dialog, navigate to the location of the sample reports in your own environment.

An example of the Add Existing Item - RS017 dialog, having been pointed to the Samples folder (which contains the Foodmart Sales report we seek), appears as partially shown in Illustration 21.


Illustration 21: Navigating to the Sample Reports Folder ...

4.  Right-click the Foodmart Sales report inside the dialog.

5.  Select Copy from the context menu that appears, as depicted in Illustration 22.


Illustration 22: Performing a Quick Copy of the FoodMart Sales Report

6.  Within the Add Existing Item - RS017 dialog, navigate to the RS017 folder we created earlier, when we added the new RS017 project.

7.  Right-click somewhere in the white space inside the RS017 folder, within the dialog.

8.  Select Paste from the context menu that appears, as shown in Illustration 23.


Illustration 23: Select Paste to Complete Copying ...

A copy of the Foodmart Sales report appears within the dialog.

9.  Right-click the new file.

10.  Select Rename from the context menu that appears.

11.  Type the following name in place of the highlighted existing name:

Ad Hoc TopBottomFoodmart Sales.rdl

NOTE: Be sure to include the .rdl extension in the file name.

The renamed copy of the Foodmart Sales sample report appears as depicted in Illustration 24.


Illustration 24: The New Report File, AdHoc_TopBottomFoodmart Sales.rdl

12.  Select the new file by clicking it, if necessary.

13.  Click Open on the dialog box to add the new report to report project RS017.

AdHoc_TopBottomFoodmart Sales.rdl appears in the Reports folder, within the RS017 project tree in the Solution Explorer, as shown in Illustration 25.


Illustration 25: The New Report Appears in Solution Explorer - Report Folder

Having created a copy of the functional report, we are now ready to make the enhancements requested by the FoodMart Marketing Department information consumers.




Modify the Foodmart Sales Report Layout to Meet Business Requirements



As we have mentioned, the FoodMart Sales report that we have cloned as AdHoc_TopBottomFoodmart Sales.rdl contains a prompt to allow users to filter by Product Family. We will make general layout changes in the report, while also eliminating the existing prompt, before adding the parameters that form the core of our focus in this lesson.



1.  Right-click AdHoc_TopBottomFoodmart Sales.rdl in the Reports folder.



2.  Select Open, as depicted in Illustration 26, from the context menu that appears.



Click for larger image

Illustration 26: Select Open from the Context Menu ...




AdHoc_TopBottomFoodmart Sales.rdl opens and appears on the Layout tab of the Report Designer, as shown in Illustration 27.




Illustration 27: AdHoc_TopBottomFoodmart Sales.rdl Appears in Report Designer - Layout Tab

3.  Click the Preview tab to execute the report.

AdHoc_TopBottomFoodmart Sales.rdl executes briefly, and then returns data. We see, atop the Preview tab, the Product Family parameter selector, as depicted (expanded) in Illustration 28.


Illustration 28: AdHoc_TopBottomFoodmart Sales.rdl Appears in Preview (Partial View) - with Parameter Selector Expanded

4.  Select Non-Consumable in the Product Family selector.

5.  Click the View Report button to execute the report.

The report appears, with different row items and values, as shown in Illustration 13. The differing results are due to its being filtered, via the parameter selection, on the Non-Consumable Product Family.


Illustration 29: AdHoc_TopBottomFoodmart Sales. Appears with Results Filtered for Non-Consumable Products

Each of the three Product Family selections gives us a results set filtered for its members. The Marketing team has asked for a "consolidated view," where all products appear on a single report. Elimination of the parameter in place will be a simple way to get started toward this first objective, and to pave the way for the new parameters that we will construct next.

6.  Click the Layout tab to return to the Layout view.

7.  Select Report -> Report Parameters from the main menu atop the Report Designer, as depicted in Illustration 30.


Illustration 30: Select Report -> Report Parameters from the Main Menu

The Report Parameters dialog, where we define parameters for the report, appears, as shown in Illustration 31.


Illustration 31: The Report Parameters Dialog

We will get plenty of exposure to this dialog later in our session, and throughout the MSSQL Server 2000 Reporting Services series in general. Our objective at this point is to remove the existing parameter, and to consolidate the Product Families thereby.

8.  In the Parameters list, on the left side of the dialog, click the single entry, ProductFamily, to select it.

9.  Click the Remove button underneath the Parameters list.

The ProductFamily parameter is removed from the list.

10.  Click OK to accept our removal of the parameter.

The now empty Report Parameters dialog closes, and we are returned to the Layout view of the report. We now have only to delete a reference to the parameter we have removed, which we can access via the Properties dialog for the matrix.

11.  Click at some point within the label textbox of the report (containing the label Foodmart Sales), to make the row and column headers of the matrix data region visible.

12.  Right-click the upper left corner of the matrix. (If the headers disappear as you touch them with the cursor, you should still see a faint outline of the matrix.)

13.  Select Properties from the context menu that appears, as depicted in Illustration 32.


Illustration 32: Accessing the Matrix Properties

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

14.  Click the Filters tab.

15.  Click the Value field of the single occupied row to select it.

16.  Click the Delete button to delete the reference to the parameter, as indicated in Illustration 33.


Illustration 33: Select and Delete the Parameter Reference

The remaining reference to the now-deleted parameter is itself deleted. All that remains is a bit of "level extraction." We recall that the consumers have told us that the row axis needs only to display the lowest level of the Product dimension, Product Name. Moreover, they have declared that the custom report will no longer require drilldown features, as it will be a fixed report whose purpose in life is as a limited analysis tool.

Our next steps will deal with the requested "extractions," to ready the report for the specialized date and top and bottom count capabilities that the marketing department has specified. These extractions include the Product Family, Product Category and Product Subcategory portions of the row axis, and the column levels Year and Quarter (the interactive date drilldown will be replaced by a hierarchical date picklist, which will allow date aggregation selection on the fly at run time).

The targeted "extractions" are depicted in Illustration 34.

Click for larger image

Illustration 34: Targeted Levels for Removal in the Customized Report

17.  Click the Groups tab.

Four groups appear in the Rows list box, and two added groups appear in the Columns list box. The groups appear, with those targeted for elimination circled, as shown in Illustration 35.


Illustration 35: Existing Groups in the Clone Report

18.  Click the BrandSales_Product_Department field (the top in the Rows list) to select it.

19.  Click the Delete button to delete the group.

20.  Click the BrandSales_Product_Category field (currently the middle entry in the Rows list) to select it.

21.  Click the Delete button to delete the group.

22.  Click the BrandSales_Product_Subcategory field (currently the middle entry in the Rows list) to select it.

23.  Click the Delete button to delete the group.

24.  Click the BrandSales_Year field (currently the top entry in the Columns list) to select it.

25.  Click the Delete button to delete the group.

26.  Click the BrandSales_Quarter field (currently the middle entry in the Columns list) to select it.

27.  Click the Delete button to delete the group.

The Groups tab appears, after our deletions, as depicted in Illustration 36.


Illustration 36: Groups Tab after Eliminations

28.  Click the BrandSales_Brand_Name group (now the only entry in the Rows list) to select it.

29.  Click Edit.

The Grouping and Sorting Properties dialog for the group opens to the General tab.

30.  Click the Visibility tab.

31.  Click the Visible radio button, to enable static visibility for the BrandSales_Brand_Name group, in accordance with the information consumers' wishes.

32.  Uncheck the box marked "Visibility can be toggled by another report item" located on the lower half of the tab.

The Grouping and Sorting Properties dialog appears as shown in Illustration 37.


Illustration 37: The Grouping and Sorting Properties Dialog with Our Changes

Although the information consumers have requested that the Product Name appear in the report, we are leaving the Product Brand Name group as a placeholder, which we will convert to house the Product Name data element once we modify the underlying data source in later steps.

33.  Click OK to accept changes and close the Grouping and Sorting Properties dialog for the BrandSales_Brand_Name group.

34.  Click OK to accept changes, to close the Matrix Properties dialog, and to return to the Layout view in Report Designer.

35.  Click the Preview tab to execute the modified report.

AdHoc_TopBottomFoodmart Sales.rdl appears as partially depicted in Illustration 38.


Illustration 38: AdHoc_TopBottomFoodmart Sales.rdl (Partial View), Reflecting Our Modifications

The report's layout has been altered sufficiently to meet the layout requirements as expressed by the information consumers. Let's make one more "elimination:" let's remove the Dataset that supported the Product Family picklist we have all but dismantled from its capacity in the original report.

36.  Click the Data tab.

37.  In the Dataset selector atop the Data tab, (which is now occupied by ProductData, a Dataset created by the simple MDX query to support the sample FoodMart Sales report), select ProductList.

This exposes the simple MDX query that supported the picklist.

38.  Click the Delete Selected Dataset button, shown circled in Illustration 39.


Illustration 39: Deleting the ProductList Picklist Query ...

The Dataset definition is deleted, leaving the ProductData query in its place. In the next section, we will add parameterization to complete the requirements.



Procedure


Create the Dataset to Support the Hierarchical Date Picklist

We will take our next step from our current position on the Data tab, within the Report Designer. Here we will create the Dataset that will serve as the foundation for the Hierarchical Date picklist.

1.  Select New Dataset, as depicted in Illustration 40.


Click for larger image

Illustration 40: Select New Dataset in the Dataset Selector


The Dataset dialog appears.

2.  Type the following into the Name box.

ds_pX_Date_Hier

3.  Type (or cut and paste) the following basic MDX into the Query string box.


SELECT
 
   {RS_pX_Time_Hier} ON COLUMNS,

   {[Time].Members} ON ROWS

FROM 

   [PARAM_SUPPORT_SALES]

The Dataset dialog appears as shown in Illustration 41.




Illustration 41: Completed Dataset Dialog

4.  Click OK to accept our input.

The Dataset dialog closes.

5.  Click the Run button on the toolbar to execute the query, as depicted in Illustration 42.


Illustration 42: Click the Run Button to Execute the Query

The new Dataset appears in the Results pane, below the query, as partially shown in Illustration 43.


Illustration 43: Results Dataset Appears (Partial View)

This Dataset will serve as the support for our parameter picklist, (hence its name, ds_pX_Date_Hier) which we will establish next.

Create the Parameter for the Hierarchical Date Picklist

1.  Select Report -> Report Parameters from the main menu atop the design environment, as depicted in Illustration 44.


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

The Report Parameters dialog appears.

2.  Click Add.

3.  Type the following into the Name box.

pX_Date_Hier

4.  Type the following into the Prompt box.

Period:

5.  Ensure that the Allow null value and Allow blank value checkboxes are cleared.

6.  In the Available Values section, click / select the radio button to the left of From query.

7.  In the Dataset selector to the right, select ds_pX_Date_Hier.

8.  In the Value field selector, select Measures_RS_pX_TimeMSASName_Hier.

9.  In the Label field selector, select Measures_RS_pX_TimePLName_Hier.

10.  In the Default Values section, click / select the radio button to the left of None, if necessary.

The Report Parameters dialog appears as shown in Illustration 45.


Illustration 45: Report Parameters Dialog - with Hierarchical Date Parameter Settings

11.  Leave the Report Parameters dialog open for the next section.

We will next create two parameters to allow the ad hoc selection of top or bottom n products.

Create the Parameters for TopCount and BottomCount Capabilities

We will enable runtime selection of 1) whether the consumer wishes to retrieve top or bottom products, and 2) the number of top or bottom products they wish to retrieve. These capabilities will allow us an opportunity to examine to different types of parameters / prompts.

1.  Within the Report Parameters dialog once again, click Add.

2.  Type the following into the Name box.

pX_OutlierType

3.  Type the following into the Prompt box.

Top or Bottom?

4.  Ensure that the Allow null value and Allow blank value checkboxes are cleared.

5.  In the Available Values section, click / select the radio button to the left of Non-queried.

6.  In the table to the right, type in the two rows depicted in Table 3.

Label

Value

Top

TopCount

Bottom

BottomCount


Table 3: Expression Components Summary

7.  In the Default Values section, click / select the radio button to the left of None.

The Report Parameters dialog appears as depicted in Illustration 46.


Illustration 46: Report Parameters Dialog - with Outlier Type Selector Parameter Settings

8.  Within the Report Parameters dialog, click Add, once more.

9.  Type the following into the Name box.

pX_TopBotNum

10.  Type the following into the Prompt box.

Number:

11.  In the Data type selector, select String.

12.  Ensure that the Allow null value and Allow blank value checkboxes are cleared.

13.  In the Available Values section, click / select the radio button to the left of Non-queried.

14.  Leave the table to the right empty.

15.  In the Default Values section, click / select the radio button to the left of None.

The Report Parameters dialog appears, as shown in Illustration 47.


Illustration 47: Parameters Dialog - Showing Top or Bottom Number Parameter Settings

16.  Click OK to save our additions, and to close the Report Parameters dialog.

We are returned to the Layout tab. Now all that remains to complete parameterization of our new report is the addition of parameter references to the core Dataset.

"Parameterizing" the Core Dataset

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

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

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

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


Illustration 48: Select the ProductData Dataset Again ...

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


SELECT 

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

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

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

FROM

   [SALES]

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

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


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

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


Illustration 50: Results Dataset Appears (Compressed View)

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

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

Replace:

With

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

" +Parameters!pX_OutlierType.Value +"

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

" +Parameters!pX_TopBotNum.Value +"

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

" +Parameters!pX_Date_Hier.Value +"


Table 4: Expression Components Summary

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


Illustration 51: MDX Query in the Query Pane

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


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

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

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

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

Component

Meaning

  ="SELECT

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

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

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


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

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

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

+Parameters!pX_OutlierType.Value + 

and

+Parameters!pX_TopBotNum.Value +  

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

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

{" +Parameters!pX_Date_Hier.Value +"} ON
  AXIS(2) 

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

FROM [SALES]"

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

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


Table 5: Parameterized Expression Components Summary

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

Modifying the Report's Row Axis to Align to Business Requirements

Recall that the information consumers requested, among other changes, that we modify the report to display Product Name in the rows of the report. After removing the drilldown levels that previously existed, we left the report's Matrix Properties dialog with a single group, BrandSales_Brand_Name, although we knew that this was not the field that we ultimately wished to group upon, nor to display. My reasoning here was to leave the group in place, and then return to modify it, and re-point it to the Product Name field (which did not exist in the Dataset, anyway, until several steps after our modification of the groups).

Let's return to the Groups tab of the Matrix Properties dialog to make the first of two final modifications to the report.

1.  Click the Layout tab to return to the Layout of the report.

2.  Click at some point within the label textbox of the report (containing the label Foodmart Sales), as we did earlier, to make the row and column headers of the matrix data region visible.

3.  Right-click the upper left corner of the matrix, once again.

4.  Select Properties from the context menu that appears.

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

5.  Click the Groups tab.

6.  Click the single entry in the Rows list, BrandSales_Brand_Name, to select it.

7.  Click Edit.

The Grouping and Sorting dialog opens.

8.  Replace the current text in the Name box, BrandSales_Brand_Name, to the following:

ProductSales_Product_Name

9.  In the top row of the Group on list, select the following expression:

=Fields!Product_Product_Name.Value

The Grouping and Sorting dialog, containing our modified input, appears as depicted in Illustration 52.


Illustration 52: The Modified Row Group

10.  Click OK to accept changes, and to return to the Groups tab of the Matrix Properties dialog.

The Groups tab appears as shown in Illustration 53.


Illustration 53: The Renamed Row Group Appears

11.  Click OK to close the Groups tab, together with the Matrix Properties dialog, and to return to the Layout tab.

12.  Right-click the textbox containing the current row definition, currently shown as =Fields!Brand_Name.Value,

13.  Select Expression from the context menu that appears, as depicted in Illustration 54.


Illustration 54: Selecting the Textbox Defining the Row Display for Modification

The Edit Expression dialog opens.

14.  Replace the existing expression in the Expression pane of the dialog with the following (you might also select it from the tree on the left):

= Fields!Product_Product_Name.Value

The Edit Expression dialog appears as shown in Illustration 55.


Illustration 55: Edit Expression Dialog, with our Replacement

15.  Click OK to accept our replacement, and to close the Edit Expression dialog.

Execute the Report and Verify Results

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

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

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

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


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

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

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

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

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

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


Illustration 57: Report Preview, Based upon Our Parameter Selection

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

Conclusion ...

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

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

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

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

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