When dealing with large numbers of scripts it is a good idea to categorize those reports using a directory structure:
A common desire when creating reports is to have the ability to call one report with another report. In order for that to work, you have to have both reports in the same directory. This can be a problem if you want to share a report across directories.
The solution to this is to create a Javascript file and place it in the Common Files folder. Copy the code from the report you wish to share and put it in this Javascript file.
This is fine if all you’re going to do is call the report and run it. However, this can still result in a great deal of duplication of functions, variables, etc. throughout your Reports structure. The best way to eliminate this is to take an object oriented approach to developing ARIS scripts. The key is to remember that you have the Javascript domain at your disposal. Javascript is inherently object oriented. By taking advantage of this, you can greatly reduce the amount of duplication you may encounter when developing large numbers of individual reports.
I will attempt to demonstrate the approach I’ve taken to help cut down on a lot of code-copying. I will use some Excel Formatting Classes that I built that I can call from any report. This design pattern separates data from presentation. That is a key component of efficient data processing.
Classes
A key component to OOP (Object Oriented Programming) is the Class. A Class is nothing more than a collection of functions and variables that have a common ‘theme’. In this example in ARIS, the classes are individual Javascript files stored under Common files.
By placing this class here, I will be able to call it from any report by going to the properties of my report and selecting ‘Imported files’. This will display all files available under Common files as well as any other reports that are available in my current directory. We are only concerned with Common Files.
Creating a Class in ARIS
To create a class, you’ll need to import an existing file. You can simply make an empty text file and give it an extension of ‘js’. Or you can put some predefined calls in there or possibly comments. In any case, you create this file outside of ARIS using Notepad or some other text editor. I created a file simply called ‘script.js’. Then, right click on Common files and select Add files. Navigate to the file you created. When you import, you will see ‘script.js’ in the list of files. Rename that file. You will want to create a class for each ‘theme’ you are creating the class for. In the example, I’ve created a single class to deal with the ‘theme’ of building an Excel Report.
Naming Classes
A common way to name a class is to use dot notation (similar to how web addresses are formatted). This allows you to give the file a meaningful name such My.Excel.Report.js.
By using an intelligent naming scheme, you will be able to easily identify the intended purpose of the class. You will also be able to relate this to the internal name of the class which I will explain in a moment.
Public and Private Members
Hopefully you have had at least a minimum of exposure to writing scripts in ARIS. If so, then you are already familiar with ‘private’ members. A ‘Member’ is either a variable or a function. You have declared private variables using ‘var’:
var MyPrivateVariable = “foo”;
and you have declared private functions using ‘function’:
function MyPrivateFunction(){ Dialogs.MsgBox(MyVariable); }
Private members can only be accessed within the class where they’re declared. This doesn’t do much good if you want to access a function within a class that you’ve imported. In order to make it visible to your importing class, you have to make it public. The way to do it is to use the keyword ‘this’:
this.MyPublicVariable = “foo”;
and for public functions:
this.MyPublicFunction = function(){ Dialogs.MsgBox(“foo”); }
There is an additional keyword ‘prototype’ that can be used, but to keep things simple I will not be discussing its use.
Declaring the Internal Class
To start building your class, open up the file you created earlier. The internal class is the first thing you’ll define by, oddly enough, declaring a private function:
function MY_EXCEL_REPORT(){ }
The Internal Class name is ‘MY_EXCEL_REPORT’. Notice I’ve followed a similar naming convention as the file. This will help keep things organized. As written though, it doesn’t really do anything. So let’s start populating it. We will probably want to pass in a parameter to let the class know what name we want to give our Excel Workbook.
Function MY_EXCEL_REPORT(_workbookName){ }
Then we’ll actually want the class to initialize an Excel Workbook
Function MY_EXCEL_REPORT(_workbookName){ var oWorkbook = Context.createExcelWorkbook(_workbookName + ".xls"); this.WorkBook = oWorkbook; }
Accessing the Class
There are two simple steps needed for your report to have access to the class.
Step 1.) Import the class. In the properties of a Report, select ‘Imported files’. You should see Common Files/My.Excel.Report.js in the list. Click the check box to import the file, creating a reference to the class.
Step2.) Declare the class inside your report somewhere near the top:
var oMyExcelReport = new MY_EXCEL_REPORT(“My Excel Report File”);
Now, you will be able to access the public ‘WorkBook’ variable that’s inside the MY_EXCEL_REPORT class simply using:
var oMyWorkbook = oMyExcelReport.Workbook;
Using the 'new' keyword
Notice that when we first initialized the 'oMyExcelReport' variable, we used the 'new' keyword in front of the class name. It is important to understand how this works. Using the 'new' keyword creates a copy of the class or 'object' in memory that the script can access. Inside the class itself, the class itself, this will initialize all the members in the class. This is important to know as it allows you to have pre-set values within the class when you initialize it. Note that things like colors and fonts are already set in the class so you don't have to mess with any of that in your main code.
Hopefully that simple example demonstrates the power of making your ARIS reports more Object Oriented. You will no longer need to write the code necessary in each and every report. You simply reference the class that will take care of that for you.
Still, you may not be convinced. I mean, referencing a class then getting a value out seems to be more work than simply using ‘createExcelWorkbook’ at the top of your report. However, that’s just the tip of the iceberg. Beyond just declaring the Workbook, you need to also declare all the formatting for fonts, column units, widths, heights, etc. This is where the class takes a real load off. You use the class to manage all the details that you have been re-writing into all your reports. Below is the final class with all public and private members.
If you find this useful (or if you find any major mistakes!) please give feedback.
In my next article, I will post the Dataset class I use to populate this Excel Report class.
Enjoy!
Rick Beddoe
Cargill Aris Technical Analyst
Minneapolis, MN, USA
///My Excel Report Builder function MY_EXCEL_REPORT(_workbookName) { //My.Excel class definition var oWorkbook = Context.createExcelWorkbook(_workbookName + ".xls"); this.WorkBook = oWorkbook; ///creates a Worksheet this.Report = function(oReportName) { ///public properties this.Name = oReportName; this.Table = null; this.Rows = []; this.Data = []; this.Worksheet = oWorkbook.createSheet(this.Name); //default font for Header this.HeaderFont = { name:"Arial", height:8, color:Constants.C_BLACK, foregroundColor:Constants.C_GREY_80_PERCENT, fillPattern:Constants.SOLID_FOREGROUND}; //default font for Data this.DataFont = { name:"Arial", height:8, color:Constants.C_BLACK, foregroundColor:Constants.C_TRANSPARENT, fillPattern:Constants.NO_FILL}; ///public functions ///populate the Excel Worksheet this.Write = function(_sort){ var oHeaderCell = _setCell(this.HeaderFont); var oDataCell = _setCell(this.DataFont); var oColumnIndex = 0; for (var oColumn in this.Table.Columns.sort(_columnSort)){ var oColumnValue = this.Table.Columns[oColumn].value; var _excelRow = this.Worksheet.createRow(0); writeXlsCell(_excelRow,oColumnIndex,oHeaderCell,oColumnValue); oColumnIndex++; } var oOrderedRows = JSLINQ(this.Table.Rows) .OrderBy(function(row){return row.ItemArray[_sort];}).ToArray(); //for (var oRow in this.Table.Rows.sort(function _rowSort(a,b){return a.ItemArray[_sort]-b.ItemArray[_sort];})){ for (var oRow in oOrderedRows){ var _excelRow = this.Worksheet.createRow(parseInt(oRow) + 1); var oDataRow = oOrderedRows[oRow]; oColumnIndex = 0; for (var oColumn in this.Table.Columns.sort(_columnSort)){ var oColumnKey = this.Table.Columns[oColumn].key; writeXlsCell(_excelRow,oColumnIndex,oDataCell,oDataRow.ItemArray[oColumnKey]); oColumnIndex++; } } _autoFitColumns(oWorkbook,this.Worksheet,this.Table.Columns.length); } ///private functions ///set cell formatting function _setCell(_fontStyle){ var oFont = oWorkbook.createFont(); with (oFont) { setFontName(_fontStyle.name); setColor(_fontStyle.color); setFontHeight(_fontStyle.height * 20); } var oCell = oWorkbook.createCellStyle(oFont,1,1,1,1,1,1,1,1,1,1,1,1,1); with (oCell) { setFont(oFont); setFillForegroundColor(_fontStyle.foregroundColor); setFillPattern(_fontStyle.fillPattern); } return oCell; } ///ensure column data is sorted via sort order - ascending function _columnSort(a,b){ return a.index-b.index; } function _rowSort(a,b){ return a._sort-b._sort; } ///format and fill a cell function writeXlsCell(row,cellIndex,cellStyle,p_String) { var xlsCell = row.createCell(cellIndex); xlsCell.setCellStyle(cellStyle); xlsCell.setCellValue(p_String); } ///automatically fit column widths function _autoFitColumns(_workBook, _workSheet) { var oHeaderRow = _workSheet.getRowAt(0); if (oHeaderRow == null){return;} var _columnCount = oHeaderRow.getAbsoluteFilledCellCount(); var oRowCount = _workSheet.getAbsoluteFilledRowCount(); for (i=0;i<_columnCount;i++) { var oColWidth = 0; var oCellWidth = 0; var oCellFontHeight = 0; for (j=0;j<oRowCount;j++) { var oCell = _workSheet.getCell(j, i); var oFont = _workBook.getFontAt(oCell.getCellStyle().getFontIndex()); if (oCell != null) { var oHeight = oCellFontHeight / 20; var oValue = oCell.getCellValue(); var oCharCount = oValue.length(); if (oCharCount > oCellWidth) { oCellWidth = oCharCount ; oCellFontHeight = oFont.getFontHeight(); } } } oColWidth = (oCellWidth + 0) * 256 * (oHeight / 9); _workSheet.setColumnWidth(i,oColWidth); } } } }
That's a really nice contribution!
One should create and maintain a small library of common functions with reusable basic "top-level" functionalities, that are always needed in a project:
- excel import / export is a must (built at an abstract level, like the one presented above, never too specific)
- extensions to javascript basic functions (ex: extending the javascript Array prototype)
- logging & debugging helpers (e.g: simple logging of text to a file & javascript object inspectors)
The time it takes to build this reusable library really pays off during a project!
Rick, Thanks for sharing the code and creating awareness on this topic!
Hi Rick,
Fantastic contribution. Although we all create report every now and then but we tend to forget this basic rules.
It will also help the new guys who are starting writing script in ARIS.
Thanks
Abhijit Das