Resetting parameterized report to default value

I have a report set up with a single parameter. That parameter is used in a where clause which is nested in an if/else statement so that if no parameter is set it will run the query without that filter at all:

  {% if customer != 'all_customers' %}
  AND e.event_data:customer_key = '{{customer}}'
  {% endif %} 

Form:

{% form %}

customer:
  type: select
  default: 'all_customers'
  options:
    values: CUSTOMER
    labels: CUSTOMER

{% endform %}

The report works and when I ran it the first time it displayed results as if the where clause didn't exist (using the default as expected). I changed the parameter from the report view and ran it again and it worked, using the where clause this time as expected. However, now I am unable to reset the parameter to the default, which means I can no longer run the report without that where clause. Am I missing something? How can I return to the default value?

Edit: I should also note that the parameter list is dynamically generated from a separate query (which is where it derives CUSTOMER) but I don't think that has anything to do with the issue.

11replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi Robby ,  What happens when you reset to your default to 

    default: 'all_customers'

    and run the report?  If you go into your Query after running the report and click on View History -> Rendered tab what does it show was sent to the query as the parameter?  

    Reply Like
  • Thanks for the quick response, Donna McGahan ! So, that's the real issue: 'all_customers' doesn't appear in the dropdown so I can't select it!

    Reply Like
  • Robby Zar Could you go to ? -> Contact Us in Mode and send us a link to your report so we can take a look?  Thanks!

    Reply Like
  • I was about to do that but then a co-worker solved this for me! The real issue is that this parameter ('all_customers') isn't returned in the query that dynamically generates the dropdown list. So all we had to do was add a UNION and a simple SELECT to add that parameter to the list!

    UNION
    SELECT 'all_customers' AS customer
    ORDER BY customer

    Voila!

    I re-ran the report and it appeared in the dropdown - and when I ran it, it ran as expected (without that filter in the query). Wahooooo!

    Reply Like
  • Robby Zar Glad you were able to get that working!

    Reply Like
  • Hi, I am trying to use this trick but me parameter is a multiselect and I can't figure out how to write the following condition to make it work:

     {% if customer != 'all_customers' %}
     AND e.event_data:customer_key in ({{customer}})
     {% endif %} 

    The customer is a multiselect and the condition is never verified and I end up with a rendered query that has the line:

      AND e.event_data:customer_key in ('all_customers')
    
    

    How should I write my condition to detect when 'all_customers' is the value of the parameter customer?

    Reply Like
  • Hi Lysiane Charest ,

     

    Thanks for reaching out! Can you please include the form logic that you currently have for the customer parameter? Thanks!

    Reply Like
  • The form that I use looks like:


    {% form %}

    campaign_filter:
      type: multiselect
      default: 'all_customers'
      options:
        values: customer
        labels: customerLabel   

    {% endform %}

     

    There's a SQL query before it (in the same editor) that pulls customer and customerLabel from a table. 

    Reply Like
  • Hi Lysiane Charest ,

    Thanks for following up! So I see in the form logic that the parameter name is campaign_filter. However, the if/else logic references a parameter called 'customer' instead. Can you please try replacing 'customer' with 'campaign_filter' in the if/else logic and let me know if that resolves the issue? Thanks!

    Reply Like
  • roberto salcido Hi Roberto, This was a mistake on my side when adapting my query to fit this example. The parameter name that I define does match with the parameter name that I use in my query.  

    The issue is that I do not know of to get the value of this parameter using Liquid's syntax when this parameter is a multiselect. 

    Reply Like
  • Hey Lysiane Charest , you can render the value of your multiselect into your SQL query using the following syntax: {{ parameter_name }}

    This will render your parameter as a comma-separated list of values.

    Reply Like
Like Follow
  • Status Answered
  • 8 days agoLast active
  • 11Replies
  • 388Views
  • 5 Following

Create A New Discussion

Share your thoughts