MDX in Analysis Services: Create a Cube-Based Hierarchical Picklist

Monday Jul 26th 2004 by William Pearson
Share:

Provide MSAS-based picklist support for parameterizing enterprise reports. MSAS Architect Bill Pearson shows how to build picklist support with calculated members, then to use them in a reporting application.

About the Series ...

This article is a member of the series MDX in Analysis Services. The series is designed to provide hands-on application of the fundamentals of MDX from the perspective of MS SQL Server 2000 Analysis Services; our primary focus is the manipulation of multidimensional data sources, using MDX expressions, in a variety of scenarios designed to meet real-world business intelligence needs.

For more information on the series, as well as the hardware / software requirements to prepare for the tutorials we will undertake, please see the first lesson of this series: MDX Concepts and Navigation.

Note: At the time of writing, Service Pack 3 updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples. Images are from a Windows 2003 Server environment, upon which I have also implemented MS Office 2003, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2000 and MSSQL Server 2000 Analysis Services ("Analysis Services" or "MSAS"). The same is generally true, except where differences are specifically noted, when MS Office 2000 and above are used in the environment, with respect to any MS Office components presented in a given article.

Along with MSAS, of which we have made repeated use in the previous articles of the series, additional application considerations apply for this article, because it introduces another Microsoft application, MSSQL Server 2000 Reporting Services ("Reporting Services").

For those joining the series at this point because of a desire to work with Reporting Services and its components from the perspective of authoring or managing reports, it is assumed that, along with Reporting Services (with Service Pack 1), MSSQL Server 2000, Visual Studio.NET and any other appropriate support applications are accessible to / installed on your PC, with the appropriate access rights to the associated environments, to parallel the steps of the article. If this is the first time Reporting Services is being accessed on your machine, you may need to consult the Reporting Services ReadMe files, and any associated online documentation, for installation and configuration instructions. In addition, my Reporting Services series at Database Journal offers a growing body of guidance in various aspects of using Reporting Services, which may be of assistance.

Overview

As most of us who work in the Business Intelligence community are aware, 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 report. These filters 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.

There are two primary types of parameters, type-in and picklist, which can be mechanized through various means. 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.

Every enterprise level reporting system of which I am aware allows parameterization, most in various forms. The mechanics behind parameters differ between them, at least to some degree, but all provide the capability for prompting the consumer for filter information at runtime. The skill and forethought with which parameterization is built into a report is a critical matter, and deficiencies in this arena can ruin the user experience, no matter how capable the underlying system with which we are creating business intelligence applications.

Because it is important to always anticipate consumer desires, I maintain an "inventory" of successful approaches to meeting the "need for user friendliness." I come across such nuances frequently as a BI architect and consultant. In working with MSAS, I have found countless opportunities to "embed" support for such instrumentality at the MSAS level.

In this article, I will provide an option for the support of a picklist that we leverage within the parameters of the reporting environment. After constructing its foundation within the components of the cube, I will show the use of the picklist in Reporting Services, primarily because it is free and readily available to anyone with an MSSQL Server license. (A 120-day evaluation of both Reporting Services and MSSQL Server / Analysis Services can also be downloaded free by anyone with access to the Microsoft site, at the time of writing). It has also been my reporting tool of choice since I began beta-testing it last year. The concepts involved, however, extend to any enterprise reporting package designed to report from common OLAP data sources, and even some of the more proprietary ones, like Cognos, Business Objects, and others that provide "one-way" connectivity to MSAS cubes. It is especially applicable in the cases of tools like Crystal Analysis Pro, ProClarity and other advanced, yet relatively "open," OLAP reporting applications.

If you can successfully designate an MSAS cube as a data source, regardless of the reporting application you have, you can probably use the concepts we will be discussing here. In this article, we will:

  • Create calculated members to support a hierarchical pick list for the reporting environment;
  • Explain the MDX that we use to create the calculated members;
  • House the calculated members in a named set for easy re-use in the report authoring process;
  • Create a very basic report in Reporting Services with our MSAS cube as a data source;
  • Add a parameter to the report, based upon the structures we have created in the cube;
  • Generate the report with run-time parameter to verify its operation.

Create a Cube-Based Hierarchical Picklist

Objective and Business Scenario

In the following sections, we will perform the steps required to create calculated members and a named set within a sample cube, to illustrate cube-level support for an organizational reporting function. Once we have accomplished the simple structural additions to MSAS, we will construct a parameter within a basic report that we have created in Reporting Services. We will base the parameterization of the report upon the calculated members and a named set that we have created in MSAS.

For purposes of our practice procedure, we will assume that information consumers within the Finance department of the FoodMart organization have expressed the need for a single, prompted parameter, based upon geographical location of their stores, within a basic report that focuses on total expense for a given location. While the consumers wish the capability to narrow the report to a view of the expenses of individual stores at any time, they have also expressed that a "nice to have" would be the capability to select, within the same report, upon various other hierarchical levels in location, such as city, state and so forth.

We immediately recognize that such a hierarchical picklist might be valuable in many other reports, and we decide to make the investment in creating the support for this functionality in the MSAS cube. We can then reuse the underlying structure easily in prospective efforts, by simply referencing it in any report we author.

We realize that other parts of the organization, who report from MSAS cubes through various applications, will be able to leverage the structure we provide in this manner: Any application that can use a calculated member / named set can take advantage of these components with ease. Moreover, we can see that the requested selection capability is applicable within other dimensions, as well, and that the concepts involved can be put in place elsewhere.

Obvious benefits are many, and include easier, more consistent reporting (to a degree, perhaps, "managed authoring...") as well as greater functionality centralized within a given report. The 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 at different "rollup," and other, levels.

Considerations and Comments

For purposes of this exercise, we will prepare a copy of the Budget cube in the FoodMart 2000 sample database, which accompanies the installation of MSAS, along with other samples. The "clone" will allow us to leave the original sample cube 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 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, and that the copy of an existing MSAS cube within the FoodMart 2000 database presents no other issues in your environment. After the session, the clone can be deleted or used for another purpose, whatever is convenient.

If the sample database was not installed, or was removed prior to your beginning this article, please see the MSAS documentation, including the Books Online, for the straightforward procedure to restore the database from the archive (.cab) file containing the samples. As of this writing, a copy of the archive can be obtained from the installation CD or via download from the appropriate Microsoft site(s).

Hands-On Procedure

Let's first copy the existing 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 Budget 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 shown in Illustration 2.


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

The Duplicate Name dialog appears.

10.  Rename the new database as follows:

Exp_Finance

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 depicted in Illustration 3.


Illustration 3: Duplicate Name Dialog with Our Input

11.  Click OK to create the clone cube.

The new Exp_Finance cube appears in the tree as shown in Illustration 4.


Illustration 4: The New Cube Appears



Procedure



1.  Right-click the new Exp_Finance cube



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




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_StoreGeogPLName_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, that it has been created to support picklists ("PX"), and that this particular calculated member exists to generate a picklist display name ("PLName") in our reports for the various levels of the Store dimension.

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

Space([Store].Currentmember.Level.Ordinal * 6) + [Store].CurrentMember.Name

The MDX expression above returns, via our calculated member, a display name that we will use to generate the picklists that the information consumers see, as part of 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 1.

Expression

Meaning

Space([Store].Currentmember.Level.Ordinal * 6)

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 six (6).

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

+ [Store].CurrentMember.Name

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


Table 1: 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 depicted in Illustration 7.


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

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_StoreGeogMSASName_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 unique name (the "qualified name") within MDX.

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

[Store].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 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 depicted in Illustration 8.


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

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_ns_PX_GeogStore_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, 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 levels of the Store 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_StoreGeogPLName_Hier],
  [Measures].[RS_PX_StoreGeogMSASName_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 Store levels they wish to appear in the report; the latter name supplies the MDX- "qualified" name required to filter the report appropriately.

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

A confirmation dialog should appear, indicating that Syntax is OK, as shown in Illustration 11.


Illustration 11: Confirmation Dialog Indicates Syntax is Correct

19.  Click OK to close the confirmation dialog.

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

The new named set appears in the tree as depicted in Illustration 12.


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

23.  Click No on the dialog that appears, as shown in Illustration 13; we will not design aggregations at present.


Illustration 13: Click "No" to Designing Aggregations

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

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

25.  Click Close to dismiss the viewer.

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

27.  Close Analysis Services, as desired.

We will leave Analysis Manager opened, as we begin a report in Reporting Services next.

Verification and Use

We can verify the adequacy of our MSAS picklist support structures using the named set (and thus the calculated members that compose it) in a simple report, as we will do in the following steps.

Create the Report Project

First, 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 16.

Click for larger image

Illustration 16: 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 17.

Click for larger image

Illustration 17: 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 list, 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:

MXAS017

The New Project dialog appears, with our addition, as shown 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

We have now created a Report Project, and are ready to proceed with creating a Report File.

Create the Report File

Creating the Report File is straightforward, and handled through the following steps:

10.  Right-click the Reports folder in Solutions Explorer.

11.  Select Add from the context menu that appears.

12.  Click Add New Item from the cascading menu, as shown in Illustration 20.

Click for larger image

Illustration 20: Select Add ---> Add New Item

The Add New Item dialog appears.

13.  Click Report in the Add New Item dialog.

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

MXAS017

The Add New Item dialog appears, as shown in Illustration 21.

Click for larger image

Illustration 21: The Add New Item Dialog - Initial View

15.  Click the Open button at the bottom of the Add New Item dialog.

The design environment opens. We see the Data, Layout and Preview tabs appear. Our report has opened in Data View, as shown in Illustration 22.


Illustration 22: The Design Environment - Data View Tab (Compacted)

The design environment that we see is known as Report Designer. We will construct a very basic OLAP report at this point, primarily to illustrate the use of the components that we assembled in MSAS. For more detail on the Report Designer environment, or on Reporting Services in general, see my MSSQL Server 2000 Reporting Services series at Database Journal.

Create the Data Connection

Our next step is to set up a Data Connection, from which we create a Dataset within our practice example.

1.  Select New Dataset in the Dataset selector at the top of the Data tab, as depicted in Illustration 23.


Illustration 23: Select New Dataset in the Dataset Selector - Data Tab

As soon as we click the New Dataset selection, the Data Link Properties dialog box appears, defaulted to the Connection tab.

2.  Click the Provider tab to select the appropriate provider for our data source.

3.  Select Microsoft OLE DB Provider for OLAP Services on the Provider tab, as shown in Illustration 24.


Illustration 24: Select Microsoft OLE DB Provider for OLAP Services on the Provider Tab

4.  Click Next to move to the Connection tab.

5.  Type the name of the computer housing the targeted MSAS database, FoodMart 2000, in the Data Source box near the top of the Connection tab.

6.  Enter the appropriate security information / settings (I am using Windows NT Integrated security for this lesson in my own environment).

7.  Select FoodMart 2000 in the initial catalog box at the bottom of the tab.

8.  Click the Test Connection button to verify connectivity to the data source.

We receive a message box, indicating a successful test connection, as depicted in Illustration 25.


Illustration 25: We Test Positive for Connectivity

The settings on the Connection tab of the Data Link Properties dialog should resemble those shown in Illustration 26.


Illustration 26: Data Link Properties Dialog - Connection Tab

9.  Click OK to accept the settings we have made, and to close the Data Link Properties dialog.

Report Designer next presents us with the dataset design tool, based upon our newly connected source. We are immediately positioned to design our dataset query, which brings us to the next step.

Creating the Core Dataset

Recall, for purposes of our practice session in this article, that the Finance department has requested a basic expense summary report, which lists various store location's expense totals. The consumers have expressed the need for a single prompted parameter, based upon geographical store location, which allows them to filter on location at runtime. An ideal scenario, they have told us, would include the capability to select, within the same report, various other hierarchical "rollups" surrounding location, such as city, state and so forth.

We will design the basic report, beginning with the unparameterized dataset, prior to adding the parameter. This allows us to gain an appreciation for design first, for, as we will see, addition of the parameter makes dataset refreshment a bit less "elastic."

1.  Type (or cut and paste) the following basic MDX into the Query pane - Data tab of the Report Designer.


SELECT 

  {[Measures].[Amount], 
    RS_ns_PX_GeogStore_Hier} ON COLUMNS,


  {[Account].[All Account].
    [Net Income].[Total Expense]} ON ROWS

FROM 

   [Exp_Finance]

The Query pane appears as shown in Illustration 27.


Illustration 27: MDX Query in the Query Pane

2.  Click the Run button in the toolbar (the "!" icon), shown in Illustration 28, to execute the query.


Illustration 28: The Run Icon

The dataset appears in the Results pane, below the query, as partially shown in Illustration 29.


Illustration 29: Results Dataset Appears (Collapsed, Default View)

3.  Click the Refresh icon in the toolbar (circled in blue, to the left of the Run button), shown in Illustration 28 above, to update the data fields.

NOTE: It is important that this step is accomplished, and that the fields appear in the Fields tab (possibly hidden / undocked), before proceeding.

Creating the Parameter Picklist Dataset

1.  In the Dataset selector atop the Data tab, (which is now occupied by FoodMart 2000, the dataset we just created), select New Dataset, as depicted in Illustration 30.


Illustration 30: Select New Dataset in the Dataset Selector

The Dataset dialog appears.

2.  Type the following into the Name box.

ds_px_LocationHier

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


SELECT 

   {RS_ns_PX_GeogStore_Hier} ON COLUMNS,

   {[Store].Members} ON ROWS

FROM 

[EXP_FINANCE]

The Dataset dialog appears as shown in Illustration 31.


Illustration 31: Completed Dataset Dialog

4.  Click OK to accept our input.

The Dataset dialog closes.

5.  Click the Run icon in the toolbar, to execute the query.

The new dataset appears in the Results pane, below the query, as partially shown in Illustration 32.


Illustration 32: Results Dataset Appears (Partial View)

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

Creating the Parameter

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


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

The Report Parameters dialog appears.

2.  Click Add.

3.  Type the following into the Name box.

px_LocationHier

4.  Type the following into the Prompt box.

Location:

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

6.  In the Dataset selector to the right, select ds_px_LocationHier.

7.  In the Value field selector, select Measures_RS_PX_StoreGeogMSASName_Hier.

8.  In the Label field selector, select Measures_RS_PX_StoreGeogPLName_Hier.

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

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


Illustration 34: Results Dataset Appears (Partial View)

10.  Click OK to accept and save settings, and to close the Report Parameters dialog.

"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 the handling of parameterization of our 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.

This requires only a bit more effort than coding the MDX we have used to create the original dataset; 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.

Let's revisit our initial query, through which we have made the soon-to-be-inhibited designer aware of all the fields we expect to require from 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 FoodMart 2000 dataset in the Dataset selector, as shown in Illustration 35.


Illustration 35: Select the FoodMart 2000 Dataset Again ...

2.  Modify the original FoodMart 2000 dataset query to precisely the following (cut and paste, if helpful):

 ="SELECT {[Measures].[Amount]} ON COLUMNS,
{[Account].[All Account].[Net Income].[Total Expense]} ON ROWS FROM
[Exp_Finance] WHERE(" +Parameters!px_LocationHier.Value +")"

NOTE: Type as if a single line, without "artificially" creating line breaks via the Enter key, etc.

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

Component

Meaning

="SELECT

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

{[Measures].[Amount]} ON COLUMNS, {[Account].[All Account].[Net Income].[Total Expense]} ON ROWS FROM [Exp_Finance]

The corpus of our initial query, sans the store / location dimension component. We will be setting up the capability to filter by geographic location via our hierarchical picklist; because this means that the "qualified" geography dimension will be specified in the WHERE clause (by its nature as a filter), we will remove the reference to the same store location information here.

WHERE(" +Parameters!px_LocationHier.Value +")"

The WHERE clause, a staple of any MDX query (whether explicit or not), enclosing the reference to the value of the parameter - in the present case, the value of the location parameter from the hierarchical picklist, stored in the "qualified" format, which therefore "completes" the WHERE clause.

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


Table 2: Parameterized Expression Components Summary

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


Illustration 36: MDX Query in the Query Pane

NOTE: The Run icon is disabled, as depicted above, once the query is enclosed in double quotes.

Designing the Report Layout

We have put core and parameter picklist generation queries into place, to designate the dataset from which the report will select the data it presents, as well as to facilitate filtering upon specific location within the Store hierarchy of our cube. We now need to turn to an abbreviated physical Layout of the report.

1.  Click the Layout tab.

The Layout tab presents the Design Surface, as depicted in Illustration 37. We will draft a quick blank report, simply to illustrate the operation of our MSAS-based parameter picklist, and associated components.

Click for larger image

Illustration 37: The Layout Tab and Design Surface

2.  Click the Toolbox icon, atop the Report Designer window, (see Illustration 38) to open the Toolbox, as necessary.


Illustration 38: The Toolbox Icon

The Toolbox appears to the left of the Layout tab.

3.  Click the Matrix data region icon, within the Toolbox, as depicted in Illustration 39.


Illustration 39: Toolbox, with Matrix Icon Selected

4.  Click on the design surface (a small matrix icon will appear in place of the usual mouse pointer).

The Report Designer renders a two-columned matrix, straddling the Design Surface, as shown in Illustration 40.


Illustration 40: The Matrix Appears on the Design Surface

5.  Right-click the upper left corner (within the grey "header" area) of the matrix.

6.  Select Properties from the context menu that appears, as depicted in Illustration 41.


Illustration 41: Select Properties from the Context Menu

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

7.  Marry the dataset we created earlier to the matrix data region by selecting FoodMart 2000 in the selector, as shown (circled) in Illustration 42.


Illustration 42: Select FoodMart 2000 in the Dataset Selector - General Tab

8.  Click the Group tab on the Matrix Properties dialog.

9.  Ensuring that the matrix1_RowGroup1 line is highlighted, click the Edit button to the right of the Rows box, as depicted in Illustration 43.


Illustration 43: Editing the Row Group

The Grouping and Sorting Properties dialog appears, defaulted to the General tab.

10.  Change the row group title in the Name box to the following:

matrix1_RowGroup_Location

11.  In the Group On: section, in the top field, select =Fields!Measures_RS_PX_StoreGeogMSASName_Hier.Value, as shown in Illustration 44.


Illustration 44: Selecting the Value for Row Grouping

12.  Click OK to save changes and return to the Matrix Properties dialog, Groups tab.

13.  Click the single item in the Columns box, to select it.

14.  Click Edit to open the Grouping and Sorting Properties dialog for the column group, just as when we worked with the row group.

15.  Change the column group title in the Name box to the following:

 matrix1_ColumnGroup_Acct

16.  In the Group On: section, in the top Expression field, select =Fields!Account_Level_03.Value to populate the field, as depicted in Illustration 45.

Click for larger image

Illustration 45: Selecting the Expense Account as the Column ...

17.  Click OK.

We return to Matrix Properties - Groups tab, where we see the newly added column group for Account, just below the row group we added earlier for Location, as depicted in Illustration 46.


Illustration 46: Our Newly Added Groups...

18.  Click OK to close the Matrix Properties dialog, and return to the Design Surface.

We need to add row, column, and data textbox details next.

19.  Right-click the bottom left textbox in our matrix (the textbox to the left of the box marked Data).

20.  Select Properties on the context menu, shown circled in Illustration 47.


Illustration 47: Select Properties from the Context Menu...

The Textbox Properties page appears.

21.  Change the textbox title in the Name box to the following:

 textbox_Location

22.  Type (versus select) the following:

=Parameters!px_LocationHier.Label

into the Value selector, as depicted in Illustration 48.


Illustration 48: Selecting Row Textbox Details...

23.  Leaving all other textbox settings at default, click OK to accept our selection, and to close the Properties page.

24.  Right-click the top right textbox in the matrix (the textbox immediately above the box marked Data).

25.  Select Properties on the context menu, once again.

The Textbox Properties page appears.

26.  Change the textbox title in the Name box to the following:

       textbox_Account

27.  Select =Fields!Account_Level_03.Value in the Value selector, as depicted in Illustration 49.


Illustration 49: Selecting Column Textbox Details

28.  Again leaving all other textbox settings at default, click OK to accept our selection, and to close the Properties page.

29.  Click the textbox we have just named once to select it.

30.  Click the "Center" icon in the format group on the design environment toolbar, as shown in Illustration 50.


Illustration 50: Centering Label Contents ...

Now let's perform the same binding exercise for the Data box.

31.  Right-click the box marked Data in the matrix (bottom right corner of the matrix).

32.  Select Properties on the context menu, once again.

The Textbox Properties page appears.

33.  Change the textbox title in the Name box to the following:

       textbox_Measures_Amount

34.  Select =Fields!Measures_Amount.Value in the Value selector.

35.  Enclose the selected value with the SUM() function, as follows:

=SUM(Fields!Measures_Amount.Value)

NOTE: The "=" sign must precede all characters assigned to the textboxes.

36.  Under Format, in the right half of the Properties page, ensure that the radio button to the immediate left of the Standard label is selected.

 

37.  Select Currency in the Value selector.

The Properties page appears as shown in Illustration 51.


Illustration 51: Our Completed Properties Page

38.  Again leaving all other textbox settings at default, click OK to accept our selection, and to close the Properties page.

39.  Click the textbox we have just named once, to select it.

40.  Click the "Center" icon in the format group on the design environment toolbar, as we did with the last textbox.

The matrix, with new textbox values partially shown, appears similar to that depicted in Illustration 52.


Illustration 52: Matrix with Textbox Values

There is far more involved than what we have accomplished, obviously, in writing a robust and useful OLAP report in any reporting package. Many more considerations, including a host of formatting nuances, more involved data selection and grouping, and almost certainly more restrictive filtering (such as by time, etc.), as well as others, can come into play. Our intent here is merely to see our MSAS-based parameter support in operation, so we have accomplished enough to conclude with executing and reviewing the report.

If Reporting Services, the new paradigm in enterprise reporting, is of interest to you, see my series devoted specifically to the application at Database Journal. The same concepts will apply, as we have noted, with any enterprise-level reporting package that can access MSAS cubes and calculated members / named sets.

Execute the Report and Verify Results

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

This begins the chain of events surrounding parameterization. The parameter selector box, labeled Location, appears immediately below the Preview tab, indicating initialization of the parameter and the execution of the picklist dataset.

2.  Scroll down the selector, and click / select USA, as shown in Illustration 53.

Click for larger image

Illustration 53: Select USA Level in the Store Location Hierarchy

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

3.  Click View Report to activate the primary dataset query.

The parameter we have chosen (in its "qualified" format) is, in turn, passed to MSAS. The results display quickly, and appear similar to those depicted in Illustration 54.


Illustration 54: Results Dataset, Based upon Our Parameter Selection

4.  Explore using the parameter further, selecting various levels in the picklist to return varying results, as desired.

Conclusion and a Look Ahead ...

In this article, we explored an approach for supporting a hierarchical pick list for the reporting environment. Our focus was the creation of calculated members, housed within a named set, to provide cube-based parameter prompt support. We explored reasons that this might be beneficial, and then created the structures involved, before processing our cube to ready it for reporting.

We created a report within Reporting Services to gain some experience using the parameter picklist components we assembled in MSAS, primarily because it is readily available at no cost to MSSQL Server licensees, and to anyone else via download of a free evaluation copy from Microsoft (in contrast to the prohibitive cost of most other enterprise reporting packages). As we noted, our cube-based parameter support concept will work equally well with any business intelligence solution that supports connectivity to an MSAS cube. We added a parameter in the report, based upon the cube structure we built, and verified its operation by generating the report with a run-time parameter.

In our next article, we will return to Time concepts, and explore scenarios surrounding running totals.

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

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