This project has moved and is read-only. For the latest updates, please go here.

GetCustomDrillthroughMDX and Hidden Dimension

Topics: User Forum
Aug 25, 2016 at 11:30 AM
Edited Aug 25, 2016 at 12:06 PM
Hello.
Thanks for really useful extension. It works great, but I'm struggling with a problem.
I use a GetCustomDrillthroughMDX function in Action expression. It works well until I set the visible property of one of the dimensions used in the tuple to 'false'. Then it simply returns null. Any ideas to solve this?

I'm using ASSP v1.3.7 with unrestricted permissions and SQL Server 2012 (11.0.6537).

Will appreciate any help.
Aug 25, 2016 at 2:09 PM
How are you testing the return value of the function? Have you been tracing with profiler or using a cell calculation?

I used the following cell calculation in a query to see what value was returned from the function when a member from a hidden attribute was used and it appears to be returning the expected drillthrough statement
WITH CELL CALCULATION [Test Drillthrough] 
FOR '(Measures.[Reseller Sales Amount])'
AS  ASSP.GetCustomDrillthroughMDX(
    "[Internet Sales].[Internet Sales Amount]
    ,[Internet Sales].[Internet Total Product Cost]
    ,NAME([$Date].[Date])
    ,NAME([$Customer].[Address])
    ,NAME([$Product].[Product])
    ,NAME([$Promotion].[Promotion])
    ,NAME([$Source Currency].[Source Currency Code])"
   ,([Date].[Calendar].PrevMember, [Customer].[City].[Melbourne])
   ,1000
   )
select 
[measures].[reseller sales amount] on 0 , 
NON EMPTY [Date].[Calendar].[Date].&[20060217]:[Date].[Calendar].[Date].&[20060305] on 1 
from [Adventure Works]
Aug 25, 2016 at 3:25 PM
Thanks for reply, dgosbell.
How are you testing the return value of the function?
In management studio I created mdx query like this:
with member [x] as
ASSP.GetCustomDrillthroughMDX(
         "NAME([$Item].[Item ID]) as [Item ID]"
    ,(
       StrToMember("[Date].[YMD].[Date].&[" + [Measures].[Last Selected Date] + "T00:00:00]")
      ,[FlagValue].[N].&[1]
      ,[Measures].[Item Count]
     )
)
select [x] on 0
from [Cube1]
When [FlagValue].[N] attribute is visible it returns one cell with 'drillthrough select (...) on 0 from [Cube1] return NAME([$Item].[Item ID]) as [Item ID]'.
When I set [FlagValue].[N] Visible property to 'false', deploy changes and run the same query again it returns (null).
Have you been tracing with profiler or using a cell calculation?
Never did this before (not even know how to do it right now actually). I'll try as soon as I will find out how to do it.
Aug 25, 2016 at 10:24 PM
Using WITH MEMBER does not give you the same sort of execution context that you get inside an action which is why you get all the commas. Using a CELL CALCULATION is the best way to test something that you are going to use in an action expression. If you have a working action, but it's producing unexpected results you can run SQL profiler against SSAS while you execute the action from a client like Excel. You should then be able to see the client execute the drillthrough command that it gets back from the action expression.

I just tried setting the AttibuteHierarchyVisible property to False on the [Customer].[City] attribute and the GetCustomDrillthroughMDX still works fine for me so I'm not sure what is going on for you. (even the WITH MEMBER query still returns something) Is that the only change you've made?
Aug 28, 2016 at 10:52 AM
Sorry for the late answer.
Just tried the same setup as yours and still get the same result -- null.
And yes, this is the only change I've made (here's screen record: http://sendvid.com/0udeglrm).
Sep 1, 2016 at 11:26 PM
Hmm, I wonder if this is a new bug. You are on a new build than me. I've been testing with 11.0.6020.0 and the same procedure works on that server.