Sprinkle Docs
  • What is Sprinkle?
  • Quick Start
  • Analysing your data
    • 🔭Analytics Overview
    • 💠Data Models
      • *️Variables
      • 🌲Hierarchies
      • 🤿Column Mask
    • 🎉Switch to New Reports & Dashboards
    • 🆕Reports
      • Overview
      • Build Using Tables
        • Create a new Report
        • Layout and options
        • Build and Format - Overview
        • Apply Row Limits
        • Identify Date Columns
        • Filter your data
        • Visualizations
          • Table
          • Pivot
          • Line Chart
          • Bar Chart
          • Column Chart
          • Area Chart
          • Combo Chart
          • Scatter & Bubble Plot
          • Pie Chart
          • Funnel Chart
          • Stat Card
          • Point Map
          • Heat Map
          • Radial gauge chart
        • Advanced Features
          • Custom Analysis
          • Variables
          • Table & Quick Calculations
          • Drill - Hierarchical & Date
          • Break Out
          • RLS in Table reports
          • Scheduled Exports
          • Embedding Table Reports
      • Build Using Models
        • Create a new report
        • Layout and options
        • Visualizations
        • Advanced Features
      • Build SQL Reports
        • Create a new Report
        • Layout and options
        • Writing a SQL Code on Editor
        • Visualizations
        • Variables in SQL Reports
    • 🆕Dashboards
      • 🌀Filters
      • 👆Click Behaviour
      • ⏰Data Alerts
      • 🗓️Date Drill
      • 📤Scheduled Exports
      • 🔗Embed link
      • 🖥️Dashboard layout
      • 📱Mobile Dashboards
  • Transforming your data
    • 🔰SQL Transform
    • 📓Python Notebooks
  • Integrating your data
    • ☁️Destination Warehouses
      • AWS Athena
        • Manage storage of Flow tables
      • AWS Redshift
      • Azure Synapse
      • Databricks
      • Google BigQuery
      • MySQL
      • Postgres
      • Snowflake
      • SQL Server
      • K8 Setup
        • AWS EKS
        • Google GKE
        • Azure AKS
    • ⚙️Warehouse & Storage Setup
  • Ingesting your data
    • ☄️Data Imports
      • Databases
        • Azure Cosmos DB
        • Azure Table Storage
        • Google BigQuery
        • Mongo DB
        • MySQL DB
        • Oracle DB
        • Postgres DB
        • SQL Server DB
        • Features
          • Ingestion Modes
          • Add Multiple Datasets
          • CDC Setup
            • CDC setup in Mysql
            • CDC setup in Postgres
            • CDC setup in Mongo
            • CDC setup in SQL Server
          • Destination Create Table Clause
          • SSH Tunnel Setup
      • Files
        • AWS S3
        • AWS S3 External
        • Azure Blob
        • FTP
        • Google Cloud Storage
        • Google Sheet
        • SFTP
      • Applications
        • Apple Search Ads
        • Appsflyer
        • Branch
        • Clevertap
        • Facebook Ads
        • Freshdesk
        • Freshsales
        • Google Ads
        • Google Ads V2
        • Google Analytics
        • Google Analytics 4
        • Google Analytics MCF
        • Google Search Console
        • Hubspot
        • Impact Ads
        • Intercom
        • Klaviyo
        • Leadsquared
        • LinkedIn Ads
        • Magento
        • Mailchimp
        • Marketo
        • Mixpanel
        • MoEngage
        • Rocketlane
        • Salesforce
        • SAP S4
        • Shopify
        • Snapchat Marketing
        • TikTok Ads
        • WooCommerce
        • Zendesk Chat
        • Zendesk Support
        • Zoho Analytics
        • Zoho Books
        • Zoho CRM
        • Zoho Desk
        • Zoho Invoice
        • Zoho Subscription
      • Events
        • Apache Kafka
        • AWS Kinesis
        • Azure EventHub
    • 📤File Uploads
    • 🤖API Pulls
    • 🕸️Webhooks
  • Collaborating on data
    • 📤Sharing
    • 💬Comments
    • ⚡Activity
    • 🏷️Labels
  • Managing Schedules and Data Refreshes
    • ⏱️Schedules
    • 🔔Notifications
  • User Management
    • 🔑Access Management
    • 🧑‍🤝‍🧑Groups
    • 📂Folders
    • 🔄Syncing users, groups and RLS
    • 📧Azure AD Integration
  • Data Security & Privacy
    • 🔐Security at Sprinkle
    • 📄GDPR
    • 📄Privacy Policy
  • Release Notes
    • 📢Release Notes
      • 🗒️Release Notes - v12.1 (New)
      • 🗒️Release Notes - v12.0
      • 🗒️Release Notes - v11.0
      • 🗒️Release Notes - v10.8
      • 🗒️Release Notes - v10.7
      • 🗒️Release Notes - v10.6
      • 🗒️Release Notes - v10.5
      • 🗒️Release Notes - v10.4
      • 🗒️Release Notes - v10.3
      • 🗒️Release Notes - v10.2
      • 🗒️Release Notes - v10.1
      • 🗒️Release Notes - v10.0
      • 🗒️Release Notes - v9.31
      • 🗒️Release Notes - v9.30
      • 🗒️Release Notes - v9.29
      • 🗒️Release Notes - v9.28
      • 🗒️Release Notes - v9.27
      • 🗒️Release Notes - v9.25
      • 🗒️Release Notes - v9.24
      • 🗒️Release Notes - v9.23
      • 🗒️Release Notes - v9.22
      • 🗒️Release Notes - v9.21
      • 🗒️Release Notes - v9.20
      • 🗒️Release Notes - v9.19
      • 🗒️Release Notes - v9.18
      • 🗒️Release Notes - v9.17
      • 🗒️Release Notes - v9.16
      • 🗒️Release Notes - v9.14
      • 🗒️Release Notes - v9.13
      • 🗒️Release Notes - v9.12
      • 🗒️Release Notes -v9.8
      • 🗒️Release Notes - v9.7
      • 🗒️Release Notes - v9.6
      • 🗒️Release Notes - v9.5
      • 🗒️Release Notes - v9.4
      • 🗒️Release Notes - v9.3
      • 🗒️Release Notes - v9.2
      • 🗒️Release Notes - v9.1
      • 🗒️Release Notes - v9.0 (Major)
      • 🗒️Release Notes - v7.23
      • 🗒️Release Notes - v7.21
      • 🗒️Release Notes - v7.20
      • 🗒️Release Notes - v7.15
      • 🗒️Release Notes - v7.14
      • 🗒️Release Notes - v7.13
Powered by GitBook
On this page
  • How to create/add Local variables in SQL Report:
  • Where to Use Variable in SQL Report:
  • Note**:
  • RLS variables in SQL Reports
  1. Analysing your data
  2. Reports
  3. Build SQL Reports

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 three types of Variables in SQL Reports based on the scope of their usage & it's use:

  • 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.

  • RLS Variables: These allow you to restrict data visibility in reports based on RLS attributes & user permissions.

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:

    1. Variable Name: This allows you to name the Variable you want to add.

    2. Label: This allows you to add a Label to your Variable.

    3. Data Type: This allows you to choose the Data type of the Variable's value. It can be Text, Date, and Numeric.

    4. Input Method: This allows you to choose the input method for Variable entries such as Type In, Pickup List, or both combined.

    5. Suggest Value From: If the data type is text and input method is any-other than Type In, then this field will be shown. It has two option Manual or Column.

      1. Manual: When selected, variable values must be manually added or entered at the time of variable creation.

      2. Column: When selected, users can choose values directly from existing columns in the dataset.

    6. 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.

    • Special Case: Date Data Type

      If the Data Type of the variable is set to Date, you'll be given two options for how to define its default value:

      1. Manual

      • Allows you to manually enter a specific date during variable creation.

      • Best when the default value is fixed or rarely changes.

      2. Custom Relative Date

      • Lets you choose a dynamic, relative date based on the current date.

      • Ideal for variables that should adapt over time.

      Available Options:

      • Today

      • Tomorrow

      • Yesterday

      • Start of current week (Starts Sunday)

      • End of current week (Ends Saturday)

      • Start of current month

      • End of current month

      • Start of current year

      • End of current year

  • 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.

RLS variables in SQL Reports

To apply RLS to a SQL report:

  1. Create an RLS Attribute: Define an RLS attribute on the RLS page.

  2. Create an RLS Variable:

    • To use an RLS attribute in an SQL report, you'll need to create an RLS variable first.

    • In the report's variables section, create an RLS variable linked to the desired RLS attribute.

  3. Using RLS Variable in SQL: Incorporate the RLS variable into your SQL query using the syntax {{rls.rls_variable_name}} within a IN clause.

  4. Run the Report: The RLS will be applied when the report is executed.

Example:

To filter sales data based on a manager's country, create an RLS variable linked to the "Country" column in the RLS attribute. Use this variable in your SQL query like:

SELECT * FROM sales_data
WHERE country IN {{rls.manager_country}}

PreviousVisualizationsNextDashboards

Last updated 29 days ago

🆕