I have this query in the FoodMart 2000 database that I want to run. Sometimes
there may be Promotion Media types of Direct Mail and at other times there
may not be.

How can I get this query to always successfully run even when there is no
records with a Promotion Media type of Direct Mail?

With Member [Promotion Media].[ExcludingMail] as
'Aggregate(Except([Promotion Media].[Media Type].members, {([Promotion
Media].[All Media].[Bulk Mail]),([Promotion Media].[All Media].[Direct
Mail])}))'
select {[Measures].[Store Sales]} on columns, {[Store].[Store State].members
} on rows from sales where [Promotion Media].[ExcludingMail]

This should work:

With Member [Promotion Media].[ExcludingMail] as
'Aggregate(
Except([Promotion Media].[Media Type].members, StrToSet(
IIF(
IsError(StrToValue("([Promotion Media].[All Media].[Direct
Mail])")),
"{([Promotion Media].[All Media].[Bulk Mail])}",
"{([Promotion Media].[All Media].[Bulk Mail], ([Promotion
Media].[All Media].[Direct Mail])}"
)
))
)'
select {[Measures].[Store Sales]} on columns, {[Store].[Store State].members
} on rows from sales where [Promotion Media].[ExcludingMail]



"Thearon" wrote: