Filtering using member key in mdx query

#1

Hi,

I have a Customers Dimension with CustomerName as the Dimension Level.
The member key and member name property of the CustomerName level are
the Key field and Description field of the table.

I would like to know if it is possible to refer to the member key
column of the dimension member in the WHERE clause of an mdx query
using its member name column value?

E.g.

SELECT
{ } ON ROWS,

{ } ON COLUMNS

FROM TestCube

WHERE ([Customers].[CustomerName].[xxxxx])

where [xxxxx] should actually refer to the key field.

Appreciate any response to this!

thanks,
ktk

#2

Here's a sample query for the Foodmart Sales cube, which selects the
Customer with a Key of 3 in the "where" slicer.
The [CustName] calculated measure confirms that the customer with an ID
of 3 is indeed selected:

Quote:

With Member [Measures].[CustName] as

'[Customers].CurrentMember.Name'
select Measures.AllMembers on columns
from Sales
where (Filter([Customers].[Name].Members,
[Customers].Properties("KEY") = "3").Item(0))

Quote:

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

#3

Thank you for your response!

But I would like to know if there is a way, where i can provide the
member name in the where slicer,that would internally refer to the
member key value, as the key value may not be known for using in the
where slicer.

This is because, the user is displayed with only the member names, and
would drill down by selecting a particular customer name. Only the
name is available at this point for filtering and not the key value.

Thanks in advance,

ktk

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

Quote:

Here's a sample query for the Foodmart Sales cube, which selects the
Customer with a Key of 3 in the "where" slicer.
The [CustName] calculated measure confirms that the customer with an ID
of 3 is indeed selected:

With Member [Measures].[CustName] as
'[Customers].CurrentMember.Name'
select Measures.AllMembers on columns
from Sales
where (Filter([Customers].[Name].Members,
[Customers].Properties("KEY") = "3").Item(0))

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

#4

Maybe I'm missing something, but if you have the member name, why can't
you use it in the where slicer? If it's available as a string, you can
use StrToMember(), like:

where (StrToMember("MyMemberName"))

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

#5

If there are 2 members with the same name and different key values, at
present, I am not able to control retrieving information of the desired
member using the member name in the slicer.

ktk

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

#6

Can you explain the specifics of this scenario - how do you propose to
"control the desired member using the name" when both members have the
same name - is the key value known?

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

#7

I'd like to explain to you with the actual scenario, which is similar
to my previous question:

My Dimension Dim has 2 levels, Operating Companies(OCs) and Cost
Center(CCs). Here OC and CC have the ID field of the table as the
Member Key Column and name field of the table as Member Name Column.

In one of drillable the reports, I am displaying all OCs using an MDX
Query: There is no requirement to display the OCID.

SELECT

{[Dim].[OCName].members} ON ROWS,
{[Measures].[Amt] } ON COLUMNS

If there are 2 OCs with same name, there are 2 records for the same
with their respective Amounts.

XXX 100
XXX 200
YYY 300

Assume the IDs of these records is 1,2 3 resp. But this is not
fetched. On clicking on an OC, I need to display the CCs under that
OC.

SELECT
{[Dim].[OCName].[XXX].children} ON ROWS,
{[Measures].[Amt] } ON COLUMNS

Since there may be 2 OCs with the same name, I need a way to display
the corres. CCs for the selected OC. At present, the above query
displays the CCs for only one of the OC which ever may be selected.
Since the OCID is not known, I'd like to know if there is any means of
referencing [XXX].children with the key column value(1 or 2) rather
than [XXX](name column)?

Thanks,
ktk

#8

How about using the key to specify the member, like:

Quote:

SELECT

{[Dim].[OCName].&[1].Children} ON ROWS,
{[Measures].[Amt] } ON COLUMNS

Quote:

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

#9

Deepak,

The key value [1] is what is not known!
We only have the OCName member name values known(E.g XXX , YYY etc.).

Thanks,
ktk

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

#10

Since I don't understand why 2 different members are assigned the same
name, I can only suggest that you add some disambiguating info to the
member name, like:

"NameX[ID=1]" and "NameX[ID=2]"

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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