A variable in Apricot Results Reporting (ARR) is any object created within the report, as opposed to objects that are generated based on the form fields brought into the report query. Variables allow you to manipulate your existing objects with formulas to present the specific subset of data you're looking to highlight in your report, similar to custom expressions in the Native Reporting tool.
Follow the steps listed below to learn how to create a variable in a Results report.
Step 1: Open the Variable Editor
At the top-right corner of ARR, ensure that the clipboard icon is highlighted in blue to expand the right-hand panel. Then, select the cube icon to open the Document Objects tab of the panel.
Click '+ Add Variable' to open the Variable Editor window.
Step 2: Set up the variable
Create a name that describes the data you want the variable to return and distinguishes it from other objects and variables in your report. The description is optional and allows you to describe the variable's function.
The available options for Qualification are Dimension, Measure, and Detail. By default, the variable will show as a dimension. This will automatically change to a measure if the formula performs an aggregate function. The third qualification type is a detail, which is often used for merged dimensions. To learn more about this, click here. The variable type will also populate automatically after the formula is entered depending on the objects and functions in the formula.
Step 3: Build the formula
Type your formula for the variable into the Formula text box of the Variable Editor. Then, click the check icon to the right to validate if the formula is defined correctly. The 'x' icon will remove any changes made since the last validation.
Click OK at the bottom of the window to save your variable.
Below are some important considerations as you build your own formulas:
Brackets [ ] should surround data elements from the Objects panel, and semicolons should be placed between dimensions when referencing multiple data elements.
Parentheses ( ) should surround and group together the functions or operators to be used; everything within the parentheses of a function or operator is related to that action. Count your parentheses to ensure there aren't too few or too many around each grouping
Formulas are performed following mathematical logic, meaning actions are performed from the inside to the outside. You may return different results depending on the order and grouping of your formula.
You can also access the SAP Help Portal to learn more about formulas here.
Step 4 (optional): Edit the variable
Variables can be used in a report table or within other formulas similar to any other report object. They will appear in the Variables folder within the Document Objects tab.
While hovering over a variable's name, click the horizontal three-dot icon to do any of the following:
Rename the variable without opening the Variable Editor window
Duplicate the variable to create a copy that can be edited and adjusted separately from the original variable (often used to manually flatten data; to learn more, click here)
Delete the variable permanently
Edit the variable by opening the Variable Editor again
Step 5 (optional): Create a variable from a cell formula
Any formula created within a single cell or column can be turned into a variable if the formula needs to be used in other formulas or variables.
For example, we've pulled in the Date of Service object into the report table. By clicking into a cell in the column then adding the Count function around the object in the formula bar, the report returns the number of unique dates each participant has received a service.
To create a variable from this formula, highlight the cell or column and click the paper icon to the left of the formula bar. The Variable Editor will open for you to name and further configure the variable.
Click the OK button to save the variable, and the formula will now be a reusable object in the Variables folder.