Introduction

Microsoft Excel® is perhaps the most widely adopted business analytics tools ever created. Having data available in Excel enables quick calculations, sorting and aggregation. It is far from perfect and we are not here to argue if it is the best tool or even the right tool, it is just the tool.

Maximo provides the ability to export result sets as an Excel workbook, however this is limited to the columns displayed within the list results and therefore of limited utility. In this post we will examine how to add a menu item to export any Mbo data attribute to an Excel workbook from a given result set.

Excel Utility Script

Sharptree has published a helpful Excel utility script that we will be using for our examples. Full details for the script can be found on GitHub, here: https://github.com/sharptree/autoscript-library/tree/main/excel.

Please deploy this script before continuing with the example below and ensure that the script is named SHARPTREE.EXCEL to align with the remaining examples.

As with our other scripts the library script contains a scriptConfig variable and is compatible with the Maximo Development Tools extension for VS Code here: https://marketplace.visualstudio.com/items?itemName=sharptree.maximo-script-deploy

Inline Import

We have discussed importing library scripts before using the service.invokeScript() function, found here. This creates a new object with its own internal context and implicit variables, from which functions on that object may be called. In our examples below we are use the builtin load function to import the utility script into the current script context and make the functions of the imported script available to the current script directly.

Copy
// Import the ScriptCache Java class.
ScriptCache = Java.type('com.ibm.tivoli.maximo.script.ScriptCache');

// Load the Excel library inline with the current script.
load({ script: ScriptCache.getInstance().getScriptInfo('SHARPTREE.EXCEL').getScriptSource(), name: 'SHARPTREE.EXCEL' });

The ScriptCache = Java.type('com.ibm.tivoli.maximo.script.ScriptCache'); line imports the Maximo ScriptCache class so we can access the name and source for the SHARPTREE.EXCEL automation script. The load({ script: ScriptCache.getInstance().getScriptInfo('SHARPTREE.EXCEL').getScriptSource(), name: 'SHARPTREE.EXCEL' }); line loads the the SHARPTREE.EXCEL source inline with the current script.

Create Export Link

We are going to create a menu item that exports the current result set with an arbitrary list of export column. To support this, deploy the following script as SHARPTREE.EXPORT. The script fetches the user's current WebClientSession from the HttpSession context, then fetches the current application and finally gets the result set to export.

Copy
// Import the ScriptCache Java class.
ScriptCache = Java.type('com.ibm.tivoli.maximo.script.ScriptCache');

// Load the Excel library inline with the current script.
load({ script: ScriptCache.getInstance().getScriptInfo('SHARPTREE.EXCEL').getScriptSource(), name: 'SHARPTREE.EXCEL' });

// The maximum number of records in a result set that can be exported.
var MAXIMUM_EXPORT_COUNT = 10000;

main();

/**
 * The main entry point for the script.
 */
function main(){
    // only execute if we are running in the context of a direct web script invocation.
    if (typeof request !== 'undefined') {
        // get the current web client session.
        var wcs;
        var session = request.getHttpServletRequest().getSession(); 
        var names = session.getAttributeNames();
 
        while (names.hasMoreElements()) {
            var name = names.nextElement();
            if (name.startsWith('webclientsession')) {
                wcs = session.getAttribute(name);
                break;
            }            
        }
        
        // Return an error if the web client session was not found.
        if (!wcs) {
            responseBody = JSON.stringify({'status':'error','message':'Could not retrieve te current user WebClientSession.'}, null, 4);
            return;            
        }

        // Return an error if the current app cannot be determined.
        if (!wcs.getCurrentApp()) {
            responseBody = JSON.stringify({ 'status': 'error', 'message': 'Could not determine the current application from the user WebClientSession.' }, null, 4);
            return;
        }

        // Get the DataBean Id to export, here we are using the default result list.
        var dataBeanName ="results_showlist";    
        
        // Get the DataBean to export, return an error if it cannot be found.
        var dataBean = wcs.getCurrentApp().getDataBean(dataBeanName);
        if (!dataBean) {
            responseBody = JSON.stringify({ 'status': 'error', 'message': 'A DataBean with id ' + dataBeanName + ' was not found.' }, null, 4);
            return;
        }
        
        // Get the list of attributes to export. At least one attribute is required.
        var attributes = request.getQueryParam("attributes").split(',');

        if (!attributes || attributes.length == 0) {
            responseBody = JSON.stringify({ 'status': 'error', 'message': 'At least one attribute must be provided in the "attributes" query parameter.' }, null, 4);
            return;            
        }

        // This is an arbitrary limitation that can be changed based on system capabilities.
        if (dataBean.getMboSet().count() > MAXIMUM_EXPORT_COUNT) {
            responseBody = JSON.stringify({ 'status': 'error', 'message': 'Cannot export more that ' + MAXIMUM_EXPORT_COUNT + ' records, please reduce the size of the result set to export.' }, null, 4);
            return;     
        }
        
        var mboSet;
        try {
            // Make a copy of the current MboSet so exporting does not change the current record position
            mboSet = MXServer.getMXServer().getMboSet(dataBean.getMboSet().getName(), userInfo);
            mboSet.setWhere(dataBean.getMboSet().getCompleteWhere());
            // Export the results to an Excel workbook and then download the workbook
            downloadWorkbook(exportSet(mboSet, {
                'attributes': attributes,
                'autosize': true,
            }), dataBean.getMboSet().getName().toLowerCase() + '.xlsx');
        } finally {
            if (mboSet) {
                mboSet.close();
                mboSet.cleanup();
            }
        }    
    }    
}

var scriptConfig = {
    'autoscript': 'SHARPTREE.EXPORT',
    'description': 'Sharptree export excel spreadsheet',
    'version': '',
    'active': true,
    'logLevel': 'ERROR'
};

Create Launch in Context

From the navigation menu, select Integration and then select the Launch in Context application.

Click the + button to create a new Launch in Context record.

Enter EXPORTLIST for the Launch Entry Name, Export result list for the Description, /maximo/oslc/script/sharptree.export?attributes=wonum,description,asset.description for the Console URL where the attributes query parameter is a comma-separated list of the attributes you want to export and _blank for the Target Browser Window. In our example we are exporting the work order number, description and the asset description.

Create the Menu Item

From the navigation menu, select System Configuration, then Platform Configuration and then select the Application Designer application. Search for the WOTRACK application and select the WOTRACK record.

From the Select Action menu select Add/Modify Signature Options, then click the New Row button. For the Option value enter EXPORTLIST, for the description enter Export List, expand the Advanced Signature Options and select Associate to launch entry to enable launch in context radio button and then enter EXPORTLIST for the Launch Entry Name, this is the name of the Launch in Context created in the previous step. Finally click the OK button to save the new Signature Option.

From the Select Action menu select Add/Modify Select Action Menu, then click the New Row button. Enter OPTION for the Element Type and then enter EXPORTLIST for the Key Value. The Position and Subpostion determine the placement within the menu list of your menu it. We are using 999 for the Position and 0 for the Subposition to place the new menu item at the bottom of the list of menu items in a Maximo Demo environment. In a system with custom menu items you may need to adjust this value based on the values in use in your system. Finally, enter LIST for the Tabs field. Click OK to save the new menu item.

Grant the Security Option

From the navigation menu, select Security, then select the Security Groups application.

Search for the group you want to grant the export privileges to, in our case we are using MAXADMIN, then click on the Applications tab. Expand the filter for the Applications section and search for Work Order Tracking. Select the Work Order Tracking application and then in the Options section at the bottom of the page, expand the filter and search for the Export List option and check the box Grand Access? check box. Click the Save button and log out to refresh the user's permissions.

Try it Out

From the navigation menu, select Work Orders and then select the `Work Order Tracking application.

Perform a query or just select all records and then select the Export List menu item. A window or tab will momentarily open and then the Excel workbook will download.

Note that the default script limits to exporting 10,000 records. This is defined in the SHARPTREE.EXPORT script by the MAXIMUM_EXPORT_COUNT variable, which is located at the top of the script.

Final Thoughts

In this post we showed how the Sharptree SHARPTREE.EXCEL library can be used to export a result set to an Excel workbook. This is a simple example of how this very useful library might be used, but many other use cases exist for generating Excel workbooks. While not covered in this post, it should be noted that the SHARPTREE.EXCEL library also provides functionality to email an exported Excel workbook.

Finally, we demonstrated how other scripts can be inline imported into a script and the functions called directly. This is extremely useful for developing libraries for automation scripts and opens the door for significantly more sophisticated script applications.

If you have any questions or comments please reach out to us at [email protected]