I want to retrieve record(s) from the multidimensional
cube when I give a value like productid.

For example like in SQL where we say
1) select * from products where productid = "P123" OR
2) select productname, productdesc, productrate,
productqty from products where productid = "P123"

MDX WHERE and SQL WHERE are very different. To do the equivelent of SQL
WHERE in MDX use the Filter function, ala this foodmart example:

SELECT
{[Store Sales]} ON COLUMNS,
Filter([Store Name].Members,
[Store].CurrentMember.Name = "Store 3") ON ROWS
FROM Sales

tom @ the domain below
www.tomchester.net


"Deepa Pai" <dpai (AT) worldbank (DOT) org> wrote

Thanks Tom,
I have come across this example in Foodmart database,
but my problem is that I want the granular record for the
given value.
I will tell you exactly what I want. I have tried a query
which is just beating round the bush.

It is as follows
Select NON EMPTY CROSSJOIN({Time.Fy.members},
{Measures.MeasuresLevel.members}) on columns,
NON EMPTY CROSSJOIN({[Country].members},
{[ApprovalFY].members}) on Rows
from BPRT_POC where ProjIOCC.P083856

This fetches me a resultset as follows

Here 2002 and 2003 are time dimensions. PA and Working are
measures

2002 2003
---- ----
COUNTRY APPROVALFY PA Working PA Working
------- ---------- -- ------- -- -------

All Countries All ApprovalFY 1,234 500 1,000 625
2004 1,234 500 1,000 625

Latin America All ApprovalFY 1,234 500 1,000 625
2004 1,234 500 1,000 625

EC Ecuador All ApprovalFY 1,234 500 1,000 625
2004 1,234 500 1,000 625

Where as I wanted to get the resultset as shown below.
2002 2003
---- ----
COUNTRY APPROVALFY PA Working PA Working
------- ---------- -- ------- -- -------
EC Ecuador 2004 1,234 500 1,000 625


Sorry I could not find simulate the similar query in the
Foodmart database. So I had to give this query of mine.
Somebody please help me in this regard.

Thanks in advance
Deepa

Thanks Brian,
I tried using the level and it did work. But my
problem is that to retrieve the record(s) for the given
project I will not be knowing which level the value in
each dimension exists. A dimension may have more than 2
levels also. So do you have any solution for this type of
query.

Thanks in advance
Deepa Pai