Author: Ian Britz
Mapping Filters are a component used to create a custom displays based on any number of specific filter selections. As Mapping Filters have a high degree of flexibility, there are a variety of use-cases for them. In this article, we'll examine the three most common:
- Use-Case 1: Advanced Filter Display
- Use-Case 2: Drive Multiple Filters with Single Selection
- Use-Case 3: Custom Sorting for Filters
Configure Mapping Filters
First, let's take a look at how to configure a Mapping Filter. In the Modeler, under Application in the right-hand navigation pane, select All Mapping Filters and choose Add.
Here we can name the Mapping Filter and choose which Dimensions it depends on.
When we click Save, Kepion creates a new table in the database with the following naming scheme:
[dbo].[MF_<Mapping Filter Name>]
The structure of the table is simple, which gives Mapping Filters their flexibility. There is a column for each of the Dimensions we chose and then a Display column. To build the Mapping Filter, we simply need to insert records for each combination of Dimension Members, and what should be displayed when they're selected.
Note: There's currently no UI to manage these mappings in Kepion. Please use SQL Server Management Studio to populate your Mapping Filter tables.
Advanced Filter Display
Now let's take a look at a common use-case for Mapping Filters: Filter Displays are a way to display the attribute values of a Dimension Member. However, Filter Displays can only display information from a single Dimension. If we want to display values that are dependent on multiple Dimensions, we need to use Mapping Filters.
For example, businesses with multiple entities, using multiple local currencies, need to consolidate all their data into a single reporting currency. If you wanted to display an entity's currency as a Filter Display, you would need to know both which Entity was selected, and whether we were viewing Local or Reporting Currency.
Step 1. To configure this, let's create a new Mapping Filter that depends on the Currency Type and Entity Dimensions.
Step 2. In the database, let's insert the records we need. For this configuration, if Reporting Currency is selected, the display is always 'USD'. Otherwise the display is the Entity's currency.
Step 3. In the Dashboard Editor, we can right-click and select Add Mapping Filter.
In the configuration window, let's select the Mapping Filter we configured. We also need to associate a Form with the Mapping Filter to drive the relationships between the filters. The No Mapping field allows us to specify what to display if the selected mapping doesn't exist, and the Read-Only option prevents users from using the Mapping Filter to change the filter selections.
Now when a combination we defined is selected, we'll see the specified display:
Drive Multiple Filters with Single Selection
A powerful facet of Mapping Filters is that they work bidirectionally. While the filter selections can drive what's displayed in the Mapping Filter, the Mapping Filter can also drive the filter selections. This is particularly useful if you have several filters that depend on one another.
For example, imagine you have Entity and Customer Filters. If you want to examine a particular customer, you'd have to first select the Entity and then the Customer, even though each Customer only belongs to a single Entity. This would be particularly tedious if you had several such layers of Filters, that always needed to be selected one-at-a-time.
However, with Mapping Filters, we can drive the filter selections based on the Mapping Filter Display. In the Mapping Filter table below, the Display column is a list of customer names, while the other two columns are the MemberIds for the associated Entity and Customer Dimension Members.
Now when we select the customer name in the Mapping Filter, the correct filter selections will be updated automatically.
If we were configuring this feature for a large number of filters and wanted to select some of them manually, and then auto-fill the rest, we could use the Filter results... section to scope the Mapping Filter by the selected Dimensions. For example, we could have users select the Entity filter first, and then scope the Mapping Filter results to just customers within that entity.
Custom Sorting for Filters
Another useful feature of Mapping Filters is that the order the records exist in the Mapping Filter table is the same order the options are displayed in Kepion. This allows you to create custom sorts or orderings of Dimension Members in a filter.
In this example, we have a list of customers in the Customer Dimension in no particular order.
Let's create two new Mapping Filters that both depend on just the Customer Dimension. One will be used to sort by the Customer ID, and the other will be used to sort by the Customer Name.
In the tables, we can insert the records sorted by the desired criteria.
Then we can replace the normal Customer filter in the Dashboard with a Mapping Filter, as the Mapping Filter selection updates the Customer filter behind the scenes.
To ensure that the Mapping Filters are always sorted correctly, we could add a Deployment Script similar to the one below:
TRUNCATE TABLE [dbo].[MF_Sort Customer by ID]
INSERT INTO [dbo].[MF_Sort Customer by ID]
WHERE [MemberId] > 0
ORDER BY [Customer Id]