About the Series ...

This is the sixth article of the series MSSQL Server 2000 Reporting Services. The series is designed to introduce MSSQL Server 2000 Reporting Services ("Reporting Services"), with the objective of presenting an overview of its features, together with many tips and techniques for real-world use. I will also use the column as a vehicle for sharing my conviction in Reporting Services' role as a new paradigm in enterprise reporting. As I advise clients on a more and more frequent basis these days, this is the future in a big way. I hope you will consider my input valuable, and that you will investigate closely the savings and advanced functionality that will soon be available to anyone with an MSSQL Server 2000 (and beyond) license.

For many of the articles in this series, it is assumed that you have prepared security to allow "power user" status in virtually every regard. 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 our series, as well as the Reporting Services Books Online.

Overview

A common requirement of enterprise reporting is the capability for information consumers to filter reports at run time for specific information they need. This is typically managed via parameterization, also known as "prompting," where the filter criteria is requested (and hence the consumer is "prompted") when the report is run. Depending upon the parameter type (the most common are type-in and picklist), the filters are typically enacted when the consumer types or selects a value, or a series of values.

Type-in parameters accept directly typed user input for the value upon which the report is based. Alternatively, the picklist presents a selection of choices to a consumer based upon a static file, a dataset from a larger data source, or through other means. The picklist is often the tool of choice, because of its inherent elimination of typing errors. A well-constructed picklist makes selection easy for the consumer, who is not often pleased with a long scrolling process, or other cumbersome method, as the initial step in generating a commonly requested report.

A further refinement of the picklist parameter type is the cascading picklist. In a cascading picklist scenario, the set of values of one parameter depends upon the value chosen in another, typically previous parameter. For example, the first parameter could present a list of states within which the organization's customers reside. When the consumer selects a state, the set of possible values presented from which to select the second parameter is updated with a list of cities within the chosen state. A third parameter could then display a list of customers within the selected city. The customer name or other ID number could then be used to filter the report to a particular customer. The process of filtering a list of parameter values, based upon a value from a previous parameter, is described as "cascading" (and is also known as "hierarchical" or "dependent").

I have implemented cascading parameters in numerous ways. One of my favorite ways to accomplish any sort of picklist parameterization, and especially applicable here, is to create support objects within the MSAS cube that is used as a data source for the reports under consideration. For an example of implementing support for a hierarchical picklist in this manner, see my Database Journal article Create a Cube-Based Hierarchical Picklist.

In this article, we will provide a basic approach to creating cascading picklists completely within Reporting Services. Subsequent articles will introduce additional approaches and nuances, but our objective here is to isolate and expose the rudimentary concepts. In this session, we will:

• Discuss parameterization in general, and cascading picklists specifically;
• Create a clone of an existing sample report to modify for a hypothetical business need;
• Add several components to the report to support cascading parameters;
• Discuss the MDX queries that underlay the various datasets;
• Emphasize the importance of physical arrangement when parameterizing an MDX query;
• Verify parameterization within the overall operation in the report.

Create Cascading Picklists for an OLAP Report

Objective and Business Scenario

In the following sections, we will perform the steps required to create a cascading picklist parameter for a clone of an existing sample report. Once we have prepared the report for our exercise, we will construct the datasets required to support cascading parameters. We will base our report and parameter datasets on the FoodMart 2000 sample Sales cube that accompanies the installation of MSAS.

For purposes of our practice procedure, we will assume that information consumers within the Marketing department of the FoodMart organization have expressed the need for cascading, prompted parameters, based upon geographical location of their stores, within a basic report that focuses on sales by product. The Marketing team informs us that they already have a report in place with which they are happy, from the perspective of appearance. The existing report, however, contains a simple parameter that allows them to filter the presentation by Product Family, a characteristic that will not be useful in the report that we are being asked to produce. The consumers want the report to combine all Product Families so as to present a consolidated view of Product sales, which they can then filter to present by individual store.

Having been originally created to present product sales, cost and profit, the report also carries a couple of columns that we will remove, Store Cost and Store Profit, for purposes of this simple revenue report.

The requirement for parameterization surrounds geographical store location. The consumers indicate that they want users of the report to be able to select on a given Country within which FoodMart operates, and then select a State (or other province) from a second parameter, whose picklist presents only the states that reside within the country that is selected in the initial parameter. Next, a third picklist will become available for the Cities within the state selected in the second prompt. Finally, the specific Store can be selected from a fourth parameter picklist, which presents only the stores that exist in the city selected in the third parameter.

Obvious benefits are many, and include easier, more consistent reporting, as well as greater functionality centralized within a given report--parameterized reports can be used to retrieve a wider range of information within a single report, meaning a dramatic reduction in the number of individual reports in the library that, in essence, accomplish the same objectives for individual locations.

Considerations and Comments

For purposes of this exercise, we will prepare a copy of the FoodMart Sales sample report that accompanies the installation of Reporting Services, along with other samples. The "clone" will allow us to leave the original sample report in its pristine (or otherwise existing) condition, as we might have saved various settings, structures, and so forth, for referential or other reasons. There will therefore be no need to remember to return and remove settings that we modify for purposes of the lesson, or to otherwise restore the original sample to its previous state. We can simply discard our clone upon the conclusion of our session, or at any convenient time thereafter.

While the cloning process is simple, ensure that you have the authority, access and privileges needed to accomplish the process, as well as a place to store the copy of the sample report outside of its original location. After the session, the clone can be deleted or used for another purpose, whatever is convenient.

If the sample reports were not installed, or if the FoodMart Sales report was removed prior to your beginning this article, please see the Reporting Services documentation, including the Books Online, for straightforward instructions for obtaining the sample files. As of this writing, a copy of the samples set can be obtained from the installation CD or via download from the appropriate Microsoft site(s).

I am selecting an OLAP report here for several reasons. Primarily, I intend to focus on techniques for using Reporting Services for OLAP reporting within the MSSQL Server 2000 Reporting Services as a specific interest area. Having worked with virtually all enterprise-level OLAP tools over the years, I am quite impressed with the OLAP capabilities that have appeared in the first release of Reporting Services. (In many cases, which I try to outline in my articles at appropriate junctures, the functionality of the reporting solutions of the "Big Sisters" is already met or exceeded, for a fraction of the cost).

Another motivation for wanting to deliver articles surrounding OLAP reporting in this exciting new tool is that a vacuum in the existing documentation is both obvious and unfortunate. The first three major books that were rushed to market almost completely ignore OLAP reporting with Reporting Services (one depicted an MDX snippet expression at the end of the book, as if an afterthought) focusing entirely on relational reporting and making 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.)

This is, in my opinion, a serious "undersell" of OLAP reporting (indeed, I spoke with a client representative the other day who stated that her department had reviewed Reporting Services, but rejected it, as it "didn't do OLAP!"). I hope to contribute to making this arena more accessible to everyone, and to share my implementation 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.

Hands-On Procedure

Preparation

Create a Clone Report to Parameterize

Let's first copy an existing report to provide a disposable work environment. When we installed Reporting Services, the sample report files were copied to the PC, with the default installation point being 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

The FoodMart Sales.rdl file appears in the new location.

RS007.rdl

We now have a disposable report that we can use in Report Designer without fear of damaging a potentially useful sample. Next, we will create a project in Visual Studio, from which to work, and then open the report and remove the existing basic prompt to generate a consolidated product sales report for the Marketing group.

Create the Report Project

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

The equivalent on my PC appears as shown in Illustration 4.

The New Project dialog appears. We note that Business Intelligence Projects appears in the Project Types tree, indicating an installation of Reporting Services (the folder was added by the installation of Reporting Services, as it established the Report Designer in Visual Studio .NET).

RS007

The New Project dialog appears, with our addition, as shown in Illustration 6.

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

We have now created a Report Project, and are ready to proceed with opening the new clone Report File.

Modify the Report Clone to Show Consolidated Product Sales

As we have mentioned, the FoodMart 2000 Sales report that we have cloned as RS007 contains a prompt to allow users to filter by Product Family.

The Add Existing Item - RS007 dialog appears.

The Add Existing Item - RS007 dialog appears as depicted in Illustration 9, with the targeted file.

Report File RS007 appears in the Reports folder of the RS007 project.

Report RS007 opens and appears on the Layout tab of the Report Designer, as depicted in Illustration 11.

RS007 executes briefly, and then returns data. We see, atop the Preview tab, the Product Family parameter selector, as shown (expanded) in Illustration 12.

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

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 to this first objective, and to pave the way for the cascading parameters that we will construct next.

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

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.

The ProductFamily parameter is removed from the list.

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 page of the matrix.

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

The remaining reference to the now-deleted parameter is itself deleted. All that remains to tailor the report template to the Marketing department's specifications is to remove the Store Cost and Store Profit columns.

The Layout view of the report now appears as depicted in Illustration 19.

The report is ready to execute, without any limiting filters, so we can examine its new layout.

The report executes, and returns the full list of products, as shown (zoom reduction at 75%) in Illustration 20.

We have produced the core view requested by the Marketing department, and we are ready to build the cascading prompts that will meet their additional specifications.

Add Cascading Parameters

Add Datasets and Parameters

The FoodMart 2000 Sales report that we have modified is supported by a dataset, called ProductData, which is generated by an MDX query.

The MDX query appears, as depicted in Illustration 21.

We would almost certainly remove the Cost measure from the query, as well as the Profit calculated field from the Fields tab (located either atop the report, or within the Dataset dialog of the ProductData dataset), but we will leave all in place for now for purposes of our practice exercise. (Were this a real world report, there are several items that I would modify / eliminate, but they will not interfere with our present purposes).

WHERE ([Store].[All Stores].[USA].[OR].[Portland].[Store 11])

The modified query appears in the Query view of the Dataset tab as shown in Illustration 22.

In adding the WHERE clause, we have sliced the cube by a member of the Store dimension. Although we have been asked to create a parameter that allows information consumers to specify the identity of the store upon which they wish to slice the data at runtime, for the time being we are getting a working, "hard-coded" query into place that does what we wish, before adding parameters.

This is a good practice, as we shall see, because once we add parameters into the mix, we lose the flexibility of being able to generate datasets. Furthermore, to create a cascading picklist scenario, we will need to create a dataset for each level of "drilling" involved in getting to the lowest level member, an individual store, in our present case. Portions of the results are passed from one to the next, with each passed component acting as the parameter for the next subordinate dataset filter. The output of the lowest level dataset, the individual store in our case, forms the "qualified," unique MDX name that is then fed to the WHERE clause of the core dataset. The flow of the process conceptually resembles that depicted in Illustration 23.

As we can see in the illustration above, we will require four separate datasets, in addition to the core dataset (labeled in the illustration), to which we will return after assembling the additional datasets. The core dataset will be modified to insert the parameter output of the added datasets to the WHERE clause of the core dataset. While the order in which the components are created is not rigidly dictated, we will do them in order of intended operation, in an effort to make the steps involved more memorable, and their overall integration in the report clearer.

First, let's clean house further and delete the old parameter picklist dataset, ProductList.

The MDX query for the ProductList dataset appears in the query view.

The ProductList dataset disappears. We are ready to begin the creation of our cascading picklist datasets at this point.

The Dataset dialog appears, defaulted to the Query tab.

ds_pX_Country 

-- RS007-1 MDX Query to Support Country Picklist



WITH



MEMBER



[MEASURES].[Country_Qualified_Name] 



AS



'[Store] .CurrentMember.UniqueName'



MEMBER



[MEASURES].[Country_Report_Name] 



AS



'[Store].CurrentMember.Name'



SELECT 



{[MEASURES].[Country_Qualified_Name],

 

   [MEASURES].[Country_Report_Name]}  ON AXIS(0),



{[Store].[Store Country].Members} ON AXIS(1)



FROM



[Sales]

The Dataset dialog, Query tab appears as depicted in Illustration 27.

We are returned to the design environment, Data tab, where we see the new MDX query appear in the Query view in the upper section of the tab.

The query executes, and returns the dataset depicted in Illustration 29.

Having created the first of four datasets, we follow the same process with three additional datasets, one for each drilldown level of the Store hierarchy.

ds_pX_State 

-- RS007-2 MDX Query to Support State Picklist



WITH



MEMBER



[MEASURES].[State_Qualified_Name] 



AS



'[Store].CurrentMember.UniqueName'



MEMBER



[MEASURES].[State_Report_Name] 



AS



'[Store].CurrentMember.Name'



SELECT 



{[MEASURES].[State_Qualified_Name],

 

   [MEASURES].[State_Report_Name]}  ON AXIS(0),



{[Store].[Store State].Members} ON AXIS(1)



FROM



[Sales]

ds_pX_City 

-- RS007-3 MDX Query to Support City Picklist



WITH



MEMBER



[MEASURES].[City_Qualified_Name] 



AS



'[Store] .CurrentMember.UniqueName'



MEMBER



[MEASURES].[City_Report_Name] 



AS



'[Store].CurrentMember.Name'



SELECT 



{[MEASURES].[City_Qualified_Name],

 

   [MEASURES].[City_Report_Name]}  ON AXIS(0),



{[Store].[Store City].Members} ON AXIS(1)



FROM



[Sales]

ds_pX_Store 

-- RS007-4 MDX Query to Support Store Picklist



WITH



MEMBER



[MEASURES].[Store_Qualified_Name] 



AS



'[Store] .CurrentMember.UniqueName'



MEMBER



[MEASURES].[Store_Report_Name] 



AS



'[Store].CurrentMember.Name'



SELECT 



{[MEASURES].[Store_Qualified_Name],

 

   [MEASURES].[Store_Report_Name]}  ON AXIS(0),



{[Store].[Store Name].Members} ON AXIS(1)



FROM



[Sales]

Be sure, once you have added each dataset, to execute it via the Run button, as we did for ds_pX_Country above, to populate the fields of the dataset and to ensure accuracy of query input. Once the datasets are in place and have been executed, click the Fields tab, and choose each to verify its contents, from the selector shown in Illustration 30.

Our next action is to tie together the datasets of the cascading chain. We will designate ds_pX_Country as the "lead" dataset - it will not require modification, as we shall see. However, each of the remaining ds_pX datasets will require a modification, which we will accomplish in the next steps.

The ds_pX_State dataset opens.

At this stage, we will need to focus upon precision in the modifications we make to our MDX query. In essence, we will be converting it to a string, which will pass parameters to MSAS. First, we will add the parameter reference, and then we will shape the query into a string.

{[Store].[Store State].Members} ON AXIS(1)

{DESCENDANTS({" & Parameters!pX_Country.Value & " }, [Store].[Store State])} on AXIS(1)

NOTE: It is critical to remember that the query must be joined together in a single string - it would stretch to a single, long line if the query view were opened wide enough to contain it. What we must avoid specifically is the presence of artificial line breaks / "carriage returns," created by the Enter key. In addition, absence of the equals ("=" sign at the beginning of the query, which notifies Reporting Services that a string containing an expression follows, or either or both of the opening and closing quotes, will result in failure when we next execute the query.

Once we take the above modification actions, the Run button is disabled. This is a bit cumbersome, as converting the query to a string results in our inability to actually see the query in action going forward, at least with regard to its populating the results pane underneath it. This is why precision is critical - we may not know we have an error until we run the query in the context of the full report. (A consolation, however, is that Reporting Services will inform us of the offending dataset(s) in the event of a failure, helping us to isolate and repair syntactical errors).

We will perform a similar process with the queries returning the ds_pX_City and ds_pX_Store datasets. But first, let's bind the ds_pX_State query to a report parameter, linking it, in the process, to the first member of the "cascading chain," ds_pX_Country.

The Report Parameters dialog appears.

A new parameter with default name appears.

Prompt:

The completed Report Parameters dialog appears as shown in Illustration 35.

Having created the first of four report parameters, we will follow the same process with the three remaining parameters.

Next, we will complete the procedure we performed in the ds_pX_State dataset earlier, and tie together the remaining datasets for City and Store.

The ds_pX_City dataset opens. Here we will again be converting the existing MDX query to a string, which will pass parameters via Reporting Services to MSAS. As we did with the ds_pX_State dataset, we will add the parameter reference, and then we will shape the query into a string.

{[Store].[Store City].Members} ON AXIS(1)

{DESCENDANTS(

  {" & Parameters!pX_State.Value & " }, 

  [Store].[Store City])} on AXIS(1)

The modified dataset appears as shown in Illustration 36.

The presence of the Parameters!pX_City.Value reference binds our modified query to its respective parameter. Keeping in mind the need for the query to be joined together in a single string, we will continue to the modification of the ds_pX_Store dataset.

The ds_pX_Store dataset opens. Here we will again be converting the MDX query to a string, which will pass parameters via Reporting Services to MSAS. As we did with the ds_pX_State and ds_pX_City datasets, we will add the parameter reference, and then form the query into a string.

{[Store].[Store Name].Members} ON AXIS(1)

{DESCENDANTS({" & Parameters!pX_City.Value & " }, [Store].[Store Name])} on AXIS(1)

This action completes the binding of all of our queries to their respective report parameters. We are now ready to verify operation of the cascading parameters we have constructed.

Verification and Operation

Preview the Report

Let's preview operation of the report, supplying parameters as we go, in order to observe the cascading nature of the prompts.

The report parameters appear in the heading area of the Preview tab. The first parameter, Country, appears in an enabled state, because it is the first in the list of report parameters we created as a set.

As soon as USA is selected, the State selector is enabled. When we click the State selector, we see only the states (there are three States in which the FoodMart organization operates) that coincide with our country selection, USA. This is the proper action of a cascading parameter.

Selecting the State parameter enables the City picklist. We note, too, that the cities are, indeed, limited to those in the state of Washington.

Our complete selection using the cascading picklists appears as shown in Illustration 41.

The report appears, as depicted in Illustration 42. (The View Report button is circled for easy identification).

Through the forgoing steps, we have met the requirements of the information consumers within the FoodMart Marketing department. We have provided a cascading, prompted parameter set, based upon geographical location of the organization's stores, within a basic report that focuses on consolidated product revenues. The fact that the Marketing team already had in place a report whose general attributes were satisfactory made our job a bit easier than in a scenario where we might have had to design and create the entire report from scratch, in addition to engineering the cascading parameter components. We had to perform only small modifications to the report, primarily to tailor its presentation to an integrated view of product revenue, before adding the parameterization requested by the intended audience.

Conclusion...

In this article, we explored an approach to providing cascading prompts, to meet the illustrative business requirement of a group of hypothetical information consumers. We initially made a copy of a report that the consumers already liked, and modified the clone to show an integrated view of the revenues associated with the FoodMart products. We then began the creation of the various components involved in supporting cascading picklists.

We mentioned along the way that much of the work we were performing might be reused, provided we built the functionality into calculated members within the cube underlying such a report. We completed the definition of the calculated members involved at the report level, for purposes of our practice exercise, to isolate the rudimentary concepts. We noted other benefits of using parameters, including easier, more consistent reporting, as well as greater centralization of functionality and maintenance; a parameterized report can be used to retrieve a wider range of information, meaning a dramatic reduction in the number of individual reports in the library that, in essence, accomplish the same objectives for individual locations. Finally, after constructing the datasets and report parameters required to support cascading prompt picklists, we executed the report and verified its operation as a whole.

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

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