drillthrough limitations

Topics: User Forum
Coordinator
Sep 8, 2010 at 7:13 PM
deepakp Today at 6:06 PM 

Well unfortunately this has some limitations and is not foolproof:
1. Works only on Dimension.Column Name and doesnt work on Dimension.Hierarchy.Column Name, it expects direct column name referencing.
2. Doesnt work on fixed values of Dimension.Column Name.&[Value] i.e. to take the example above replace [$Date].[Date] with [$Date].[Date].&[1] and it doesnt work.
3. Doesnt work on Measures across Measure Groups, i.e. if your calculated member contains Measures from [Internet Sales] and [Reseller Sales] then this drillthrough doesnt work.
Can these be taken as feature requests?
Thanks,
Deepak

Coordinator
Sep 8, 2010 at 7:20 PM

Deepak-

1. This is a limitation with SSAS drillthrough, not with ASSP, I think. Since every level in a hierarchy is also an attribute, you can just refer to the "Column Name" without the hierarchy name and it will work fine.


2. The [$Date].[Date] syntax is telling it which columns to return. So specifying a unique name for a member is incorrect syntax. Note the RETURN columns syntax here: http://technet.microsoft.com/en-us/library/ms145964.aspx

Instead, specify that [Date].[Date].&[1] in the "where clause". With ASSP, that would be the "tuple" parameter. For example:

ASSP.GetCustomDrillthroughMDX(
    "[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])"
   ,([Date].[Date].&[1])
   ,1000
   )

 

3. Yes. I believe drillthrough only returns the detailed rows from one measure group at a time. So either you have to pick one measure group, or you have to define two rowset actions, one for each measure group. This is an SSAS limitation, I believe.

 

For anything where it's an SSAS limitation that you feel should change, feel free to enter a suggestion on Connect and post the suggestion link back here:
https://connect.microsoft.com/sqlserver/feedback