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 combine Selection columns and Filters, for example you could show the data by season for months with 31 days.

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 an 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.