About the Series ...
This article is a member of the series, MDX Essentials. The series is designed to provide hands-on application of the fundamentals of the Multidimensional Expressions (MDX) language, with each tutorial progressively adding features designed to meet specific real-world needs.
For more information about the series in general, as well as the software and systems requirements for getting the most out of the lessons included, please see my first article, MDX at First Glance: Introduction to MDX Essentials.
Note: Current updates are assumed for MSSQL Server, MSSQL Server Analysis Services, and the related Books Online and Samples. Overview
In this lesson, we will expose another useful function in the MDX toolset, the MeasureGroupMeasures() function. The general purpose of the MeasureGroupMeasures() function is to return a list of all measures within a specified measure group. The MeasureGroupMeasures() function is useful in limiting scope to the member measures from a specified measure group within our queries, MDX scripts, and elsewhere.
MeasureGroupMeasures() can be leveraged in activities that range from generating simple lists to supporting sophisticated conditional and other calculations and presentations. We will introduce the function, commenting upon its operation and touching upon creative effects that we can employ it to deliver. As a part of our discussion, we will:
* Examine the syntax surrounding the function;
* Undertake illustrative examples of the uses of the function in practice exercises;
* Briefly discuss the results datasets we obtain in the practice examples.

The MeasureGroupMeasures() Function
Introduction

According to the Analysis Services Books Online, the MeasureGroupMeasures() function “returns a set of measures that belongs to the specified measure group.” MeasureGroupMeasures() has numerous applications. For example, the function can be leveraged within queries of various types, used to define scopes within MDX scripts, or employed to specify the Target property within Analysis Services Actions. As is the case with most MDX functions, combining it with other functions allows us to further extend its power.
We will examine the syntax for the MeasureGroupMeasures() function after a brief discussion in the next section. We will then explore, from the straightforward context of MDX queries, and within practice examples constructed to support hypothetical business needs, some of the uses it offers the knowledgeable user. This will allow us to activate what we explore in the Discussion and Syntax sections, where we will get some hands-on exposure in creating expressions that employ the MeasureGroupMeasures() function.
Discussion
To restate our initial explanation of its operation, the MeasureGroupMeasures() function, returns the set of measures belonging to the measure group specified by a string expression we supply. MeasureGroupMeasures() can be used for a great deal more than simple list retrieval, as we have intimated. When coupled with other functions or used within MDX scripts, among other applications, we can leverage MeasureGroupMeasures() to support a wide range of analysis and reporting utility.
Let’s look at some syntax illustrations to further clarify the operation of MeasureGroupMeasures().
Syntax
Syntactically, in using the MeasureGroupMeasures() function to return a set of measures, the measure group upon which we seek to apply the function is specified within the parentheses to the right of the MeasureGroupMeasures keyword. The function takes the string expression we supply as its argument, and returns a list of the measures contained within the specified measures group. The general syntax is shown in the following string:
MeasureGroupMeasures(String_Expression)
Putting MeasureGroupMeasures() to work is straightforward. When using the function to return the list of measures belonging to the measure group with which it works, we simply specify, via a string expression within the parentheses of the function, the measure group for which we seek to retrieve the measures list. As an example, within a query executed against the sample Adventure Works cube, for a measure group named Sales Summary, the following pseudo-expression:

MeasureGroupMeasures(‘Sales Summary’)
returns the measures, together with their values, contained within the Sales Summary measure group of the cube. Based upon the very nature of the set of measures that MeasureGroupMeasures() returns, the function lends itself to the role of limiting data returned via the MDX Filter() function (as we shall see within our practice section), among others. It is also easy, for the same reason, to see why it is useful as a scoping mechanism within MDX scripts.
NOTE: For detailed information about the Filter() function, see my article Basic Set Functions: The Filter() Function, within the Database Journal MDX Essentials series.
We will practice some uses of the MeasureGroupMeasures() function in the section that follows. Practice
Preparation: Access SQL Server Management Studio
To reinforce our understanding of the basics we have covered, we will use the MeasureGroupMeasures() function within queries that illustrate its operation. The intent is to demonstrate the use of MeasureGroupMeasures() in a straightforward, memorable manner that efficiently illustrates its operation.
We will turn to the SQL Server Management Studio as a platform from which to construct and execute the MDX we examine, and to view the results datasets we obtain. If you do not know how to access the SQL Server Management Studio in preparation for using it to query an Analysis Services cube (we will be using the sample Adventure Works cube in the Adventure Works DW Analysis Services database), please perform the steps of the following procedure, located in the References section of my articles index: Prepare MSSQL Server Management Studio to Query Analysis Services
This procedure will take us through opening a new Query pane, upon which we will create our first query within the section that follows.
Procedure: Satisfy Business Requirements with MDX
For purposes of our practice example, we will assume that we have received a request for assistance from representatives of our client, the Adventure Works organization. As we have noted in other articles of the series, the Reporting department, a group of client-facing authors and developers, often requests assistance with designing queries to support organizational analysis and reporting efforts. As a part of our relationship with Adventure Works, as well as with other clients, we provide on-site staff augmentation for business requirements gathering and training, as well as combined development workshops / “train the trainer” events.
In a brief discussion with members of the Reporting department, we learn that a need has arisen to craft MDX queries for some new analysis and reporting requirements. First, several requirements have been identified to generate datasets, from the Adventure Works cube, to support OLAP reports that management has requested. The client has implemented the integrated Microsoft BI solution, and, in addition to using Analysis Services as an OLAP data source, they use Reporting Services as an enterprise reporting solution. The MDX we explore together, we are told, will thus be adapted for ultimate use within Reporting Services, in multiple parameterized reports.
The requests relayed by the client representatives evidence a need to filter multidimensional data in a manner that we think might best be served with the MeasureGroupMeasures() function. Once our colleagues provide an overview of the business requirements, and we conclude that MeasureGroupMeasures() is likely to be a key component of the option we offer, we provide the details about the function and its use just as we have done in the earlier sections of this article. We convince the authors that they might best become familiar with the MeasureGroupMeasures() function by examining an introductory example, where we employ the function to generate a straightforward list of measures that are contained within one of the cube’s measure groups. Once the basics are understood, we then propose, we will explore the use of MeasureGroupMeasures() to accommodate a more challenging requirement that the client has proposed.

Full article...