A tuple such as ([Reseller Sales Amount]), which uses an explicit member name from one dimension, is very common in MDX expressions . A tuple such as ([Reseller Sales Amount],[2010]), which uses explicit member names from two or more dimensions, is less common . Usually, when you explicitly include members from more than one dimension in a tuple, you use functions that operate on the current member of a hierarchy to retrieve another member in the hierarchy . This gives your MDX expression greater flexibility, like an Excel formula that retrieves values from a different row or column .
Suppose you want to calculate what percent each product or product category contributes to the total sales for all products . This expression would have two component values: a numerator (Sales for the current product) divided by a denominator (Sales for All Products) . The numerator will change from product to product, but the denominator always retrieves the member from the All level of the product dimension .
In this procedure, you’ll create a calculated member that displays the percentage that a category contributes to the total sales for all products .
Create a percent of total calculation
1. In the Cube Designer, click the Calculations tab and then click the Reconnect button . 2. In the Script Organizer, select Reseller Avg Sales and then click the New Calculated
Member button on the Calculations toolbar .
3. In the Calculated Member Editor, change the name to [Product % of Total] . 4. Enter the following MDX expression in the Expression text box .
Note You can drag items from the Calculation Tools pane to make creating this ex-pression easier, or you can copy the exex-pression from the file C:\Microsoft Press\Analysis Services 2008 SBS\Chapter 06\MDX\Product % of Total .txt .
([Measures].[Reseller Sales Amount])/
([Measures].[Reseller Sales Amount],[Product].[Product by Category].[All])
The tuple in the numerator is going to retrieve Reseller Sales Amount in the context of the rows, columns, and filter of your report . The denominator is also going to retrieve Reseller Sales Amount, but it will ignore any member of the Product By Category hi-erarchy that appears on the report . Instead, it will retrieve the value of Reseller Sales Amount from the All member of Product By Category .
5. Select “Percent” from the Format String list .
6. In the Non-Empty Behavior list, select Reseller Sales Amount and click OK .
7. In the Associated Measure Group list, select Reseller Sales . You can group related mea-sures and calculated members in folders by giving each the same value for the Display Folder property .
8. In the Display Folder text box, type Product % . The Calculated Member Editor should look similar to the following image .
Calculating the relative contribution to the total is a comparatively easy calculation to create in a spreadsheet . In every cell, the denominator is anchored to the cell that contains the to-tal . Creating a formula that calculates a value’s contribution to the value of its parent is more difficult . For dates, you can create a formula that divides month values by quarter subtotals and then copy this formula to all the month cells . You could also create a formula that divides quarter subtotals by the year total . Then you could copy the set of cells for the months and quarters and paste it into the cells for all the other years . This exercise is not too difficult, because at every level a parent has the same number of children . But if the number of chil-dren varies from one parent to the next, creating the percent of parent spreadsheet report becomes very tedious .
If you could create a formula that divides a value by the value of its parent, you could use a single formula in all the cells of the report . A spreadsheet, however, has no conception of a hierarchy . Analysis Services is aware of hierarchies, and one of its remarkable strengths is the ability of expressions to use the hierarchical relationships in dimensions .
In this next procedure, you’ll create a calculated member that displays the percentage that a member of the Product Category hierarchy contributes to its parent’s sales .
Create a percent of parent calculation
The Product % of Parent calculated member that you need to create is very similar to the Product % of Total calculated member that you have already created . Unfortunately, you can’t copy a calculated member when the Calculated Member Editor is displayed . So in this procedure, you will switch to the Script View of the Calculations tab, copy the Product % of Total calculated member, and then modify it to become Product % of Parent .
1. On the Calculations toolbar, click the Script View button to open the Script Editor .
The Script Editor replaces the Calculated Member Editor . In the Script Editor, you see the complete MDX statements used to create each of the calculated members . Each calculated member statement begins with CREATE MEMBER and ends with a semicolon (;) . In the Script Editor, you can copy the Product % of Total calculated member and then modify it .
Important The first statement in the Calculation Script is CALCULATE . Don’t delete or comment out this statement . If you do, you will not be able to retrieve any data from your cube .
2. Select the statement that creates the Product % of Total calculated member .
3. Copy the statement and then paste it at the end of the calculation script .
Tip The Calculation Script will be much easier to read if you enter a blank line between each statement . MDX ignores white space, so it is a best practice to employ spacing and indenting for readability .
4. Change the name of the calculated member to Product % of Parent .
5. In the denominator of the expression, change [Product] .[Product by Category] .[All] to [Product] .[Product by Category] .CurrentMember .Parent . The code for the Product % of Parent calculated member should look like this:
Now the denominator of the formula will be equal to the value of the parent of the current member of the Product by Category hierarchy . For example, if the Mountain Bikes subcategory is on rows, the value of the denominator will be the value for the Bikes category .
This expression works well for all members of the Product by Category hierarchy except the All member . Because the All member belongs to the top level of the hierarchy, it does not have a parent and the Product % of Parent calculated member will return an
error . You need to change the expression in the create member statement to first test whether the current member of the Product by Category hierarchy is the All member . 6. In the calculated member, change the expression from
([Measures].[Reseller Sales Amount])/
([Measures].[Reseller Sales Amount],[Product].[Product by Category].CURRENTMEMBER.
PARENT)
to the following:
IIF([Product].[Product by Category].CurrentMember IS [Product].[Product by Category].[All]
,1
,([Measures].[Reseller Sales Amount],[Product].[Product by Category].
CurrentMember)/
([Measures].[Reseller Sales Amount],[Product].[Product by Category].
CurrentMember.Parent) )
Note You can drag items from the Calculation Tools pane to make creating this ex-pression easier, or you can copy the exex-pression from the file C:\Microsoft Press\Analysis Services 2008 SBS\Chapter 06\MDX\Product % of Parent .txt .
The code for the Product % of Parent calculated member should now look like this:
The first parameter of the IIF function determines whether the current member of Product by Category is the All member . If it is, the function returns a 1; otherwise, it returns the percent of parent value .
7. Click the Check Syntax button . If your calculation script contains a syntax error, you will not be able to switch back to the Calculated Member editor .
8. In the Check Syntax dialog box, click OK . If you received a syntax error that you cannot resolve, you can replace the new CREATE MEMBER statement with the contents of the C:\Microsoft Press\Analysis Services 2008 SBS\Chapter 06\MDX\Product % of Parent Complete .txt file .
9. Click the Form View button .
You can see the new calculated member in the Calculated Member editor .
You must deploy the AdventureWorks SSAS project before you can browse the cube and see the two new calculated members .
Deploy and browse the cube
1. On the Build menu, select Deploy AdventureWorks SSAS .
2. After the cube has been deployed and processed successfully, click the Browser tab of the Cube Designer and then click the Reconnect button on the Browser tab toolbar . 3. Remove Reseller Count and Reseller Avg Sales from the report .
4. In the Metadata pane, expand the Product % folder and add the Product % of Parent and Product % of Total calculated members to the Report pane totals area . The re-port shows that Mountain Bike sales ($26,492,684) are 39 .96% of parent Bike sales ($66,302,382) and 32 .93% of total product sales ($80,450,597) .
You may want to verify the functionality of these two calculated members by expand-ing one of the Bike subcategories and reviewexpand-ing the Product level values .