This gives use several challenge's.

1. What is the total amount one level up. With the .CurrentMember property we know where we are. If we combine this with the .Parent property we can calculate the total amount: ([Measures].[Sales Amount],[Date].[Calendar].

**CurrentMember**.

**Parent**) .

2. We want to avoid a division by null, there might be no sales for a specific period. We can use the

**IsEmpty**functionality for that:

WHEN IsEmpty

(

[Measures].[Internet Sales Amount]

)

THEN NULL

3. We want to be sure there is one level up. For the [(All)] level there is no level above. Also a calculation on the [(All)] might not make any sense.

WHEN [Date].[Calendar].CurrentMember.Level Is

[Date].[Calendar].[(All)]

Then 1 // or NULL

If we combine all this together we get:

WITH MEMBER [Measures].[Percentage of Whole CY]

// This Will only give the correct results when

// used together with Date.Calendar hierarchy

// for other hierarchy adjust accordantly

AS

CASE

// Test to avoid division by zero.

WHEN IsEmpty

(

[Measures].[Internet Sales Amount]

)

THEN NULL

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

// you might want replace the 1 with NULL if a percentage

// of the (All) level doesn't make any logical sense.

WHEN [Date].[Calendar].CurrentMember.Level Is

[Date].[Calendar].[(All)]

Then 1 // or NULL

ELSE (

// The actual calculation

[Measures].[Sales Amount]

/

([Measures].[Sales Amount],[Date].[Calendar].CurrentMember.Parent)

)

END

, format_string='0.00%'

// Format it as percentage

SELECT

NON EMPTY

(

{

[Date].[Calendar].[All Periods]

,[Date].[Calendar].[Calendar Year].&[2013]

,[Date].[Calendar].[Calendar Semester].&[2013]&[2]

,[Date].[Calendar].[Calendar Quarter].&[2013]&[3]

}

) ON COLUMNS

,

{

[Measures].[Sales Amount]

,[Measures].[Percentage of Whole CY] }ON ROWS

FROM [Adventure Works]

Since a percentage for All Periods doesn’t make any sense, this one is better:

Have Fun And Till Next Time

## No comments:

## Post a Comment