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
repo_bands
If you examine the data in the custom_reports table the values in the repo_bands field present a potentially confusing list of values
repo_bands
4
5
6
7
21
23
29
31
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:
If you examine the data in the custom_reports table the values in the repo_bands field present a potentially confusing list of values
repo_bands
4
5
6
7
21
23
29
31
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
repo_printoptions
The value in the repo_printoptions field is used with Page Footer/Header bands
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.
repo_options
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:
repo_options
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
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.
