Hi,

Here's a simple example from Adventure Works that shows a problem which
I don't think can be considered a feature:

- Open AdventureWorks cube in the cube browser.
- Drag and drop 'Reseller Order quantity' measure from "Reseller Sales"
folder to details field in the pivot table. The result is 214378
- Expand "Reseller" dimension and drop Reseller attribute to Rows
field. Now you can see a list of resellers with their order quantoties
and the grand total equal to 214378. So far so good
- Drag "Reseller Type" hierarchy from the same "Reseller" dimension and
drop it to the "Drop Filter Fields Here" field in the pivot table.
Nothing changed.
- And now it becomes interesting. Expand "Reseller type" in the filter.
Unselect "All". Expand "Specialty Bike Shop" type and select "A Great
Bicycle Company" only. Click OK

Now the pivot table shows 1 reseller with Reseller Order Quantity equal
to 21 AND GRAND TOTAL equal to 214378.

If you remove the Reseller Type hierarchy from the filter field of the
pivot table and use Filter Pane instead you'll get what you'd expect to
get: GRAND TOTAL equal to 21.

It wouldn't be such a huge deal if Excel pivot table had this Filter
Pane as well. But it doesn't and ProClarity is not an option in my
case.

Can somebody from Microsoft explain this behavior please?

Thanks,
Tom

Moving the Reseller Type hierarchy from the filter field of the pivot
table to the "Filter Pane" changes the type of MDX query filtering, as
also the results. You can compare the results of these 2 MDX queries:

Non Empty [Reseller].[Reseller].Members on 1
from [Adventure Works]
where [Reseller].[Reseller Type].[Reseller Name].&[238]
-------------------------------------------------------
select {[Measures].[Reseller Order Quantity]} on 0,
Non Empty [Reseller].[Reseller].Members on 1
from (select
{[Reseller].[Reseller Type].[Reseller Name].&[238]} on 0
from [Adventure Works])

Keep in mind that Excel 2003 was released well before AS 2005 - if you
try the new Excel 2007 Beta 2, the results should be similar to the
"Filter Pane".


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Deepak,

Of course I used the profiler and saw the difference between MDX
queries. My question was why they're different. I agree with you that
Excel 2003 was released before AS 2005 and one might expect such
inconsistencies. But having the same problem in the cube browser in
Management Studio/BIDS cannot be justified by the same argument. Is
there a chance this bug will be fixed now instead of waiting for the
new Office?

Deepak Puri wrote:

Not sure why you view it as a "bug" - the AS 2005 browser "Filter Pane"
supports the new MDX subquery capability. The filter, on the other hand,
controls the MDX where clause contents.

The browser merely reflects the results of the MDX query with the where
clause - unless, of course, you think the results returned by this MDX
query are incorrect.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

I think the issue here is that you are in effect, attempting to putting
the same attribute on both the row axis and in the where clause because
it is a part of two different hierarchies. (which was impossible to do
when Excel 2003 was made)

Assuming that you want to see a list of one or more members on the row
axis, another option would be to just choose the member(s) in the row
axis itself. This will produce the expected figures. This is just my
opinion, but I think that filtering the set in this fashion is the
"better" way to do this.

It would produce MDX equivalent to:

, Non Empty {[Reseller].[Reseller Type].[Reseller Name].&[238]} on 1
from [Adventure Works]
--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <uzsc2b3hGHA.2208 (AT) TK2MSFTNGP05 (DOT) phx.gbl>,
deepak_puri (AT) progressive (DOT) com says...

Deepak,

In my opinion it's a bug because both Filter Pane and the filter field
should provide same results in this particular case. Otherwise, if it
is intended behavior this should be reflected in the documentation.

Tom

Deepak Puri wrote:

Darren,

You're right about the issue. It happens when you have the same
attribute in the filter and in any axis. The workaround would be to
have additional attribute that can be used in axis and to make the
attribute used in the hirarchy invisible. In our case the Reseller
attribute must be made invisible and a new attribute, let's name it
'Reseller Name' must be used instead of Reseller in row axis.

Tom

Darren Gosbell wrote:

Another aspect which may cause confusion is the "Auto-Exists" behavior,
which results in only those rows being returned that can exist with the
"where" filter. Even though VisualTotals are enabled by default in the
browser(which would normally cause the "All" member to be the sum of the
visible members), this doesn't seem to apply here, because no members
have manually been deselected from the rows. If an "existing" filter is
applied prior to VisualTotals, then the "All" member only reflects the
sum of filtered members:

VisualTotals(Existing [Reseller].[Reseller].Members,
"* - Partial") on 1
from [Adventure Works]
where [Reseller].[Reseller Type].[Reseller Name].&[238]
-------------------------------------------------------
Reseller Order Quantity
All Resellers - Partial 21
A Great Bicycle Company 21

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***