custom_reports settings and 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.

custom_reports settings and Summary Reports

  • Comments 6
  • Likes
The information included here maybe useful for those people wanting to add features such as automatic definition of reports, or cloning of reports and dynamic groups and editing of summary reports, etc. 

Please read first a previous article "Reports, Saved Searches and Groups".

There are 3 fields I wish to discuss here that belong to the custom_reports table.
  • repo_bands
  • repo_printoptions
  • repo_options

If you examine the data in the custom_reports table the values in the repo_bands field present a potentially confusing list of values


To understand these values we have to know that reports are made up of different areas or band sets. These areas of the report are the page header, title, detail, summary and page footer. These reflect the choices that may be made as the report is being defined.

The repo_band field is an integer bit field. This idea is found in several places in CRM and is used to store a set of Boolean datatype flags in a compact fashion. The internal code of CRM defines a set of constants, each a power of two, that semantically associates each individual bit with its respective Boolean flag.

These integer constant values are:
  • 1 Title
  • 2 Summary (including Grand Total information)
  • 4 Detail (must be included)
  • 8 Page Footer
  • 16 Page Header
The values can be totalled together to produce reports with different bands (or Areas) within the report turned on. User defined reports may typically have all options turned on, so they would have a value of 31 in the repo_bands field.
  • 4 indicates that just the detail is turned on. This is the minimum setting.
  • 5 indicates that the detail and title are turned on.
  • 6 indicates that the detail and summary are turned on
  • etc

The value in the repo_printoptions field is used with Page Footer/Header bands
  • null: Setting for default Saved Searches
  • 0: Setting for Saved Searches on Custom Entities
  • 1: Setting for Groups both Static and Dynamic (Target Lists)
  • 63: Standard value setting for all other reports.
Note: Because all valid values are always on for user defined reports we can effectively ignore this field so long as the value 63 is populated.


The field repo_options in the custom_reports table is an integer bit field that is responsible for storing a variety of configurable options. The valid constant values are:
  • 1 Not used, but always set
  • 2 Turn on Auto hyperlinking
  • 4 Show original currency values
  • 8 Filter on current running user
  • 16 Filter on current running user's primary channel
  • 32 Filter on current running user's primary territory
  • 64 An advanced find report or group
  • 128 A group
  • 256 A combination report, used from Entity Summary pages
  • 512 Select DISTINCT
  • 1024 A saved filter (used by Find and MyContacts)
Customizing Summary Reports

From the above we can see that the indicator for a summary report is 256. Because 1 has to be used as an option. It means that we can identify all rows used in the summary reports using
select * from custom_reports
where repo_options = 257

We can extend the existing Summary reports with our own custom reports. We can build a report in the usual way but to include it we have to change the settings in the back end.

For example if we have created a new report that we want included in the Company Summary report we must set the correct report category and the report options.

update custom_reports set repo_options=257, repo_category='Company'
where repo_name='CompanySummaryRecentCount'

Valid report categories are:

  • Account (only available in certain integrated Sage CRM systems)
  • Case
  • Company
  • Opportunity
  • Person

Note: Reports are included in Summary Report in Alphabetical Name order.

We may also have to set the default search options for the report which can find in the custom_reportsearches. To examine the default for the existing constituent reports of the summary report we can run the following SQL

SELECT ReSe_ReportSearchID, ReSe_ReportID, ReSe_SearchName, ReSe_QueryFields, ReSe_SQLText
FROM custom_reportsearches
WHERE rese_reportid IN
(SELECT repo_reportid FROM custom_reports WHERE repo_options=257 and repo_category='company')

If the changes are not picked up within the Summary report after a Meta data refresh you may have to restart IIS.

  • This article (and other referenced in it) do not answer the question about putting a summary report button on the project screen.  It states above that there are only 5 categories and project isn't one of them.  Can we add to the list of report categories?

  • If I remember correctly, isn't there a limit of how many reports can be included in each summary report?

  • I have the same remark: how can you add the summary report option on a custom entity?

  • I've used this to edit an existing Summary Report to add in another report - works great. But I don't see how to create a Summary Report for a new custom entity.

  • I'm trying to add Notes to the Case summary report. I believe I have followed the directions on here correctly, but the notes don't show up.  When I turn on SQL profiler,  I can see that CRM is finding the new custom report when you run the Summary report. but it's not displayed.

    Here's the query i can see in the Profiler:

    SELECT Repo_ReportID, ReBa_ViewName, Repo_SearchID, Repo_AdvFindID FROM Custom_Reports WITH (NOLOCK) , Custom_ReportBands  WHERE Repo_ReportID = ReBa_ReportID AND  Repo_Category = N'Case' AND Repo_Options & 256 <> 0 ORDER BY Repo_Name

    That returns 3 records,  the first two being the default Case Progress report and Care Communications,  and the 3rd being my customer notes report.

    I did refresh meta data, as well as recycle the app pool, but still a no go.

    Perhaps it does need a full IIS reset?

  • Joe

    Rather than explain everything here, I created another blog article with a worked example.