Creating custom reports
New reports can be created in Admin > System > Reports. A ResourceSpace report is simply an SQL query - those with knowledge of SQL should be able to write new reports given an understanding of the ResourceSpace database. You can view and edit existing reports within the system from the same location.
Example: Create report for pending submissions
The following line of SQL would create a report that showed all the resources pending submission.
select * from resource where archive=-2;
Note: Allowing date range selection
The following lines of SQL would allow a report to recieve a date range input from the front end.
# --- date ranges # Make sure date is greater than FROM date where ( d.year>[from-y] or (d.year=[from-y] and d.month>[from-m]) or (d.year=[from-y] and d.month=[from-m] and d.day>=[from-d]) ) # Make sure date is less than TO date and ( d.year<[to-y] or (d.year=[to-y] and d.month<[to-m]) or (d.year=[to-y] and d.month=[to-m] and d.day<=[to-d]) )
An alternative date range SQL can also be:
AND rl.date >= date('[from-y]-[from-m]-[from-d]') AND rl.date <= adddate(date('[to-y]-[to-m]-[to-d]'), 1)
Report applied to search results
From version 9.6+ ResourceSpace will have a new report placeholder - [non_correlated_sql] - which will allow a particular report to be run only on search results.
The placeholder will only add the actual search query. The user writing the report can then use either the IN/ NOT IN clause to filter the set based on the report needs. For example, this is how the default report "Resource download summary (search results)" is written:
SELECT r.ref AS 'Resource ID', ( SELECT `value` FROM resource_data AS rd WHERE rd.resource = r.ref AND rd.resource_type_field = [title_field] LIMIT 1 ) AS 'Title', count(*) AS 'Downloads' FROM resource_log rl JOIN resource r on rl.resource = r.ref WHERE rl.type = 'd' AND rl.date >= date('[from-y]-[from-m]-[from-d]') AND rl.date <= adddate(date('[to-y]-[to-m]-[to-d]'), 1) AND r.ref IN [non_correlated_sql] GROUP BY r.ref ORDER BY 'Downloads' DESC;
These reports will only be runnable from a search result. Go to Advanced search, do a search and view the search results. From the top unified dropdown actions, you can select Run report on these results which will take you to the report page.
Please note that the report page will filter out reports depending how you've reached the page:
- Showing normal reports when coming from Admin > Reports
- Showing only reports supporting search results when coming from a search