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

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

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
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="//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>&nbsp;
    <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>


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

Code: Select all
<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>


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);
    logger.info(view);
    query.set_viewXml(view);
  view = view.replace('{End}', endD);
    logger.info(view);
    query.set_viewXml(view);
  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 () {     
      if((valD<=this.DueDate)&&(valD>=this.StartDate))
      {
        countFTE=countFTE+this.FTE;
      }
    });
    dataTmp.push({"DDay":valD,"FFTE":countFTE});   
    });
      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.process(el);
});

processor.subscribed = true;
}

return true;
}


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


Return to Dashboard Designer for SharePoint 2013

cron