MDX Filter Question

#1

if I understand you correctly this should do what you want:

WITH MEMBER [Store].[Company1] AS 'Aggregate(except
([Store].[Store Name].members, {[Store].[All Stores].[USA].
[CA].[Beverly Hills].[Store 6] }) ) '
select
{ [Measures].[Units Shipped], [Measures].[Units
Ordered] } on columns
from Warehouse
where [Company1]

HTH,
Brian
www.geocities.com/brianaltmann/olap.html

Quote:

-----Original Message-----
How can I use the filter function to show totals for the
entire company
without including one store? Here is an example from
FoodMart using a
calculate Member (I did not include ever store in this
example):

WITH MEMBER [Store].[Company1] AS 'Aggregate({[Store].[All
Stores].[Canada].[BC].[Vancouver].[Store 19],
[Store].[All Stores].[Canada].[BC].[Victoria].[Store 20],
[Store].[All Stores].[Mexico].[DF].[Mexico City].[Store
9],
[Store].[All Stores].[USA].[WA].[Bellingham].[Store 2],
[Store].[All Stores].[USA].[WA].[Bremerton].[Store 3],
[Store].[All Stores].[USA].[CA].[Los Angeles].[Store 7],
[Store].[All Stores].[USA].[CA].[Beverly Hills].[Store
6]})'
select
{ [Measures].[Units Shipped], [Measures].[Units
Ordered] } on columns
from Warehouse
where [Company1]

I know I can do the following to list every store except
say store 6:
select
{ [Measures].[Units Shipped], [Measures].[Units
Ordered] } on columns,
NON EMPTY Filter([Store].[Store Name].members,
[Store].CurrentMember.Name <> "Store 6") on rows
from Warehouse

But this same approach is useless when I try to sum for
the company, like
this:
select
Filter({ [Measures].[Units Shipped], [Measures].
[Units Ordered] },
[Store].CurrentMember.Name <> "Store 6") on columns
from Warehouse

Any suggestions?

.

#2

Thank you for you help. This does work if you add the "Store Name" level in
the first parameter of the Filter function. However, why would the post
from "Brian", perform better? Are these 2 methods not both doing the same
thing? Do they compile differently? To me they both look like the
Aggregate function simply generates a simple set without "Store 6".

Here is the suggestion from Brian:
WITH MEMBER [Store].[Company1] AS 'Aggregate(except
([Store].[Store Name].members, {[Store].[All Stores].[USA].
[CA].[Beverly Hills].[Store 6] }) ) '
select
{ [Measures].[Units Shipped], [Measures].[Units
Ordered] } on columns
from Warehouse
where [Company1]

This is not a problem in FoodMart of course but when I used each of these
methods in my real life query the "except" method takes 9 seconds to query
while the "filter" method takes 17 seconds with that being the only
difference. I am just curious as to the technical differences here.

Thanks to anyone with any more info!

"Matt Carroll [MS]" <sqldev (AT) microsoft (DOT) com> wrote

Quote:

It sounds like you want something like this:

WITH member Store.SumOfSomeStores as 'Aggregate(Filter(Store.Members,
[Store].CurrentMember.Name <> "Store 6"))'
select
{ [Measures].[Units Shipped], [Measures].[Units Ordered] } on 0,
{SumOfSomeStores} on 1
from Warehouse

- Matt Carroll
--
This posting is provided "AS IS" with no warranties, and confers no
rights.

"Chris Umbaugh" <chrisumbaugh (AT) toast (DOT) net> wrote in message
news:ea8YX7CXDHA.2284 (AT) TK2MSFTNGP10 (DOT) phx.gbl...
How can I use the filter function to show totals for the entire company
without including one store? Here is an example from FoodMart using a
calculate Member (I did not include ever store in this example):

WITH MEMBER [Store].[Company1] AS 'Aggregate({[Store].[All
Stores].[Canada].[BC].[Vancouver].[Store 19],
[Store].[All Stores].[Canada].[BC].[Victoria].[Store 20],
[Store].[All Stores].[Mexico].[DF].[Mexico City].[Store 9],
[Store].[All Stores].[USA].[WA].[Bellingham].[Store 2],
[Store].[All Stores].[USA].[WA].[Bremerton].[Store 3],
[Store].[All Stores].[USA].[CA].[Los Angeles].[Store 7],
[Store].[All Stores].[USA].[CA].[Beverly Hills].[Store 6]})'
select
{ [Measures].[Units Shipped], [Measures].[Units Ordered] } on
columns
from Warehouse
where [Company1]

I know I can do the following to list every store except say store 6:
select
{ [Measures].[Units Shipped], [Measures].[Units Ordered] } on
columns,
NON EMPTY Filter([Store].[Store Name].members,
[Store].CurrentMember.Name <> "Store 6") on rows
from Warehouse

But this same approach is useless when I try to sum for the company,
like
this:
select
Filter({ [Measures].[Units Shipped], [Measures].[Units Ordered] },
[Store].CurrentMember.Name <> "Store 6") on columns
from Warehouse

Any suggestions?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

MDX Filter Question

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap

MDX Filter Question - 08-06-2003 , 01:16 PM

Re: MDX Filter Question - 08-06-2003 , 04:05 PM