how to invoke/call Drillthrough Action

In order to invoke drillthrough from an RS 2005 report, you can try the OLE DB for OLAP 9.0 Provider - there is an Adventure Works sample query in this OLAP newsgroup thread:

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

>>

microsoft.public.sqlserver.olap > 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]

>>

DeepakPuri at 2007-8-30 >

Well, invoking a Drillthrough Action actually returns a Drillthrough statement. Thus, the Drillthrough Action Form Editor provides a convenient means of customizing the Drillthrough statement; but it still needs to be executed by the client. Maybe I'm misunderstanding what you're trying to achieve here - could you explain it in more detail?

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_anservdrill.asp

>>

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.

There is an interesting Boolean property called Default on a drillthrough action. A cube can have multiple drillthrough actions with Default=true. The Default property does not affect the behavior of the action itself. When a client sends a DRILLTHROUGH statement that does not contain the RETURN clause, the server looks for a default drillthrough action whose target subspace contains the cell coordinate for which drillthrough is being executed. If such an action is found, the server uses the return columns from that action. If there are multiple drillthrough actions that meet these criteria, the server picks one arbitrarily. Thus default drillthrough actions enable the cube designer to override the default RETURN clause.

>>

DeepakPuri at 2007-8-30 >

As explained in the excerpt from the MSDN paper on Drillthrough, you can actually take advantage of a predefined AS Drillthrough Action in a Drillthrough statement, if the action is the applicable default for the selected cell. In that case, the columns returned by a Drillthrough statement (without an explicit return clause) are determined by the predefined default drillthrough action.

The action can be invoked directly via the Schema Rowset interface; but the cube and the cell coordinate still need to be specified, which is what the Drillthrough statement is specifying. So I'm not sure what you would gain:

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

>>

MDSCHEMA_ACTIONS Rowset

...

Restriction Columns

>>

DeepakPuri at 2007-8-30 >