I’m trying to make a really cool interactive visualization, but to do this I need to create linked variables which produce key-value pairs.
For the first variable, I can do normal key-value pairs, e.g. Custom variable → var1 : v1 , var2 : v2 etc.
For the second variable, I have referenced the first variable in a postgres query and used cases in the select to concatenate the appropriate values, e.g.:
SELECT level || ' : ' ||
CASE
WHEN level = 'var1' THEN 'v1'
WHEN level = 'var2' THEN 'v2'
END
FROM (VALUES ('var1'), ('var2')) as t (level)
WHERE level NOT IN ('$Lvl1')
where Lvl1 is the name of my first variable.
It doesn’t seem to work like the key-value var’s above.
Is what I am trying possible in grafana? (I essentially need to just translate between user-language and database column names, while using linked variables so that the user can’t choose the same column twice)
But, that was for one key-value variable. Now I want to link key-value variables to get the following functionality:
Example:
3 keys: cat1, cat2, cat3. They will be referenced in each key value variable.
In 1st variable, user selects cat1.
Then, they should only be allowed to select from cat2, cat3 for the second key-value variable. This is why I want to link them.
Think it’s possible?
It’s very straightforward to link ‘normal’ variables, but key-value variables seem to not be compatible with the “Query” variable type, as I tried in the query above.
(1) I want to enable the User to specify an ordering of values. To achieve this, rather than listing out all possible orderings, I want the User to be able to select what goes first, second, and so on. The options for second place should not include what was selected for the first place; the options for each place should not include any values chosen in a place before that.
(2) Here’s the catch: these values must be mapped (translated) from user-friendly language to technical identifiers.
Ok, end of plain English part.
Here’s a little techno
Chained variables lend themselves to solving the “remaining options” problem in (1), and key-value variables lend themselves to the translation task of (2). However, it seems these tools cannot be combined. It seems that key-value variables cannot be used with Query variables.
My specifics/the cool visualization I’m building:
I’m using a PostgreSQL datasource. The translation of (2) is to reference column names in a query for a visualization. It’s a sunburst diagram from ECharts. I want to let the user select the order of the layers in the chart. This makes sense in my case because there are always overlapping subgroups in the data, which means there is no natural choice for the order of the layers. In the linked ECharts example, there is a natural ordering. To imagine my case, imagine if “Brown Spice” was also represented in the “Fruity” group in the example. If someone wanted to see the whole Brown Spice group aggregated together, then they could select that category to be the first (innermost) layer of the chart.
this really sounds like something you could/should do in the database, and then use grafana to visualize it, not something you would do in grafana. let the database do the logic and let grafana do the visualization…
check out parent child relationships in postgres or hierarchical data:
Level 1 corresponds to the innermost circle. Level 2 the next level of the circle. Notice that “School type” is not an option for Level 2 because it has been selected in Level 1:
The values you see in the innermost circle - “Sekundar” and “Grundschule” - are values in a column referenced by “School type” (but the column is not called exactly that. This is where I wanted to use key-value var’s to map user-friendly language to column names)
Notice the circled 1’s in the next level (Level 2) of the chart (corresponding to “Cohort” chosen in the “Level 2” variable). These 1’s are really part of the same cohort, the cohort is just present in both school types.
If a user wants to see the cohorts brought together, they could select “Cohort” as level 1:
Notice that Cohort is no longer an option for Level 2.
Now you can see the entire cohort “1” and how it is distributed among the school types.
For this demo I manually changed the variables and the chart. If it were possible to link (chain) key-value variables, this would be no problem.
Also there are more groupings (other than school type, cohort etc.) so I want to make a flexible approach (also so that i can use this powerful visualization schema for other complicated sets of data).
The javascript I wrote for the chart is ready for this task. It’s really just the interaction with the user that is left. I need (1) a way to specify the order of the layers (which refer to DB col’s), and (2) the order must be translated from user-friendly language to db col names.
