how do you check to see if a member exists with MDX?

[Guest_imported]

I am currently using dynamic dropdown box selections to populate data fields on my web site. The dropdown boxes are populated from Cube A but the data displayed to the user must be retrieved from Cube A AND Cube B. Cube A and B have the same dimension names but because of the difference in data stored, the members differ (ie these are not and can not be shared dimensions). When the dropdown selections are passed from the dropdown boxes, Cube B sends a failure if the member doesn't exist. Is there anyway to capture this failure and display " " to the user? Or is there anyway, without creating 2 MDX statements, to check to see if the selected member exists and then return the cell value or null instead of failing?

Some things to note:
My dropdowns must be dynamic because of the quantity of data(5 boxes - 16,000 valid combinations)
I thought about creating a virtual cube but I don't believe this can be stored locally. Is this correct?
I cannot edit the dimensions in either cube because of how extensively they are used in other areas.

 

[issahar]

---I sure exists something easier but this way can be an option too:

you can write a COM component with a method that will get array of members and the member name and return true or false if the member is inside the array
(use SetToArray MDX function output as input of your function...)

and use In mdx
"USE LIBRARY "c:\functions\mylib.dll"" to load this library

and
use your function call inside the MDX query

more about this you can read in MSDN
mkMSITStore:C:\Program%20Files\Microsoft%20Visual%20Studio\MSDN\2001APR\1033\olapdmad.chm::/agmdxnonosfunct_7c6r.htm

Good Luck

Issahar Gourfinkel
senior software engineer
Israel

http://softwatch.com

 

[Guest_imported]

Thanks - I was actually able solve it through the code at the bottom of this page. Thought I'd post it in case anyone else has a similiar issue.

The following code assumes the user has selected OK from the dropdown menu. If OK exists as a member in the State dimension, costs for OK is returned by stores. If not, 0 is returned.

WITH MEMBER [Geography].[State].[Test] AS 'StrToValue(" [Geography].[State].[OK] &quot '
MEMBER Measures.X AS 'IIF(IsError([Geography].[State].[Test]), Measures.Cost, 0 )'
SELECT {Measures.X} ON Columns,
[Stores].children on Rows
FROM StoreSales
WHERE ([Geography].[State].[Test])

 

[issahar]

I understand this is the solution for OLAP 2000...
I spoke about the OLAP 7.

I think the function ISError doens't exist in OLAP 7, doesn't it? Issahar Gourfinkel
senior software engineer
Israel

http://softwatch.com