I have couple of dimensions placed in the page section of the pivot
report and they are quite large like customer, products, etc I want to
exclude a few customers and products but I cannot figure out how to
exclude certain customers or products in the pivot report I can only
include.

But include does not help since apart from the few customers I want to
exclude I need to include a large of remaining customers.

Thanks
Karen

Not doesn't exist in olap - it alwas finds a tupple which means the
combinations
you choose.

If it is a form of permanent exclusion the best way is to put some logik
into
your server environment - you put an extra column on your customer
relational table
with Include / Exclude as values

Then you can make this a virual dimension based on a member property on the
customer
dimension in AS.

With this method however you need to have some kind of front end for
maintaining these
groupings but the need for these could be in other dimensions as well.




<karenmiddleol (AT) yahoo (DOT) com> wrote

Actually, there is a way to do it natively in Analysis Services.

The EXCEPT function in MDX can do what you're describing.

From books online, its syntax:

Except(«Set1», «Set2»[, ALL])

Duplicates are eliminated from both sets prior to finding the difference.
The optional ALL flag retains duplicates. Matching duplicates in «Set1» are
eliminated and nonmatching duplicates are retained.


What you'd want to do is put what you want to exclude in Set2, while being
sure it's also in Set1, and leave the ALL agrument out.

Let's take Foodmart as an example. Say you wanted to exclude CA from the
three states that have data in the customer dimension:

select measures.members on columns,
except({[Customers].[country].[USA].children}, {[Customers].[All
Customers].[USA].[CA]}) on rows
from sales


See how Califronia is now excluded from the rows? If you have the ability
to control what gets passed into an MDX statement with your front end, and
what functions can be used, the EXCEPT MDX function is the way to go.

Good luck.


"karenmiddleol (AT) yahoo (DOT) com" wrote:

SQL McOLAP wrote:


Hi SQL McOLAP & Michael

Many thanks for the pains in explaining the Except clause.

But my problem unlike :
select measures.members on columns,
except({[Customers].[country].*[USA].children}, {[Customers].[All
Customers].[USA].[CA]}) on rows
from sales

I do not want to display Customers on rows I want Say Measures on
columns and products on rows I want to include many customers and
exclude a few I cannot do this in the where clause when I use the
Except in the where clause it gives errors saying cannot convert a set
to tuple

Thanks
Karen

Oh, that's OK, instead of actually showing that named set on the grid, you
can create a calculated member to sum just the customers you want, and then
slice on that calc member. Something like:

--create the calc member to sum just the customers you want
with member [Customers].[ThoseIWant] AS
'SUM(except({[Customers].[country].[USA].children}, {[Customers].[All
Customers].[USA].[CA]}))'

Select {[measures].members} on columns ,
{[Product].[Product Category].members} on rows
From [Sales]
Where ([Customers].[ThoseIWant])

Good luck!

- Phil


"karenmiddleol (AT) yahoo (DOT) com" wrote:

However - if you want to do drill through at some point then that won't be
possible with
this aggregating ?

But if that isn't necessary then it could the way to go..


<karenmiddleol (AT) yahoo (DOT) com> wrote


SQL McOLAP wrote:


Hi SQL McOLAP & Michael

Many thanks for the pains in explaining the Except clause.

But my problem unlike :
select measures.members on columns,
except({[Customers].[country].*[USA].children}, {[Customers].[All
Customers].[USA].[CA]}) on rows
from sales

I do not want to display Customers on rows I want Say Measures on
columns and products on rows I want to include many customers and
exclude a few I cannot do this in the where clause when I use the
Except in the where clause it gives errors saying cannot convert a set
to tuple

Thanks
Karen

No, drillthrough will have no problems with the slice that's been set, it
will be able to use just the underlying set that's been defined.

"Michael Vardinghus" wrote:

SQL McOLAP & Michael

Many thanks I read a few books on MDX could not figure the Except
clause MDX as been so difficult when compared to SQL syntax wide.

I was beating my head to the wall with this problem. Many thanks for
your help I will try this solution if I have any issues I will repost
the question.

I never thought excluding values in dimensions was this difficult.

Also, now we are excluding values only from the customers dimension how
about if I had a condition I also wanted to exclude [Gender].[All
Gender].[M] as well apart from the above customers from the sales
reported how do I put a AND condition to include this additional
condition in the where clause please advice.

Thanks again
Karen

If you want to do the same thing with other dimensions, just create a calc
member for the sum of that dimension you did an except on, and then include
it in your where clause.

Example:

with member [Customers].[ThoseIWant] AS
'SUM(except({[Customers].[country].[USA].children}, {[Customers].[All
Customers].[USA].[CA]}))'
member [Gender].[GendersIWant] AS
'SUM(except({[Gender].[Gender].members},{[Gender].[All Gender].[M]}))'

Select {[measures].members} on columns ,
{[Product].[Product Category].members} on rows
From [Sales]
Where ([Customers].[ThoseIWant],[Gender].[GendersIWant])


This may not be the best example, because there are only 2 members in
gender, and it would be MUCH easier to just put Female in your WHERE clause
instead of doing a SUM on an EXCEPT, and then putting that calc in your WHERE
clause.

But I think you get the idea, just create a calc member on an except
dimension for all the dimensions you want, and throw those calc members in
your where clause.

Good luck!

- Phil


"karenmiddleol (AT) yahoo (DOT) com" wrote:

Phil

Many thanks for the quick update.

How about in a scenario I want the output to be included meet the
following criteria:

Customers].[ThoseIWant ]
or
[Gender].[GendersIWant]

I think the above syntax you provided works for

Customers].[ThoseIWant ]
AND
[Gender].[GendersIWant]


Thanks
Karen