Formulas are logical equations that are used to calculate values. Formulas are used to manipulate the data in queries.
The formula bar is located at the top of the report document.
- This is where the formula will go. If you click into a cell, you will be able to see which object or formula is currently present in a cell.
- fx: This is the Formula Editor. The Formula Editor will pop up in a new window if you click this button. The Formula Editor is described in detail below.
- Create Variable: this will allow you to create a variable from an existing formula. For more details, see linked article.
- Cancel: this will cancel any changes you have recently made
- Validate Formula: this will check the formula for any syntax errors (e.g. missing parenthesis, brackets, values) If you receive an error message, you will need to correct the formula before proceeding
- This will expand the box with the formula. If you have a lengthy formula, you can click this to see the full text.
- To create a formula, click into the column or row where the formula should be performed
- Users can either type their formula directly into the formula pane box or they can click on the fx button within the formula bar to open the Formula Editor in a separate window. The Formula Editor will assist users by listing out all available functions and operators and providing descriptions of how to use them
The Formula Editor
- This is where the formula will go. Simply start typing your formula into the box.
- The green check mark will check the syntax of your formula. If the formula is correct, you will see a message saying "The formula is defined correctly." If you have any errors in your formula, the message box will attempt to show you where your error might be.
- The blue x will cancel all changes you have made since opening the box. It will revert your formula to its original state.
- The Available Objects box includes all objects in your report. If you are unsure of an object's exact name, you can scroll through the objects to find the correct one, double click the object and it will populate into the formula box. This list is sorted the same way the Available Objects pane is within your report. So you can sort it by Query, Data Source (which will show you the root folder the object comes from), or Alphabetic order.
- The Available Functions are helpful if you are looking for a function that you may not be super familiar with. You can look through categorized folders or search through All functions. You can also double click these objects to have them populate into the formula box.
Pro tip: if you click on a function in this list, it will give you the definition of the function within the formula editor. You can also click the link for "More on this function" for SAP documentation that will detail the function's functionality.
- Available Operators: these are your syntax objects.
All formulas must start with an =.
- Values: To use this, select an object from the Available Objects window. Example: Name. Then double click the Values option. A list of available values for the Name object will appear. You can this click the value you are looking for, select OK, and the value will populate into the formula bar with quotes around it.
This is useful when needing to get exact text from touchpoint questions or Program Names, for example.
- Prompts: This will give you the text from any prompts on the report. For example, if you are running the report to prompt for a date range and you've changed the prompt text to be "Begin Date" and "End Date", this will tell you the prompt text and which query it is applied to.
This is useful when using variables that include the UserResponse() function.
More on this function - will take you to in depth explanation of function (opens in a new tab in your browser)