Applies To

SharePoint Online and SharePoint On-Premises

Description

This article describes the process of configuring a NITRO Report using custom JavaScript to display the total counts for options selected in a multi-choice column as well as a multi-lookup column.

In the example used below to demonstrate this configuration, the multi-choice column is titled as ‘Options’ and the multi-lookup column is titled as ‘Department’.

These names can be changed as per the name of the column(s) used in the actual scenario.

Use Case 1: Configuration to show the total count for the options selected in a multi-choice column.

• The ‘Options’ column used in this example has following selected choices:

• The total count based upon above entries is as follows:

Cable = 1, Keyboard = 2, Laptop = 1, Mouse = 3, PC = 3, Printer = 2, Router = 2, and Switch = 2

There are only 5 items in this view, but the total selected options are 16.

• The NITRO Report need to display the individual count for each option as well as the total as shown below:

• To configure this report, navigate to the NITRO Report configured for the list –> Advanced settings –> Customize Report –> Add/Edit Script as shown below:

• Use the script as shown below:

• The JavaScript used is:

scriptManager.GetFormattedColumnValues = function(listItem){
  var result = [];
  //For Non Large List Reports
  var fieldValues = listItem.get_item("Options");
  //For Large List Reports
 // var fieldValues = listItem["CheckBoxes"] ? listItem["Options"].split("; ") : [];
  if (fieldValues && fieldValues.length > 0) {
      for (var i = 0; i < fieldValues.length; i++) {
          var item = {};
          item["Options"] = fieldValues[i];
          result.push(item);
      }
  }
   return result;
};

• If the list has a large number items, then to avoid the list threshold error, below option can be selected:

• If the above shown option is selected then, the lines related to ‘For Large List reports’ should be uncommented and used.

• In this example, the demonstrated list is not having large number of items and hence, the lines related to ‘For Non-Large List Reports’ are used.

Use Case 2: Configuration to show the total count for the options selected in a multi-lookup column.

• In this example, the column used as a NITRO Multi-Lookup column is titled as ‘Departments’ and the same internal name would be used in the sample script.

• The ‘Department’ multi-lookup column has following entries selected:

• The total count based upon above entries is as follows:

HR = 1, IT =3, Sales = 2, and the total selected departments is 6. Number of items in view are only 3.

• The NITRO Report need to display the individual count for each option as well as the total as shown below:

• To configure this report, navigate to the NITRO Report configured for the list –> Advanced settings –> Customize Report –> Add/Edit Script as shown in the above Use Case 1.

Use the script as shown below:

• The JavaScript used is:

scriptManager.GetFormattedColumnValues = function (listItem) {

    var result = [];

    //For Non Large List Reports
    var fieldValues = listItem.get_item("Department");
    //For Large List Reports
    //var fieldValues = listItem["Department"] ? listItem["Department"].split("; ") : [];

    if (fieldValues && fieldValues.length > 0) {
        for (var i = 0; i < fieldValues.length; i++) {
            var item = {};
            item["Department"] = fieldValues[i] && fieldValues[i].get_lookupValue ? fieldValues[i].get_lookupValue() : fieldValues[i];
            result.push(item);
        }
    }
    return result;
};

• As mentioned in Use Case 1, use the lines related to large list if that optio is enabled in the report:

For a detailed information about other script options available in the ‘Add/Edit Script’ section, please refer to this article.