How to declare and set a variable using SQL editor for a chart in Jira Analytics ?

Question

Hello,

My context is a creating a custom chart within Jira Analytics.

I would like to use a variable within my SQL statement, in order to keep that statement more generic and reusable for other charts.

I have read the Databricks documentation (if I have understood it well, Jira Analytics is based on Databricks).

I have tried to use the statement 'DECLARE VAR myvar ..;'  plus the statement 'SET VAR myvar=...:' but I got syntax error from parser.

I guess that type of statement is not supported yet by Analytics. If it is the case, how can I declare a variable ?

To be precise, I have already used a Common Table Expression (CTE) in my SQL, and I cannot use more than one (which I find weird by the way) to solve this.

Any hints/tips ?

Thanks !

Jared Wallen · Answer

those articles show how to do variables in the visual editor or reference it in sql. is there a way to declare or set the value of {VARIABLES} in the SQL editor?

Jaime Escribano _knowmad mood_ · Answer

Hi Gregory! Welcome to the community!

You are completly right that Analytics is built on top of Databricks. Sadly declaring variables the way you attempted is not supported 😣

What you might wanna do is declare variables at the dashboard or global level as detailed in this documentation: https://support.atlassian.com/analytics/docs/use-and-edit-variables/

You can also learn the details about variables here: https://support.atlassian.com/analytics/docs/what-are-variables/