Date Filter using a control

by luis.perez » Mon Dec 19, 2016 3:58 pm

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.
User avatar
Posts: 1
Joined: Mon Dec 19, 2016 3:47 pm

by Dmitry Kozlov » Tue Dec 20, 2016 2:10 pm


Yes, you can add any custom filters via JavaScript. Please, find a sample at our demo-site:

As you can see, there is a drop-down allowing you to filter data by agents. Here is the configuration:
User avatar
Dmitry Kozlov
Posts: 129
Joined: Wed Nov 12, 2014 3:24 pm

by tonio94 » Tue Jan 03, 2017 8:41 am

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 :

1) 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 :
Code: Select all
<!-- Include Required Prerequisites -->
<script type="text/javascript" src="//"></script>
<script type="text/javascript" src="//"></script>
<link rel="stylesheet" type="text/css" href="//" />
<!-- Include Date Range Picker -->
<script type="text/javascript" src="//"></script>
<link rel="stylesheet" type="text/css" href="//" />

<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>&nbsp;
    <span></span> <b class="caret"></b>

var startD="";
var endD="";
var between = [];
var currentDate;

<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'));

        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);


2) A custom CAML query to get my results fitered depening on the list I selected :

Code: Select all
      <FieldRef Name="ID" />
          <FieldRef Name="DueDate" />
          <Value Type="DateTime" IncludeTimeValue="TRUE">{End}</Value>
          <FieldRef Name="StartDate" />
          <Value Type="DateTime" IncludeTimeValue="TRUE">{Start}</Value>
    <FieldRef Name="LinkTitle" />
    <FieldRef Name="FTE" />
    <FieldRef Name="DueDate" />
    <FieldRef Name="StartDate" />
    <FieldRef Name="Status" />
    <FieldRef Name="Estimation" />
    <FieldRef Name="ID" />
  <RowLimit Paged="TRUE">1000</RowLimit>
  <Aggregations Value="Off" />

3) A custom handlers.requestInit to get my custom CAML query processed (you can find the startD/endD for the selected range from the Javascript)

Code: Select all
handlers.requestInit = function(query, logger) {
    var view = query.get_viewXml(); 
    view = view.replace('{Start}', startD);;
  view = view.replace('{End}', endD);;
  return true;

4) A custom handlers.requestSuccess to process my results and get an array of dates/workload for the selected range

Code: Select all
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 () {     
      data.items = dataTmp;
    return true;

5) 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.subscribed = true;

return true;

I can then display the fields DDay as category and FFTE as value
Posts: 4
Joined: Thu Dec 08, 2016 4:09 pm

Return to Dashboard Designer for SharePoint 2013