Hi there,

Here is my working drillthrough in MDX
DRILLTHROUGH MAXROW 5000 SELECT FROM mycube WHERE [Sites].[All Sites].[USA],
[Products].[All Candy];
it works fine.

However, once i try to drillthrough the cell with more than one site, the
cube just return an error message said "can not get details for this
aggregation, and check the permission for drillthrough". I already had the
drillthrough enabled on all the dimensions, but it still didnt work......

Here is the MDX i having problem,
DRILLTHROUGH MAXROW 5000 SELECT FROM mycube WHERE [Sites].[All
Sites].[USA], [Sites].[All Sites].[CAN], [Products].[All Candy];

Is it anyway to select more than one site in the sites dimension on a single
drillthrough?

Thanks in advance.

Aaron

You need to do multiple drill-through's with AS 2000:


http://msdn.microsoft.com/library/de.../en-us/olapdma
d/agdrillthrough_8583.asp
from a regular, virtual, or linked cube and retrieves a result set from
the source data for that cell in order to get more detailed information.
...
Drillthrough is not supported for cells that have values based on
expressions such as calculated member cells or ones that are associated
with custom member formulas. This includes cells that display values for
the level members of a mining model dimension.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Thanks for your reply Deepak.

I already did the "drillthrough enable" in all dimensions, but still doesn't
work....



"Deepak Puri" wrote:

Hi Aaron,

What I meant was that, with AS 2000, you have to issue 2 separate
DrillThrough queries and then merge the results. There are more
drill-through options with AS 2005:

Sites].[USA],
[Products].[All Candy]
-- And --
DRILLTHROUGH MAXROW 5000 SELECT FROM mycube WHERE [Sites].[All
Sites].[Can],
[Products].[All Candy]
- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Are there any functions to merge the resultset in MDX? If not, i can write my
own routine to do it...but it probably wont be as efficient as a build-in
function..

AS 2005 will be able to do the queue? but is AS2005 still under beta? Are
there anyway i can get a beta copy?

Thanks.

"Deepak Puri" wrote:

As I know, there is not.


I agree with you.


Very good idea! How about feedback this, as an enhancement request, to
Microsoft directly?

However, I guess there also occur another issues to be solved although it is
planned. For example, different drillthrough settings on different
partitions and executing drillthrough on cells from the different
partitions. Then, separate result sets in different format will be required.
(This is just my opinion)

And... Express version is available.

http://lab.msdn.microsoft.com/express/sql/

If you are a beta tester or subscriber to MDSN, you can download AS2005 beta
fully. Although if not, you could get it at offline events, I guess.

Ohjoo Kwon




"Aaron" <Aaron (AT) discussions (DOT) microsoft.com> wrote

Thanks Ohjoo.

I tried to install the AS2005 express on my win2k box with AS2000 on it. It
makes my AS2000 disappear....i have to uninstall AS2005 express to get my
AS2k back.

I will try to search for a full AS2005 beta somewhere....

Aaron

"Ohjoo Kwon" wrote:

Of couse I dont' know the spefic busines situation you're dealing with but
we've had similar problems so we avoid as much as possible selecting more
than one dimension value in the
same dimension.

That means we sometimes have to put extra fields into the dimension tables
grouping these together so we in reports can select a dimension (another
one - the grouping) and then do drill through. This drill will be done on
all the grouped dimensions.

Another way to go would be to do drillthrough in another tool than AS native
drillthrough such as using AS actins to call a reporting services report.

This should be able to be done using the current technology - as 2000 and
rep s 2000.

Have had this on my list for sometime - will soon find time to look at it.

Several mentioned that this is an alternative to native drill through where
you can have more control over what is supposed to be done. But I admit I
haven't seen any cases.


Aaron" <Aaron (AT) discussions (DOT) microsoft.com> wrote

Hi Michael,

Could you explain little bit more on "using AS actions to call a reporting
services report"? I am new in AS2k as well as report services, so i dont
really understand how it can be done... Maybe you can tell me where to get
more info/documents about report services with anaylsis service.

Thanks.
Aaron

"Michael Vardinghus" wrote:

It is a bit advanced admitted - I haven't the whole solution myself yet

In AS you can define an URL action to activate a reporting services report.
Reporting Services reports can be activated in a browser with
a specific URL statement - each report has its own identification. But from
AS you can bring along parameters to the reporting services report
that controls what zoom this report will be doing.

In AS you in the URL statement need to put in all the current selections as
information along with the report name. The current selections
can be fetched using Dimension.Currentmember.Name (MDX-statement)

However - I'm a little bit uncertain how this currentmember function will
work with multiple selections ... havent' tried that - perhaps someone
else could come with input here ? Would be nice if it gave me all the chosen
values.

Syntax URL example

"http://search.msn.com/results.asp?q="+[Store].Currentmember.name+[Product].
currentmember.name

however - you need to replace the first part with the reporting services
report name and follow the syntax for calling this. Example:

http://localhost/ReportServer?/Compa...&rc:zoom=100&r
c:Parameters=True&rc:HTMLFragment=False&rs:Format= HTML4.0

In this statement you can bring along parameters to control output....

Here's Deepaks answer regarding AS action to Reporting S report (havent
tried it out yet so I cannot exlain that much about it...)

Hi

I desperately need to call an sql report based on a selection int he OLAP
cube. But I have some difficulties getting the logic right...

From AS I can use for instance an URL action to call a reporting services
(or report portal) SQL report and bring parameters along to control the
output of this report.

The parameters I send along could be determined by dimx.currentmembers in
the MDX URL statement....

The problem here isn't the exact values - the problem is the All member ...
where do I do something about this .... do I handle this in the SQL report
.... have some kind
of logic that says to disregard currentmembers when choice is all member ? I
cannot avoid sending dimensions along where All member is chosen ? What
about the
SQL sentence then ?

How would I do that with reporting services / Analysis Services ?

Help will be greatly appreciated...

/Michael


Maybe there's a better solution; but one way would be to build the SQL
query as an expression in Reporting Services, rather than using SQL
query parameters (this method is also used for MDX 2000 queries). In
that case, the "where" clauses could be selectively added, only when the
corresponding report parameter is not "All". For Time dimension example,
expression fragment is like:

" and ReportTable.TimeColumn = " + Parameters!pTime.Value)

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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


"Aaron" <Aaron (AT) discussions (DOT) microsoft.com> wrote