When creating a report with related modules in Sugar’s Reports module there is an often overlooked option that can make a huge difference in what records are displayed: Optional Related Modules.
To understand why this is significant, you need to first understand how Sugar relates records in the Reports module. Let’s say you are making a report that shows the Subjects of all the Notes sent to all the Contacts within all of the Accounts in your instance. In the report, you want to show all Contacts and all Accounts, even if there were no Notes sent to the Contact or no Contacts assigned to the Account. For this example, the display columns would probably look like this:
Accounts > Name
Accounts > Contacts > Name
Accounts > Contacts > Notes > Subject
Along with filtering these records, Sugar looks for all of the COMPLETE combinations of these records that are related. In other words, if any of the combinations does not have at least one of these three module’s records, then that combination will not show in the list.
This poses a problem for showing any Account/Contact combinations with no Notes, or any Accounts that have no Contacts. Keep in mind, you wanted ALL Accounts and Contacts to show, even if there were no Notes or Contacts. This is where the “Optional Related Modules” feature becomes useful.
In your report go to the Report Details tab and there you will see the “Optional Related Modules” section. In this case the options be two check box options for:
Accounts > Contacts
Accounts > Contacts > Notes
They parallel the module combinations expressed in the rest of the Report definition. When you check one of the options, you are telling Sugar Reports that a record combination does not have to have a record from a specific module to make a reportable (complete) record combination. In this case, if you checked the “Accounts > Contacts > Notes” option, Accounts without Contacts WILL NOT show but Accounts/Contacts combinations that don’t have any notes will show – and a blank will be shown in the Notes Subject column.
In this example, if Accounts > Contacts > Notes is checked, the Report will show all filtered combinations of Accounts and Contacts including one each for those with no notes. If Accounts > Contacts is checked, the Report will show all Accounts, even if the Account has no Contacts.
Many times, when users say record information is not show up in their report, that is one of the first places to look.