Microsoft Excel Calculation leverages Microsoft Excel spreadsheets to perform complex calculations. This allows business users to easily separate the UI from the logic.
To configure the data connection 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.
- Navigate to Data Connections in Manage and click New Data Connection.
- Provide a Data Connection Name.
- Change Connection Type to Microsoft Excel Calculation.
- Upload the spreadsheet as a Schema.
- Provide a valid Connection Attributes, the following keywords are accepted:
debugfor debugging purposes. For our example:
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.
- Click Test Connection, you should get Connection Successful.
- Click Save.
It is recommended to enable Export Connection Attributes as it will include the connection attributes in exported projects.
- 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
Sheetand display fields to be applied per Data Object.
- Specific Filter Fields can be included using Add Custom.
sheetis 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
cellsis used to define the outputs from the Excel spreadsheet. For example,
cells=A1,A2means A1 and A2 will be returned as display fields.
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.
- Click Save.
It is recommended to change the Display Name of Filter and Display Fields, otherwise, they will appear in Design as Cell Names like
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:
Updated 19 days ago