Hi there,

We are looking for a tool that enables drillthrough to a cube with
multiple partitions through an Excel plugin.

Although we are initially looking for an Excel plugin (which supports
drillthrough), in the future would like to consider implementing a web
based thin client (either Java, DHTML, ActiveX) which can support
slice and dice analysis on an MS OLAP tool. We could then use consider
using this tool for developing dash boards etc.

As a pure perfect solution, if we could also buy a reporting tool from
the same vendor that provides web based paper orientated canned
reporting for either MS-OLAP or RDBMS data, this would be great.

Does anyone know of such a tool? I thought we were doing well with
Crystal Analysis until we found that it didn't support drillthrough to
multiple partitions (this was confirmed by Crytal/BO AND they
confirmed they have no plans to address the issue in future releases).

I just can't believe that no one supports drill through result sets
from multiple partiions - seems very strange to me.

As far as drill-through to multiple partitions from Excel, I use an
add-in developed by someone in this newsgroup:

http://groups.google.com/groups?hl=e...8&th=c057dee7f
68b2c67&rnum=1
Subject: Excel XP DrillThrough Sample for Multiple Partitions

View this article only
Newsgroups: microsoft.public.sqlserver.olap
Date: 2003-02-20 22:31:21 PST

The sample Excel XP DrillThrough VBA code (posted to this group last
year) works fine for cubes with a single partition. But multiple
partitions require enhancement to the code , to handle the multiple
recordsets returned (1 per partition). Does anyone have a version that
works with multiple partitions?

Message 2 in thread
From: Thomas (thomasgreuel (AT) hotmail (DOT) com)
Subject: Re: Excel XP DrillThrough Sample for Multiple Partitions

View this article only
Newsgroups: microsoft.public.sqlserver.olap
Date: 2003-02-27 07:12:36 PST

The recordset retrieved from the drill through has data from all
partitions. The data has just to be pasted into the XL sheet by
looping through all the partitions. Assuming that rst is the recordset
you retrieved, the following code will do the job (it might not be the
best implementation but it works.)

If you're interested I can send you a XL add-in that adds drill
through (multiple partitions) functionality to XL 2000 / XP.

HTH

Thomas


'Get data from recordset to worksheet

intNumberPartitions = 0

Do Until Done

On Error GoTo ExitDo
' repeat until all partitions done
intNumberPartitions = intNumberPartitions + 1
' show current partition
Application.StatusBar = "Pasting Partition: " &
CStr(intNumberPartitions)

Dim strPasteArea As String
' determine next free row
strPasteArea = "A" & CStr(Range("a65536").End(xlUp).row + 1)
' paste data
Range(strPasteArea).CopyFromRecordset rst
' next partition
Set rst = rst.NextRecordset

If rst.State = adStateClosed Then Done = True

Loop

ExitDo:


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks for that, but we are looking for a comercial product. Also we
only have Office 2K at the moment, so the code you suggested is no
good to me.

Thanks anyway :-)