❗️

Contact your sales representative if you are interested in obtaining a license for the Microsoft Excel Calculation Module for your SmartIQ environment.

Microsoft Excel Calculation leverages Microsoft Excel spreadsheets, xlsx files, containing pre-set excel formulas to perform complex calculations and behaves as a data source. It takes in data through the filter fields, which behave as the spreadsheet cell inputs. It will output the calculated values through the display fields through to the Data Questions Answer.

Connector Set-Up

Upload a spreadsheet containing formulas, where filter fields are those cells used in your formulas and display fields the actual results. Upload the spreadsheet as a Schema.

connection attributes

filterFields=B2,B3,B4,B5,B6;displayFields=B7;debug=true

Data Objects

Cell Result Data Object Name / Definition

cells is used to define the outputs from the Excel spreadsheet, for example sheet=Sheet1;cells=A2,A4,A5 for specific sheet and display cells.

The first sheet will be assumed for references not specifying a sheet. For more complex spreadsheets with several sheets, you can define which Sheet and display fields to be applied per Data Object using a custom filter. Use the typical Excel format of SheetName!A1.

All Filter Fields values must be provided, null values will break the execution, so all Filter Fields should be implemented using the All Of Condition, calling the Datasource only when all inputs are provided. The Datasource Answer points to values returned by the spread sheet formula.

👍

It is recommended to change the Display Name of Filter and Display Fields, otherwise, they will appear in Design as Cell Names like B4

🚧

The Input Cells should have the correct data types for what they are expecting

If a cell is a date, its data type should be set to date. By default cells are treated like numbers.

Troubleshooting

If debug is enabled the input and output cells will be logged to EventLog table.