I have created a semi-additive (Last Non-empty) measure in a cube that I am developing. For that measure group I have created a drillthrough action. When I use that drillthrough action it is returning not just one row for each item as I would expect, but one row for each item for each date, as though it is not recognizing that the measure is semi-additive.

The same issue is mentioned in a couple of Microsoft Connect articles from 2009 here and here, but there is no comment on how to resolve the issue, and I've found very little else on this problem.

I'm using SQL Server 2012 Enterprise.

Is there a solution or a work-around for this problem?

  • @paulH, have you had a chance to spend more time on this or test my answer? – GregGalloway Jan 13 '16 at 4:17
  • Your answer looks promising but I haven't been able to do anything with it yet - other priorities have taken me off that work temporarily. I would have preferred something already built in to SSAS but I strongly suspect there is nothing. – paulH Jan 13 '16 at 9:03
  • 1
    @paulH I made this question attract attention by adding a bounty. I'd appreciate it if you could test it in your situation and see if the answer should be accepted – Tom V - Team Monica Jan 13 '16 at 21:34
  • Hi Tom, I appreciate you adding the bounty thanks, and I will get around to testing this as soon as I can and report back, but I'm sure you understand that my work is dictated by business priorities and today the priorities are elsewhere. From what I can tell though the proposed solution sounds like it will work fine for my requirements and I'm sure I'll be able to mark it as an answer. – paulH Jan 14 '16 at 12:20

Assuming it is SSAS Multidimensional you can deploy ASSP and build a rowset action which returns the right rows. The rowset action expression would be something like:

ASSP.GetDefaultDrillthroughMDX(Tail(NonEmpty(Existing [Date].[Date].[Date].Members), 1).Item(0))

To explain that code, we get the existing dates (the dates in context of the current cell) then we filter down to only dates with a non empty value for the current measure (inheriting all the other filters on the current cell) then find the most recent date.

More info on this stored procedure can be found here: http://asstoredprocedures.codeplex.com/wikipage?title=Drillthrough&referringTitle=Home

Note a rowset action can't be the double click drillthrough action in Excel unfortunately. You will have to expose your action when users right click on the cell and choose Additional Actions.

Excel has a double click behavior. Double click performs a drillthrough returning either the default columns or returning the columns you define in the drillthrough action you define for that measure group as default. If I execute a DRILLTHROUGH SELECT statement manually without specifying the columns I want then I will get back the columns defined in the drillthrough action flagged as the default

  • I'll award you even if I couldn't test but I strongly believe this will work. Too bad the op abandoned this and didn't accept yet – Tom V - Team Monica Jan 16 '16 at 12:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.