SharePoint Experts Blog

How to add drill-down to SharePoint chart

In this article I will show you how to add drill-down to a SharePoint chart created with Dashboard Designer to navigate through details of your data by using linked list or another chart that visualizes the detailed information on the selected series of the main chart. For instance, I will implement two cases: a chart linked to a list and a chart linked to another chart.

Link chart to SharePoint list

In this example I’ve created a chart based on Issues list. I grouped data by Assigned To field on Data Source → Aggregate tabs and by Status field on Dashboard → Chart tab. Here is the result:

SharePoint chart: Issues by agents

Under the chart I put the list of issues. Now I want to filter the list by clicking series in the chart. Say, if a user clicks green (In Progress) series of Anne Dodsworth, the list has to display only ‘In Progress’ issues that are assigned to Anne Dodsworth.

First, I will introduce to you how filtration of SharePoint 2013 lists works. A list view tracks changes of a hash part of a URL that contains filtering and sorting properties of a particular view on the current page. Controls above the list, that are in charge of sorting and filtering, just modify the hash in accordance with the following format:

#InplviewHash{ViewId}=FilterField1={Field1}-FilterValue1={Value1}-FilterField2={Field2}-FilterValue2={Value2}

We will generate this hash in our chart via JavaScript based on user actions. First, you need to obtain Id of the view. You can filter your list by using column headers and copy the view Id directly from the query string. Also, you can use ctx JavaScript variable that contains configuration of the lastly added view. If you have multiple views on the page, you’d better use the first approach. If you have a single view on the page, use ctx variable as I do below to avoid hardcoded GUIDs in your code.

Now, open the chart in Dashboard Designer and switch to Dashboard → Advanced tab. Insert the following code here:

var handlers = {};
handlers.preRender = function (config, logger) {
    logger.debug('Configuration: ', config);

    var filtrationApplied = false;

    config.plotAreaClick = function (e) {
        if (!filtrationApplied) {
            window.location.hash = '';
        }

        filtrationApplied = false;
    }

    config.seriesClick = function (e) {
        if (ctx && ctx.clvp) {
            var filter =
              'FilterField1=AssignedTo-FilterValue1=' + e.dataItem.AssignedTo + '-' +
              'FilterField2=Status-FilterValue2=' + e.dataItem.Status;

            window.location.hash = 'InplviewHash' +
            ctx.clvp.wpid + '=' + encodeURIComponent(filter);

            filtrationApplied = true;
        }
    }

    return true;
}

As you can see, here I defined two handlers which are executed when a user clicks a specific series or the plot area. If a user clicks the chart area that doesn’t contain series, the filtration resets. As mentioned above, ctx.clvp.wpid contains Id of the lastly added view. If you have multiple view on your page, you should replace this variable with Id of a particular view. Here is the demo, quite easy, isn’t it?

Link Chart to a SharePoint List

Link chart to another chart

This case covers linking a SharePoint chart to another that expands the main chart and conveys more detailed information on the selected series. Here I’ve created two charts: Revenue per Sales representative and Sales over a time period. You can find the first chart in our demo. The second one is quite easy, it just displays sum of all orders by date.

Now that a user clicks on a particular sales rep in the first chart, I want to display sales of the selected sales rep in the second chart. I will pass information on the selected series via a hash part of a URL. In this case a user will be able to select a sales rep, copy the URL from his/her browser, and open it in another browser or send to another user. The filtration will save after opening such a link.

Open the first chart (Revenue per Sales rep.) in SharePoint Designer and navigate to the Dashboard → Advanced tab. Insert the following code in it:

var handlers = {};
handlers.preRender = function (config, logger) {
    logger.debug('Configuration: ', config);

    var filtrationApplied = false;

    config.plotAreaClick = function (e) {
        if (!filtrationApplied) {
            window.location.hash = '';
        }

        filtrationApplied = false;
    }

    config.seriesClick = function (e) {
        window.location.hash = 'userId=' + e.dataItem.SalesRepId;
        filtrationApplied = true;
    }

    config.axisLabelClick = function (e) {
        if (e.axis.type === 'category') {
            window.location.hash = 'userId=' + e.dataItem.SalesRepId;
            filtrationApplied = true;
        }
    }

    return true;
}

Here I subscribed to three events: plotAreaClickseriesClickaxisLabelClick. The first one is raised when the user clicks the plot area, the second – when a user clicks a particular series and the last one – when a user clicks a particular sales rep in category axis. Depending on the area which the user clicks, I pass Id of the appropriate sales rep. in userId parameter of the hash.

Now, let’s navigate to the second chart and configure filtration by the selected sales rep. Open the Data Source → SharePoint list tab. Here I injected {Filter} token into CAML-query that I will replace via JavaScript in requestInit handler:

  
    
      
    
    {Filter}
  
  
    
    
    
    
    
  
  3000

Ok, now switch to the Advanced tab and define requestInit handler following way:

handlers.requestInit = function (query, logger) {
    var userId = 0;

    var hash = location.hash.substr(1);
    var idx = hash.indexOf('userId=');
    if (idx !== -1) {
        userId = parseInt(hash.substr(idx)
                              .split('&')[0]
                              .split('=')[1]);

        if (isNaN(userId)) {
            userId = 0;
        }
    }

    var view = query.get_viewXml();
    if (userId !== 0) {
        view = view.replace('{Filter}', '\
\
    \
    ' + userId + '\
\
');

        query.set_viewXml(view);
    } else {
        query.set_viewXml(view.replace('{Filter}', ''));
    }

    return true;
}

Here I filter the source list by the value from userId property or just remove {Filter} token if the hash doesn’t contain userId property.

Finally, we need to subscribe to ‘hash changed’ event to reload the chart; and override title property to display the name of the selected sales rep. Go to the Dashboard → Advanced tab and insert the code below:

var handlers = {};
handlers.preRender = function (config, logger, processor, el) {
    logger.debug('Configuration: ', config);

    if (processor && !processor.subscribed) {
        $(window).bind('hashchange', function () {
            el.html('loading...');
            processor.process(el);
        });
        processor.subscribed = true;
    }

    if (location.hash.indexOf('userId=') !== -1) {
        config.title = 'Sales of ' + config.series[0].data[0].SalesRep;
    }

    return true;
}

As you can see, when the hash changes, the handler replaces the previous chart with the loading indicator and starts a new process of chart rendering. Also, I inserted the name of the selected sales rep into the title. Here is the result:

Link SharePoint chart to another chart

Posted in: Dashboard Designer

  • beth
    I have just come across your product and I like it very much. But I need to be able to drill down to list without using code. I will have a bunch of people using this app and they won’t be technically savvy. is there another options that doesn’t use code?
  • Phal SOK
    Hello Sir. or Madam, I strongly hope that you all are fine but me because I have an important issue to solve. I just want to know how to set my figure as percentage in Plumsail because now I just set it in number in my bar chart. Or the Revenue per sale rep can be shown in percentage? Please lets me know if possible.Thanks you in advance for your time. Your sincerely,SOK Phal.