Overview
QBO's Excel Engine leverages Microsoft Excel spreadsheets to perform calculations. The Excel Engine plugin handles two things:
Injecting data into the spreadsheet, so Excel can perform calculations, and
Extracting data out of the spreadsheet, storing this data in QBO's ScoreItem table
To leverage this plugin:
Ensure the qbo.Score.AsposeCells plugin is deployed
Ensure the Scoring Excel Engine package has been imported
Create a new Score Template, ensuring you select 'ExcelEngine' from the Score Engine drop down list
Upload an Excel spreadsheet (.xlsx file) as the Render attachment for the template
Below is information on how to configure your spreadsheet to work with the plugin.
Injecting Data
There are two methods of injecting QBO data into Excel:
Individual data points cell-by-cell, or
Entire data tables
Individual Data Points
The simplest method of injecting data into Excel is to enter a value starting with "?=":
?=UPBAmount: substitute the Loan.UPBAmount into a cell
?=Borrower.1.LastName: substitute the Borrower.LastName of the first borrower associated with the record being scored.
?=MyTask.MyField: substituted the user-defined field "MyField" from the user-defined task "MyTask"
?=//Some/Crazy/XPath/Expression: evaluate the XPath expression against the parent record's Summary output
If one enters a "?=" expressions into a cell's value, references to this cell may result in the dreaded #VALUE! errors.
An alternative is to add a Name with a Comment, where the Comment contains the same "?=" expression above. To add a name and comment to a cell, either:
Right-click on the cell, and choose "Define Name", or
From the Formulas ribbon, choose "Name Manager"
See the image on the right for an example.
Injecting Entire Tables
To inject tables into your spreadsheet, in any cell, enter a method signature beginning with "?=" (without the quotes). For example:
List of users: ?=Person/Search?OrderBy=LastName&SqlColumns=FirstName,LastName,Address,Person,PersonID
Valuations associated with a Loan: ?=Valuation/Search?Object=Loan&ObjectID={LoanID}
Random ledger items: ?=LedgerItem/Search?DisplaySize=5&OrderBy=-LedgerItemID
The method signatures may use string substitution, including any data from the score's parent object, or the score row itself. For example, if you are scoring a Loan, you may substitute any column from the Loan table. If you are scoring a Person, you may substitute any column from the Person table.
When the data is injected, spreadsheet cells to the right of the method signature cell will be overwritten with the resulting data. However, spreadsheet rows below the method signature line will remain in place; each row after the first row of data will cause a new spreadsheet row to be inserted. For example:
this Excel template
will result in this Excel result
Note that the 'Goodbye cruel world' cell was overwritten with 'John', but the 'Hello World' row remains intact.
Extracting Data from Excel to SQL
Scores can comprise zero or more Score Items, which are individual data points that contribute to a score. In the case of the Excel Engine plugin, each Score Item can be mapped to a cell in the spreadsheet by setting the name of the cell to match the name of a Score Item. For example, assume you have the following Score Items defined for a template:
Per Diem
NPV
Confidence
In your spreadsheet, choose a cell (any cell in any sheet) that will represent the value to populate your Score Item with. The cell will typically be an Excel formula, and may leverage any data injected into the spreadsheet you wish. Select the cell, and change the 'name' of the cell to match the safe name of the Score Item. Safe name is the Score Item's name, with spaces and special characters removed. For example, the safe name of 'Per Diem' is 'PerDiem'.
Score/Calculate
When Score/Calculate is called, the following happens:
A spreadsheet is created from the 'template' uploaded as the Score Template's Render document
Each method signature is found, substituted, executed, and the method signature cell is overwritten with the resulting data
For each defined Score Item, if the spreadsheet contains a named cell matching the Score Item's safe name, the cell's value is saved to the Score Item
The final spreadsheet is copied to the default imaging store, and associated with the Score.RenderID
Technical Notes
If you include spaces in the Item name, the spaces will be ignored when looking for an Excel cell name. E.g. 'Per Diem' maps to a range named 'PerDiem'
Method signatures must specify a method that returns a DataSet (or DataReader); XmlReaders are not supported
Page updated
Report abuse