OLAP Cube Results from List

[Shairal]

Hello everyone,

I use an OLAP cube in Excel (2007 and 2010). Our Customers cube is an hierarchical cube - SalesDivision, SalesRegion, Customer - so to get to Customer I need to drill down through Division and Region first.
My question is, is there a way to use a look up list and have the cube return only those customers listed elsewhere on the worksheet? This would be a huge time-saver if I did not have to drill through multiple divisions/regions to pick out each customer that I need. Any suggestions would be appreciated!

Thanks!
Shairal

 

[jeffreyweir]

do you mean that you want to filter a pivot based on a list of names etc stored somewhere on the worksheet?

This might help:
http://blog.stevienova.com/2009/07/...r-defined-named-sets-in-excel-2007-using-vba/

Or some of my code that i posted here:
http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/

...although it might need some amendment to handle the OLAP side of things.

 

[Kyle McGhee]

maybe this would help point you in the right direction?
http://datapigtechnologies.com/blog...procedure-from-excel-with-dynamic-parameters/

 

[jeffreyweir]

Kyle - Unfortunately you can't parameterize OLAP Pivots, as far as I know, so Mike's great trick won't work in this instance.

It would be possible to create a report using Excel's CUBE functions, but it might not be that dynamic.

 

[Shairal]

Thanks guys for both your responses - great for getting me started in the right direction. I appreciate all the help!

 

[jeffreyweir]

shairal - I've been meaning to revise my 'Filter pivot based on external range' code to ensure it works with OLAP pivots for some time, so if that's what you want let me know, and I'll get a revised macro to you.