You can use a Script to customize a User Report in Infoplus, to develop many custom real-world solutions for your business.  


How To Create a Script for customizing a User Report

  1. Create a Script record in Infoplus, and choose the option Report in the Script Typefield.
    1. The Code field in the Script record will be auto-populated with a comment referencing some of the details explained here, as well as a simple example of adding a column to a report.  You will need to edit this code to customize the script to meet your unique needs.   
  2. Edit your User Report, expand the Advanced section of the screen, and choose your script from the dropdown menu:
  3. Run your Report and review the output.  
  4. If necessary, edit your script's code, and re-run the report again until it's functioning as expected.  


Code Details

This is the flow that occurs when Infoplus uses a Script to customize a User Report:

  1. Infoplus fetches all rows matching the Smart Filter assigned to the report, and populates all columns to be included in the report.
  2. The script is  executed, with the report's data made available in an object named report.  
    1. Specifically, the rows of the report can be accessed by calling the getOriginalRows method. 
  3. The script is responsible for determining what the rows in the generated report will be.  It does this by calling the addRowmethod.  
    1. This point is critical:  If you do not call the addRow method, then no rows will be included in your report.  To include all original rows, you must iterate / loop over them, and call the addRow method for each of them.
    2. This allows you to remove rows from the report, if you choose to, or to add additional rows if needed.   
  4. After the script completes, the rows which were passed through the addRow method are rendered in the requested formats by Infoplus (Excel, PDF).


Object references

Report type scripts have 3 objects available in their context:  report, utils, and infoplusApi.  


The report object has the following methods:

Method NameParametersReturn ValueNotes
getOriginalRowsvoidList of Row objectsEach object in the list represents a row in the report as fetch by Infoplus.  

This list object has methods size, which returns the number of elements in the list, and get, which takes a 0-based integer index, and returns the Row object corresponding to that index.  

These Row objects have 2 methods:
  • get - takes a string column name as input, and returns the value in that column for the row.  Note that the column name matches what is seen in the output report.  
  • put - takes a string column name and any object value as input, and puts that value into the row in the specified column. 
addRowRow objectvoidAdds the input Row object to the report that will be produced for the user.  The Row object must either be one of the objects returned by the getOriginalRows method, or the duplicateRow method.
duplicateRowRow objectRow objectCreates a duplicate of the input Row object.  This new row can be added to the output of the report by calling the addRow method.
addColumnstring column namevoidAdds a new column to the report.  The column header will be the value passed to this method.  To put values in this column in the rows of the report, call the put method on the Row objects, passing this column name.  
removeColumnstring column namevoidRemoves a column from the report.  


The utils object has the following methods:

Method NameParametersReturn ValueNotes
logstring log messagevoidAdds a line to the script log record built when the script is executed.  


The infoplusApi object is documented here.


A Note about using Scripts with Pivot Reports

User report scripts run prior to the when the "pivoting" happens in a report.  This means that the pre-pivoted rows can be touched using a script but the post-pivoted rows cannot.  For example, when working with an order based User Report that has "SLA Status" as a row, this row can be used by a script, but once this field is pivoted so "Shipped On Time" and "Shipped Late" become rows in the report with counts, those two rows cannot be accessed by a script.


Example Use Cases

Add Additional Columns

An extra column can easily be added to a report by using the addColumn method plus looping over all rows in the report and putting the desired value into the column.  Here's an example script that does this, to add the Cubic volume of Items to a report on the Items table:  


// Define the new column to be added to the report
report.addColumn("Item Cubic Feet");

// Loop over the rows in the report
for(var i=0; i<report.getOriginalRows().size(); i++)
{
    // Get the row out of the list of original rows.
    var reportRow = report.getOriginalRows().get(i);

    // Get the needed values out of the row
    var length = reportRow.get("Length (in)");
    var width = reportRow.get("Width (in)");
    var height = reportRow.get("Height (in)");

    // Compute the new value (in this example, computing cubic feet from cubic inches)
    var cubicFeet = (length * width * height) / (12*12*12);

    // Put the value into the row (in this example, rounding it to 2 decimal places)
    reportRow.put("Item Cubic Feet", Number(cubicFeet).toFixed(2));

    // Add the row into the output report
    report.addRow(reportRow);
}