Job Aid for Reporting on Admin-Side Reports

JOB AID FOR REPORTING ON ADMIN-SIDE REPORTS

Question: How can we get a list of all admin-side reports in the system?

Answer: Create a report on reports. Here is the query. Get the rptdesign file from the RD Job Aid Portal.

-- SELECT ALL ADMIN-SIDE REPORTS

-- exclude reports with Performance in the report name

-- exclude old reports (legacy) in pre-Report Designer format

-- NOTE: reports in Performance category are only included if name contains "competenc"

-- SELECT ALL ADMIN-SIDE REPORTS

-- exclude reports with Performance in the report name

-- exclude old reports (legacy) in pre-Report Designer format

-- NOTE: reports in Performance category are only included if name contains "competenc"

-- no Compensation reports

select DECODE (rpt.published, 'Y', 'Yes', 'No') as current_publish_status

, null as PUBLISH_YES_NO

, NVL(label.label_value1, label.label_value_base) as report_title

, rpt.report_desc as current_description

, null as NOTES

, rpt.group_id as current_report_group

, rpt.label_id

, rpt.workflow_id as admin_workflow

, rpt.dmn_id as rpt_domain

, rpt.report_name as rpt_import_file

, decode(type, 'S', 'Legacy', 'U', 'Customer', 'B', 'RD_version', 'NA', 'C', 'Custom') as report_type

, rpt.report_id

from PA_REPORT rpt, PA_I18N_ACTIVE_LOCALE_LABEL label

where report_name not like 'Psa%'

and report_name not like '%Legacy%'

and label.label_id=rpt.label_id

and label.locale_id='English'

and category_id not like 'Compens%'

and report_name not like '%Performance%'

and category_id not like 'Miscell%'

and report_name not like '%Legacy%'

and report_name not like '%Compens%'

and report_name not like '%Gap%'

and report_desc is not null

and label.label_value_base not like '%Legacy%'

and label.label_value_base not like '%Rater%'

and report_name not like '%Assessment%'

and (group_id not like '%Risk%'AND group_id not like '%Drop%'and group_id not like '%UserPlan%'

OR group_id is null)

/** and rpt.published in [PublishStatus]*/

/** and rpt.dmn_id in [DomainSearch]*/

-- ADD UNION FOR NEW COMPLIANCE REPORTS

UNION

select DECODE (rpt.published, 'Y', 'Yes', 'No') as current_publish_status

, null as PUBLISH_YES_NO

, nvl(label.label_value1, label.label_value_base) as report_title

, rpt.report_desc as current_description

, null as NOTES

, rpt.group_id as current_report_group

, rpt.label_id

, rpt.workflow_id as admin_workflow

, rpt.dmn_id as rpt_domain

, rpt.report_name as rpt_import_file

, decode(type, 'S', 'Legacy', 'U', 'Customer', 'B', 'RD_version', 'NA', 'C', 'Custom') as report_type

, rpt.report_id

from PA_REPORT rpt, PA_I18N_ACTIVE_LOCALE_LABEL label

where label.label_id=rpt.label_id

and label.locale_id='English'

and rpt.report_name like '%Compli%'

and report_name not like '%Compens%'

/** and rpt.published in [PublishStatus]*/

/** and rpt.dmn_id in [DomainSearch]*/

-- ADD UNION FOR CUSTOM REPORTS

UNION

select DECODE (rpt.published, 'Y', 'Yes', 'No') as current_publish_status

, null as PUBLISH_YES_NO

, nvl(label.label_value1, label.label_value_custom) as report_title

, rpt.report_desc as current_description

, null as NOTES

, rpt.group_id as current_report_group

, rpt.label_id

, rpt.workflow_id as admin_workflow

, rpt.dmn_id as rpt_domain

, rpt.report_name as rpt_import_file

, decode(type, 'S', 'Legacy', 'U', 'Customer', 'B', 'RD_version', 'NA', 'C', 'Custom') as report_type

, rpt.report_id

from PA_REPORT rpt, PA_I18N_ACTIVE_LOCALE_LABEL label

where rpt.type='C'

and label.label_id=rpt.label_id

and label.locale_id='English'

/** and rpt.published in [PublishStatus]*/

/** and rpt.dmn_id in [DomainSearch]*/

/** and [security:PA_STUDENT] */

order by report_title

Prompt for admin running this report (notice NEW report param for PublishStatus (see next screen cap)

Settings for the PublishStatus report parameter: