ReportSQL - Pulling Activity record information without limiting results

The Activity subtables limits results to only cases that have at least one Activity record, or timeslips that are linked to an Activity record.

Cases

Both examples are text formula fields on the Case Data table.

Aggregate all the notes on activity records linked to a case, separated by " || ", newest notes first:

(
select string_agg(note, ' || '  order by activity.id asc)
from activity
join matter on matter.id = activity.matter_id
where matter.id = %tableAlias%.id
)

Aggregate all the Activity Item names:

(SELECT string_agg(lai.name, ', ' ORDER BY lai.name)
FROM matter m
LEFT JOIN activity a ON m.id = a.matter_id
LEFT JOIN lookup_activity_item lai ON a.item_id = lai.id
where m.id = %tableAlias%.id
)

Timekeeping

Both are text formula fields from the top level Timekeeping table.

For the Activity Action:

(
SELECT 
lookup_activity_action.name 
FROM activity 
JOIN service ON service.id = activity.service 
JOIN lookup_activity_action ON lookup_activity_action.id = activity.action_id
WHERE activity.service = %tableAlias%.id
)

For the Activity Item:

(
SELECT lookup_activity_item.name 
FROM activity 
JOIN service ON service.id = activity.service 
JOIN lookup_activity_item ON lookup_activity_item.id = activity.item_id
WHERE activity.service = %tableAlias%.id
)