x Want to participate? Join the group to interact
Sage CRM
Recommended

Reports, Saved Searches and Groups

2 minute read time.
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.

http://6-02009/crm/eware.dll/Do?SID=133335128835441 &Act=1410 &Mode=1 &CLk=T &Key0=41 &Key4=4 &Key41=78

custom_reports
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.
custom_reportsearches
This holds the saved search options for a report.
custom_reportfavourites
Provides the link to the Favourite reports category and the Dashboard option.
custom_reportbands
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.
custom_reportcharts
If a report has been defined with a Chart, the settings for the chart will be held in this table.
custom_reportfields
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)
custom_reportgroups
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
E.g.
select * from custom_reports where repo_ddcategoryid >0
Anonymous