Table Filter

A Table Filter is a selection type you can make for a hierarchy. It allows you to filter and group your cube members into new aggregations, dynamically based on Excel data.

You can:

• Group members into new total members
• Easily switch views between different total types
• Filter which members are displayed in you report based on either the cube or dynamic members

In this example I have a cube with the Months of the year as an existing hierarchy. I want to be able to view the data by Season or Number of Days, but these fields are not built into the cube.

I start by creating the following table in Excel with the cube members in one column and the custom attributes in other columns.

I click the toolbar button to add a Table Filter and select my Excel range as the data range

Here the data range has all the columns of data. The Members selection is set to "Month" - this is the column with the cube members in it.

From here I can create a few different reports.

New Total members

I can simply change the "Selections" option and pick Season as the column in the dropdown. Now the seasons are the grid selections and I can aggregate my data by properties that are not in the cube.

Filter by propery

If I create a new table filter I can set the No. Days column to a cell reference. In this cell I have the number 31, so I restict the months being reported on.

You can filter by multiple columns at once, and pick a range of cells to multi-select the filter values

Dynamic Selections

If I use and Excel cell to control the Selections value then I can make a report where the user can easily swith between the various custom properties I have defined. I have added a cell validation drop down to a cell, and the user can then switch views with a click.

These filter and selection types can be combined together to make more complex reports if you need them.