I use a lot of BI systems like Cognos and Power BI. One feature that is highly used is the calendar filter to the dashboards. Is there a way of filtering the charts using an external control or calendar control?? I would like to build a dashboard with various charts linking to a date filter on the top. Im more on the administration side than the coding so please take me step by step. I can work with javascripting but need a good guide. Since this is pretty standard I think it will be beneficial for the community.
Hi,
Yes, you can add any custom filters via JavaScript. Please, find a sample at our demo-site:
http://www.spchart.com/demo/helpdesk-dashboard
As you can see, there is a drop-down allowing you to filter data by agents. Here is the configuration:
http://www.spchart.com/demo/helpdesk-dashboard/config
Hi Luis,
I'm working on the same kind of issue to calculate the daily task workload on a range based on a SharePoint task list (with an additional FTE field that correspond to my daily workload for each task). Here's how I proceeded :
- One javascript file to handle the date picker side. It let me select a start date/end date and also to prepare an array with the list of days between the range :
<!-- Include Required Prerequisites -->
<script type="text/javascript" src="//cdn.jsdelivr.net/jquery/1/jquery.min.js"></script>
<script type="text/javascript" src="//cdn.jsdelivr.net/momentjs/latest/moment.min.js"></script>
<link rel="stylesheet" type="text/css" href="//cdn.jsdelivr.net/bootstrap/3/css/bootstrap.css" />
<!-- Include Date Range Picker -->
<script type="text/javascript" src="//cdn.jsdelivr.net/bootstrap.daterangepicker/2/daterangepicker.js"></script>
<link rel="stylesheet" type="text/css" href="//cdn.jsdelivr.net/bootstrap.daterangepicker/2/daterangepicker.css" />
<div id="reportrange" class="pull-right" style="background: #fff; cursor: pointer; padding: 5px 10px; border: 1px solid #ccc; width: 30%">
<i class="glyphicon glyphicon-calendar fa fa-calendar"></i>
<span></span> <b class="caret"></b>
</div>
<script>
var startD="";
var endD="";
var between = [];
var currentDate;
</script>
<script type="text/javascript">
$(function() {
var start = moment();
var end = moment().add(29, 'days');
function cb(start, end) {
$('#reportrange span').html(start.format('MMMM D, YYYY') + ' - ' + end.format('MMMM D, YYYY'));
startD=start.format('YYYY-MM-DD');
endD=end.format('YYYY-MM-DD');
checkRange(startD,endD);
}
$('#reportrange').daterangepicker({
startDate: start,
endDate: end,
ranges: {
'This Week': [moment().startOf('isoWeek'), moment().endOf('isoWeek')],
'Next 7 Days': [moment(), moment().add(6, 'days')],
'Next Week': [moment().add(1,'week').startOf('isoWeek'), moment().add(1,'week').endOf('isoWeek')],
'This Month': [moment().startOf('month'), moment().endOf('month')],
'Next 30 Days': [moment(),moment().add(29, 'days')],
'Next Month': [moment().add(1, 'month').startOf('month'), moment().add(1, 'month').endOf('month')],
'This Quarter': [moment().startOf('quarter'), moment().endOf('quarter')],
'Next Quarter': [moment().add(1, 'quarter').startOf('quarter'), moment().add(1, 'quarter').endOf('quarter')]
}
}, cb);
cb(start, end);
function checkRange(start, end) {
currentDate = new Date(start);
end=new Date(end);
between = [];
while (currentDate <= end) {
between.push(new Date(currentDate));
currentDate.setDate(currentDate.getDate() + 1);
}
}
});
</script>
- A custom CAML query to get my results fitered depening on the list I selected :
<View>
<Query>
<OrderBy>
<FieldRef Name="ID" />
</OrderBy>
<Where>
<Or>
<Lt>
<FieldRef Name="DueDate" />
<Value Type="DateTime" IncludeTimeValue="TRUE">{End}</Value>
</Lt>
<Gt>
<FieldRef Name="StartDate" />
<Value Type="DateTime" IncludeTimeValue="TRUE">{Start}</Value>
</Gt>
</Or>
</Where>
</Query>
<ViewFields>
<FieldRef Name="LinkTitle" />
<FieldRef Name="FTE" />
<FieldRef Name="DueDate" />
<FieldRef Name="StartDate" />
<FieldRef Name="Status" />
<FieldRef Name="Estimation" />
<FieldRef Name="ID" />
</ViewFields>
<RowLimit Paged="TRUE">1000</RowLimit>
<Aggregations Value="Off" />
</View>
- A custom handlers.requestInit to get my custom CAML query processed (you can find the startD/endD for the selected range from the Javascript)
handlers.requestInit = function(query, logger) {
var view = query.get_viewXml();
view = view.replace('{Start}', startD);
logger.info(view);
query.set_viewXml(view);
view = view.replace('{End}', endD);
logger.info(view);
query.set_viewXml(view);
return true;
}
- A custom handlers.requestSuccess to process my results and get an array of dates/workload for the selected range
handlers.requestSuccess = function (data, logger) {
var dataTmp=[];
//for each day selected in the range "between[]" (see javascript dates.js)
$.each(between, function (i,val) {
//we set the date to correct format (H-1)
var valD=new Date(val.getFullYear(),val.getMonth(),val.getDate());
var countFTE=0;
//we iterate in the list to see for each task if it's impacted by the current day, if yes we add it's daily FTE to the count
$.each(data.items, function () {
if((valD<=this.DueDate)&&(valD>=this.StartDate))
{
countFTE=countFTE+this.FTE;
}
});
dataTmp.push({"DDay":valD,"FFTE":countFTE});
});
data.items = dataTmp;
return true;
}
- A custom handlers.finish to relaunch my query each time I change my range
handlers.finish = function(data, logger, processor, el) {
//used to reload chart after dates update
logger.debug('Data is processed: ', data);
if (processor && !processor.subscribed) {
$('#refresh').click(function () {
el.html('<img alt="loading..." src="/_layouts/15/images/gears_anv4.gif" />');
processor.process(el);
});
processor.subscribed = true;
}
return true;
}
I can then display the fields DDay as category and FFTE as value
Where to put the JavaScript file?