Example on level specific actions based on level names

In this example it demonstrates how to perform certain actions at a specific hierarchy level by comparing the level name. The scenario here wants to change the Grand Total and Sub Total value to 0 but only do this when there is a total value, otherwise if all children are null then change the total value to NULL. The leaf level member should keep its original value.

The screenshot below shows two identical pivot table. The table on the left has Always Display Null Rows enabled so you can see all the null rows. The table on the right has Always Display Null Rows disabled so any rows with all NULLs are hidden.

IIF(
[Product.Product Categories].CurrentMember.Level.Name = "(ALL)" ,
0,
   IIF(
       ( ([Product.Product Categories].CurrentMember.Level.Name = "Category") or
       ([Product.Product Categories].CurrentMember.Level.Name = "Subcategory") )
       and
       IsEmpty( sum( ([Product.Product Categories].CurrentMember.children, [Measures].[Order Quantity])) ),
       null,
       IIF(
           ([Product.Product Categories].CurrentMember.Level.Name = "Product"),
           [Measures].[Order Quantity],
           0
      )
   )
)

This example came from a customer requirement that wold like to show items with zero sales so they have a measure with all members with no sales shows a zero value but all member with sales are null. However this turned most of the sub-totals and grand total also into null so Analyzer would hide the level incorrectly. The MDX above reverts the process by turning all sub-totals and grand total to zero so Analyzer can display its children. If the children are all null then we still keep the value as null to hide the member.