Variables in SQL Reports
A variable is a data value that can be a static or a dynamic value based on your requirement.
Here we have two types of Variables in SQL Reports based on the scope of their usage:
Local Variables: These variables can be used only within the scope of the SQL Report in which they are created, you can use it any/all tabs in the report.
Global Variables: These variables can be used in any report.
How to create/add Local variables in SQL Report:
First, you need to click on the Variables Icon '{x}' left of the Schema Browser.
Now, you can click on the '+Add Variable' button in the Local tab.
Now the Add Variable window will open. It has the following components:
Variable Name: This allows you to name the Variable you want to add.
Label: This allows you to add a Label to your Variable.
Data Type: This allows you to choose the Data type of the Variable's value. It can be Text, Date, and Numeric.
Input Method: This allows you to choose the input method for Variable entries such as Type In, Pickup List, or both combined.
Default Value: Here you need to specify the default values for the Variable as per your need.
For Type In variables- You need to specify a single Default value.
For Pickup List variables- You need to give multiple comma-separated values as default values. Then you can choose the desired Variable value from the Pickup list.
Now click on Add button and your Local variable will be added.
Note**: Global variables can only be created in Admin >> Settings>> Global Variable.
Where to Use Variable in SQL Report:
You can use variables in the SQL Editor to create filters, use dynamic columns for selection or group by clauses.
For Eg:
If you have Banking Loan data and you want to create a new expression "Total Payout" by calculating the total loan amount to be paid over a tenure based on different sets of Interest rates 10%, 15%, 20%, etc..
Here you can use a Variable with a Numeric data type named "Interest Rate" with some default value like 10 and use this in the SQL Report. So now you can later change the variable value to some different interest rate based on which new "Total Payout" will be calculated at run time in view mode as well.
Note**:
To identify the variable in the SQL Report, use {{variableName}} and enclose it within curly brackets.
To identify the global variable in the SQL Report, use {{Globals.variableName}} and enclose it within curly brackets.
Once the Variable has been created and used in the SQL Report, then you can directly change it in the Reports' View Mode itself. You will have the Variable Panel on the left-hand side from where you can modify the Variable and run the Report to see the changes.
Last updated