Is it possible to have a dynamic SELECT statement based on multiselect parameter?

Hi! 


Hope everyone is doing well! I need your help :) 

I want to create a single report that allows me to input a list of ids and select which columns should be included in the result set, given that these might differ each time I run the query. For example, today I might want the names and emails associated with the ids provided, but tomorrow I might want to date those ids were created.

I don't want to have multiple reports for this, so I was looking at using a multiselect parameter in the SELECT clause, but instead of getting the corresponding values from the selected columns, I'm getting the column names. 

This is what my query currently looks like:

{% form %}
id_list: 
  type: text
  default: 1
  description: input list. 
{% endform %}

{% form %}
columns: 
  type: multiselect
  default: [name, email, date]
  options: 
    labels: [name, email, date]
    values: [name, email, date]
  description: Data to be included. 
{% endform %}

SELECT id, {{ columns }}
FROM table
  WHERE id in ('{{ id_list | replace: ", ", "', '" }}')

Is something like this possible? If so, what am I doing wrong?

Thanks!
Diogo

1reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • So, you should be able to accomplish what you're looking for using something like the following. You might need to mess around with it a bit depending on what kind of default behavior you're looking for, how to handle if nothing is selected, etc.

     

    SELECT name
    {% for entry in columns %}
      , {{ entry | remove: "'" }}
    {% endfor %}
    
    FROM table
    WHERE ...
    
    {% form %}
    columns:
      type: multiselect
      default: [email, date]
      options: [email, date, etc1, etc2, etc3]
      description: Data to be included.
    {% endform %}
    
    Like
Like Follow
  • 11 days agoLast active
  • 1Replies
  • 8Views
  • 2 Following