How can I use MDX to exclude one (1) member of a dimension from a
report off a cube?

Hi Folks! Thanks for any help in advance. I get that I can useea
where clause in an mdx statement/query to restrict the result set to
one "member" of a dimension or level like 2003 in a year set.

I don't think I can use a "where" clause to exclude one, individual
member of a level or dimension, can I? You know, like

WHERE [state].members <> 'Minnesota'

or

WHERE [cereal company].members <>'General Mills'

Is there another MDX function that would allow me to build a view
exclduing just one, specific member of a level or a dimension? Like
maybe the except function?

Many thanks.

Pete

Pete Hohenhaus
Woodinville, WA, East Side Seattle, Good Old USA!

You should use the EXCEPT() MDX function. It's documented in BOL.

Sean


--
--
Sean Boon
SQL Server BI Product Unit

**This posting is provided AS IS, with no warranties, and confers no
rights.**

"Pete Hohenhaus" <cam_pete (AT) hotmail (DOT) com> wrote

An MDX WHERE clause is a tuple. Since only one member per
dimension can go in a tuple, the only way to do this is by
creating a calc member that is an aggregation of the set,
like so:

WITH
MEMBER Store.AggStores AS
' Aggregate ({ [Store Name].Members - {[Store 1]} }) '

SELECT
etc...
WHERE (Store.AggStores)

-- [Courtesy Tom]

Also U can try using the EXCEPT function as follows

With member store.total as 'aggregate({except({store.
[store name].members},{[store].[store name].[store
1]})})' select {[measures].members} on columns,
{[Customers].[Name].members} on rows from sales where
store.total

This statement excludes the [STORE 1].

HTH.

Cheers,
Sanka

"Sanka" <loonysan (AT) mailcity (DOT) com> wrote


Pete

"Sanka" <loonysan (AT) mailcity (DOT) com> wrote


Sean --

Thanks, but I do not find BOL very helpful. Nor, did I find the MS
2093 Course very helpful. MDX support has a long way to go.

Pete

Please pardon the personal digression here, but I'd like to take this
opportunity to chime in for the public record:

A company that I co-founded was involved in the creation of this course. I'd
like to state publically that 1) Pete is right, the course is incredibly
lame, and 2) I personally had nothing to with its creation, and incidentally
am no longer associated with them. This is not meant to disparage the
company, as they are a decent outfit, however 2093 wasn't one of their finer
moments.

tom @ the domain below
www.tomchester.net

"Pete Hohenhaus" <cam_pete (AT) hotmail (DOT) com> wrote

"Tom Chester" <publicNOSPAM (AT) tomchester (DOT) net> wrote

Tom & All --

Thanks for the info and the validation. I meant nothing disparaging
to any individual or group. But "lame" is the right word for the
course. Sometimes conscientious efforts go wide of the mark. I just
felt like MS 2093 left me more confused than anything. I haven't
taken the equivalent course from ProClarity so I don't know if that
one is a good one or not.

I have latched onto the "Fast Track to MDX" book by Mark - Robert -
Mosha. I think it gives a much better grounding.

Pete
Woodinville, WA, East Side Seattle, Good Old USA