MUX Reporting - SQL query to retrieve filters / saved values for scheduled reports

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Looking for a query to retrieve saved values for scheduled Native MUX Reporting reports in Clarity.

You need to know when a report is run with filters in the New Reporting workspace, is the selected filter values for the report is saved somewhere and can it be retrieved when the report is running

How to check which filters are used with selected filter values when the report output is exported or scheduled report output is sent.

Environment

All Supported Clarity releases with MUX Reporting

Resolution

Feel free to use and adapt the queries below: 

-- Oracle: Native Report Schedules with Filter Values and User Info

SELECT

    jd.JOB_CODE AS report_code,

    rpt.NAME AS report_name,

    job.NAME AS schedule_name,

    job.SCHEDULE_SETTINGS AS export_settings,

    av.ATTRIBUTE_VALUE AS filter_definition,

    JSON_VALUE(av.ATTRIBUTE_VALUE, '$.filter.filterQuery') AS filter_query_summary,

    u_creator.FIRST_NAME || ' ' || u_creator.LAST_NAME AS scheduled_by_name,

    u_creator.USER_NAME AS scheduled_by_login,

    job.CREATED_DATE AS schedule_created,

    job.STATUS_CODE AS schedule_status,

    jr.START_DATE AS last_run_date,

    jr.STATUS_CODE AS last_run_status,

    u_runner.USER_NAME AS ran_by

FROM CMN_SCH_JOB_DEFINITIONS jd

INNER JOIN CMN_LOOKUPS lkp ON lkp.ID = jd.JOB_TYPE

    AND lkp.LOOKUP_TYPE = 'SCH_JOB_TYPE'

    AND lkp.LOOKUP_CODE = 'NATIVE_REPORT'

INNER JOIN CMN_SCH_JOBS job ON job.JOB_DEFINITION_ID = jd.ID

INNER JOIN CMN_SEC_USERS u_creator ON u_creator.ID = job.CREATED_BY

INNER JOIN CMN_BUSINESS_OBJECTS bo ON bo.PK_ID = jd.ID

    AND bo.TABLE_NAME = 'CMN_SCH_JOB_DEFINITIONS'

INNER JOIN CMN_ATTRIBUTE_VALUE_SETS avs ON avs.BUSINESS_OBJECT_ID = bo.ID

    AND avs.BUSINESS_OBJECT_INSTANCE_ID = job.ID

INNER JOIN CMN_ATTRIBUTE_VALUES av ON av.ATTRIBUTE_SET_ID = avs.ID

INNER JOIN CMN_ATTRIBUTES attr ON attr.ID = av.ATTRIBUTE_ID

    AND UPPER(attr.COLUMN_NAME) = 'FILTERDEF'

LEFT JOIN ODF_REPORTS rpt ON jd.JOB_CODE = rpt.CODE

LEFT JOIN CMN_SCH_JOB_RUNS jr ON jr.JOB_ID = job.ID

LEFT JOIN CMN_SEC_USERS u_runner ON u_runner.ID = jr.USER_ID

ORDER BY job.CREATED_DATE DESC;

 

-- PostgreSQL: Native Report Schedules with Filter Values and User Info

SELECT

    jd.JOB_CODE AS report_code,

    rpt.NAME AS report_name,

    job.NAME AS schedule_name,

    job.SCHEDULE_SETTINGS AS export_settings,

    av.ATTRIBUTE_VALUE AS filter_definition,

    av.ATTRIBUTE_VALUE::json->'filter'->>'filterQuery' AS filter_query_summary,

    u_creator.FIRST_NAME || ' ' || u_creator.LAST_NAME AS scheduled_by_name,

    u_creator.USER_NAME AS scheduled_by_login,

    job.CREATED_DATE AS schedule_created,

    job.STATUS_CODE AS schedule_status,

    jr.START_DATE AS last_run_date,

    jr.STATUS_CODE AS last_run_status,

    u_runner.USER_NAME AS ran_by

FROM CMN_SCH_JOB_DEFINITIONS jd

INNER JOIN CMN_LOOKUPS lkp ON lkp.ID = jd.JOB_TYPE

    AND lkp.LOOKUP_TYPE = 'SCH_JOB_TYPE'

    AND lkp.LOOKUP_CODE = 'NATIVE_REPORT'

INNER JOIN CMN_SCH_JOBS job ON job.JOB_DEFINITION_ID = jd.ID

INNER JOIN CMN_SEC_USERS u_creator ON u_creator.ID = job.CREATED_BY

INNER JOIN CMN_BUSINESS_OBJECTS bo ON bo.PK_ID = jd.ID

    AND bo.TABLE_NAME = 'CMN_SCH_JOB_DEFINITIONS'

INNER JOIN CMN_ATTRIBUTE_VALUE_SETS avs ON avs.BUSINESS_OBJECT_ID = bo.ID

    AND avs.BUSINESS_OBJECT_INSTANCE_ID = job.ID

INNER JOIN CMN_ATTRIBUTE_VALUES av ON av.ATTRIBUTE_SET_ID = avs.ID

INNER JOIN CMN_ATTRIBUTES attr ON attr.ID = av.ATTRIBUTE_ID

    AND UPPER(attr.COLUMN_NAME) = 'FILTERDEF'

LEFT JOIN ODF_REPORTS rpt ON jd.JOB_CODE = rpt.CODE

LEFT JOIN CMN_SCH_JOB_RUNS jr ON jr.JOB_ID = job.ID

LEFT JOIN CMN_SEC_USERS u_runner ON u_runner.ID = jr.USER_ID

ORDER BY job.CREATED_DATE DESC;