> For the complete documentation index, see [llms.txt](https://docs.coherent.global/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.coherent.global/build-spark-services/additional-mappings/call-spark-services-as-functions-xcall/tutorial-define-cs.spark_xcall-udf.md).

# Tutorial: Define CS.SPARK\_XCALL UDF

Syntax: `CS.SPARK_XCALL([folder name/service name], [input_location], [output_template, input_name_or_values], [input_name_or_values])`

Spark services can be set up to call other Spark services. Any Spark service can become a function that can be called from any other Spark service. This feature is known as `Xcall` and is defined as a function within an Excel workbook.

This enables encapsulation and reuse of calculations and data. Instead of having all the formulas and data in one workbook, calculations can be split and managed separately, while being referenced and reused multiple times across Spark services.

Use cases include:

* Model insurance product structures where each coverage is defined by separate Spark services. A "traffic director" upstream service assembles the coverage together for the policy.
* Model insurance product structures that have variances across distribution channels or product versions across time. Each product can be a separate Spark service and a "traffic director" upstream service manages them with a common API interface.
* Spark services where a complex part of a calculation like a solve needs to be run multiple times. The solve can be put into a separate file.

## Prerequisites

```mermaid
flowchart LR

    subgraph U["Upstream service"]
        X["Xcall function"]
    end
    subgraph D["Downstream service"]
        C["Calculations and data"]
    end
    
    X -- "Send request" --> C
    C -- "Receive response" --> X
```

* Two Spark services are required:
  * The downstream service that has the encapsulated calculations and data. This can be any Spark service that includes [Inputs and outputs (Xinput and Xoutput)](/build-spark-services/additional-mappings/inputs-and-outputs-xinput-and-xoutput.md).
  * The upstream service that makes the API request and receives the response. This workbook must include the `CS.SPARK_XCALL` user-defined function to call the downstream service.
* Both Spark services must be uploaded in the same tenant and accessible with the same credentials.
* [Coherent Assistant](https://docs.coherent.global/assistant/) is required to define `CS.SPARK_XCALL` user-defined functions in Excel.

## Use the right `Xcall` definition

There are many ways that the `CS.SPARK_XCALL` function can be defined. To understand how best to use this function, follow these steps.

1. Review the target downstream service. It should already be uploaded to Spark.
2. Identify the inputs you need to send to this service and the outputs that you want to receive. Depending on your use case you may not need all the inputs and outputs.
3. Categorize the inputs and outputs as either being single value or tables.
4. Assess the best `CS.SPARK_XCALL` definition using the table below.
5. Follow the step-by-step example for the definition.

<table><thead><tr><th>Downstream inputs to send</th><th width="249">Downstream outputs to receive</th><th>Recommended CS.SPARK_XCALL definition</th></tr></thead><tbody><tr><td>Any number of inputs and types</td><td><code>1</code> single value output</td><td><a data-mention href="#xcall-without-output-template">#xcall-without-output-template</a></td></tr><tr><td>Any number of inputs and types</td><td><code>2+</code> single value output<br>OR<br><code>1+</code> table outputs</td><td><a data-mention href="#xcall-with-output-template">#xcall-with-output-template</a></td></tr><tr><td>Make multiple <code>Xcall</code> at a time with single value inputs only</td><td><code>1+</code> single value outputs</td><td><a data-mention href="#array-xcall-for-scaling">#array-xcall-for-scaling</a></td></tr></tbody></table>

## `Xcall` without output template

| Downstream inputs              | Downstream outputs      |
| ------------------------------ | ----------------------- |
| Any number of inputs and types | `1` single value output |

### Example of `Xcall` without output template

1. In Spark, create a folder called `xcalls` if it does not exist.
2. Upload the Excel files for the downstream service and upstream service into the folder.
3. Use the [API Tester](/navigation/api-tester.md) to interact with the upstream service `matrix_math_single xcall`. The key calculations are performed by the downstream service `matrix_math_single`. The provisioned endpoint can be used for integration.
4. Open the upstream file `matrix_math_single xcall.xlsx` in Excel.
5. Load [Coherent Assistant](https://docs.coherent.global/assistant/) and sign in to the same environment and tenant. Change the input fields and observe that the `CS.SPARK_XCALL` function will recalculate.
6. If you would like to make any changes to the upstream service, edit the Excel file and re-test the `CS.SPARK_XCALL` function with Coherent Assistant. Upload the Excel file again to update the service.

{% tabs %}
{% tab title="Downstream service" %}
Review all of the mappings in the **Name Manager**, either:

* Select the **Formulas** tab and select **Name Manager** OR,
* Use the keyboard shortcut `Ctrl+F3`.

| Input or Output      | Description                                                                                                                                  |
| -------------------- | -------------------------------------------------------------------------------------------------------------------------------------------- |
| `Xinput_scalar`      | Single value input.                                                                                                                          |
| `Xinput_matrix`      | Table input with headings `c1` and `c2`.                                                                                                     |
| `Xoutput_scalar_det` | <p>Single value output of the <a href="https://en.wikipedia.org/wiki/Determinant">determinant</a> <br><code>=det\[scalar⋅matrix]</code>.</p> |

{% file src="/files/mcjRvno7UEQ2bzUHbd0b" %}
{% endtab %}

{% tab title="Upstream service" %}
Review all of the mappings in the **Name Manager**, either:

* Select the **Formulas** tab and select **Name Manager** OR,
* Use the keyboard shortcut `Ctrl+F3`.

| Input                   | Description                                                                   | Cell address |
| ----------------------- | ----------------------------------------------------------------------------- | ------------ |
| `Xinput_up_scalar`      | Single value input sent downstream as `scalar`.                               | `C3`         |
| `Xinput_up_matrix`      | Table input with headings `c1` and `c2` sent downstream as `matrix`.          | `C5:D7`      |
| `Xoutput_up_double_det` | Single value output derived from downstream response  `scalar_det` then `×2`. | `C20`        |

{% file src="/files/iwWK2DjCRfXGpn3PvXjK" %}
{% endtab %}

{% tab title="Upstream service function definition" %}
`CS.SPARK_XCALL("xcalls/matrix_math_single", , "scalar_det", "scalar", C3, "matrix", C5:D7)`

<figure><img src="https://2714190325-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fc7fek1ZgAUH5MA3m5pH8%2Fuploads%2FrStKnu4j3kRguem1qPTY%2Fimage.png?alt=media&amp;token=5bad16c0-f0c7-4220-b01a-f6a3ec0dd3fb" alt="CS.SPARK_XCALL references scalar in cell C3 and matrix in C5:D7."><figcaption></figcaption></figure>

<details>

<summary>Function parameters</summary>

| Parameter                | Description                                                                           | Value                                                                                                              |
| ------------------------ | ------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------ |
| `[service]`              | Service URI expressed in `{folder}/{service}[version]` format.                        | `"xcalls/matrix_math_single"`                                                                                      |
| `[input_location]`       | Leave this empty.                                                                     |                                                                                                                    |
| `[output_template]`      | Name or reference to the name of the single value output from the downstream service. | `"scalar_det"`                                                                                                     |
| `[input_name_or_values]` | Name or reference to the name of the first input to the downstream.                   | `"scalar"`                                                                                                         |
| `[input_name_or_values]` | Value or reference to the values of the first input to the downstream.                | <p><code>C3</code><br>Take the value of <code>Xinput\_up\_scalar</code> and use in the downstream call.</p>        |
| `[input_name_or_values]` | Name or reference to the name of the second input to the downstream.                  | `"matrix"`                                                                                                         |
| `[input_name_or_values]` | Value or reference to the values of the second input to the downstream.               | <p><code>C5:D7</code></p><p>Take the values of <code>Xinput\_up\_matrix</code> and use in the downstream call.</p> |

</details>
{% endtab %}
{% endtabs %}

## `Xcall` with output template

| Downstream inputs              | Downstream outputs                                                                |
| ------------------------------ | --------------------------------------------------------------------------------- |
| Any number of inputs and types | <p><code>2+</code> single value output<br>OR<br><code>1+</code> table outputs</p> |

### Example of `Xcall` with output template

The example is similar to [#example-of-xcall-without-output-template](#example-of-xcall-without-output-template "mention") except the downstream also includes a table response. An `[output_template]` is recommended for the `CS.SPARK_XCALL` function to properly layout the outputs returned by the downstream service.

1. In Spark, create a folder called `xcalls` if it does not exist.
2. Upload the Excel files for the downstream service and upstream service into the folder.
3. Use the [API Tester](/navigation/api-tester.md) to interact with the upstream service `matrix_math_w_table xcall`. The key calculations are performed by the downstream service `matrix_math_w_table`. The provisioned endpoint can be used for integration.
4. Open the upstream file `matrix_math_single xcall.xlsx` in Excel.
5. Load [Coherent Assistant](https://docs.coherent.global/assistant/) and sign in to the same environment and tenant. Change the input fields and observe that the `CS.SPARK_XCALL` function will recalculate.
6. If you would like to make any changes to the upstream service, edit the Excel file and re-test the `CS.SPARK_XCALL` function with Coherent Assistant. Upload the Excel file again to update the service.

{% tabs %}
{% tab title="Downstream service" %}
Review all of the mappings in the **Name Manager**, either:

* Select the **Formulas** tab and select **Name Manager** OR,
* Use the keyboard shortcut `Ctrl+F3`.

| Input or Output       | Description                                                                                                                                    |
| --------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------- |
| `Xinput_scalar`       | Single value input.                                                                                                                            |
| `Xinput_matrix`       | Table input with headings `c1` and `c2`.                                                                                                       |
| `Xoutput_scalar_det`  | <p>Single value output of the <a href="https://en.wikipedia.org/wiki/Determinant">determinant</a> <br><code>=det\[scalar ⋅ matrix]</code>.</p> |
| `Xoutput_scalar_mult` | <p>Table input with headings <code>c1</code> and <code>c2</code><br><code>=scalar ⋅ matrix</code>.</p>                                         |

{% file src="/files/R0zRW5pcG4cqNwVj9B2t" %}
{% endtab %}

{% tab title="Upstream service" %}
Review all of the mappings in the **Name Manager**, either:

* Select the **Formulas** tab and select **Name Manager** OR,
* Use the keyboard shortcut `Ctrl+F3`.

| Input                      | Description                                                                                        | Cell address |
| -------------------------- | -------------------------------------------------------------------------------------------------- | ------------ |
| `Xinput_up_scalar`         | Single value input sent downstream as `scalar`.                                                    | `C3`         |
| `Xinput_up_matrix`         | Table input with headings `c1` and `c2` sent downstream as `matrix`.                               | `C5:D7`      |
| `Xoutput_up_double_det`    | Single value output derived from downstream response  `scalar_det` then `×2`.                      | `C20`        |
| `Xoutput_up_double_matrix` | Table output with headings `c1` and `c2` derived from downstream response `scalar_mult` then `×2`. | `C22:D24`    |

{% file src="/files/3eJrq3rwbZgIZU6rlQh2" %}
{% endtab %}

{% tab title="Upstream service function definition" %}
`CS.SPARK_XCALL("xcalls/matrix_math_w_table", B9:D12, "scalar", C3, "matrix", C5:D7)`

<figure><img src="https://2714190325-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fc7fek1ZgAUH5MA3m5pH8%2Fuploads%2FweJL7Q23a4pRyuatHCTJ%2Fimage.png?alt=media&amp;token=efd1d5fe-5345-45ef-a3b3-0220897ac0c3" alt="CS.SPARK_XCALL references scalar in cell C3 and matrix in C5:D7 with an output template in B9:D12."><figcaption></figcaption></figure>

<details>

<summary>Function parameters</summary>

| Parameter                | Description                                                                                                                                                                                                                                                                                                                                                     | Value                                                                                                                                   |
| ------------------------ | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------- |
| `[service]`              | Service URI expressed in `{folder}/{service}[version]` format.                                                                                                                                                                                                                                                                                                  | `"xcalls/matrix_math_w_table"`                                                                                                          |
| `[input_location]`       | Leave this empty.                                                                                                                                                                                                                                                                                                                                               |                                                                                                                                         |
| `[output_template]`      | <p>Output template is defined by a range reference. Inside this range:</p><ul><li>The left most column is for the output names.</li><li>The columns to the right define the space where the outputs will be placed. Single values need only one cell.<br>Tables should have the table headings and the maximum size of the output response allocated.</li></ul> | <p><code>B9:D12</code></p><p>where<br><code>scalar\_det</code> <code>B9:C9</code><br><code>scalar\_mult</code> <code>B10:B12</code></p> |
| `[input_name_or_values]` | Name or reference to the name of the first input to the downstream.                                                                                                                                                                                                                                                                                             | `"scalar"`                                                                                                                              |
| `[input_name_or_values]` | Value or reference to the values of the first input to the downstream.                                                                                                                                                                                                                                                                                          | <p><code>C3</code><br>Take the value of <code>Xinput\_up\_scalar</code> to send the downstream call.</p>                                |
| `[input_name_or_values]` | Name or reference to the name of the second input to the downstream.                                                                                                                                                                                                                                                                                            | `"matrix"`                                                                                                                              |
| `[input_name_or_values]` | Value or reference to the values of the second input to the downstream.                                                                                                                                                                                                                                                                                         | <p><code>C5:D7</code></p><p>Take the values of <code>Xinput\_up\_matrix</code> to send the downstream call.</p>                         |

</details>
{% endtab %}
{% endtabs %}

## Array `Xcall` for scaling

| Downstream inputs                                             | Downstream outputs        |
| ------------------------------------------------------------- | ------------------------- |
| Make multiple `Xcall` at a time with single value inputs only | `1+` single value outputs |

### Example of array `Xcall` for scaling

If you need to perform multiple `Xcall` to a service at a time and downstream inputs and outputs are only single values, then you can make use of array `Xcall`. Array `Xcall` are easier to define many `Xcall` together and can be configured for higher synchronous `Xcall` performance.

1. In Spark, create a folder called `xcalls` if it does not exist.
2. Upload the Excel files for the downstream service and upstream service into the folder.
3. Use the [API Tester](/navigation/api-tester.md) to interact with the upstream service `ab_operations xcall`. The key calculations are performed by the downstream service `ab_operations`. The provisioned endpoint can be used for integration.
4. Open the upstream file `ab_operations_xcall.xlsx` in Excel.
5. Load [Coherent Assistant](https://docs.coherent.global/assistant/) and sign in to the same environment and tenant. Change the input fields and observe that the `CS.SPARK_XCALL` function will recalculate.
6. If you would like to make any changes to the upstream service, edit the Excel file and re-test the `CS.SPARK_XCALL` function with Coherent Assistant. Upload the Excel file again to update the service.

{% tabs %}
{% tab title="Downstream service" %}
Review all of the mappings in the **Name Manager**, either:

* Select the **Formulas** tab and select **Name Manager** OR,
* Use the keyboard shortcut `Ctrl+F3`.

| Input or Output   | Description                            |
| ----------------- | -------------------------------------- |
| `Xinput_a`        | Single value input.                    |
| `Xinput_b`        | Single value input.                    |
| `Xoutput_sum`     | Single value output of `SUM(a,b)`.     |
| `Xoutput_product` | Single value output of `PRODUCT(a,b)`. |
| `Xoutput_sumsq`   | Single value output of `SUMSQ(a,b)`.   |

{% file src="/files/z83bcbXnTApiFr856WMt" %}
{% endtab %}

{% tab title="Upstream service" %}
Review all of the mappings in the **Name Manager**, either:

* Select the **Formulas** tab and select **Name Manager** OR,
* Use the keyboard shortcut `Ctrl+F3`.

| Input            | Description                                                                                                                                                                                                                                                                                                                      | Cell address |
| ---------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------ |
| `Xinput_table`   | Table with headings `a` and `b` . The table records will be sent downstream as `a` and `b`.                                                                                                                                                                                                                                      | `C3`         |
| `Xoutput_table1` | <p>Table with columns <code>concat</code>, <code>sum</code>, <code>product</code>, <code>sumsq</code> derived from downstream response <code>sum</code>, <code>product</code>, <code>sumsq</code>.<br>The source <code>CS.SPARK\_XCALL</code> references the data in <code>Xinput\_table</code> as a single contiguous range</p> | `I10:L13`    |
| `Xoutput_table2` | <p>Table with columns <code>concat</code>, <code>sum</code>, <code>product</code>, <code>sumsq</code> derived from downstream response <code>sum</code>, <code>product</code>, <code>sumsq</code>.<br>The source <code>CS.SPARK\_XCALL</code> references the data in <code>Xinput\_table</code> as separate columns.</p>         | `I15:L18`    |

{% file src="/files/GkcpLTu1dsTSQZJNEvw9" %}
{% endtab %}

{% tab title="Upstream service function definition" %}
For array `Xcall`, the input data can be referenced as a contiguous range or as separate columns depending on the layout of your worksheet. Both will work the same way.

{% tabs %}
{% tab title="Reference data as contiguous range " %}
`CS.SPARK_XCALL("xcalls/ab_operations", , C10:E10, C3:D3, C4:D6)`

<figure><img src="https://2714190325-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fc7fek1ZgAUH5MA3m5pH8%2Fuploads%2FBxO6LPkqNc0JyRmqa4J6%2Fimage.png?alt=media&amp;token=a4092cfb-3893-4cf3-af15-02997ff0cdf9" alt="CS.SPARK_XCALL references headings C3:D3 and data in C4:D6 for the outputs defined in C10:E10."><figcaption></figcaption></figure>

<details>

<summary>Function parameters</summary>

| Parameter                | Description                                                                                                            | Value                                                                                                                                            |
| ------------------------ | ---------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------ |
| `[service]`              | Service URI expressed in `{folder}/{service}[version]` format.                                                         | `"xcalls/ab_operations"`                                                                                                                         |
| `[input_location]`       | Leave this empty.                                                                                                      |                                                                                                                                                  |
| `[output_template]`      | Output template is defined by using a range. For array `xcall`, this should be arranged as a `1 row × n column` range. | `C10:E10`                                                                                                                                        |
| `[input_name_or_values]` | Name or reference to the name of the first input from the downstream.                                                  | <p><code>C3:D3</code><br>Take the headings of <code>a</code> and <code>b</code> from <code>Xinput\_table</code> to send the downstream call.</p> |
| `[input_name_or_values]` | Value or reference to the values of the first input from the downstream.                                               | <p><code>C4:D6</code><br>Take the data for <code>a</code> and <code>b</code> from <code>Xinput\_table</code> to send the downstream call.</p>    |

</details>
{% endtab %}

{% tab title="Reference data as separate columns" %}
`CS.SPARK_XCALL("xcalls/ab_operations", ,C15:E15, C3, C4:C6, D3, D4:D6)`

<figure><img src="https://2714190325-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fc7fek1ZgAUH5MA3m5pH8%2Fuploads%2FTs9WD63VCP3KA9uLXVht%2Fimage.png?alt=media&amp;token=0538426c-5822-4077-846e-5f11b25e99aa" alt="CS.SPARK_XCALL references headings C3 and D3, data in C4:C6 and D4:D6 for the outputs defined in C15:E15."><figcaption></figcaption></figure>

<details>

<summary>Function parameters</summary>

| Parameter                | Description                                                                                                                                | Value                                                                                                                         |
| ------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------ | ----------------------------------------------------------------------------------------------------------------------------- |
| `[service]`              | Service URI expressed in `{folder}/{service}[version]` format.                                                                             | `"xcalls/ab_operations"`                                                                                                      |
| `[input_location]`       | Leave this empty.                                                                                                                          |                                                                                                                               |
| `[output_template]`      | Output template is defined by a range reference. For array `xcall`, this should be arranged as a `1 row × n column` range of output names. | `C15:E15`                                                                                                                     |
| `[input_name_or_values]` | Name or reference to the name of the first input from the downstream.                                                                      | <p><code>C3</code><br>Take the heading of <code>a</code>  from <code>Xinput\_table</code> to send the downstream call.</p>    |
| `[input_name_or_values]` | Value or reference to the values of the first input from the downstream.                                                                   | <p><code>C4:C6</code><br>Take the data for <code>a</code> from <code>Xinput\_table</code> to send the downstream call.</p>    |
| `[input_name_or_values]` | Name or reference to the name of the second input from the downstream.                                                                     | <p><code>D3</code><br>Take the heading of <code>b</code> from <code>Xinput\_table</code> to send the downstream call.</p>     |
| `[input_name_or_values]` | Value or reference to the values of the second input from the downstream.                                                                  | <p><code>D4:D6</code></p><p>Take the data for <code>b</code> from <code>Xinput\_table</code> to send the downstream call.</p> |

</details>
{% endtab %}
{% endtabs %}
{% endtab %}
{% endtabs %}

## Next steps

Before making your first `Xcall`, review the [CS.SPARK\_XCALL UDF function reference](/build-spark-services/additional-mappings/call-spark-services-as-functions-xcall/cs.spark_xcall-udf-function-reference.md). Many `CS.SPARK_XCALL` configurations are available. Review these configurations before implementing `Xcall` into your own upstream Spark service.