This article will provide a worked example on how to extend Summary Reports. For this example I will add a new Notes section to the Case Summary Report.
In order to do this I drew on information contained in two previous articles.
Create the Underlying View
I created a new view for the Notes table.
'Notes' is a secondary entity that can be cound under the customization screens.
Administration -> Customisation -> Notes
I called my new view 'vCaseNotes'.
CREATE VIEW vCaseNotes
AS
SELECT dbo.Notes.Note_Note, dbo.Notes.Note_CreatedBy, dbo.Notes.Note_CreatedDate, dbo.Notes.Note_UpdatedBy, dbo.Notes.Note_UpdatedDate,
dbo.Cases.Case_caseId, dbo.Cases.Case_PrimaryCompanyId, dbo.Cases.Case_PrimaryPersonId, dbo.Cases.Case_AssignedUserId, dbo.Cases.Case_Description, dbo.Cases.Case_SecTerr,
dbo.Cases.Case_UpdatedBy, dbo.Cases.Case_CreatedBy, dbo.Cases.Case_ChannelId
FROM dbo.Notes INNER JOIN
dbo.Cases ON dbo.Notes.Note_ForeignId = dbo.Cases.Case_CaseId
WHERE (dbo.Notes.Note_ForeignTableId = 3)
Notes:
- The view will be used in the context of the Cases table and there must include the primary key case_caseid.
- The view joins to the Cases table which is covered by Security and therefore the view must include the columns case_assigneduserid, case_createdby, case_secterr, case_channelid.
- The clause "Note_ForeignTableId = 3" will mean that only Notes associated with Cases will be found.
- The view was defined as available to reports.
Create the Report
I created a new report based on the view 'vCaseNotes' under the General category. I called the report 'Case Notes'.
Edit the metadata in the database
The next step required me to open my SQL Server Management studio and run the following SQL to update the reports.
update custom_reports set repo_options=257, repo_title='Case Notes', repo_category='Case'
where repo_name = 'Case Notes'.
Notes
- Changing the repo_category to 'Case' will associate the report with the Summary Report and it will no longer appear under the general reports category.
- Providing the repo_title will allow the Report to be accessible in the Summary Report tab with the Case customization screen.
- The value 257 for the repo_options is needed for combination reports.
Refreshing of Metadata
I then needed to refresh the metadata to ensure that the changes made directly to the database were registered by the application.
The new section for the Case summary report will be available under the administration screens.
Administration -> Customisation -> Cases
As well as included in the Summary report.
