How to apply filter in the report page when using SQL query

  • Resolved adminalbatool

    (@adminalbatool)


    4 years, 7 months ago

    How can I apply a filter to the custom SQL query. The filter has to appear on the reports page. I tried using add field, but then it shows only that field and does not consider the query. The query I am trying to use is the below:

    I would like to apply filter on the post_date.

    SELECT
    posts.id,
    posts.post_date,
    posts.post_status,
    MAX(CASE
    WHEN meta.meta_key = ‘_shipping_first_name’ THEN meta.meta_value
    END) AS shipping_first_name,
    MAX(CASE
    WHEN meta.meta_key = ‘_shipping_last_name’ THEN meta.meta_value
    END) AS shipping_last_name,
    MAX(CASE
    WHEN meta.meta_key = ‘_shipping_address_1’ THEN meta.meta_value
    END) AS shipping_address_1,
    MAX(CASE
    WHEN meta.meta_key = ‘_shipping_address_2’ THEN meta.meta_value
    END) AS shipping_address_2,
    MAX(CASE
    WHEN meta.meta_key = ‘_shipping_city’ THEN meta.meta_value
    END) AS shipping_city,
    MAX(CASE
    WHEN meta.meta_key = ‘_shipping_state’ THEN meta.meta_value
    END) AS shipping_state,
    MAX(CASE
    WHEN meta.meta_key = ‘_order_shipping’ THEN meta.meta_value
    END) AS order_shipping,
    MAX(CASE
    WHEN meta.meta_key = ‘_order_shipping_tax’ THEN meta.meta_value
    END) AS order_shipping_tax,
    MAX(CASE
    WHEN meta.meta_key = ‘_order_tax’ THEN meta.meta_value
    END) AS order_tax,
    MAX(CASE
    WHEN meta.meta_key = ‘_order_total’ THEN meta.meta_value
    END) AS order_total,
    MAX(CASE
    WHEN meta.meta_key = ‘_payment_method_title’ THEN meta.meta_value
    END) AS payment_method_title
    FROM
    wp_posts as posts LEFT OUTER JOIN wp_postmeta as meta ON posts.id = meta.post_id
    WHERE
    posts.post_type = ‘shop_order’
    AND posts.post_date >= ‘2020-04-20’
    group by posts.id,
    posts.post_date,
    posts.post_status

Viewing 2 replies - 1 through 2 (of 2 total)

  • I have run into the same issue. Once a field is added in the ‘Fields (optional)’ section, only that field will display in the report. I found that I need to add every field I want to display in the report in the ‘Fields (optional)’ section whether that field is a filterable field or not. The problem now is that I cannot filter on a field. Checking ‘Yes’ on ‘Filterable (optional)’ in Advanced Field Options does not appear to have any effect. How do you apply a filter to a report? Does it have anything to do with what’s written below, and if so, how does this work?

    Available Variables: %%WHERE%% %%HAVING%% %%ORDERBY%% %%LIMIT%%
    (example: WHERE %%WHERE%% my_field=1)

    Plugin Author Scott Kingsley Clark

    (@sc0ttkclark)

    I recommend writing your SQL query and defining all fields in the “Fields” section for maximum control. Without that, it’s difficult for the plugin to know exactly what kind of data a column is and you don’t get the useful filtering abilities.

    Enabling “Filterable” should show it as it’s own field in the search fields. Do you have any further information about your field definition (SQL and the Field information you entered into the plugin)?

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘How to apply filter in the report page when using SQL query’ is closed to new replies.