Sorting or Ranking a DRILLTHROUGH query

Question

Post reply

Sorting or Ranking a DRILLTHROUGH query

Matt Allpress-274863

Old Hand

Points: 395
More actions
September 27, 2006 at 3:53 pm

#174595

Hi,

I have an Excel application that is being used to DRILLTHROUGH into a SQL Server 2005 Analysis Services cube. I want to give users the option to just see the top 20 results from the drillthrough.

To do this I have identified 3 options:

1. By adding some type of rank or sort clause within the DRILLTHROUGH MDX query.

2. By sorting the ADO recordset that is returned, and only outputting the first 20 records.

3. By outputting the entire recordset into Excel, then sorting the values with VBA code.

Currently I am using solution 3, which is the worst solution (very slow). I do not know how to sort/rank within a DRILLTHROUGH MDX, or if it is possible at all, and I am having trouble using the ADO Recordset.Sort in VBA. Does anyone know how to achieve this? Any help is much appreciated.

This is an example of the DRILLTHROUGH statement:

DRILLTHROUGH

SELECT

      {[Product].[Product Summary1].[Dishwasher]} on 0,

      {[Branch].[Branch DivisionName].[Distribution]} on 1,

      {[Product].[Product SDeptName].[Whiteware]} on 2

FROM [StockBalance]

RETURN

[$Branch].[Branch Name],

[Balance].[OH Value]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Pete Dobbs-356510 Valued Member Points: 73 More actions · Answer 1

Pete Dobbs-356510

Valued Member

Points: 73

September 28, 2006 at 9:44 am

#663054

If you want to return the top(n) records, you should use the TopCount function. I'm not an MDX expert, so I'm not going to attempt to explain the syntax but after a quick Google search I found this article which may be of help: http://www.databasejournal.com/features/mssql/article.php/10894_3500586_2

Good luck!

Matt Allpress-274863 Old Hand Points: 395 More actions · Answer 2

Hi, thanks for your response. I'm far from an MDX expert myself, but I'm not sure that the TopCount function can give me what I want. From my understanding, a DRILLTHROUGH statement is executed on a single cell. Because the MDX statement must return a single cell, it is only returning the top 1 records anyway. Perhaps what I am after is not possible, but it would be good to at least have it confirmed!

Thanks, Matt