The Chart Label Order Changer
Download: Change Labels
Introduction
The Label Changer is efficient when wanting to order text values in a specific order which is not alphabetical order.
For example: Ordering Week days names as : Sunday, Monday… or Month names as: January, February etc…
The plugins works with multiple values on Bar Chart, Column Chart and Line Chart. The plugin also supports break by.
Steps
In order to add the Label Changer plugin, please follow the listed steps below:
Step 1:
Create a custom field in the EC that has the desired values with numbers at the start. Each value will begin with the ordered number (The first one with ‘01’ ) and the desired text to present in the dashboard.
Examples:
For the days of the week use this text:
CASE WHEN DayOfWeek(Date) = 1 THEN '01Sunday' WHEN DayOfWeek(Date) = 2 THEN '02Monday' WHEN DayOfWeek(Date) = 3 THEN '03Tuesday' WHEN DayOfWeek(Date) = 4 THEN '04Wednesday' WHEN DayOfWeek(Date) = 5 THEN '05Thursday' WHEN DayOfWeek(Date) = 6 THEN '06Friday' WHEN DayOfWeek(Date) = 7 THEN '07Saturday' ELSE '' END
For the Month Names use this text:
CASE WHEN getMonth(Date) = 1 THEN '01January' WHEN getMonth(Date) = 2 THEN '02February' WHEN getMonth(Date) = 3 THEN '03March' WHEN getMonth(Date) = 4 THEN '04April' WHEN getMonth(Date) = 5 THEN '05May' WHEN getMonth(Date) = 6 THEN '06June' WHEN getMonth(Date) = 7 THEN '07July' WHEN getMonth(Date) = 8 THEN '08August' WHEN getMonth(Date) = 9 THEN '09September' WHEN getMonth(Date) = 10 THEN '10October' WHEN getMonth(Date) = 11 THEN '11November' WHEN getMonth(Date) = 12 THEN '12December' ELSE '' END
Status sorting:
CASE
WHEN Status LIKE 'Fail' THEN '01Fail'
WHEN Status LIKE 'Medium' THEN '02Medium'
WHEN Status LIKE 'AboveAverage' THEN '03AboveAverage'
WHEN Status LIKE 'High' THEN '04High'
WHEN Status LIKE 'Amazing' THEN '05Amazing'
ELSE ''
END
Step 2:
Download and extract the enclosed ChangeLabels.rar zip file into the plugins folder:
C:\Program Files\Sisense\PrismWeb\plugins\, if the "plugins" folder is not there, please create it. If you are using version 7.2 and higher unzip the contents into your C:\Program Files\Sisense\app\plugins\ folder.
Step 3:
Edit config file under: (Right Click and Edit with Notepad ++)
C:\Program Files\Sisense\PrismWeb\plugins\ChangeLabels\config.js
Add the Custom fields (You can create more than one) you created to the dimensionsToChange with as:
["[TableName.FieldName]"]
For Example:
var dimensionsToChange = ["[Commerce.DayofWeek]","[Commerce.MonthName]"];
Step 4:
Create a chart with the custom field you created. Press Apply.
Refresh the dashboard and the values will appear without the numbers.
Example of the Plugin with DayofWeek field break by Age Range:
General Note: This plug-in actually removes the first 2 characters from the values of the field in the web display but maintains the order of the values according to the 01 / 02 / 03 etc.
In case you have values that begin with numbers please add 1. / 2. / 3. etc. to the beginning of the values (instead of 01 / 02 / 03).
-
I appreciate the effort to help, however I don't understand the logic of the config file, can you explain this using the Status example (The example cube and dashboard are corrupt to me).
You have a field in your cube source file call STATUS with the record types 'Fail','Medium','AboveAverage','High''Amazing'
You then create a custom field lets say called STATUS_SORT with converts the STATUS records to CASE '01Fail','02Medium','03AboveAverage','04High','05Amazing'
In this case I don't understand what to put in the config file - do I put in 'var dimensionsToChange = ["[Commerce.Status]"]', and then put the STATUS_SORT in the Rows value of the chart widget? - how does that create a relationship between STATUS and STATUS SORT
-
Just checking am I completely missing the point which is that the field I add to the Widget is STATUS_SORT, and that in the config I add 'var dimensionsToChange = ["[Commerce.STATUS_SORT]"]', ... because the whole point is that it's not the order sort that the script does, as what the script actually does is remove the first 2 characters of the STATUS_SORT Field, i.e. the script removes the first 2 characters of any field in the chart!
-
I'm also seeing the digits not being removed. I suspect it has to do with my EC not using a real table - I picked the first table in my database, and then did custom SQL query (the only way I can put in custom SQL, as far as I know) so my data isn't truly coming from that table, it's like a placeholder. In the EC Manager, the table shows as "dbo.AF00100". I've tried putting in various versions of the table and column names into the config files. Here's my config:
var dimensionsToChange = ["[AF00100.MonthSorted]","[dbo.AF00100.MonthSorted]","[MonthSorted]","[GL00100.MonthSorted]","[TMPResults.MonthSorted]"];
Any help would be greatly appreciated!
-
Yuming, from our experience that happens a lot.
Few out of the box troubleshooting suggestions that you can try that usually works for our clients:
1) If you are using the plugin on multiple widgets, try replacing the order of fields in the config file.
For example, replace this
"var dimensionsToChange = ["[avocado.csv.PriceRange]","[avocado.csv.VolumeRange]"];"
with
"var dimensionsToChange = ["[avocado.csv.VolumeRange]","[avocado.csv.PriceRange]"];
2) Try upgrading/downgrading to a slightly different version of the Sisense server.
3) Try completly removeing the widget, and remove rom the congiguration. Reset your IIS and re-add the widget.
Ping me if you need further assistance
Ravid
-
Hi Darrell,
For pivots, what I've used is creating an order table, adding that order as a row in the pivot and then making the width of the row 0.
So, in the cube, I either do a lookup or a join to get this setup:
Month | Order
Jan | 1
Feb | 2
Mar | 3
Apr | 4
...
Dec | 12And then in the pivot, I add ORDER to the rows and then MONTH to the rows
And then in the script, I use this
widget.on("ready", function(widget, args) {
var cat_column = "[fidx=0]";
//hide first column
$('[id="' + widget.oid+ '"]').find(cat_column).css("width", "0px"); -
We are still having issues with the leading numbers not being removed. We have tried everything listed above but nothing seems to work. The sorting part works great. We use an imported Excel file to handle all of our date dimensions and added a custom column as suggested to that import. All works but the removal of the leading characters in the x-axis label.
Any suggestions would be appreciated.
-
Hi Inbal,
This is my field - it is in date-time format.
It still sorts my months alphabetically.
I created a custom field as you sugged above.
However it looks horrible on the dashboard side with the number infront of it.
Any other suggestion?
I also don't know how to DOWNLOAD AND EXTRACT THE ENCLOSED CHANGELABELS.RAR ZIP FILE INTO THE PLUGINS FOLDER:
Your assistance will be highly appreciated. -
Hi Muhsin Kader -- I've actually gotten around this a bit with using an increasing number of spaces instead of numbers. Sisense seems to collapse down multiple spaces when it's displaying
Please sign in to leave a comment.
Comments
24 comments