Welcome to OroCRM’s video tutorial series. This is Alex with OroCRM and today I’ll be demonstrating how to create a table report.
In OroCRM, you can create reports based on the details of any entity available in the system. Using OroCRM’s reporting engine you can create easy-to-understand and customizable reports. These reports can include number and value of customers, sales pipeline, marketing performance, or any other data you require. OroCRM offers a number of out-of-the-box reports such as ‘Leads by Date’ and ‘Opportunities by Status’ but you can also create your own custom reports.
In my demonstration today I will create a report to calculate total order revenue by customer. First, navigate to ‘Reports and Segments’ → ‘Manage Custom Reports’ and click the ‘Create Report’ button.
The ‘Create Report’ form is now displayed.
- Define the ‘Name’ of a report as it will be displayed within OroCRM.
- The ‘Description’ field is optional, however, it will help you or the others to understand the logic and the purpose of this report in the future.
- Next, select an ‘Entity’ from the drop-down list. Related data will be used to generate the report. Both system and custom entities will be displayed in the alphabetic order. You can also start typing the entity name in the text field to find it faster. In our case it’s ‘Magento Order’
- Choose the ‘Report Type’. We are creating ‘table’ report.
- The ‘Owner’ field defines the user who can manage the report. By default, the Owner is the currently signed in User. This can be changed by using the select menu, or by typing User’s name.
The ‘Designer’ section is used to define the structure of your report.
In our example today we will create a report which includes the a number of different calculated values by customer. Select the field ‘Order #’ from the drop-down menu in the ‘Column’ section.
- ‘Label’ is the name of the field on the grid. This value is added by default, but can be easily modified. Let’s call it ‘Number of Orders’.
- Next we can choose a ‘Function’ to perform with the field values. You will only see the functions available for the field chosen.
None: no function applied. The data will be displayed for all the available entity records.
Count: you will see the number of the field values for all the entity records.
Sum: the field values for all the entity records will be summed up.
Average: will let us see average value of all the entity records
Min and Max: only the smallest and the largest of all the field values will be displayed.
As we want to know the number of orders per customer, we select ‘Count’ function.
Finally, click the ‘Add’ button.
Now, let’s select ‘Total Amount’ field. We may use a label to display this column in the grid as ‘Revenue’.
As we want to know total revenue for each customer, we select ‘Sum’ function which will combine revenue from each order made by this Customer.
- It is possible to define the sorting order if you want the grid to be sorted by the field value.
None – no sorting applied, Ascending and Descending order.
We will apply ‘Descending’ sorting order to see the largest revenue first.
I will repeat the procedure for the remaining fields that we choose:
- Customer’s ‘First name’
- Customer’s ‘Last name’
The position of the column can be easily changed by dragging columns in necessary order. Hover cursor on the ‘Move column’ icon in the Action bar and reorder as needed. You can easily delete column or edit column settings by pressing ‘Delete’ or ‘Edit’ icons.
‘Grouping’ is used for more complex and informative reports. Once you add a field in this section, the report will be grouped by the relevant column.
In our example, we will group the results by Customer’s ‘Last name’ and ‘First Name’
In ‘Filters’ section you can define conditions to select specific records.
For example, let’s apply condition to display only the completed orders received from customers registered within recent three months. Drag the filter to the space on the right hand side. In our case it will be ‘Condition Group’ as we want to combine two conditions. Now, drag the ‘Field Condition’ filter to the condition group and select ‘Customer’ → ‘Created at’ field. Select the date interval of three months using ‘between’ condition.
Now, add another condition, indicating that the order status should be ‘Completed’. The relation between conditions may vary from ‘And’ to “Or’.
Finally, click ‘Save and Close’ button to see your report.
You can see now the number of completed orders for each Customer, registered within recent three months, and total revenue from these orders, displaying the largest first.
You can also display the results in chart. To do so, click ‘Edit’ button in the top right corner of the page, select ‘Chart type’, pick up the categories to be linked to relevant Axis and click ‘Save and Close’ again. The results of your report are now displayed in a Line Chart.
Thanks for watching! For more Tutorial videos please visit our crm training portal.