13.1.1 Report Creation Fundamentals

Understanding the CRM Data Model

Before creating a report, it is essential to have a good understanding of the available data structure. Otherwise, you risk designing a report without knowing whether a specific piece of information is actually stored in the CRM. It is also very important to know the exact field names and the modules in which the information is stored, as these elements are crucial when defining filters, aggregations, and data processing logic.

For example, it is important to know whether a filter is applied to a free-text field or a picklist field, since the methods used to search, group, and aggregate data may differ significantly.

A solid understanding of the data structure enables you to design more accurate reports and helps avoid subsequent revisions caused by missing information or incorrect interpretations of the available data.

Understanding Relationships in the CRM

Understanding the relationships between CRM modules is essential when creating reports, as they determine which information can be retrieved from related modules and how that information can be filtered, grouped, and aggregated.

The relationships between CRM modules typically include:

• 1 (one-to-many)
• N:1 (many-to-one)
• N (many-to-many)

The following examples illustrate these relationship types.

1) – Many-to-One (N:1) Relationship (Multiple Contacts – One Account)

(image)

In this example, multiple Contacts are associated with a single Account.Account. Each contact belongs to only one account, while an account can be linked to multiple contacts.

This relationship allows you to retrieve account information when reporting on contacts, or to analyze contacts in relation to their associated account.

In general, the presence of a lookup (relationship) field in a record's information tabs indicates a many-to-one (N:1) relationship.

For example, in the Contacts module, the Account Name field allows multiple contacts to be associated with the same account. In this case, the relationship is N:1, since many contacts can be linked to a single account.

2) – One-to-Many (1) Relationship (One Account – Multiple Quotes)

table image

The relationships displayed in the Relationships panel, located on the right side of the record information tabs, indicate that the selected module has a one-to-many or many-to-many relationship with another module.

To determine whether the relationship is 1 or N, you need to examine the related module.

If the related module contains a relationship field that points directly back to the original module, the relationship is 1.

Example 3 – Many-to-Many (N) Relationship (One Account – Multiple Documents)

table image

In this case, both modules appear in each other's Relationships panel. This indicates that multiple documents can be associated with the same account and, at the same time, the same document can be linked to multiple accounts.

The relationship between the two modules is therefore N (many-to-many).

Note: If, during the Filter or Select Columns steps of the report wizard, you select a field belonging to a module that has an N:1 (many-to-one) relationship with the report's primary module, the related information will be included in the report without increasing the number of rows in the final result.
This behavior occurs because each record in the primary module is associated with only one record in the related module. As a result, adding fields from that relationship does not create duplicate rows in the report.
The situation is different when you select fields from a module that has a one-to-many (1) or many-to-many (N) relationship with the primary module. In this case, the number of rows in the report may increase because each record in the primary module can be associated with multiple records in the related module.