Hi
I have a SharePoint list where topics are organized by status. I’ve managed to get some useful bar charts that display statuses by writer. However these are sorted alphabetically. I’d like to sort them logically, for exampled, planned, writing, review, ready for publishing, published…
How do i do this? I saw a similar question, but I didn’t quite see how the answer solved my question.
I can see in the CAML query, there is this:
and I thought I might be able to specify the order there. Would that work?
Thanks,
Kristine
Hi there,
Go to Dashboard -> Advanced and paste the following code into the code box:
[code]
var handlers = {};
handlers.preRender = function(config, logger) {
logger.debug('Configuration: ', config);
//this will be your ordering, left to right (or top to bottom). Here you will need to place the names of your statuses exactly as they appear in the list.
var ordering = [‘exampled’, ‘planned’, ‘writing’, ‘review’, ‘ready for publishing’, ‘published’];
for (var i = 0; i < config.series.length; i++) {
config.series[i].data.sort(function(a, b) {
//here you will need to replace ‘status’ with the exact internal name of the status field (one way of finding it out is by looking at the CamlQuery section in Data Source -> SharePoint list)
return ordering.indexOf(a.status) - ordering.indexOf(b.status);
});
};
return true;
}[/code]
Hi
Thanks for getting back to me.
I followed your instructions, but they didn’t work for me. I couldn’t find the section in the CamlQuery for the internal name of the Status field, so this is what I pasted:
var handlers = {};
handlers.preRender = function(config, logger) {
logger.debug('Configuration: ', config);
//this will be your ordering, left to right (or top to bottom). Here you will need to place the names of your statuses exactly as they appear in the list.
var ordering = [‘Planned’, ‘Writing’, ‘Technical review’, ‘Language review’, ‘Back to writer’, ‘Finalizing’, ‘Pending’, ‘Ready for publishing’, ‘published’];
for (var i = 0; i < config.series.length; i++) {
config.series[i].data.sort(function(a, b) {
//here you will need to replace ‘status’ with the exact internal name of the status field (one way of finding it out is by looking at the CamlQuery section in Data Source -> SharePoint list)
return ordering.indexOf(a.Status) - ordering.indexOf(b.Status);
});
};
return true;
}
This is what I see in my CamlQuery:
Archived
On hold
Published
5000
What do you think I’m doing wrong?
Rgds
Kristine
What you’re doing seems to be fine. Can you tell what you mean by “not working” - what is exactly happening?
Also, when you Preview the chart, before you click “Preview” open up your console (F12), click “Preview” and tell us if there are any errors and what they are.
And, what type of field is Status?
Answering your questions.
Here is the code that I used:
var handlers = {};
handlers.preRender = function(config, logger) {
logger.debug('Configuration: ', config);
//this will be your ordering, left to right (or top to bottom). Here you will need to place the names of your statuses exactly as they appear in the list.
var ordering = ['Planned', 'Writing', 'Technical review', 'Language review','Back to writer', 'Finalizing', 'Pending', 'Ready for publishing', 'published'];
for (var i = 0; i < config.series.length; i++) {
config.series[i].data.sort(function(a, b) {
//here you will need to replace 'status' with the exact internal name of the status field (one way of finding it out is by looking at the CamlQuery section in Data Source -> SharePoint list)
return ordering.indexOf(a.status) - ordering.indexOf(b.status);
});
};
return true;
}
I'd like the bar chart to organize the statuses so that the first one is Planned, then Writing, then Language review.... but as you can see the statuses are still displayed alphabetically. This doesn't make sense. Here's an image of the view:
This is what the Caml query looks like:
and here are the settings for the chart:
Dear Kristine,
I understand what your requirements are and I’ve already seen your code from the last post. What I need you to tell me is this. Press F12 on your keyboard. Find the “console” tab. Click “Peview” on the page. Now,
- What do you see on the page?
- What do you see in the console?
Does the code need to be different if you are sorting a number field?
Data Source - SharePoint list
Fields:
Total - Number
BIAID - Number
WaterfallGroup - Number
Data Source - Aggregation
Group by: WaterfallGroup
Dashboard - Chart
Type: Waterfall
[x] Display each group as a separate series
Category: Field: BIAID
Value: Sum: Total
Dashboard - Advanced
var handlers = {};
handlers.preRender = function(config, logger) {
logger.debug('Configuration: ', config);
var series = config.series;
var ordering = [‘77’, ‘10’, ‘109’, ‘115’, ‘85’, ‘110’];
/* Sort each series array (which are the items by status) by modified date so weeks are sorted correctly */
for (var i = 0; i < series.length; i++) {
series[i].data.sort(function(a, b) {
return a.BIAID - b.BIAID;
});
};
/* Sort series by the status they contain */
series.sort(function(a, b) {
return a.data[0].BIAID - b.data[0].BIAID;
});
return true;
}
The BIAID needs to display in the specified order.
There are two numbers assigned in the WaterfallGroup - 1 & 2
BIAID (77, 10, 109, 115, 85) are assigned “1” The five BIAID’s are dependent
BIAID (110) is assigned “2” independent
110 is displaying at the right side of the chart which is correct
However, the other five display in ascending order (10, 77, 85, 109, 115)
Other issue is:
I like to use this code in Dashboard - Style - Labels:
Template: #=data.value && data.value.toString().length ? data.value +’ ’ : ‘’ #
And I would like to limit the format: to 2 decimals - {0:n2}
but the template ignores the format. or I am overriding the format with the template?
Can I use this template to clean up the visual aspect of the labels in addition to specifying the format?
Never mind! 
I figured out a way to sort the Number field in the order required.
I removed the sort functionality out of the “Advanced Tab” completely.
Created a field in SharePoint: Field name: ChartOrder
In the SharePoint view I use the field “ChartOrder” to sort the items in the specific order needed.
(#77 = 1, #10 = 2, #109 = 3, #115 = 4, #85 = 5, #110 = 6)
Now both charts (days & hours) display in the correct order.
I haven’t figured out a solution for the Labels issue yet (Template and Format)
Hello.
I tried the first answer here with no success. Turns out that sorting code provided in the Dashboard Advanced tab only sorts each series, but not the series together.
[code]
var handlers = {};
handlers.preRender = function(config, logger) {
logger.debug('Configuration: ', config);
//this will be your ordering, left to right (or top to bottom). Here you will need to place the names of your statuses exactly as they appear in the list.
var ordering = [‘Utkast’, ‘Saksbehandling’, ‘Følge opp’, ‘Lukket’]; //make sure this has the same length as the series.
var tempSerie;
for(var i=0; i<config.series.length; i++){
tempSerie = config.series[i];
if(config.series[i].name !== ordering[i]){//if name doesnt match, find the correct series and replace.
for(var j=0; j<config.series.length; j++){//get index
if(ordering[i] === config.series[j].name ){
config.series[i] = config.series[j];
config.series[j] = tempSerie;
}
}
}
}
for(var i=0;i<ordering.length;i++){
config.series[i].name = ordering[i];
}
return true;
}[/code]
Re ordering the config.series[x] instead of reordering config.series[x].data…