I currently have three tables that make up many-to-many relationship. The
tables and relations are as follows and are identical on SQL Server and in
Analysis Services:

ChargeToGroup<-----GroupItems----->ChargeToItems
1 CO 1 1 1 -Car
2 NE 1 2 2 - House
3 WY 1 3 3- Boat

The ChargeToGroup is set up as a recursive parent-child relationship that
represents a grouping of ChargeToItems. For example State and County. A
State can contain many counties.
The GroupItems table is the intermediate measure group. It has a foreign
key to both the ChargeToGroup and ChargeToItems.
The ChargeToItems contains a list of charges that can be assigned to many
groups.

What is the correct MDX query to retrieve a list of ChargeToItems that
belong to a particular group in the ChargeToGroup. For example how do
retrieve a list ChargeToItems that belong to the ChargeToGroug "CO". I
would expect to get back Car, House, Boat.

Thanks in advance!
Kurt

Hello Kurt,

You may want to consider develop a UDF to achieve this. Please refer to
"Using Stored Procedures (MDX) " in BOL for more details

Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

================================================== ===


This posting is provided "AS IS" with no warranties, and confers no rights.

The Measure Group version of Exists() should work, like:

Exists([ChargeToItems].[ChargeToItems].Members,
{[ChargeToGroup].[CO]},
"GroupItems")



- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi Deepak,

That gets close to what I need. I still would like to return the name
attribute to make this useful. I cannot seem to get to
[ChargeToItems].[Name].members.

Kurt


"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

Kurt,

Not sure how the attribute relationships between
[ChargeToItems].[ChargeToItems] and [ChargeToItems].[Name] are set up,
but would an outer Exists() work, like:


Exists([ChargeToItems].[Name].Members,
Exists([ChargeToItems].[ChargeToItems].Members,
{[ChargeToGroup].[CO]},
"GroupItems"))



- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi Deepak,

That works except that I'm getting way too many records back. I should only
see 257 columns and I'm getting 11239 back. Any ideas to why? The sql
table only contains 257 records that contain that specific ChargeToID.

So close yet so far,
Kurt


"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

Kurt,

I'd guess that this is a function of how your attribute relationships
are set up. For example, if you browse the cube, select the specific
ChargeToID as a filter, then place [ChargeToItems].[Name] on rows, how
many members are listed - the Auto-Exist filtering should be similar to
the outer Exists()?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

PS: another thing that occurred to me is that, if the
[ChargeToItems].[ChargeToItems] hierarchy has an [(All)] level, that
member should be excluded from Exists(). This could be define by taking
members from the lower level:

Exists([ChargeToItems].[Name].Members,
Exists([ChargeToItems].[ChargeToItems].[ChargeToItems].Members,
{[ChargeToGroup].[CO]},
"GroupItems"))


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Deepak,

That was it!

Thanks for all your help!

Kurt


"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote