Microsoft Excel Calculation

Microsoft Excel Calculation leverages Microsoft Excel spreadsheets to perform complex calculations. This allows business users to easily separate the UI from the logic.

Datasource Configuration

To configure the connector you will need a Microsoft Excel spreadsheet to be used as a data source, where filter fields are those cells used in your formulas and display fields the actual results.

For this article let's use the following spreadsheet as an example:

A spreadsheet that calculates future value of a particular investment which has constant interest rate and periodic payments, where:

• rate: Interest rate/period.
• nper: Number of periods.
• pmt: Payment/period.
• pv: Present value.
• type: when payment is made, for this example at the end of the period.

1. Navigate to Data Connections in Manage and click New Data Connection.
2. Provide a Data Connection Name.
3. Change Connection Type to Microsoft Excel Calculation.
4. Upload the spreadsheet as a Schema.
5. Provide a valid Connection Attributes, the following keywords are accepted: filterFields, displayFields, optionally debug for debugging purposes. For our example:

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

All Filter Fields values must be provided. Null values will break the execution. If debug is enabled the input and output cells will be logged to EventLog table.

6. Click Test Connection, you should get Connection Successful.
7. Click Save.

📘

Tip

It is recommended to enable Export Connection Attributes as it will include the connection attributes in exported projects.

8. Click Data Objects > New Data Object.

• Data Object Name / Definition: In a simple scenario like our example a simple name like "FutureValue" is enough. For more complex spreadsheets with several sheets, you can optionally define which Sheet and display fields to be applied per Data Object.
  • Specific Filter Fields can be included using Add Custom.
  • If sheet is not defined the first sheet will be used for references not specifying a sheet.
  • To reference other sheets per cell use the typical Excel format of SheetName!A1.
  • cells is used to define the outputs from the Excel spreadsheet. For example, cells=A1,A2 means A1 and A2 will be returned as display fields.

sheet=Sheet1

sheet=Sheet1;cells=A2,A4,A5

📘

Cells Types

The Input Cells should have the correct data types for what they are expecting. Thus if a cell expects number, its data type category should be Number. Scientific cells are treated like Numbers. The same for date cells.

9. Click Save.

📘

Tip

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

Datasource Usage

Microsoft Excel Calculation Datasource can now be used as any other Datasource, where Filter Fields behave as the spreadsheet inputs and the Display Fields will return calculated values. Here a screenshot that shows a Project that leverages the recently created Future Value Datasource.

Note that all Filter Fields are implemented using All Of, calling the Datasource only when all inputs are provided. Datasource Answer points to value returned by Future Value formula. Here a screenshot in Produce:

To download this sample Project, Click Here

Schema

Downloading the schema from this Data Connection will create a file with a .schema extension. This file will be a text file containing the base64 of the original uploaded file and can be opened in any text file editor.

If you need to edit the schema, either use the original copy of the uploaded file (which will not have changed) or use a base64 to file converter and paste in the contents of the .schema file.