Knight's Microsoft® Business Intelligence 24-Hour Trainer: Leveraging Microsoft SQL Server® Integration, Analysis, and Reporting Services with Excel® and SharePoint®

160



LESSON 18 Creating CalCulations

The code for this calculation is similar to the previous code, with a few exceptions. The first excep-

tion is that you want to determine if the member you’re looking at is at the All top level. To do this,

you can use the

CurrentMember function to point to the cell you’re looking at presently; the Level

suffix requests the level in the hierarchy that you’re currently viewing in a given cell.

When [Sales Territory].[Sales Territory Hierarchy].CurrentMember.Level Is

[Sales Territory].[Sales Territory Hierarchy].[(All)]

Then 1

You can also use the CurrentMember function to point to the member’s parent for the mathematical

calculation. The complete code is as follows:

Case

// Test to avoid division by zero.

When IsEmpty

(

[Measures].[Sales Amount]

)

Then Null

// Test for current coordinate being on the (All) member.

When [Sales Territory].[Sales Territory Hierarchy].CurrentMember.Level Is

[Sales Territory].[Sales Territory Hierarchy].[(All)]

Then 1

Else ( [Sales Territory].[Sales Territory Hierarchy].CurrentMember,

[Measures].[Sales Amount] )

/

( [Sales Territory].[Sales Territory Hierarchy].CurrentMember.Parent,

[Measures].[Sales Amount] )

End

Year-over-Year Growth

One of the most common calculations is one that compares sales over one period to those over the

same period in the previous year. For example, how do sales in January of 2008 compare to tho ...