September 27, 2006 at 3:53 pm
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]
September 28, 2006 at 9:44 am
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!
September 28, 2006 at 4:09 pm
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
October 4, 2006 at 3:15 pm
Did you work this out Matt?
Steve.
October 4, 2006 at 8:06 pm
Hi Steve,
I also posted the same question on the MSDN forums - I received a workaround that does the job.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=769313&SiteID=1
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply