Project Dashboard

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

CAML:

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

Aggregation:
Group by: Day

Day field is defined in the requestSuccess JavaScript handler.

Aggregations:
Hours = sum of EstimationTasks = count of IDDueDate = max of TaskDueDate
Date = max of DateCompleted

Advanced:

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.

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

Style
Series:
Stack series: False

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

Advanced:

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.

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

Aggregation:
Group by: Day

Day field is defined in the requestSuccess JavaScript handler.

Aggregations:
Hours = sum of EstimationTasks = count of IDDueDate = max of TaskDueDate
Date = max of DateCompleted

Advanced:

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.

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

Style
Series:
Stack series: False

Advanced:

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.

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

CAML:

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

Aggregation:
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 EstimationTasks = count of ID

Advanced:

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.

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

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

Aggregation:
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 EstimationTasks = count of ID

Advanced:

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.

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

SharePoint List
List: Tasks
Fields: Assigned To, Task Status

Aggregation:
Group by: Task Status

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

Style
Stack series: True