The examples in Listing 1 and Listing 2 that show a Filter() function nested in a Generate() function help explain the concept of the Current property, but they're not real-world examples. You can phrase the nested expression more clearly and concisely if you use the Intersect() function, as the following example shows:
Intersect(Descendants
([Customers].[WA], [Customers]
.[Name]) , [Top 10 BY Sales])
So let's look at how you might use the Current property to solve a real-world problem. In "MDX by Example," December 2001, InstantDoc ID 22994, I presented 15 MDX examples that address the problem of sales analysis. Many of these queries require searching the Brand Name level of the FoodMart Sales cube's Product dimension. Because brands are organized into product categories, the same brand name might appear multiple times in the Product dimension. For example, Papa Joe's might make butter and lemonade, but butter and lemonade are in different product categories, so the Papa Joe's brand is duplicated in the Brand Name level. For most sales analysis, this duplication isn't a problem because you usually want to treat brands independently. You don't want the success or failure of Papa Joe's butter to affect whether you carry Papa Joe's lemonade.
But in certain cases, you want to treat all Papa Joe's brands as one item. What if you want to know the total amount of business you do with Papa Joe's? The MDX query in Listing 3 shows how to use a general approach to solve this tricky problem. The query determines the total unit sales for each brand (instead of just one brand), then it displays the brands that make up the top 80 percent of the total unit sales.
The query works by first creating a set called UniqueBrands that contains one brand member for each unique brand name. Then, the query creates a new measure called TotalBrand that sums the unit sales of all brand members that have the same name as the current selected brand.
The formula that determines unique brands uses a Generate() function to step through all the nonunique brand members. For each brand member, a Filter() function finds all the other brands that have the same name. The code then reduces this group of duplicate brand members to the first item, the Head() function. The result is that every time the Generate() function encounters a member that has a name it has encountered before, it converts that member to the first brand member with that name. So, Generate() is converting members that have the same name into duplicate members. Because Generate() automatically eliminates duplicate members, the result of the Generate() function is a set of members that have unique names.
This calculation of unique brands can be slow because some nested loops scan through the full list of brands. The query in Listing 3 is reasonably fast because the FoodMart Sales cube doesn't contain many brands. But if you're developing an application that needs to perform this type of calculation on a large set of items, consider defining the set before running your queries. You can predefine the set by using the CREATE SET command or by using Analysis Manager. You can use Analysis Manager to create a set by right-clicking a cube and selecting Edit to start the cube editor. Then, from the Insert menu, choose Named Set. In the window that appears, enter the name of the set and its formula. All client applications that have access to this cube will also have access to this set. Both approaches prevent the calculation from being evaluated every time you run a query that uses it.
The calculation that determines TotalBrand is similar to the calculation that finds UniqueBrands—with one wrinkle. What you need in this example is the set of all brands that have the same name as the currently selected one so that you can sum the brands that have duplicate names. To get this list of brands, you might write a statement such as the following:
Filter( [Product].[Brand Name].Members AS Temp2Iterator,
[Product].CurrentMember.Name =
Temp2Iterator.Current.item(0).Name )
But this statement doesn't work because as the Filter() function steps through all the brand members, it changes the CurrentMember property of the Product dimension that goes with it. Thus, you lose track of where you were in the list before the Filter() function started. To record the CurrentMember property before starting the Filter() function, you can use the Generate() function to step through a set of one item, which you identify by using the CurrentMember property. Then, you can give CurrentMember an alternate name (I used Temp1Iterator) for use inside the Filter() function.
Generate( {[Product].CurrentMember} AS Temp1Iterator,
Filter( [Product].[Brand Name].Members AS
Temp2Iterator,
Temp1Iterator.Current.item(0).Name =
Temp2Iterator.Current.item(0).Name ))
The examples in this article show the Generate() function's power. Because of the relative immaturity of analytic applications, little of this power is exposed through end-user analysis features. For now, you need to know MDX to tap this power. I'm sure this requirement will change as application developers learn how to expose complex MDX functionality in business terms for users.
To practice creating a calculated measure that predicts unit sales for each month in a given quarter, see the sidebar "February MDX Puzzle." For the answer to January's puzzle, see the sidebar "January MDX Puzzle Solution Revealed."