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

[Nika Hadzhikidi]

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

[Shammy Taz.Begum]

Thank you Nika for sharing the queries

[System]

Thanks for the tip Nika!

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