Sage CRM 2018 R1: Extending Summary Reports

Hints, Tips and Tricks

Technical Hints Tips and Tricks that cover customization and development using Sage CRM. API usage and coding are covered.

Sage CRM 2018 R1: Extending Summary Reports

  • Comments 2
  • Likes

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'.

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
dbo.Cases ON dbo.Notes.Note_ForeignId = dbo.Cases.Case_CaseId
WHERE (dbo.Notes.Note_ForeignTableId = 3)


  • 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'.


  • 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.

  • Hi Jeff

    Exists any way, tip or trick, to customize the header of the summary report to add custom content, like text, images..., adapting it to the corporate image, far away from the options that provide the "edit Summary Content" option?


  • Tino

    I am waiting to hear back from my developer colleagues.  

    Below is how the metadata for a Summary Report is assembled.  I have used as my example the CompanySummary report.

    1) Firstly all of the sub reports (and the views that they use) that are included in the Summary are identified.

    For the default CompanySummary Report these will be




    2) For each of the sub reports it will then check

    Does the Report have any search fields defined?

    What are the Report Bands (sections) included?

    What fields are included in the report?

    It then loads the captions/translations for those fields.

    It then loads the captions/translations for the report title.

    It then runs the SQL behind the report to fetch the data.

    3) It then loads a set of captions/translations that are by the Reports area for captions and messages.

    4) Then it loads the metadata for the Header Section of the Summary Report.  This is a Screen definition.  Fields can be added or removed from this screen. This is rendered as a two column header.  The 'even numbered' fields are displayed in the righthand column.

    The Custom Content Box is ignored.

    Create Scripts on Fields are ignored.

    The field properties like Position, Height & Width are ignored.

    I hope to hear from the developers if there is a way of working the any underlying XSL or CSS file.