Top Filter diffrence in excel and owc

[jankrib]

I have a pivottable using OWC11. I use vb.net. The data source is an
OLAP cube. What I want to do is always show only the top 100 members
of a field. This is my code so far:

aField.FilterOn = AxPivotTable1.ActiveView.DataAxis.Totals(0)
aField.FilterFunction =
OWC11.PivotFieldFilterFunctionEnum.plFilterFunctionTopCount
aField.FilterFunctionValue = 100

This works great when I don't have any filters on, but as soon as I
filter by a country etc. all members dissapear. What I want is that
if I have for example filtered by one country the top 100 from that
country show.

With the same cube in excel I get this effect by applying the "Top 10
AutoShow".

I have logged the MDX queries:

The Excel query:

SELECT NON EMPTY
HIERARCHIZE(AddCalculatedMembers({DrillDownLevelTop({[Dim
Geography].[City].[All]}, 100, , [Measures].[pop])}))

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS
FROM [World]
WHERE ([Measures].[pop], [Dim Geography].[Geographic
Location].[Country].&[Norway])


The OWC query:

CREATE SESSION
SET [World].[regnumber] AS
{
[Dim Geography].[City].[All],
TopCount(

AddCalculatedMembers([Dim Geography].[City].[City].MEMBERS),
100,
(
[Measures].
[pop],
[Dim
Geography].[Geographic Location].[Country].&[Norway]
)
)
}

Why doesn't excel and owc send the same query?

Can anyone help me?

 

[Alvin Bruney [MVP]]

Call it a feature? These two objects are not the same, they only share some
common code so it really isn't fair to expect the exact same functionality.
That said, this looks like a bug to me as you have described it. The
workaround is to use the appropriate MDX query and not rely on the TOP
functionality.

--
Regards,
Alvin Bruney
------------------------------------------------------
Shameless author plug
Excel Services for .NET is coming...
https://www.microsoft.com/MSPress/books/10933.aspx
OWC Black Book www.lulu.com/owc
Professional VSTO 2005 - Wrox/Wiley

 

[jankrib]

Thank you for the insight. I was afraid this was the case. It's
ashame that the owc don't use the select query like excel. When I
test the two against each other it seams that the excel way is both
faster an gets a better result.

 

[jankrib]

Thank you for the insight. I was afraid this was the case. It's
ashame that the owc don't use the select query like excel. When I
test the two against each other it seams that the excel way is both
faster an gets a better result.

I have tried to change the behavior by using the commandText property,
but when I set this property for the second time I get a query error.
It's possible to use the commandText more than one time right?

Jan Kristian Bjerke