How to filter only the first level in the hierarchy

[Patrick Ng]

Hi,

I have a question about filtering and pivot table. Support I have an
Employees dimension, which has a parent-child hierarchy to match the
management hierarchy. I also have another dimension called [City].

Suppose my management hierarchy, together with where that person lives, look
like this:

Tom (Seattle)
|----- Kate (Seattle)
|----- Jimmy (New York)
|----- Patrick (Tokyo)

Linda (Portland)
|----- Susan (Denver)
|----- Willis (Portland)


Using pivot table in Excel, I can easily see the drill down the hierarchy
and see the aggregated measures. However, suppose there are hundreds of top
level managers, and so as a user I want to first filter the top managers down
to a specific city (e.g. Seattle). However, if I apply the City=Seattle
filter, I will filter out the reports as well, and so Jimmy and Patrick will
be filtered out. But that’s not what I want.

Is it possible to apply the filter only on the first level? Can I achieve
that using two pivot tables?

Thanks,
Patrick

 

[Ron Coderre]

I think you'd need to segregate the MgrCity values from the EmployeeCity
values.

Try something like this:

Mgr MgrCity Employee EmployeeCity
Tom Seattle Kate Seattle
Tom Seattle Jimmy New York
Tom Seattle Patrick Tokyo
Linda Portland Susan Denver
Linda Portland Willis Portland

When Pivot Tabled (a word..yes?) you'd be able to filter on MgrCity and see
all managers in that city along with ALL of there employees.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Patrick Ng]

Hi Ron,

Thanks a lot for your reply. If I have that column it should be a good way.

On the other hand, a colleague provided me with a solution in Excel 2007 (I
haven't tried it in Excel 2003) which I think is pretty cool too:

"You could filter the managers in Seattle, select the visible managers and
apply filter / keep only selected items. Now you remove the filter on
Seattle and expand the managers to the next level."

I think you'd need to segregate the MgrCity values from the EmployeeCity
values.

Try something like this:

Mgr MgrCity Employee EmployeeCity
Tom Seattle Kate Seattle
Tom Seattle Jimmy New York
Tom Seattle Patrick Tokyo
Linda Portland Susan Denver
Linda Portland Willis Portland

When Pivot Tabled (a word..yes?) you'd be able to filter on MgrCity and see
all managers in that city along with ALL of there employees.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

Hi,

I have a question about filtering and pivot table. Support I have an
Employees dimension, which has a parent-child hierarchy to match the
management hierarchy. I also have another dimension called [City].

Suppose my management hierarchy, together with where that person lives, look
like this:

Tom (Seattle)
|----- Kate (Seattle)
|----- Jimmy (New York)
|----- Patrick (Tokyo)

Linda (Portland)
|----- Susan (Denver)
|----- Willis (Portland)


Using pivot table in Excel, I can easily see the drill down the hierarchy
and see the aggregated measures. However, suppose there are hundreds of top
level managers, and so as a user I want to first filter the top managers down
to a specific city (e.g. Seattle). However, if I apply the City=Seattle
filter, I will filter out the reports as well, and so Jimmy and Patrick will
be filtered out. But that’s not what I want.

Is it possible to apply the filter only on the first level? Can I achieve
that using two pivot tables?

Thanks,
Patrick