Nika_Hadzhikidi

Tech Tip - Jaspersoft queries to retrieve amount of PDF, XLS files (report outputs)

Discussion created by Nika_Hadzhikidi Employee on Oct 18, 2017
Latest reply on Oct 18, 2017 by Chris_Hackett

Hello fellow community members

 

Wanted to share with you few queries I worked on with a customer. We wanted to find:

  1. The amount of report outputs in the Users folders
  2. The different reports and file types (and counts)

 

Feel free to use and adapt the following SQL:

 

---Full List of PDFs. Replace 'pdf' by 'xls' or other extensions if needed
select jiresource.name,  jiresourcefolder.uri from jiresource
join jiresourcefolder on jiresourcefolder.id = jiresource.parent_folder
where jiresource.id in (select id from jicontentresource where file_type = 'pdf')

---Count of PDFs in the user folders
select count(*)from jiresource
join jiresourcefolder on jiresourcefolder.id = jiresource.parent_folder
where jiresource.id in (select id from jicontentresource where file_type = 'pdf')

---Full list of files (including views, reports and outputs) in Users folders
select jiresource.name,  jiresourcefolder.uri from jiresource
join jiresourcefolder on jiresourcefolder.id = jiresource.parent_folder
where jiresourcefolder.uri like '%/users/%'

 

 Hope this helps -Nika

Outcomes