Knowledge Base

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 receive a date range input from the front end. When present in the report, the date period selector will be shown on the reports page when the report is selected.

# --- 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

A report placeholder - [non_correlated_sql] - allows 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 n.name 
        FROM resource_node rn, node n 
       WHERE rn.resource = r.ref AND n.ref = rn.node 
         AND n.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

View reports as search results

Reports have the ability to be viewed as search results.

To make reports capable of being viewed as a search result, the query must have the resource ID (ref column) selected and aliased as thumbnail.

IMPORTANT: Please note that having a "report applied to search results" and "being able to view report as search results" are mutually exclusive options. In addition, be aware that if the user has any search filters applicable, it is expected to have a difference between the report and the search result view (of the report).

Concatenating multiple translation strings (v10.4+)

Report values are translated to the current users language, to combine multiple values in a single column using translation strings the following syntax can be used for the column name:

i18n<delimiter>_Column name

Start with i18n and replace <delimiter> with a character or group of characters unlikely to appear in the results. Follow the delimiter with an underscore then the name of the column for the report.

e.g. 'i18n!!_Keywords' or 'i18n##_Locations'

To add a column grouping multiple values ensure that the GROUP_CONCAT function matches the delimiter defined in the column name, e.g.

GROUP_CONCAT(n1.name ORDER BY n1.order_by SEPARATOR '!!') 'i18n!!_Keywords'