Clarity PPM

Expand all | Collapse all

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

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

    Posted 10-18-2017 09:44 AM

    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



  • 2.  Re: Tech Tip - Jaspersoft queries to retrieve amount of PDF, XLS files (report outputs)

    Posted 10-18-2017 05:07 PM

    Thank you Nika for sharing the queries



  • 3.  Re: Tech Tip - Jaspersoft queries to retrieve amount of PDF, XLS files (report outputs)