It is recommended to start from an already pre-defined report template, and then use Save as in order to add a new Report (you can use Search to find a report by related modules). However, if there is no report with your necessary module or required relationships, or if you have a custom module, you can create a new report template using the Report Builder.
1. The Report Builder is located under Reporting Tool Settings in the top-right corner of the Analytic Reporting Tool folder list. Reporting Tool Settings link is visible and available only for Admin users.
2. Click to open the tab Report Builder:
First of all, here you set permissions to report builder by selecting Allow public access and saving. If you keep this check-box un-selected, after Save only Admin users will be able to create new reports via Report Builder.
Click Open next to Report Builder in order to Open it. Simple report builder will open first, it has an easy User Interface, but can be limited in case you have some custom relationships. In case Simple Report Builder functionality doesn’t meet your needs, Admin users can open Extended Report Builder by clicking the Extended button.
3. Under Report Folder please choose the Report folder, where the new report will be stored
4. Enter the Report Name and Description
5. The recommended Report Type is Standard; Combined reports will be covered later in this section.
6. Select the main module for your new report. If you don’t need any further modules click Save;
After saving, the status Saved will appear along with the link Open Report. You can open the new report by following this link. Please remember to add the necessary columns and data for your report under Fields tab, as well as to add other needed settings and then Save again.
If non-admin users have granted permissions, they can open the report builder by clicking the link in Analytic Reporting Tool report and folder list:
Adding a second module to the new report:
If you need to add a second related module, stay in the Report Builder (without Save) and click the green “+” next to the selected main module. In our example below, Accounts is the main module. Clicking the “+” in front of Accounts we added a second row – and selected Leads as our related module.
Simple Report Builder has only the option to select module names, while Extended Report Builder will also allow you to identify the relationship, like in the example above.
In case you have more than one module added to your report, the correct choice of the main module is important. This means that all entries from the main module will be displayed in your report, but only matching entries from the related modules will be added.
In our example, all Accounts will be displayed in the report, even those Accounts, which don’t have a related Lead. However, only those Leads that are related to an Account will be shown. Leads without an Account will not be shown.
If you do not want to see all the entries from the main module, you can later on limit those by filters. This way we are more flexible to various reporting needs.
7. When a related module is selected the relationship fields need to be identified. In most cases those are ids, and if matching ids are identified by the system, they will be set automatically. In our case it is the account_id in the Leads module, which is related to the Account module id field. Relationship One to many is identified by default and recommended. Still sometimes module relationships are more complicated and require a many-to-many relationship. Reporting tools often don’t support those. We have tried to be more flexible, and allow a many-to-many relationship selection even via the user interface. Please see the Many-to-many relationship topic for instructions. If you need a report with no more than the two selected modules, click the Save button at the top of the Report Builder. After saving, the status Saved will appear along with the link Open Report. You can open the new report by following this link. Please remember to add the necessary columns and data for your report under Fields tab, as well as to add other needed settings and then Save again.
8. Adding further related modules
When adding more than two related modules, it is very important to identify, which relations to use as often modules are related to each other in more than one way. Continuing our previous example, if we want to add the Contacts module, we can decide to see all contacts which were related to Leads. In this case click the green “+” near Leads and then select the Contacts module. But if we require Contacts that are related to Accounts, we should click the green “+” near Accounts.
Please see the difference below (it can also be seen from the hierarchy layout):
9. Many-to-many relationship
A many-to-many relationship is necessary in cases when both entities can be related to several other module entities. For example, we can add several Accounts to the same Contact; and we can add several Contacts to the same Account.
In this case the Report Type is still Standard. Same as previously we first select the main module. In our example we want to see all Service Contracts, whether or not they have a Trouble Ticket related to them, so Service Contracts is the main module.
Next we add a new row and select Help-desk as the related module and identify the relationship type Many to many instead of One to many.
An additional row is automatically added:
This is due to the fact, that Many-to-many relationships are defined through an additional table. Our Tool automatically detected the correct values. You can follow this example if you have similar cases.
10. Combined Report Template Creation
Combined Report Types are the most complex. The reports have different logic and Report Settings UI. These are quite different from standard reports and don’t have detailed data view – only the summary. Yet, they are sometimes needed and creating them can be a little tricky.
Combined reports are needed, for example, if you would like to see the summary of all Activities related to a User (Calendar events, Contacts, Accounts, Opportunities, Tickets, Quotes, etc.). Calendar events, Contacts, Accounts, Opportunities, Tickets and Quotes do not need to be all connected. The only relation they have is the relation to the User.
In our example, we would like to create a Summary Report by Account to see all Contracts and Leads related to the Account only, but not necessarily related among themselves.
In this case we select Report Type: Combined. We then select the main module Accounts and with the green “+” near Accounts we select all the necessary related modules.
After saving this we will be able to see the count of Contracts & Leads by each Account name. From this example only the Account name will be available from the Accounts module.
However, if we need to filter or group by some additional Account information, for example, if we want to see the summary of activities and Sales by Account Industry, we need to make some additional relations.
In this case we need to Add accounts to each of the related modules: to Contracts and to Leads. And then ensure that the relations are correct. Please see the sample below:
When all is ready, please remember to click Save in order to create your new report, after that click Open report, and add the necessary fields and other settings to get your report ready for use.
