Defaulting a Transformation variable to 0 if the variable's associated query returns no data

  • What Grafana version and what operating system are you using?

9.5.15
Amazon Linux (AL2023-x86_64-20231215)

  • What are you trying to achieve?

A variable is obtained through a query. The variable is consumed in a transformation. I want the variable’s value to default to 0 in the event that the query returns no data.

For example, if ALIAS is obtained through this query:

SELECT X as ALIAS FROM Y WHERE Z

then I want ALIAS to default to 0, when the query associated with ALIAS returns no dataa nd when ALIAS is used in a transformation.

  • How are you trying to achieve it?

I tried using the coalesce function as coalesce(var_name, 0). This does not work, however.

  • What happened?

The transformation making use of the aformentioned variable fails. The transformation is a sum, involving the aformentioned variable and another varaible. I believe that the transformation fails, because the aforementioned variable’s associated query returns no data.

  • What did you expect to happen?

In Grafana 9.3.8 such variables are defaulted to 0, in the event that their associated query returns no data. I want such a defaulting.

  • Can you copy/paste the configuration(s) that you are having problems with?

I cannot paste the JSON template, because the JSON template is sensitive. However, if you wish, I could attempt to create a toy example.

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.

No.

  • Did you follow any online instructions? If so, what is the URL?

No

You should to solve it on the SQL level. No idea what kind of SQL you have, but there is many functions, that can help you to return default 0 value , e.g. CASE, NVL, UNION, IF, OR, … Just check your SQL doc and be creative.

1 Like

Let grafana do what it does best visualization

Let your db query do what it does best: what you described you want.

This is where the power of atored procedures comes in