Technical Blog Post
Query data from Maximo into Excel using MxLoader
MxLoader is a Microsoft Excel spreadsheet that allows to quickly and easily query and load data into IBM Maximo, IBM SmartCloud Control Desk (SCCD) and other TPAE based applications.
In this article I will show how simple it is to load data into Maximo from an Excel sheet using MxLoader. First of all, download MxLoader from the developerWorks community. To have a quick overview of the tool I suggest you to watch this 4 minutes video.
When you open MxLoader you are typically prompted to enable active content. Accept security prompts to enable Excel macros.
Now go on the Config tab and update the Server address to match your environment. To check that everything is working fine you can click on the Test Connection button. If the connection fails you have to find the right combination of connection properties (cells B4:B8).
In the Config worksheet there are many other several communication and tuning settings. Hovering the mouse cursor over each setting will display a detailed description.
Another important configuration is the Maximum objects parameter. This limits the number of object that can be queried or synched. It is set by default to 50 but you can increase this value if you need.
The MxLoader Excel ribbon allows to quickly access MxLoader features and some useful Excel buttons. The first two buttons are also available on the Quick Access Toolbar in the window title bar.
Clicking on the New MxSheet button the following dialog will be displayed.
Selecting the Asset template a new worksheet will be created with a predefined set of attributes.
The first row of the worksheet contains the following parameters:
- Cell A1 contains the name of the current Object Structure. Details of the Object Structure are available in Maximo under Integration > Object Structures application.
- Cell B1 contains the name of the main object of the Object Structure.
- Cell B1 contains the action that must be invoked on the Object Structure service. The Query action allows to retrieve data from Maximo and fill the current sheet.
Clicking on the Run button on the MxLoader toolbar the sheet will be populated with the data retrieved from your Maximo server.
You can add more fields from the ASSET Object Structure and click again on the Run button to refresh data.
To filter data cell D1 can be used to specify an SQL where clause.
For example to retrieve all subassets of asset '13140' in Bedford site you can type the following SQL clause: siteid='BEDFORD' and parent='13140'
MxLoader is also able to query related objects. For example if the asset '11470' has two meters attached, it is possible to retrieve them using a dotted notation like in this example.