Hi,

I'm trying to create an MDX statement which retrieves data from a
drillthrough for a group of measures.
As I'm new to MDX language, I do not find the correct syntax.
I tried this but it does not work:

Drillthrough
Select
{[DAT Currency 1].[Currency].&[118]} on 0,
{[AGT Front].[Front].&[1]} on 1

Any idea?

thx

C

Hopefully, you've seen this MSDN paper:

http://msdn.microsoft.com/library/de.../en-us/dnsql90
/html/sql2k5_anservdrill.asp

T.K. Anand
Microsoft Corporation

July 2005

Applies to:
SQL Server 2005 Analysis Services

Summary: Discover the new Analysis Services 2005 drillthrough
architecture. See how to set up drillthrough in Analysis Services 2005
and get guidance on migrating drillthrough settings from Analysis
Services 2000 databases.
...
Multiple Measure Groups

When a cube has multiple measure groups with different dimensionalities
(e.g. Sales and Inventory), the drillthrough return columns will
typically be different depending on the target measure group.

To enable drillthrough on a cube with multiple measure groups:

Add the drillthrough return columns to the cube schema as attributes or
measures.

Create multiple drillthrough actions with Default=true, one for each
measure group.

For each action, set the target as: MeasureGroupMeasures("<measure group
name>"). This restricts the scope of the action to that measure group.

For each action, set the return columns as appropriate for the target
measure group.
...
So you can control the columns returned by a drillthrough statement
directly, by enumerating the desired attributes and measures in the
"RETURN" clause. Or you can configure the columns returned by setting up
a default Drillthrough Action - in which case, the "Target" of the
action will be:
MeasureGroupMeasures("<measure group name>")

Here's an AW drillthrough sample, using the "RETURN" clause:

select {[Measures].[Internet Order Quantity]} on 0,
{[Customer].[Customer Geography].[State-Province].&[AB]&[CA]} on 1
from [Adventure Works]
where [Date].[Calendar].[Calendar Year].&[2002]
Return
[$Measures].[Internet Order Quantity] as [Orders],
[$Measures].[Internet Sales Amount] as [Sales],
Caption([$Customer].[State-Province]) as [State-Province],
Key([$Date].[Month Name], 0) as [CalYear],
Key([$Date].[Month Name], 1) as [CalMonth]
----------------------------------------------------------
Orders Sales State-Province CalYear CalMonth
1 2443.35 Alberta 2002 7
1 2181.5625 Alberta 2002 7
1 3578.27 Alberta 2002 6

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***