Knowledge BaseMetric InsightsDatabase User EngagementQuery Elements with specific Filter Values

Query Elements with specific Filter Values

USE CASE:

  • The provided query will retrieve a list of External Reports that are filtered to include ONLY specific values.
SELECT it.element_id, it.name AS filter_name, it.value
FROM (
    SELECT de.element_id, ef.name, efv.value
    FROM dashboard_element de
        INNER JOIN plugin_connection_profile pcp ON pcp.plugin_connection_profile_id = de.plugin_connection_profile_id
        INNER JOIN external_report_reference err ON err.plugin_connection_profile_id = pcp.plugin_connection_profile_id AND err.external_report_reference_id = de.external_report_external_id
        INNER JOIN external_filter ef ON ef.external_report_reference_id = err.external_report_reference_id
        INNER JOIN external_filter_usage efu ON efu.external_filter_id = ef.external_filter_id AND efu.item_type = 'element' AND efu.item_id = de.element_id
        INNER JOIN external_filter_value efv ON efv.external_filter_id = ef.external_filter_id
    WHERE de.type = 'external report'
        AND de.data_fetch_method = 'plugin'
        AND de.external_report_auto_update_image_ind = 'Y'
        AND ef.value_source = 'manual'
        AND efu.value_set = 'all'

    UNION ALL

    SELECT de.element_id, ef.name, efv.value
    FROM dashboard_element de
        INNER JOIN plugin_connection_profile pcp ON pcp.plugin_connection_profile_id = de.plugin_connection_profile_id
        INNER JOIN external_report_reference err ON err.plugin_connection_profile_id = pcp.plugin_connection_profile_id AND err.external_report_reference_id = de.external_report_external_id
        INNER JOIN external_filter ef ON ef.external_report_reference_id = err.external_report_reference_id
        INNER JOIN external_filter_usage efu ON efu.external_filter_id = ef.external_filter_id AND efu.item_type = 'element' AND efu.item_id = de.element_id
        INNER JOIN external_filter_usage_value efuv ON efuv.external_filter_usage_id = efu.external_filter_usage_id
        INNER JOIN external_filter_value efv ON efv.external_filter_value_id = efuv.external_filter_value_id
    WHERE de.type = 'external report'
        AND de.data_fetch_method = 'plugin'
        AND de.external_report_auto_update_image_ind = 'Y'
        AND ef.value_source = 'manual'
        AND efu.value_set = 'selected'

    UNION ALL

    SELECT de.element_id, ef.name, sv.value_display_name AS value
    FROM dashboard_element de
        INNER JOIN plugin_connection_profile pcp ON pcp.plugin_connection_profile_id = de.plugin_connection_profile_id
        INNER JOIN external_report_reference err ON err.plugin_connection_profile_id = pcp.plugin_connection_profile_id AND err.external_report_reference_id = de.external_report_external_id
        INNER JOIN external_filter ef ON ef.external_report_reference_id = err.external_report_reference_id
        INNER JOIN external_filter_usage efu ON efu.external_filter_id = ef.external_filter_id AND efu.item_type = 'element' AND efu.item_id = de.element_id
        INNER JOIN segment_value sv ON sv.segment_id = ef.source_segment_id
    WHERE de.type = 'external report'
        AND de.data_fetch_method = 'plugin'
        AND de.external_report_auto_update_image_ind = 'Y'
        AND ef.value_source = 'segment'
        AND efu.value_set = 'all'

    UNION ALL

    SELECT de.element_id, ef.name, sv.value_display_name AS value
    FROM dashboard_element de
        INNER JOIN plugin_connection_profile pcp ON pcp.plugin_connection_profile_id = de.plugin_connection_profile_id
        INNER JOIN external_report_reference err ON err.plugin_connection_profile_id = pcp.plugin_connection_profile_id AND err.external_report_reference_id = de.external_report_external_id
        INNER JOIN external_filter ef ON ef.external_report_reference_id = err.external_report_reference_id
        INNER JOIN external_filter_usage efu ON efu.external_filter_id = ef.external_filter_id AND efu.item_type = 'element' AND efu.item_id = de.element_id
        INNER JOIN external_filter_usage_value efuv ON efuv.external_filter_usage_id = efu.external_filter_usage_id
        INNER JOIN segment_value sv ON sv.segment_value_id = efuv.segment_value_id
    WHERE de.type = 'external report'
        AND de.data_fetch_method = 'plugin'
        AND de.external_report_auto_update_image_ind = 'Y'
        AND ef.value_source = 'segment'
        AND efu.value_set = 'selected'
) it
WHERE value like '%19%';