Reports, Saved Searches and Groups

Hints, Tips and Tricks

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

Reports, Saved Searches and Groups

  • Comments 5
  • Likes
I hope in this article to explain the meta definition of the following structures:
  1. Standard Reports
  2. Saved Searches
  3. Groups (Target Lists)
These features/structures in Sage CRM all use the Reports tables:
  • custom_reportbands
  • custom_reportcharts
  • custom_reportfavourites
  • custom_reportfields
  • custom_reportgroups
  • custom_reports
  • custom_reportsearches
Below is the simplified Data Model for the Report Tables

Note: The diagram omits the link to the Key Attribute Categories. This is relevant only for Static Groups (Target Lists). Please see section below.

Understanding a Standard Report

You can track the definition of a standard report in CRM using the following SQL
select * from custom_reports where repo_reportid = 78
select * from custom_reportsearches where rese_reportid = 78
select * from custom_reportfavourites where refa_reportid = 78
select * from custom_reportbands where reba_reportid = 78

select * from custom_reportcharts where rech_reportbandid = 633
select * from custom_reportfields where refi_reportbandid = 633
select * from custom_reportgroups where regr_reportbandid = 633
The above SQL will show you the data that defines the standard demo Sales report 'My Open Opportunities'. This has the repo_reportid of 78. You can establish the Unique ID of any report from the URLs. The value is held in the key41 variable. E.g.


This holds the header information about the report. It has a foreign key link (repo_searchid) to the default search options held in the custom_reportsearches table.
This holds the saved search options for a report.
Provides the link to the Favourite reports category and the Dashboard option.
The custom_reportbands is the detail record or body record for the report. The Charts, fields and groups all link to the report through the report band.
If a report has been defined with a Chart, the settings for the chart will be held in this table.
The fields used in the report band are itemised in this table. Fields are distinguished by the refi_usagetype, "O", "D", "S" - (Order by, Display, Search)
If the report has been defined as having groups then these are held in this table.
Understanding a Saved Search

Typically a Saved search such as 'My People' in the person find screen will only have entries in the tables
  • custom_reports
  • custom_reportsearches
  • custom_reportbands
  • custom_reportfields
Understanding a Dynamic Group

Dynamic Groups like the one shown above are similar to Saved Searches. There are defined using data in the tables
  • custom_reports
  • custom_reportsearches
  • custom_reportbands
  • custom_reportfields
Understanding a Static Group (Target List)

A Static Group uses the same tables as the Saved Searches and Dynamic groups but the membership of the group is provided by the link to the Key Attribute tables. The Key Attributes records the unique identifiers for the records referenced in the group. The link is provides by the field repo_ddcategoryid of the custom_reports table.
  • custom_reports
  • custom_reportsearches
  • custom_reportbands
  • custom_reportfields
select * from custom_reports where repo_ddcategoryid >0
  • OK - I have a question.   Has anyone ever created a new view, wanted it to be available as a group and then not seen it come through ?

  • Hi David, I think you have the same problem with the group like me.

    In earlier version of 6.1, Dublin locked down the ability to use views that had duplicate records returned. In 6.1e, they opened this up again by adding a hidden system parameter that will allow views with duplicate records to appear as a source view. To make this work:

    Run the following in query analyzer:

    update custom_sysparams set parm_value = 'y' where parm_name = 'allowduprecs'

    Then Refresh Metadata

    regards Christian

  • There is a comment here saying that the COM API exposes TargetLists.

    I would like to be able to create my own group from .Net. Is this also available in the .Net API?

    If so can anyone point me to the right namespace, I have had a dig around and can't find it.



  • Graham

    The .NET API does not have the equivalent TargetList or Group classes.  You would have to manipulate the tables yourself.

  • i need help on something similar. In one of the reports ,  one of Saved Search Drop Down Column shows values more than Default and None. How can I get rid of all vales from particular Drop down box apart form Default and None Value .  Repo_reportId= 262