Drillthrough is a great feature of Analysis Services, but it has a limitation – only cells without calculations covering them can be drilled through. This automatically rules out drillthrough for calculated members in general and calculated measures in particular. This limitation has been a subject to much debate in forums and blogs. It has been logged on connect site as “Enable drillthrough on calculated measures” suggestion, where it gathered strong support. And competition struggles with very similar issues as well.

While there is no universal solution to this problem, in this article we will show how many practical particular cases can be solved in AS2005. Let’s first understand what does it mean to drill through on calculated measure. According to the definition:

“Drillthrough is the operation in which a user specifies a single cell and the Analysis server returns the most detail level data that contributed to that cell.”

For the regular cells it is pretty straightforward to figure out which detailed level data contributed to them, but in case of arbitrary calculation, it can be very tricky. Basically, one needs to reverse engineer the expression of the calculation, find out all the dependencies, and drill through to each one of them. But in case of simple calculations, such dependencies are obvious. Let’s consider the [Internet Gross Profit] calculated measure from the Adventure Works cube. It is defined as

Create Member CurrentCube.[Measures].[Internet Gross Profit]

 AS [Measures].[Internet Sales Amount] - [Measures].[Internet Total Product Cost],
 
Format_String = "Currency";

It is obvious, that [Internet Gross Profit] is computed from two real measures: [Internet Sales Amount] and [Internet Total Product Cost]. But how can we convey this to the engine, since no matter what the expression of the calculated measure is, the DRILLTHROUGH statement is going to fail on it. The trick here is to build a DRILLTHROUGH statement which redirects from calculated measure to the real measures it derived from. Below are the exact steps in order to achieve that:

1. Define new action. Note, that it cannot be “Drillthrough action”, since it won’t even apply to calculated measures. Just a regular action.

2. Set the “Action Target” property to “Cells” – after all drillthrough is done on cells

3. Since the “Target Object” for cells actions is always “All cells”, we will use condition to constrain our action just to the desired calculated measure. In the “Condition” property set the following expression

Measures.CurrentMember IS [Measures].[Internet Gross Profit]

4. “Action Type” needs to be set to “Rowset”, since drillthrough returns rowset.

5. Set the “Caption” to “Drillthrough”, so in UI of the client tool it will show up with this title, and it will be obvious to the user.

6. “Action Expression” is the most important property. Here we need to build the MDX DRILLTHROUGH statement which redirects to two real measures. The MDX DRILLTHROUGH does not allow multiple cells to appear in the SELECT clause. So how will we get two measures ? We will use the fact that both of them belong to the same measure group, and instead of putting them into the SELECT clause, we will send them to RETURN clause. Building custom return clause also has a benefit of having flexibility to choose which attributes will show up in the result of drillthorugh. By default all the granularity attributes of the measure group will be there, but we can include less or more attributes. We also get to decide on the actual content in these attribute columns – whether we want keys, names, unique names etc. Here is how the RETURN clause may look like:

RETURN 
 [Internet Sales].[Internet Sales Amount]
,[Internet Sales].[Internet Total Product Cost]
,NAME([$Date].[Date])
,NAME([$Customer].[Customer])
,NAME([$Product].[Product])
,NAME([$Promotion].[Promotion])
,NAME([$Source Currency].[Source Currency Code])
,NAME([$Sales Reason].[Sales Reason])

In order for drillthrough to address the correct cell, we need to put current cell coordinates into the SELECT clause. One way of doing it would be to write long MDX string concatenation, using <hierarchy>.CurrentMember.UniqueName subexpression for every hierarchy in the cube. However, this is tedious and error-prone approach. There are hundreds of hierarchies, new ones can be added, old ones removed or renamed etc. Instead, we can just call stored procedure which will enumerate all the current coordinates (note that we could’ve done the same with the RETURN clause too).

Analysis Services Stored Procedures Project (ASSP) actually features some sprocs very similar to what we need here, under FindCurrentMembers class. Similar, but not quite what we need. In our particular case, we want to skip measures (because we are hardcoded them), and also for brevity, we need to consider only attribute hierarchies. Below source code achieves just that:

public static string CurrentCellAttributes()
{
    // start with empty string
    string coordinate = String.Empty;
    bool first = true;
    foreach (Dimension d in Context.CurrentCube.Dimensions)
    {
        // skip measures
        if (d.DimensionType == DimensionTypeEnum.Measure)
            continue;

        foreach (Hierarchy h in d.AttributeHierarchies)
        {
            // skip user hierarchies - consider attribute and parent-child hierarchies
            // (parent-child is both user and attribute hierarchy)
            if (h.HierarchyOrigin == HierarchyOrigin.UserHierarchy)
                continue;
            if (!first)
                coordinate += ",";
            first = false;
            coordinate += h.CurrentMember.UniqueName;
        }
    }

    return coordinate;
}

Now, equipped with this powerful stored procedure, we can build the expression for the action:

"DRILLTHROUGH MAXROWS 100 SELECT (" +
ASSP.ASStoredProcs.FindCurrentMembers.CurrentCellAttributes()
+ ") ON 0 FROM [Adventure Works] 
RETURN 
 [Internet Sales].[Internet Sales Amount]
,[Internet Sales].[Internet Total Product Cost]
,NAME([$Date].[Date])
,NAME([$Customer].[Customer])
,NAME([$Product].[Product])
,NAME([$Promotion].[Promotion])
,NAME([$Source Currency].[Source Currency Code])
,NAME([$Sales Reason].[Sales Reason])
"

We are done ! Now if we deploy this action, any client that supports actions (such as Excel 2007 or cube browser), on the right click on cell under [Internet Gross Profit] calculated measure, we will see additional menu item “Drillthrough”, and clicking on it will bring the most detail level data that contributed to that cell.