MSSQL Server Reporting Services: Mastering OLAP Reporting: Cascading Prompts

Tuesday Jul 27th 2004 by William Pearson
Share:

Provide cascading picklist support for parameterizing enterprise OLAP reports. MSAS Architect Bill Pearson provides hands-on practice building cascading parameters into a sample report to meet an illustrative business need.

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.

Important: For information concerning the applications to which you will require access to benefit the most from our series, please see our initial Database Journal article, A New Paradigm for Enterprise Reporting.

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

1.  Locate the sample reports.

2.  Right-click the FoodMart Sales.rdl file.

3.  Select Copy from the context menu that appears, as shown in Illustration 1.


Illustration 1: Select Copy from the Context Menu

4.  Navigate to a convenient location to place the copy.

5.  Right-click within the folder chosen.

6.  Select Paste from the context menu that appears, as shown in Illustration 2.


Illustration 2: Select Paste from the Context Menu

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

7.  Right-click the file once again.

8.  Select Rename from the context menu that appears, as shown in Illustration 3.


Illustration 3: Select Rename from the Context Menu

9.  Rename the file as follows:

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.

1.  Click Start.

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

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


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

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

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


Illustration 5: Selecting a New Project

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

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

6.  Click Report Project in the Templates list.

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

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

RS007

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


Illustration 6: 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 7.


Illustration 7: The New Project Appears in the Solution Explorer

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.

1.  Right-click the RS007 project in the Solution Explorer.

2.  Click Add.

3.  Select Add Existing Item, as shown in Illustration 8.

Click for larger image

Illustration 8: Select Add Existing Item ...

The Add Existing Item - RS007 dialog appears.

4.  Navigate to the location of the RS007 report file created earlier.

5.  Select the Report File in the dialog.

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


Illustration 9: Add Existing Item - RS007 Dialog

6.  Click Open (or double-click the selection) to close the dialog.

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

7.  Right-click RS007.rdl in the Reports folder.

8.  Select Open, as shown in Illustration 10, from the context menu that appears.


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

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


Illustration 11: RS007 Appears in Report Designer - Layout Tab

9.  Click the Preview tab to execute the report.

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


Illustration 12: RS007 Appears in Preview (Partial View) - with Parameter Selector Expanded

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

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

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.


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

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

13.  Select Report --> Parameters from the main menu atop the Report Designer, as shown in Illustration 14.


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

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


Illustration 15: 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.

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

15.  Click the Remove button underneath the Parameters list.

The ProductFamily parameter is removed from the list.

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

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

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

19.  Select Properties from the context menu that appears, as shown in Illustration 16.


Illustration 16: Accessing the Matrix Properties

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

20.  Click the Filters tab.

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

22.  Click the Delete button to delete the reference to the parameter, as indicated in Illustration 17.


Illustration 17: Select and Delete the Parameter Reference

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.

23.  Select the Store Cost data textbox in the Matrix Properties dialog, as shown in Illustration 18.


Illustration 18: Select the Store Cost Data Textbox (Partial View)

24.  Press the DELETE key on the PC to eliminate the Store Cost column and its contents.

25.  Perform the same deletion process for the Store Profit data textbox, removing the corresponding column.

26.  Grabbing the right side of the design surface, adjust the bare portion to the right side of the now narrower report form by dragging.

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


Illustration 19: The Newly "Narrowed" Report - "Layout" View

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

27.  Click the OK button to close the Matrix Properties dialog.

28.  Click the Preview tab again, to execute the report.

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


Illustration 20: The Unfiltered, Consolidated View of Products (75% Zoom)

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.

1.  Click the Data tab of the Report Designer.

2.  If necessary, select ProductData in the dataset selector just underneath the Data tab.

The MDX query appears, as depicted in Illustration 21.

Click for larger image

Illustration 21: The MDX Query Underlying the ProductData Dataset

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

3.  Add the following WHERE clause at the end of the MDX query:

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.


Illustration 22: The MDX Query with our Added WHERE Clause

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.


Illustration 23: Cascaded Datasets "Assemble" the "Qualified" Name ...

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.

4.  Click the Data tab, if necessary.

5.  Select the ProductList dataset in the Dataset selector atop Data tab.

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

6.  Click the Delete icon once ProductList is selected, as shown in Illustration 24.


Illustration 24: Select and Delete the ProductList Dataset

7.  Click Yes on the confirmation message box that next appears, as depicted in Illustration 25.


Illustration 25: Click Yes to Confirm Deletion ....

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

8.  Select <New Dataset ...> in the Dataset selector, as shown in Illustration 26.


Illustration 26: Select <New Dataset> ....

The Dataset dialog appears, defaulted to the Query tab.

9.  For the dialog boxes shown in Table 1, type the corresponding items:

In this Dialog Box:

Type the following:

Name:

ds_pX_Country 

Data source:

FoodMart 2000

Command type:

Text

Query string:


-- 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]
Table 1: ds_pX_Country Dataset Details

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

Click for larger image

Illustration 27: Completed Dataset Dialog - Query Tab

10.  Click OK to accept our input and close the Dataset dialog.

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.

11.  Execute the query by clicking the Run button, as shown in Illustration 28.


Illustration 28: Execute the Query with the Run Button ....

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


Illustration 29: The Returned Dataset

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.

12.  Create the following datasets:

  • ds_pX_State
  • ds_pX_City
  • ds_pX_Store

using the corresponding details contained within Tables 2, 3 and 4 below.

In this Dialog Box:

Type the following:

Name:

ds_pX_State 

Data source:

FoodMart 2000

Command type:

Text

Query string:


-- 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]
Table 2: ds_pX_State Dataset Details

 

In this Dialog Box:

Type the following:

Name:

ds_pX_City 

Data source:

FoodMart 2000

Command type:

Text

Query string:


-- 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]
Table 3: ds_pX_City Dataset Details

 

In this Dialog Box:

Type the following:

Name:

ds_pX_Store 

Data source:

FoodMart 2000

Command type:

Text

Query string:


-- 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]
Table 4: ds_pX_Store Dataset Details

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.


Illustration 30: Datasets Appear on Fields Tab

TIP:

In setting up picklist datasets for Reporting Services, I often like to leverage MSAS and take advantage of the cube structure to store the definitions instead of building the datasets as shown. This can be done through the creation of calculated members in MSAS, which are constructed to contain the MDX queries that define the datasets. The obvious benefit is the always-welcome reusability factor; if we create calculated members to contain queries that we reference frequently in our reports, we need only reference each member after typing the code once.

This also promotes consistency, and helps us to avoid errors. Named sets can act as a good complement to this approach, allowing us to go a step further and group the calculated members into a single reference that we can pull into a report axis easily. Other, perhaps less obvious benefits can also accrue to the innovative user. (This is another example of a technique that can be accomplished within the MSAS / Reporting Services combination, but which would be difficult or impossible in other enterprise OLAP reporting packages).

For examples along these lines, see my Database Journal article Create a Cube-Based Hierarchical Picklist, which focuses on the MSAS side of the subject.

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.

13.  Click the Data tab once again, as required.

14.  Select ds_pX_State from the dataset selector, as depicted in Illustration 31.

Click for larger image

Illustration 31: Select ds_pX_State from the Dataset Selector

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.

15.  Remove the comment line at the top of the query.

16.  In the query, change the ON AXIS(1) - the ON ROWS line - as follows:

Original Line:

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

Change to:

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

The modified dataset appears as shown in Illustration 32.


Illustration 32: Modified Dataset for ds_pX_State (Partial View)

17.  Pull the query together into a single string, adding a =" (an equals sign and open double quotes combination) to the beginning, and a " (close double quotes symbol), as shown in Illustration 33.


Illustration 33: Make the Query a Single String

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.

18.  Select Report --> Report Parameters from the main menu, as depicted in Illustration 34.


Illustration 34: Select Report Parameters ...

The Report Parameters dialog appears.

19.  Click Add to begin a new report parameter.

A new parameter with default name appears.

20.  For the dialog boxes shown in Table 5, type the corresponding items (replacing defaults where required):

In this Dialog Box:

Type the following:

Name:

pX_Country

Prompt:

Country:

Data type:

String (default)

Available Values:

From query

Dataset:

ds_pX_Country

Value field:

Measures_Country_Qualified_Name

Label field:

Measures_Country_Report_Name

Default Values:

None (default)

Table 5: pX_ Country Parameter Details

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


Illustration 35: Completed Report Parameters Dialog ...

21.  Click OK to accept input and close the dialog.

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

22.  Create the following report parameters:

  • pX_State
  • pX_City
  • pX_Store

using the corresponding details contained within Tables 6, 7 and 8 below.

In this Dialog Box:

Type the following:

Name:

pX_State

Prompt:

State:

Data type:

String (default)

Available Values:

From query

Dataset:

ds_pX_State

Value field:

Measures_State_Qualified_Name

Label field:

Measures_State_Report_Name

Default Values:

None (default)

Table 6: pX_ State Parameter Details

 

In this Dialog Box:

Type the following:

Name:

pX_City

Prompt:

City:

Data type:

String (default)

Available Values:

From query

Dataset:

ds_pX_City

Value field:

Measures_City_Qualified_Name

Label field:

Measures_City_Report_Name

Default Values:

None (default)

Table 7: pX_ City Parameter Details

 

In this Dialog Box:

Type the following:

Name:

pX_Store

Prompt:

Store:

Data type:

String (default)

Available Values:

From query

Dataset:

ds_pX_Store

Value field:

Measures_Store_Qualified_Name

Label field:

Measures_Store_Report_Name

Default Values:

None (default)

Table 8: pX_ Store Parameter Details

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.

23.  Click OK to close the Report Parameters dialog, if it remains open.

24.  Once the above report parameters have been created, click the Data tab once again, as required.

25.  Select ds_pX_City from the Dataset selector.

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.

26.  Eliminate the comment line atop the existing query.

27.  In the query, change the ON AXIS(1) - the ON ROWS line - as follows:

Original Line:

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

Change to:

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

The modified dataset appears as shown in Illustration 36.


Illustration 36: Modified Dataset for ds_pX_City

28.  Pull the query together into a single string, adding a =" (an equals sign and open double quotes combination) to the beginning, and a " (close double quotes symbol), as shown in Illustration 37.


Illustration 37: Make the Query a Single String

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.

29.  Select ds_pX_Store from the Dataset selector.

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.

30.  Eliminate the comment line atop the query, once again.

31.  In the query, change the ON AXIS(1) - the ON ROWS line - as follows:

Original Line:

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

Change to:

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

32.  Pull the query together into a single string, adding a =" (an equals sign and open double quotes combination) to the beginning, and a " (close double quotes symbol).

The modified dataset appears as shown in Illustration 38.


Illustration 38: Modified Dataset for ds_pX_Store

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.

1.  Click the Preview tab of the Report Designer.

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.

2.  Select USA in the selector to the immediate right of the Country label, as depicted in Illustration 39.

Click for larger image

Illustration 39: Select USA in the Country Parameter Selector

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.

3.  Select WA in the selector to the immediate right of the State label, as depicted in Illustration 40.


Illustration 40: Selecting WA from the Three USA States

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

4.  Select Seattle from the City selector.

5.  Select Store 15, the only Store that appears in the Store picklist corresponding to Seattle.

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


Illustration 41: Our Compete Selection via Cascading Picklists

6.  Click the View Report button, atop the Preview tab, and to the right of the parameter selectors.

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


Illustration 42: The Report Appears .... (75% Zoom)

7.  Exit the Report Designer environment and Visual Studio.NET, saving report file and project when prompted, when desired.

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.

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