Conditional DQL statements based on value of input variable

[HeadScratcher]

I have some DQL that presents a table of data of web service calls.  The data can be examined in different ways e.g. by consumer_id or application_name etc.   To achieve this I either have to edit query each time or create multiple tables on a dashboard. 

How can I avoid this?  Can I create an input variable called Choice (holding different DQL text) and then use that to visualise the data from different views? 

// Summarise the output... 
| summarize hit_count = count(),by:{ $Choice, response_out_http_code }

When I used the above  the $Choice resolves to the string "consumer_id" but the DQL is treating it as a string literal and not actually using it in the query. 

What  am I doing wrong? 

[krzysztof_hoja]

variables are always strings and are treated as literals in DQL query. They cannot be used as a field names, commands or function names. The workaround is possible using this trick:

data record(a=1, b="a"),
record(a=1, b="a"),
record(a=2, b="b"),
record(a=2, b="b"),
record(a=2, b="b"),
record(a=3, b="c"),
record(a=3, b="d"),
record(a=3, b="e"),
record(a=3, b="e"),
record(a=4, b="e")
| fieldsAdd split=coalesce(
  if($splitField=="a", a),
  if($splitField=="b", b)
)
| summarize cnt=count(), by: { split }

depending on variable value, proper field value will be kept under added filed of the same name independent from the choice. Now it can be directly used in a query (in by: {}).

Kris

[HeadScratcher]

I thought there must be a way.     That is a pretty neat solution and actually helps tidy up my DQL a little bit too. 

[almog42 · Accepted solution]

Heyy 

now you can use variables not as a string literal by adding the :noquote to the variable name for example:

// for refrence writing the variable value
$Problam_Columns = "event.kind"


fetch dt.davis.problems 
| limit 1
| fields $Problam_Columns:noquote

// the "code" that will be used 
fetch dt.davis.problems 
| limit 1
| fields event.kind
 

also here is the link for the docs for that:

https://docs.dynatrace.com/docs/analyze-explore-automate/dashboards-and-notebooks/dashboards-new/com...