Find reports which share a common input control

Issue Description

You might need to learn which reports share a single input control, and this might be difficult to glean in complex repository structures.

You can get the input control's id from the name and paths listed in jiresource and jiresourcefolder tables. Then lookup the report's ids via the jireportunitinputcontrol table.

Finally, get the report names for the ids from jiresource and jiresourcefolder.

Resolution

To get the reportunitID's (aka reports) using a particular inputcontrol, like one located at /public/Samples/Resources/Input_Controls/YearlyIncome_files, you could do something similar to:

select distinct
 jiresourcefolder.id                       as folderid,
 jiresourcefolder.uri,
 jiresource.name                           as inputcontrolname,
 jiresource.id                             as resourceid,
 jireportunitinputcontrol.input_control_id as inputcontrolid,
 jireportunitinputcontrol.report_unit_id   as reportunitid

from jiresource
join jiresourcefolder         on jiresourcefolder.id = jiresource.childrenfolder
join jireportunitinputcontrol on jireportunitinputcontrol.input_control_id = jiresource.id

where jiresourcefolder.URI = '/public/Samples/Resources/Input_Controls/YearlyIncome_files'

Then the following will return the report names and location/uris of the reportUnitID you found:

select jiresource.name,
 jiresourcefolder.uri

from jiresource
join jiresourcefolder on jiresourcefolder.id=jiresource.parent_folder

where jiresource.id IN
(
 select distinct jireportunitinputcontrol.report_unit_id as id
   from jiresource
   join jiresourcefolder on jiresourcefolder.id = jiresource.childrenfolder
   join jireportunitinputcontrol on jireportunitinputcontrol.input_control_id = jiresource.id
  where jiresourcefolder.URI = '/public/Samples/Resources/Input_Controls/YearlyIncome_files'
)

The above is just a simple postgres-syntax example to offer your DBA ideas. You could also use the above fields and tables to construct a domain which could return information.

Ref. Case 01532178