sorting order of fields

by KristineHowkinsLange » Thu Feb 18, 2016 2:44 pm

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:
<OrderBy>
<FieldRef Name="ID" />
</OrderBy>
and I thought I might be able to specify the order there. Would that work?
Thanks,
Kristine
User avatar
KristineHowkinsLange
 
Posts: 8
Joined: Wed Feb 10, 2016 3:54 pm

by Rostislav » Thu Feb 18, 2016 6:09 pm

Hi there,

Go to Dashboard -> Advanced and paste the following code into the code box:
Code: Select all
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;
}
User avatar
Rostislav
 
Posts: 61
Joined: Fri Oct 09, 2015 10:51 am

by KristineHowkinsLange » Fri Feb 19, 2016 8:18 am

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:
<View>
<Query>
<OrderBy>
<FieldRef Name="ID" />
</OrderBy>
<Where>
<And>
<And>
<Neq>
<FieldRef Name="Status" />
<Value Type="Text">Archived</Value>
</Neq>
<Neq>
<FieldRef Name="Status" />
<Value Type="Text">On hold</Value>
</Neq>
</And>
<Neq>
<FieldRef Name="Status" />
<Value Type="Text">Published</Value>
</Neq>
</And>
</Where>
</Query>
<ViewFields>
<FieldRef Name="Author" />
<FieldRef Name="Status" />
</ViewFields>
<Aggregations Value="On">
<FieldRef Name="Author" Type="COUNT" />
<FieldRef Name="Status" Type="COUNT" />
</Aggregations>
<RowLimit>5000</RowLimit>
</View>

What do you think I'm doing wrong?
Rgds
Kristine
User avatar
KristineHowkinsLange
 
Posts: 8
Joined: Wed Feb 10, 2016 3:54 pm

by Rostislav » Fri Feb 19, 2016 10:56 am

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?
User avatar
Rostislav
 
Posts: 61
Joined: Fri Oct 09, 2015 10:51 am

by KristineHowkinsLange » Fri Feb 19, 2016 12:35 pm

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:
Plumsail_statusOrderingResult.png
Plumsail_statusOrderingResult.png (16.07 KiB) Viewed 1378 times

This is what the Caml query looks like:
Plumsail_statusOrderingCaml.png
Plumsail_statusOrderingCaml.png (62.63 KiB) Viewed 1378 times

and here are the settings for the chart:
Plumsail_statusOrderingChart.png
Plumsail_statusOrderingChart.png (31.03 KiB) Viewed 1378 times
User avatar
KristineHowkinsLange
 
Posts: 8
Joined: Wed Feb 10, 2016 3:54 pm

by Rostislav » Fri Feb 19, 2016 1:46 pm

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,
1. What do you see on the page?
2. What do you see in the console?
User avatar
Rostislav
 
Posts: 61
Joined: Fri Oct 09, 2015 10:51 am

by KristineHowkinsLange » Fri Feb 19, 2016 2:16 pm

This is what I see:
Plumsail_statusOrdering2t.png
Plumsail_statusOrdering2t.png (90.01 KiB) Viewed 1371 times
User avatar
KristineHowkinsLange
 
Posts: 8
Joined: Wed Feb 10, 2016 3:54 pm

by Rostislav » Fri Feb 19, 2016 4:42 pm

We can schedule a support session, please send your Team Viewer details to support@spchart.com.
User avatar
Rostislav
 
Posts: 61
Joined: Fri Oct 09, 2015 10:51 am

by gunderc » Mon Oct 10, 2016 5:20 pm

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)

Example.jpg
Example.jpg (15.68 KiB) Viewed 854 times


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?
gunderc
 
Posts: 3
Joined: Fri Feb 05, 2016 4:24 pm

by gunderc » Mon Oct 10, 2016 6:56 pm

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)

example2.jpg
example2.jpg (42.9 KiB) Viewed 853 times


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)
gunderc
 
Posts: 3
Joined: Fri Feb 05, 2016 4:24 pm

Next

Return to Dashboard Designer for SharePoint 2013