Sorting or Ranking a DRILLTHROUGH query
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 that I would like to sort by OH Value:
DRILLTHROUGH
SELECT
{[Product].[Product Summary1].[Dishwasher]}on 0,
{[Branch].[Branch DivisionName].[Distribution]}on 1
FROM [StockBalance]
RETURN
[$Branch].[Branch Name],[Balance].[OH Value]
[3415 byte] By [
mta37] at [2007-12-24]
Thanks Deepak,
I'm not sure exactly what details are needed, but hopefully the following helps:
There is a Fact table for stock balances joined to two Dimension tables, a branch dimension (on Branch Number) and a product dimension (on SKU). The value in the Fact table I want to return is OH Value. The DRILLTHROUGH statement above is an example of what I want to return to the user.
If there is anything else I can tell you please let me know.
Thanks, Matt
Hi Matt,
You didn't mention whether you created a degenerate (aka fact) dimension for the fact table, which is required to drill through to that granularity. Anyway, here are some sample Adventure Works queries, modifed from a drillthrough-related thread on Mark Garner's Business Intelligence blog. Note that the "Internet Sales" measure group in AW has an associated [Internet Sales Order Details] fact dimension. The results of this original drillthrough query aren't ordered by [Internet Sales Amount]:
http://mgarner.wordpress.com/2006/06/30/no-drillthrough-queries-in-reporting-services/#comments
>>
DRILLTHROUGH
SELECT
NON EMPTY { [Measures].[Internet Sales Amount]} ON 0 FROM
[Adventure Works] WHERE
([Date].[Calendar Year].&[2003], [Customer].[Customer Geography].[City].&[Calgary]&[AB]) RETURN
[$Customer].[Customer]
,[$Date].[Calendar Year]
,[$Product].[Category]
,[$Product].[Model Name]
,[$Product].[Product]
,[$Product].[Product Line]
,[$Product].[Subcategory]
,[$Promotion].[Promotion]
,[$Sales Reason].[Sales Reason]
,[$Sales Territory].[Sales Territory Region]
,[$Sales Territory].[Sales Territory Group]
,[$Ship Date].[Date]
,[Internet Sales].[Internet Sales Amount]
,[Internet Sales].[Internet Order Quantity]
>>
But this equivalent (except for the addition of the fact dimension key attribute) MDX query is ordered descending by [Internet Sales Amount]:
>>
SELECT
NON EMPTY { [Measures].[Internet Sales Amount], [Measures].[Internet Order Quantity] } ON COLUMNS, Order
(NonEmpty( [Internet Sales Order Details].[Internet Sales Orders].[Internet Sales Order] *
[Customer].[Customer].[Customer] *
[Date].[Calendar].[Date] *
[Product].[Category].[Category] *
[Product].[Model Name].[Model Name] *
[Product].[Product].[Product] *
[Product].[Product Line].[Product Line] *
[Product].[Subcategory].[Subcategory] *
[Promotion].[Promotion].[Promotion] *
[Sales Reason].[Sales Reason].[Sales Reason] *
[Sales Territory].[Sales Territory Region].[Sales Territory Region] *
[Sales Territory].[Sales Territory Group].[Sales Territory Group] *
[Ship Date].[Date].[Date], {[Measures].[Internet Sales Amount]}),
[Measures].[Internet Sales Amount],
BDESC) ON
ROWS FROM
[Adventure Works] where
([Date].[Calendar Year].&[2003], [Customer].[Customer Geography].[City].&[Calgary]&[AB]) >> 