Displaying members with data in parent-child hierarchies

In the previous recipe we saw how to display members without children. The idea was to separate members with direct, fact table values from those whose value is the result of an aggregation. This can be quite a common task when you are trying to validate the calculations.

However, some business scenarios require direct values on non-leaf members. For example, a manager of sales can generate sales too. This feature is supported in the parent-child hierarchy model built into SQL Server Analysis Services. The key properties to be used in this scenario are the MembersWithData and MembersWithDataCaption properties.

Separating just the leaves to get members with data is not good enough in this case, because non-leaf members in parent-child hierarchies can also have direct fact data. Therefore, in addition to leaves, we need to include all non-leaf members with fact data. This recipe shows how to do this.

Getting ready

Start SQL Server Management...