Project Dashboard

List: Tasks
Fields: Date Completed, Estimation, ID, Due Date, Task Status

<View>
  <Query>
    <OrderBy>
      <FieldRef Name="ID" />
    </OrderBy>
    <Where>
      <Eq>
        <FieldRef Name="Sprint" />
        <Value Type="Text">Sprint #3</Value>
      </Eq>
    </Where>
  </Query>
  <ViewFields>
    <FieldRef Name="LinkTitle" />
    <FieldRef Name="DateCompleted" />
    <FieldRef Name="Estimation" />
    <FieldRef Name="ID" />
    <FieldRef Name="TaskStatus" />
    <FieldRef Name="TaskDueDate" />
  </ViewFields>
  <RowLimit Paged="TRUE">1000</RowLimit>
  <Aggregations Value="Off" />
</View>

Group by: Day

Day field is defined in the requestSuccess JavaScript handler.

Aggregations:
Hours = sum of Estimation Tasks = count of ID DueDate = max of TaskDueDate Date = max of DateCompleted

var handlers = {};
 
handlers.requestSuccess = function (data, logger) {
    $.each(data.items, function () {
        if (this.TaskStatus == 'Completed') {
            this.Day = this.DateCompleted.getDate();
        }
    });
    return true;
}
 
handlers.aggregationSuccess = function (data, logger) {
    var total = 0
    $.each(data.groups, function () {
        total += this.Hours;
    });
 
    data.groups = data.groups.filter(function (value) {
        return Boolean(value.value);
    });
 
    var remaining = total;
    $.each(data.groups, function () {
        this.RemainingHours = remaining;
        remaining -= this.Hours;
    });
 
    return true;
}

In the requestSuccess handler we define Day field for each task and populate it with the day of month when the task has been completed. This field allows us to group tasks by day. In the aggregationSuccess handler we calculate remaining hours for each group of tasks and remove opened tasks from the array of groups.

Type: Area
Display each group as a separate series: False
Category: Date
Value: RemainingHours
Aggregate over category: True
Function: first

Series:
Stack series: False

Category Axis:
Label format: {0:MM/dd}

var handlers = {};
handlers.preRender = function (config, logger) {
    logger.debug('Configuration: ', config);
 
    var data = config.series[0].data;
    config.series[0].missingValues = 'gap';
 
    config.series.push({
        name: 'Ideal Trend',
        type: 'line',
        data: [{
            Date: data[0].Date,
            RemainingHours: data[0].RemainingHours
        }, {
            Date: data[0].DueDate,
            RemainingHours: 0
        }],
        field: 'RemainingHours',
        categoryField: 'Date',
        markers: {
            visible: false
        }
    })
 
    config.series.push({
        name: 'Completed Work',
        type: 'column',
        data: data,
        field: 'Hours',
        categoryField: 'Date',
        tooltip: {
            visible: true,
            template: 'Tasks: #= dataItem.Tasks #<br>Hours: #= dataItem.Hours #'
        }
    });
 
    config.categoryAxis.max = config.series[0].data[0].DueDate;
 
    window.config = config;
    return true;
 
}

Here we define additional series for Ideal Trend and Completed Work diagrams with their own chart types: line and column.

List: Tasks
Fields: Date Completed, Estimation, ID, Start Date, Due Date, Task Status

Group by: Day

Day field is defined in the requestSuccess JavaScript handler.

Aggregations:
Hours = sum of Estimation Tasks = count of ID DueDate = max of TaskDueDate Date = max of DateCompleted

var handlers = {};
 
handlers.requestSuccess = function (data, logger) {
    $.each(data.items, function () {
        if (this.TaskStatus == 'Completed') {
            this.Day = this.DateCompleted.getDate();
        }
    });
    return true;
}
 
handlers.aggregationSuccess = function (data, logger) {
    var total = 0
    $.each(data.groups, function () {
        total += this.Hours;
    });
 
    data.groups = data.groups.filter(function (value) {
        return Boolean(value.value);
    });
 
    var remaining = total;
    $.each(data.groups, function () {
        this.RemainingHours = remaining;
        remaining -= this.Hours;
    });
 
    return true;
}

In the requestSuccess handler we define Day field for each task and populate it with the day of month when the task has been completed. This field is used only to group data by day, so if your project takes more than one month, you should concatenate the day with the month to group data correctly: this.Day = this.DateCompleted.getDate() + ‘_’ + this.DateCompleted.getMonth(). In the aggregationSuccess handler we calculate remaining hours for each group of tasks and remove opened tasks from the array of groups.

Type: Area
Display each group as a separate series: False
Category: Date
Value: RemainingHours
Aggregate over category: True
Function: first

Series:
Stack series: False

var handlers = {};
handlers.preRender = function (config, logger) {
    logger.debug('Configuration: ', config);
 
    var data = config.series[0].data;
    config.series[0].missingValues = 'gap';
 
    config.series.push({
        name: 'Ideal Trend',
        type: 'line',
        data: [{
            Date: data[0].Date,
            RemainingHours: data[0].RemainingHours
        }, {
            Date: new Date(2014, 07, 31), // project's deadline
            RemainingHours: 0
        }],
        field: 'RemainingHours',
        categoryField: 'Date',
        markers: {
            visible: false
        }
    });
 
    config.series.push({
        name: 'Completed Hours',
        type: 'column',
        data: data,
        aggregate: 'sum',
        field: 'Hours',
        categoryField: 'Date',
    });
 
    window.config = config;
 
    return true;
}

Here we define additional series for Ideal Trend and Completed Work diagrams with their own chart types: line and column.

List: Tasks
Fields: Date Completed, Estimation, ID, Task Status

<View>
  <Query>
    <OrderBy>
      <FieldRef Name="ID" />
    </OrderBy>
    <Where>
      <Eq>
        <FieldRef Name="Sprint" />
        <Value Type="Text">Sprint #3</Value>
      </Eq>
    </Where>
  </Query>
  <ViewFields>
    <FieldRef Name="LinkTitle" />
    <FieldRef Name="Estimation" />
    <FieldRef Name="DateCompleted" />
    <FieldRef Name="TaskStatus" />
    <FieldRef Name="ID" />
  </ViewFields>
  <RowLimit Paged="TRUE">1000</RowLimit>
  <Aggregations Value="Off" />
</View>

Group by: empty

As you can see the empty field doesn’t exists in the data source. We use it here to calculate aggregate values over all rows in the data set because for each row it equals to “undefined”, thus we get a single group containing all items.

Aggregations:
Hours = sum of Estimation Tasks = count of ID

var handlers = {};
handlers.aggregationSuccess = function (data, logger) {
    var hours = 0;
    var tasks = 0;
 
    $.each(data.items, function () {
        if (this.TaskStatus == 'Completed') {
            hours += this.Estimation;
            tasks++;
        }
    });
 
    data.items = [{ Hours: data.groups[0].Hours, Tasks: data.groups[0].Tasks, Category: 'Required' },
                  { Hours: hours, Tasks: tasks, Category: 'Actual' }];
    data.groups[0].items = [data.items[0]];
    data.groups[0].value = 'Required';
 
    data.groups.push({
        value: 'Actual',
        items: [data.items[1]]
    });
 
    return true;
}

The code above calculates completed and remaining hours and replaces the data with calculated values.

Type: Bar
Display each group as a separate series: True
Category: Category
Value: Hours
Aggregate over category: False

List: Tasks
Fields: Date Completed, Estimation, ID, Task Status

Group by: empty

As you can see the empty field doesn’t exists in the data source. We use it here to calculate aggregate values over all rows in the data set because for each row it equals to “undefined”, thus we get a single group containing all items.

Aggregations:
Hours = sum of Estimation Tasks = count of ID

var handlers = {};
 
handlers.aggregationSuccess = function (data, logger) {
    var hours = 0;
    var tasks = 0;
 
    $.each(data.items, function () {
        if (this.TaskStatus == 'Completed') {
            hours += this.Estimation;
            tasks++;
        }
    });
 
    data.items = [{ Hours: data.groups[0].Hours, Tasks: data.groups[0].Tasks, Category: 'Required' },
                  { Hours: hours, Tasks: tasks, Category: 'Actual' }];
    data.groups[0].items = [data.items[0]];
    data.groups[0].value = 'Required';
 
    data.groups.push({
        value: 'Actual',
        items: [data.items[1]]
    });
 
    return true;
}

The code above calculates completed and remaining hours and replaces the data with calculated values.

Type: Bar
Display each group as a separate series: True
Category: Category
Value: Hours
Aggregate over category: False

List: Tasks
Fields: Assigned To, Task Status

Group by: Task Status

Type: Bar
Display each group as a separate series: True
Category: Assigned To
Value: Assigned To
Aggregate over category: True
Function: first

Stack series: True