How to Create a New Variable
- Right click on the Variables folder in the Available Objects window and select New. This opens up the Variable Editor.
- Name: Name the variable something descriptive
- Type: This will populate automatically after the formula is entered
- Description: This is optional; allows you to describe the variable's function
- Qualification: Dimension, Measure, or Detail (variables will automatically change to measure if there is an aggregate function)
- Formula: Type the formula for the variable into the formula pane
- Validate Formula: Tells you if the formula is defined correctly or if there are errors
- Cancel: erases any changes made within the last edit
You can use created variables in a formula or within the report in exactly the same way as you use other report objects. They will appear in the Variables folder within the Available Objects window.
- To edit a variable, double click it, or right click and choose Edit.
- To delete a variable, right click on the variable and select Delete. This will permanently delete the variable.
- To create a copy of a variable, right click on the variable and select Duplicate. The copy can be edited and adjusted as needed.
This is useful when similar variables need to be created many times (very helpful for flattening data).
Any formula created can be turned into a variable if the formula needs to be used in other formulas/variables.
Square brackets [ ] should surround data elements. Always place a semicolon (;) between dimensions where referencing multiple data points.
Rounded Parentheses ( ) should surround and group the functions/operators to be performed; everything within the parentheses of a function or operator is related to that action
- Issues with too many or too few parentheses are common—be sure to count your parentheses to be sure you are aren’t missing any!
- If your variable doesn’t behave the way you expect, misplaced parentheses are a common culprit. Be sure you have grouped your actions they way you intended.
Formulas are performed following mathematical logic--actions are performed from the inside to the outside. Be sure to remember this as you consider the order of your formulas.
Specific considerations/actions are performed first (and will be towards the middle of a formula) and over-arching actions are performed last (and will be towards the outside of the formula).