When using a tabular data source your first thought may be to use the Tabular Values filter type in Dashboard Designer when wanting to apply a filter to your data source. However, the Tabular Values filter, admittedly poorly named (we are working on that), should not be used when creating a filter to apply to a tabular data source. Let’s look at what happens when it is used.
First, examine my data source, a SharePoint list containing KPIs, their values and geography slices.
Now, when I create a scorecard off of this data source and want to apply a geography filter in a dashboard I can see there are multiple filter types to choose from. Again, your first thought might be to create a Tabular Values filter type. But, what you should select is the Member Selection filter type.
I placed 2 copies of my scorecard into the following dashboard. With the scorecard on the left I applied a Tabular Values filter pointing to the Geography column in my data source. With the scorecard on the right I created a Member Selection filter also pointing to the members in the Geography column of my data source. Note, that in order for the filters to not produce error messages, I had to set up the filter link settings as follows:
For the Tabular Values filter:
For the Member Selection filter:
In my published dashboard, first notice first that the Tabular Values filter will list each member multiple times in the filter selection dialog.
Additionally, the Tabular Values filter ignores aggregations – therefore, it will not aggregate the values correctly, in fact what it will do is add the KPI values together across all geographies regardless of which member I select in the filter. The Member Selection filter will work as expected and only show the 4 members once in the filter selection.
Therefore, when using Tabular data sources, you almost always create Member Selection filters to apply to those tabular data sources in order to get the expected behavior.
So, what purpose does the Tabular Values filter serve? For this, let’s consider the following scenario.
Suppose we have 2 different cubes or 2 different data sources that have the same members, but different hierarchy paths or dimension names. The Tabular Values filter can be used to construct a mapping table to map members to their appropriate fully qualified names and then one filter can be used to pass the correct values to 2 different report views in your dashboard, each report view being sourced from a different data source.
Consider the following example (note, I am using 2 unrelated cubes but hopefully you can extrapolate to the usefulness of this feature if you have related cubes with 2 different hierarchies or member names).
I will use the Adventure Works DB which has a Customer.Geography hierarchy of the following nature:
I will also use the Project REAL cube which has a Customer.By Geography hierarchy of the following nature:
Now, if I want to include some views being sourced from each cube in a single dashboard page and have only one Geography filter, I could create a Tabular Values filter to accomplish this goal. This would first require that I create a data source for my Tabular Values filter in the following form. One column for the name of the member I want in the filter, one column for the parent member name, one column for the Member UniqueName of that member in the Adventure Works cube and one column for the Member UniqueName of that member in the Project REAL cube. I will only add a few members here to show the pattern:
I will now, create my Tabular Values filter as follows:
1. Create a new filter – select Tabular Values as the type.
2. Name it and select your Tabular data source that contains your filter values as the data source for the filter.
3. Click through the Preview screen and then set the key and parent settings as follows:
a. Set the Key to the column that contains your member names.
b. Set the Parent key to the column that contains your parent member names.
c. Set the Display value to the column that contains your member names.
4. Select Tree as the type and then click Finish.
Now, link the filter to each of the different views in your dashboard by the appropriate column name. In my case, I link as follows:
For the scorecard view from the Adventure Works data source I link as follows:
For the analytic grid view from the Project REAL data source I link as follows:
Now, when viewing my dashboard I see the following:
Note, that my filter is a tree and that my members are now passing to each of the different views from different data sources:
NOTE: this scenario will NOT work when you have KPIs in a single scorecard coming from 2 different data sources because you can only pass one filter link into a single object in the dashboard.
Alyson Powell Erwin (Microsoft)