Sorting the Filter Entities using the Remove Digits Prefix from Filter
** Please also see The Chart Label Order Changer
Download Link: Remove Digits Filter Members
Introduction
The “Remove Digits Prefix from Filter” plugin enables sorting of the entities within the filter selection based on prefixes without displaying the prefixes themselves. The following article describes the implementation steps.
Supported Prefixes
The prefix must be a digit and you should add a space to separate between the prefix and the value.
The plugin supports 2 types of prefixes:
- [Digit].<space>[Name]. for example: 1. AAA
- [Digit]<space>[Name], for example: 1 AAA
Business Case
In big data sets, there is a strong possibility that the list of available entities for filtering would be very long, for example: tens of product categories, hundreds of customers, etc.
There are cases where there are more logical and efficient ways to sort these entities, rather than by plain alphabetical order, for example:
- Based on expected frequency of use.
- Based on a certain rule or field.
Example
- Source table with prefix:
- Filter list sorted by prefix, but not showing them:
Steps
Step 1 – Download and extract the enclosed folder into the plugin folder:
V7.1 and lower:
C:\Program Files\Sisense\PrismWeb\plugins , if the “plugins” folder doesn’t exist, create it.
V7.2 and higher unzip the contents into your
C:\Program Files\Sisense\app\plugins\ folder.
The plugin is available for download here.
Step 2 – Add prefix to your data
If your data doesn’t contain prefixes originally, you can add a prefix by creating a custom field in the ElastiCube. Remember – there must be a space between the prefix and the value itself.
For example, if the “Dairy Products” category should appear first and the “Beverages” category should appear second, the SQL expression will be:
CASE
WHEN [Category] = 'Dairy Products' THEN '1. Dairy Products'
WHEN [Category] = ‘Beverages' THEN '2. Beverages'
ELSE 'Other'
END as [Categories Filter]
Step 3 – Configure the “config.js” file
The config file:
var dimensionsToTruncateConfig = {
tables:{
Sheet1:["Days"] // Enter the table and dimensions’ names.
}
};
Replace the default configuration with yours in this line:
Sheet1:["Days"] should appear like "Table Name" : ["Dimension Name"]
- In case the table name has spaces or special signs, it should be written within quotes.
- Dimensions should always be written within brackets and quotes [" "].
- Separate multiple dimensions by comma: ["Dimension 1", "Dimension 2"].
Step 4 – Save the script and refresh the Dashboard
Download Link: Remove Digits From Filter Members
Thanks to Lidor Rosencovich for the plugin!
-
To make sure the value that displays on the actual filter when you view the dashboard is correct, you must also update the directive for ucBoxText. Unfortunately I was not able to trim the value successfully. I have posted my code below. It successfully sends the value to the convertItem function, but it does not alter it because it is not type "String". When I tried to convert it to string, I get undefined. Unfortunately, I am not a jquery expert. Any help resolving this would be great. If I had to guess, I bet I need to get the value from somewhere other than $scope.parent.display, but unfortunately trial and error did not yield a successful result.
mod.directive('ucBoxText', [
function () {
return {transclude: false,
restrict: 'C',
link: function link($scope, lmnt, attrs) {if ($scope.$parent.in && $scope.$parent.in.table && $scope.$parent.in.column){
if (shouldConvertField($scope.$parent.in.table,$scope.$parent.in.column)){
$(lmnt).text(convertItem($scope.$parent.in.table,
$scope.$parent.in.column,
$scope.$parent.display));
}
else{
$(lmnt).text($scope.$parent.display);
}
}}
}
}]); -
Thanks Chen,
Another way to add the prefix number is using the rank function.There's a need sometimes to order the value in a descending way - and not manually adjust the numbersExample on creating the dim table:SELECT Category, tostring(r) + '.' + CategoryFROM (SELECT DISTINCT c.Category,rankdesc(c.Category) AS rfrom [category] c) data -
I just noticed the issue with date filters as well. I am not the most familiar with Javascript but believe I have a half-baked solution. The configuration asks you to specify the columns for which you want the prefix truncated. However, the code only checks if a valid table, column, and value exist before doing anything. Later, it checks whether the column is in your config before deciding whether to remove the first two spaces. At this point it is too late, as the entire value will be converted to text and returned. So, even if it is not one of the columns you wish to alter, it is still converted to text. My solution is to alter the mod.directive('span' function to check if the column is in the list in the config before doing anything. That way, if it is not specified the script will not convert it to text. Open the main.js file and replace the "mod.directive('span'" section with:
mod.directive('span', [
function () {
return {transclude: false,
restrict: 'E',
link: function link($scope, lmnt, attrs) {
if ($$get($scope,"$parent.$parent.in.table") && $$get($scope,"$parent.$parent.in.column")){
if (shouldConvertField($scope.$parent.$parent.in.table,$scope.$parent.$parent.in.column)){
var text = convertItem($scope.$parent.$parent.in.table,
$scope.$parent.$parent.in.column,
$scope.$parent.$parent.member);$(lmnt).text(text);
setTimeout(function(){
$(lmnt).prop("title",text);
});
}
}
}
}
}]);Save the file, turn the plugin off and then on again in the web app to make it compile, and then your dates should be fixed. Let me know if you have issues.
-
Ok, I found out the code I posted above still had issues. I am not sure if these are caused by the 7.1 upgrade, but at any rate... please fix follow the directions above with this code:
mod.directive('span', [
function () {
return {transclude: false,
restrict: 'E',
link: function link($scope, lmnt, attrs) {
if ($$get($scope,"$parent.$parent.in.table") && $$get($scope,"$parent.$parent.in.column")){
if (shouldConvertField($scope.$parent.$parent.in.table,$scope.$parent.$parent.in.column)){
var text = convertItem($scope.$parent.$parent.in.table,
$scope.$parent.$parent.in.column,
$scope.$parent.$parent.member);
setTimeout(function(){
$(lmnt).text(text);
$(lmnt).prop("title",text);
});
}
}
}
}
}]); -
This plugin breaks filter > ranking
TypeError: Cannot read property 'toLowerCase' of undefined
at shouldConvertField (main.cc317a9ee86d3731420a.js:32)
at Object.$naming.humanize (main.cc317a9ee86d3731420a.js:32)
at y (common.js?g=342dd8bfd58dc567945d4244cb22f29a:1)
at Object.getFilterDescription (common.js?g=342dd8bfd58dc567945d4244cb22f29a:1)
at Object.getTags (app-main.js?g=342dd8bfd58dc567945d4244cb22f29a:1)
at C (app-main.js?g=342dd8bfd58dc567945d4244cb22f29a:1)
at Object.filterChange (app-main.js?g=342dd8bfd58dc567945d4244cb22f29a:1)
at d.e.apply (app-main.js?g=342dd8bfd58dc567945d4244cb22f29a:1)
at app-main.js?g=342dd8bfd58dc567945d4244cb22f29a:1
at app-main.js?g=342dd8bfd58dc567945d4244cb22f29a:1
Please sign in to leave a comment.
Comments
12 comments