SharePoint Experts Blog

Using multiple lists in Dashboard Designer

Good day!

Today I’d like to introduce you to a way you can use Dashboard Designer with multiple lists that can be located within the current site or on various sites in the current site collection. To illustrate this feature I will use two similar use cases.

Let’s say, you have multiple projects running in your company, each project has a separate site, all located under one site collection. The root site of the site collection contains a list of Projects with their relative URLs. Each project has a task list that contains all tasks assigned to every employee for that project. What we’ll do is get the project list, iterate over the projects, get the data located in the task lists in each of the projects and do some graphical presentation with this data.

Site collection hierarchy:

  • /sites/project_tasks_demo (site collection)
    • /Project (list)
    • /Charts (page where our charts will be located)
    • /market_research_project (one of the project subsites)
      • /Task List (list containing tasks for the project)
    • … other projects with similar structure

Our Project list:

SharePoint Project List

Task list in one of the projects:

SharePoint Task List

Task totals for each project, grouped by status

For this use case we’ll create a chart that displays Projects against their task counts for each status type, e.g. ‘Completed’, ‘Deferred’ and others.
This is how we’ll do this:

  1. Point Dashboard Designer to /sites/project_tasks_demo location
  2. Select the Project list
  3. Select the ‘Title’ and ‘URL’ fields which represent names and locations of projects
  4. Paste the code below to the editor under the Advanced tab panel
  5. Click ‘Process’
  6. Go to Aggregation tab and select ‘Status’ as the ‘Group by’ value
  7. Click ‘Process’
  8. Go to Dashboard and select the following options:
    • Bar chart
    • Display each group as separate series
    • Category: Project
    • Value: ID
    • Aggregate over category
    • Unit: fit
    • Step: auto
    • Function: count
  9. Save. This is the chart that we get:

Task Progress per Project Chart

The code

//...

handlers.requestSuccess = function(data, logger) {
    var dfd = $.Deferred();
    var projectList = data.items;
    data.items = [];
    var deferreds = [];
    var query = new SP.CamlQuery('<View> <ViewFields>' +  
    '<FieldRef Name="Status"/> <FieldRef Name="ID"/>' + 
    '</ViewFields> <Query/> <Aggregations Value="Off"/>' + 
    '<RowLimit Paged="TRUE">10000</RowLimit> </View>');
    for (var project in projectList){
        var d = request(query, projectList[project], 
        'Task List', ['ID', 'Status'], data);
        deferreds.push(d);
    }
    console.log('All requests fired.');
    $.when.apply(null, deferreds).done(function() {
        console.log('All requests have been resolved.');
        dfd.resolve(); 
    });
    return dfd.promise();
}

//...

function request(query, project, listName, fields, data) {
  
    var dfd = $.Deferred();
    var ctx = new SP.ClientContext(project['URL']);
    var list = ctx.get_web().get_lists().getByTitle(listName);

    var itemCollection = list.getItems(query);
    ctx.load(itemCollection, 'Include(' + fields.join(',') + ')');

    ctx.executeQueryAsync(function () {
        var itemCollectionEnumerator = itemCollection.getEnumerator();
        while (itemCollectionEnumerator.moveNext()) {
            var listItem = itemCollectionEnumerator.get_current();

            var item = {};
            $.each(fields, function () {
                try {
                    var value = listItem.get_item(this);
                    item[this] = value;
                }
                catch (e) { }
            });
            item['Project'] = project['Title'];
            data.items.push(item);
        }
        console.log('Request to ' + project['URL'] + ' has been resolved.');
        dfd.resolve();
    }, function(caller, args){
        console.log('Error: ' + args.get_message());
    });

    return dfd.promise();
}

There are two parts to this code: the handler and the request function. The handler is executed by Forms Designer just after it retrieves data from the Project list. The handler iterates through project list and calls the query method to retrieve the task items from each of the projects’ task lists. Both functions use jQuery deferred objects in order to retrieve the items in a synchronous fashion.

Task totals for each employee, grouped by project

This use case is about representing task count for each employee, grouped by projects.

In order to do this:

  1. Point Dashboard Designer to /sites/project_tasks_demo location
  2. Select the Project list
  3. Select the ‘Title’ and ‘URL’ fields which represent names and locations of projects
  4. Paste the code below to the editor under the Advanced tab panel
  5. Click ‘Process’
  6. Go to Aggregation and select ‘Project’ field as the ‘Group by’ value
  7. Click ‘Process’
  8. Go to Dashboard and select the following options:
    • Bar chart
    • Display each group as separate series
    • Category: AssignedTo
    • Value: ID
    • Aggregate over category
    • Unit: fit
    • Step: auto
    • Function: count
  9. Save the chart and this is what we see:

Task Totals per Employee Chart

The code

The code is exactly the same as for the previous use case, except the places colored red:

// ...

handlers.requestSuccess = function (data, logger) {
    var dfd = $.Deferred();
    var projectList = data.items;
    data.items = [];
    var deferreds = [];
    var query = new SP.CamlQuery('<View> <ViewFields>' + 
    '<FieldRef Name="AssignedTo"/> <FieldRef Name="ID"/>' + 
    '</ViewFields> <Query/> <Aggregations Value="Off"/>' + 
    '<RowLimit Paged="TRUE">10000</RowLimit> </View>');
    for (var project in projectList) {
        var d = request(query, projectList[project], 
        'Task List', ['ID', 'AssignedTo'], data);
        deferreds.push(d);
    }
    console.log('All requests fired.');
    $.when.apply(null, deferreds).done(function () {
        console.log('All requests have been resolved.');
        dfd.resolve();
    });
    return dfd.promise();
}

// ...

function request(query, project, listName, fields, data) {

    var dfd = $.Deferred();
    var ctx = new SP.ClientContext(project['URL']);
    var list = ctx.get_web().get_lists().getByTitle(listName);

    var itemCollection = list.getItems(query);
    ctx.load(itemCollection, 'Include(' + fields.join(',') + ')');

    ctx.executeQueryAsync(function () {
        var itemCollectionEnumerator = itemCollection.getEnumerator();
        while (itemCollectionEnumerator.moveNext()) {
            var listItem = itemCollectionEnumerator.get_current();

            var item = {};
            $.each(fields, function () {
                try {
                    var value = listItem.get_item(this);
                    if (this == 'AssignedTo' && value) {
                        item[this] = 
                        value[0].get_lookupValue().replace(/\\/, '&#92;');
                    }
                    else {
                        item[this] = value;
                    }
                } catch (e) { }
            });
            item['Project'] = project['Title'];
            data.items.push(item);
        }
        console.log('Request to ' + 
        project['URL'] + ' has been resolved.');
        dfd.resolve();
    }, function (caller, args) {
        console.log('Error: ' + args.get_message());
    });

    return dfd.promise();
}

The difference with the first use case is that we’re using different fields. Besides using new names for the fields we are also retrieving the lookup values of AssignedTo fields, which are the names of our employees.

Summary

What I hope I’ve illustrated with this post is that you can (and how you can) utilize the handler methods to retrieve additional data, including data from other sites, insert it into the data object and use that data to you construct your chart.

Posted in: Dashboard Designer

  • Maarten de Keijzer
    Hi, you mention: “Paste the code below to the editor under the Advanced tab panel”. Do you mean replace all default code with the new code, or should it be appended? I’m trying to create a likewise chart, but as soon as I put this code into the Advanced panel, it looks whether the handlers.requestSuccess isn’t executed anymore (I put some debug statements there). What commands are available in the “Run” field? Can they ease debugging?
    • Maarten de Keijzer
      The problem appears ti be in the CamlQuery part. When I leave the definition empty the code is executed. But how to fix it?
      • Dmitry Kozlov
        Check the internal field names and ensure that the syntax is correct. Our support team can help you with the task. Send the detailed requirements to support@spchart.com and we will estimate them.
    • Dmitry Kozlov
      Hi, You need to replace the ‘handlers.requestSuccess’ handler only.