__value and __text for variables not working as expected

  • What Grafana version and what operating system are you using?
    9.3.6 running on MacOS, datasource is using the BigQuery datasource from Grafana Labs version 1.2.4.

  • What are you trying to achieve?
    Setup a variable with a value and a text field so that the text field is displayed but in the dashboard I can reference the value field in other sql.

  • How are you trying to achieve it?
    I am using the following SQL for the variable:
    SELECT DISTINCT
    ctx.Site AS __text,
    ctx.SiteID AS __value
    FROM contexts_v0.installation_context_v0 ctx

I have tested this SQL in BigQuery Explorer and it executes as I’d expect returning two columns with the appropriate names.

The variable is called varSiteId and I reference it using ${varSiteId}.

  • What happened?
    ${varSiteId} is being replaced with the name of the site (from __text) rather than the id (from __value).

  • What did you expect to happen?
    The site name to be displayed and ${varSiteId} to contain the (integer) id (for example “Test Site 01” would see “${varSiteId}” returning “21”.

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

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

  • Did you follow any online instructions? If so, what is the URL?
    PostgreSQL data source | Grafana documentation

1 Like

Welcome

What is the data type of the above column?

It’s an integer - does it need to be text?

no you dont, just try

${varSiteId:raw}

I just get the string value - the problem is it’s not correctly mapping the __text and __value columns.

However, if I put them in reverse order (__value and then __text) it works which is a fix … of a kind … probably still a bug that needs looking at though!

So in summary the SQL;

SELECT DISTINCT
ctx.SiteID AS __value,
ctx.Site AS __text
FROM contexts_v0.installation_context_v0 ctx

Works. But column ordering seems to be a real issue that I’m not sure is documented anywhere.

1 Like