Removing duplicates from OLAP

Topics: Resolved
May 18, 2010 at 7:07 PM

Hi guys, I stumbled across your OLAP Pivot tool while looking for an answer to what should be a simple question... at least I thought so.  Apologies in advance for any noobishness, I'm a developer but new to MDX in general and certainly stuff like this.

We have an OLAP SSAS cube that our DBA made on SQL2k8 and created a linked Pivot table to.  For simplicity/relevance sake, lets just say that the pivot table has two rows "Company X", "Company Y"  and two columns, "Past Due" and "Total Sales".

So cell A1 is showing us Company X, Past Due and B1 is showing Company X, Total Sales.  The way the dimensions are working on the cube, a drillthrough on "A1" is showing us all the values which make up the "Past Due" total for Company X.  Peachy.  But it is also showing us "0"s for companies which DONT have a past due balance... because these companies are included in the dimension which has total sales.

What I would like to do, and am trying to use your tool to accomplish, is to either change the drillthrough on the existing column using MDX to only pull distinct results for column A, or else, create a new calculated value which only has distinct values from the past due column and then define a drillthrough for it.  I got halfway down the second path and then hit a wall - apparently you cant define a "show detail" / drillthrough action for a calculated field?  

Is that last bit correct, or am I just not following how to do it?  Is there an easier way?  Am I trying to use this tool for a purpose it was never intended for? 

Thanks in advance for any help.

Coordinator
May 19, 2010 at 6:20 AM

Unfortunately, I don't think OLAP PivotTable Extensions is going to be able to help you. Drillthrough is something you define as actions in the cube. It's not something OLAP PivotTable Extensions controls.

I would honestly post this as a question in the Analysis Services forum:
http://social.msdn.microsoft.com/forums/en-US/sqlanalysisservices/threads/

You might also look at this as a way of building custom drillthrough on a calculated measure.
http://asstoredprocedures.codeplex.com/wikipage?title=Drillthrough

 

Marked as answer by furmangg on 6/10/2014 at 3:37 PM
May 19, 2010 at 6:45 AM

Thanks fur, I know OLAP is defined in the cube... I should probably have clarified further (didnt want to clutter the post) our DBA doesnt want to change the drillthrough because 99% of users love it the way it works, just my boss is unhappy with it and is therefore making my life heck until I change his copy of the Excel sheet to something he can understand.

The second link, at a glance, looks a lot more what I was gunning for - I was hoping this tool (or this tool in conjunction with some slick coding) could get me either an "overlay" on the existing drillthrough (doesnt sound possible from everything I'm reading) or else a custom drillthrough on a calculated field (I've got the calculated field showing up perfect thanks to OLAP PTE). 

Long and short, I'll peruse that second link with fresh eyes in the AM - I greatly appreciate that steer, looks like that might get me what I need.

Thanks again for taking the time to reply!!