PivotTable custom "Show Top..." filter

[Paul Skelton]

I've worked with OWC quite a bit and know how to add custom menu items but
I'm not sure how the PivotTable implements the "Show only the top 10", 25,
25%, etc. What I'd like to do is have my custom menu implement a filter
that does something like "Show values > 1,000". Is there some programmatic
way to perform the same sort of filtering that the PivotTable does with the
"Show only top / bottom" menus?

 

[Alvin Bruney [MVP]]

Yup, you can use the filterfunction method to filter by a top number of
quantities.

--
________________________
Warm regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
Professional VSTO.NET - Wrox/Wiley
The O.W.C. Black Book with .NET
www.lulu.com/owc, Amazon
Blog: http://www.msmvps.com/blogs/alvin

 

[Paul Skelton]

I took a look at the FilterFunction but unless I'm missing something it
doesn't look like that will work. I only want to show rows that have a
value > 1,000.

Microsoft.Office.Interop.Owc11.PivotFieldFilterFunctionEnum only contains
Count, Percent, and Sum options - none of which are what I am looking for.



Thanks!

Yup, you can use the filterfunction method to filter by a top number of
quantities.

--
________________________
Warm regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
Professional VSTO.NET - Wrox/Wiley
The O.W.C. Black Book with .NET
www.lulu.com/owc, Amazon
Blog: http://www.msmvps.com/blogs/alvin
-------------------------------------------------------

I've worked with OWC quite a bit and know how to add custom menu items
but I'm not sure how the PivotTable implements the "Show only the top
10", 25, 25%, etc. What I'd like to do is have my custom menu implement
a filter that does something like "Show values > 1,000". Is there some
programmatic way to perform the same sort of filtering that the
PivotTable does with the "Show only top / bottom" menus?

 

[Alvin Bruney [MVP]]

How about using the macro recorder in Excel? Turn it on, hook up your pivot
table and filter on your criteria. When you are done, have a look at the VBA
script that the macro recorder generated and port it to your code. That's a
sure-fire way to get what you want.

--
________________________
Warm regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
Professional VSTO.NET - Wrox/Wiley
The O.W.C. Black Book with .NET
www.lulu.com/owc, Amazon
Blog: http://www.msmvps.com/blogs/alvin
-------------------------------------------------------

I took a look at the FilterFunction but unless I'm missing something it
doesn't look like that will work. I only want to show rows that have a
value > 1,000.

Microsoft.Office.Interop.Owc11.PivotFieldFilterFunctionEnum only contains
Count, Percent, and Sum options - none of which are what I am looking for.



Thanks!

Yup, you can use the filterfunction method to filter by a top number of
quantities.

--
________________________
Warm regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
Professional VSTO.NET - Wrox/Wiley
The O.W.C. Black Book with .NET
www.lulu.com/owc, Amazon
Blog: http://www.msmvps.com/blogs/alvin
-------------------------------------------------------

I've worked with OWC quite a bit and know how to add custom menu items
but I'm not sure how the PivotTable implements the "Show only the top
10", 25, 25%, etc. What I'd like to do is have my custom menu implement
a filter that does something like "Show values > 1,000". Is there some
programmatic way to perform the same sort of filtering that the
PivotTable does with the "Show only top / bottom" menus?

 

[Paul Skelton]

I tried playing with Excel but that didn't help. The OWC pivot table is
different than the Excel pivot table. Maybe I just don't know how to use
Excel pivot tables properly but I couldn't find a way to create the sort of
filter I'm after within the Excel UI.

Thanks!
Paul

How about using the macro recorder in Excel? Turn it on, hook up your
pivot table and filter on your criteria. When you are done, have a look at
the VBA script that the macro recorder generated and port it to your code.
That's a sure-fire way to get what you want.

--
________________________
Warm regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
Professional VSTO.NET - Wrox/Wiley
The O.W.C. Black Book with .NET
www.lulu.com/owc, Amazon
Blog: http://www.msmvps.com/blogs/alvin
-------------------------------------------------------

I took a look at the FilterFunction but unless I'm missing something it
doesn't look like that will work. I only want to show rows that have a
value > 1,000.

Microsoft.Office.Interop.Owc11.PivotFieldFilterFunctionEnum only contains
Count, Percent, and Sum options - none of which are what I am looking
for.



Thanks!

Yup, you can use the filterfunction method to filter by a top number of
quantities.

--
________________________
Warm regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
Professional VSTO.NET - Wrox/Wiley
The O.W.C. Black Book with .NET
www.lulu.com/owc, Amazon
Blog: http://www.msmvps.com/blogs/alvin
-------------------------------------------------------


I've worked with OWC quite a bit and know how to add custom menu items
but I'm not sure how the PivotTable implements the "Show only the top
10", 25, 25%, etc. What I'd like to do is have my custom menu
implement a filter that does something like "Show values > 1,000". Is
there some programmatic way to perform the same sort of filtering that
the PivotTable does with the "Show only top / bottom" menus?