In the previous post, we wrote about the new filter functionality we are planning to add to Ranet OLAP WPF. One of them is applying filters to hierarchy levels in the pivot table. One of the features in the pivot table hierarchy filter will be the top filter. By applying it, you will be able to limit the hierarchy member set to the specified number of members you require. It will be possible to apply a top filter to each hierarchy level separately.
Let’s see how top pivot table hierarchy filter is going to work how it can impact the hierarchy value aggregation.

We have a “Geography” hierarchy, which contains “Country”, “State-Province”, “City” and “Postal Code” levels.

pivot-table-hierarchy-filter-all-geographies

We want to see which three countries have the highest reseller sales amount. So, we set a top filter to Country level by Reseller Sales Amount parameter.

pivot-table-hierarchy-filter-country

As you can see, only three countries with the biggest reseller sales amount value remain.  The values of Country members are the same. However, the whole hierarchy aggregated value (All Geographies) has changed and now equals to the summary of the three remaining child members.

If we also want to see three provinces within each country that have the highest reseller sales amount we can apply another top pivot table hierarchy filter to State-Province level.

pivot-table-hierarchy-filter-province

Now, there are only three child members in each Country member. Country member value equals to the sum of the remaining child members, and the values in Country and All Geographies levels have changed accordingly.

If you set a top filter to several hierarchy levels, they will apply to data in the sequence that they follow in the hierarchy.

We’ll continue to write about the new features in the upcoming Ranet OLAP builds.

If you have any questions about the pivot table hierarchy filter or our other plans for Ranet OLAP WPF – be free to ask us.