Thanks Chris. I couldn't get the ISERROR to work the way I wanted, but I did take your advice and I changed my dimension to come off a pre-existing table that contained all values. Thanks


----- Chris Webb wrote: ----

Hi Andrea

First of all, to avoid your question, you shouldn't have a scenario where a dimension can sometimes contain a member and sometimes not. Similar to the way you should build a time dimension from a pre-existing table containing all possible dates rather than the ones in your fact table, you should probably build your own Gender dimension table containing M, F and U so you never encounter this problem

That said, if you want to write MDX that handles missing members, you can use the ISERROR and STRTOSET functions. Take a look at the following thread to see how
http://groups.google.co.uk/groups?hl....sqlserver.ola

HTH

Chri


----- Andrea Worley wrote: ----

I am trying to get values for a dimension member that is sometimes in the data and sometimes not

If I wanted to show Unit Sales for gender Female, I would run
select {[measures].[Unit Sales]} on columns, {[Gender].[F]} on rows from Sale

But assuming that sometimes the data contained gender = U for unknown, if I wanted to show Unit Sales for gender unknown, I would run

select {[measures].[Unit Sales]} on columns, {[Gender].[u]} on rows from Sale

If that day the cube contained no data for Gender type unknown, I get the error: Unable to open cellset. Formula error - cannot find dimension member ("[Gender].[u]") in a name binding function

Is there anyway to structure the MDX so that it doesn't throw an error in this case, it just shows as blank

Thank you.