3725573 - issues with SAP.getvariables function in sac excel add-in

• Using SAP.GETVARIABLES formula to combine multiple inputs into a single cell results in merged values with line breaks instead of comma-separated values.
• Multiple inputs for a variable split into different cells instead of being displayed in a single cell, comma-separated.
• Customer has a consulting question on how to get the data in a single cell with delimiters.

SAP Analytics Cloud, add-in for Microsoft Office

1. Create a table in the SAC Excel Add-In using a HANA Cloud Calculation View containing input prompts.
2. Open the Prompt dialog and select multiple values for a variable (e.g., Company Code).
3. Insert the formula SAP.GETVARIABLES to display the prompt selections.
4. Refresh the table.
5. Observe:

• Formula does not update automatically.
• Multiple values split into separate cells in certain layouts.
• In layouts intended to combine values, the output merges values using line breaks rather than producing a comma-separated result.

The behavior is by design.  The parameters of the formula may result in splitting values into separate cells or merging them into a single string without delimiters, depending on the layout parameter configuration.

The function SAP.GETVARIABLES returns the variable values of a data source.
1.Table, Enter the table name or data source alias in quotation marks.
2.Variables, Enter a comma-separated list of variable IDs in quotes, This parameter is optional.
3.Variable Display, 0 ID, 1  description of the variable (default value), This parameter is optional.
4.Value Display, 0 ID variable value (default value), 1 description of the variable value, This parameter is optional.
5.Layout, 0 in a cell and all values in one cell, 1 for the variable in a cell and the values in separated cells (default), 2 for all variable values in one cell without the variable, 3 for the variable values in separated cells without the variable, This parameter is optional.
6.Orientation, 0 for vertical, 1 for horizontal (default), This parameter is optional.

To achieve comma-separated values in a single cell:

• Use the EXCEL function TEXTJOIN formula in combination with SAP.GETVARIABLES:

TEXTJOIN(",",1,SAP.GETVARIABLES("DataTable42","Region Code",,,3))

Refer to:

https://help.sap.com/docs/SAP_ANALYTICS_CLOUD_OFFICE/29624d6020b74bf8817bb7df398e5cb6/66b1a0396a9c4a6e8a3789ce6613dff7.html

 SAC Excel Add-in Help - SAP.GETVARIABLES /a

TEXTJOIN function
https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c

sac excel add-in, getvariables, manual refresh, formula update, multiple inputs, comma-separated values, textjoin formula, sac hana cloud calculation view, sac excel prompt, sac excel table refresh, sac excel formula layout, sac excel variable delimiter , KBA , LOD-ANA-OF-XLA , SAP Analytics Cloud, add-in for Microsoft Office , Problem

SAP Analysis for Microsoft Office 2.8 ; SAP Analytics Cloud 1.0