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.

Installation Requirements

Before installing this extension you must consider the following requirements:

• You must have administrator/elevated permissions to the SmartIQ web server, including the ability to copy and replace files in the SmartIQ installation folder. This is usually c:\inetpub\wwwroot\<<productname>> however may vary from system to system.

• You must have the following installation files:

• MicrosoftExcelForCalculations.dll

• Ensure you have a compatible version of the connector with your current SmartIQ system (Connector and SmartIQ should match versions)

• Ensure you have an active license for the connector.

👍

Remember

Changes to the configuration may cause interruptions to users in forms.
Before installing this Connector in a production environment, ensure the environment is backed up.
It is recommended that this installation is tested in a non-production environment first, and installed during scheduled downtime.

Installation and Configuration

Use the instructions below to install the connector. If you are upgrading the extension from a previous version, refer to the section Upgrading the Connector.

Note that [<<productname>> Produce Path] refers to the installation path on the webserver running SmartIQ, where the Produce site has been installed. By default this is c:\inetpub\wwwroot\<<productname>>\produce however may vary from environment to environment. Equally, [<<productname>> Manage Path] refers to where the Manage site has been installed, and [<<productname>> Scheduler Path] refers to where the Scheduler service has been installed.

How to install the Connector

1. Copy the file listed above into the following folder on the SmartIQ web server:
   [<<productname>> Produce Path]
2. Using a text editor such as Notepad, open the following file:
   [<<productname>> Produce Path]\appsettings.json

📘

Note

Ensure you have elevated privileges when opening the appsettings.json file, otherwise you may not be able to save in the current folder.

3. Locate the <Extensions> section of the appsettings.json file and add the following entries:

"MicrosoftExcelForCalculations.MicrosoftExcelCalculationDatasource, MicrosoftExcelForCalculations"

A similar process has to be done in Manage.

1. Copy the file listed above into the following folder on the SmartIQ web server:
   [<<productname>> Manage Path]
2. Using a text editor such as Notepad, open the following file:
   [<<productname>> Manage Path]\appsettings.json

"MicrosoftExcelForCalculations.MicrosoftExcelCalculationDatasource, MicrosoftExcelForCalculations"

Using a web browser, navigate to the SmartIQ Produce and SmartIQ Manage applications to allow the system to register the Connector.

Installation to Scheduler

If the target environment is using the Scheduler, it is best practice to always install connectors and accelerators to this service at the same time as the installation in SmartIQ Produce application. If this is not installed to the Scheduler there may be errors if any projects are executed from the scheduler that refers to this connector.

To install any connector to the Scheduler, follow the instructions in this guide as they apply to SmartIQ Produce, except instead of [Produce Path] use [SmartIQ Scheduler Path], noting that there is no ‘bin’ folder in this path so all files should be copied into the root of this path. For example, consider the path:

c:\inetpub\wwwroot\<<productname>>\produce

To install to the Scheduler, simply use this path:

c:\inetpub\wwwroot\<<productname>>\IntelledoxScheduler

Also, you must apply the same configuration changes as instructed for the Produce appsettings.json file.

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