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.
- Group members into new total members
- Easily switch views between different total types
- Filter which members are displayed in you report base 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
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.