Do I need to set a Drillthrough action to have Reporting Services perform a drillthrough?

Hi,

Do I need to set a Drillthrough action to have Reporting Services perform a drillthrough? Or are they two separate things?

To be clearer, is the point of the Drillthrough action (or any of the actions) only for the Cube Browser is AS2005 or are they used elsewhere, i.e. Reporting Services?

Thank you.

Gumbatman

I was also curius on whether reporting services support drillthrough or not, and found the following link. I guess, they don't.

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126175

P.S. You don't mandatory need to have an DT action to use a Drillthrough statement.

|||

Irinia,

Thank you for the information. It is a great help. I was pulling my hair out trying to find an answer.

-Gumbatman

|||

A few clarifications:

- Drillthrough and other actions are often used in applications beyond just the AS 2005 cube browser.

- This MSDN paper describes the configuration of a drillthrough action:

http://msdn2.microsoft.com/en-us/library/ms345125.aspx

>>

Enabling Drillthrough in Analysis Services 2005

...

Analysis Services 2005 introduces a new action type called Drillthrough. The target of a drillthrough action can only be cells in the cube. The MDSCHEMA_ACTIONS schema rowset exposes these as rowset actions. The action expression is a DRILLTHROUGH statement that can be executed by the client application and the resulting rowset displayed to the user.

Clearly drillthrough fits in very cleanly into the actions framework. But the real advantage of drillthrough actions is that it provides the cube designer with the ability to pre-define the return columns of the DRILLTHROUGH statement (Figure 2). This is analogous to the Analysis Services 2000 experience where the database administrator specifies the tables and columns in the Drillthrough Options dialog in Analysis Manager.

...

>>

- Reporting Services has its own concept of "drillthrough" within a report (see paper excerpt below), so I assume that you're only referring to reporting the results of an Analysis Services drillthrough query.

- Though the Analysis Services Provider doesn't support AS drillthrough queries, the older OLE DB Provider still does, as mentioned in this OLAP newsgroup thread:

http://groups.google.com/group/microsoft.public.sqlserver.olap/msg/4870097ed8fcffda

>>

Message from discussion Linked Server to Analysis Services 2005 gets Access denied.

How about trying the OLE DB option? I couldn't get the following AW
Drillthrough query to work with the RS 2005 Analysis Services Provider;
but it returned 8 records when I used OLE DB for OLAP 9.0 instead:

Drillthrough
Select [Ship Date].[Calendar].[Calendar Year].&[2001] on columns,
[Customer].[Customer Geography].[State-Province].&[TAS]& on rows
from [Adventure Works]
where [Measures].[Internet Order Quantity]
>>

- For guidance on setting up AS drillthrough in Reporting Services using OLE DB, you can refer to this MSDN paper (even though it was written for SQL Server 2000):

http://msdn2.microsoft.com/en-us/library/aa902647(SQL.80).aspx

>>

Integrating Analysis Services with Reporting Services

...

Adding "Drill-through" Capability to a Report

The concept of "drill-through" can mean different things depending on the technology being used. For those familiar with Analysis Services, drill-through represents the ability to return the detail records that contribute to the value of a cell. For Reporting Services, "drill-through" is the capability to jump from one report to another report when the user selects an action-enabled object on the report. It's quite possible to develop a report that uses the drill-through action type for Reporting Services to issue an Analysis Services DRILLTHROUGH MDX query, and it's also possible to use the drill-through action type of Reporting Services to return a report that returns data from Analysis Services without using the DRILLTHROUGH MDX statement. Sound confusing? Hopefully a couple of examples can help us understand the difference between the two, and how the two concepts can also be used together.

...

>>

|||

Deepak,

Thank you so much for the information. It has clarified a bunch of things that I was having trouble getting my head around. There just didn't seem to be enough information about this. Plus, with Report Builder, it seems to do an "auto" drillthrough but I can't figure out how to control which fields are returned.

I am going to read all the information you sent.

Thanks again.

-Gumbatman

sql