Important

You are browsing the documentation for version 4.1 of OroCommerce, OroCRM and OroPlatform, which is no longer maintained. Read version 5.1 (the latest LTS version) of the Oro documentation to get up-to-date information.

See our Release Process documentation for more information on the currently supported and upcoming releases.

Reports & Segments

Reports

OroPlatform allows you to create customized reports about the entities in your application. For example, you may want to create a report that displays the achieved accounts by opportunity like this:

../../../_images/report.png

See also

You can also configure reports via the web UI.

Configure a Report

Building a new report is as easy as defining a data grid. A data grid is a YAML configuration living in a file called datagrids.yml in the Resources/config/oro directory of your bundle. Take a look at the following example:

 1# src/Acme/DemoBundle/Resources/config/oro/datagrids.yml
 2datagrids:
 3    orocrm_report-opportunities-won_by_period:
 4        pageTitle: orocrm.report.opportunities_won_by_period
 5        source:
 6            type: orm
 7            acl_resource: oro_report_view
 8            query:
 9                select:
10                    - CONCAT(MONTH(o.closeDate), ' / ', YEAR(o.closeDate)) as monthPeriod
11                    - CONCAT(QUARTER(o.closeDate), ' / ', YEAR(o.closeDate)) as quarterPeriod
12                    - YEAR(o.closeDate) as yearPeriod
13                    - SUM(o.closeRevenue) as value
14                    - COUNT(o.id) as cnt
15                from:
16                    - { table: OroCRMSalesBundle:Opportunity, alias: o }
17                join:
18                    inner:
19                        - { join: o.status, alias: s }
20                groupBy: o.closeDate
21                where:
22                    and:
23                        - s.name = 'won'
24                        - o.closeDate IS NOT NULL
25        properties:
26            monthPeriod: ~
27            quarterPeriod: ~
28            yearPeriod: ~
29        totals:
30            total:
31                extends: grand_total
32                per_page: true
33                hide_if_one_page: true
34                columns:
35                    period:
36                        label: orocrm.magento.datagrid.columns.page_total
37            grand_total:
38                columns:
39                    period:
40                        label: orocrm.magento.datagrid.columns.grand_total
41                    cnt:
42                        expr: COUNT( o.id )
43                    value:
44                        expr: SUM( o.closeRevenue )
45                        formatter: currency
46
47        columns:
48            period:    { label: orocrm.report.datagrid.columns.period }
49            cnt:       { label: orocrm.report.datagrid.columns.number_won, frontend_type: integer }
50            value:     { label: orocrm.report.datagrid.columns.total_value, frontend_type: currency }
51        sorters:
52            columns:
53                period:     { data_name: period }
54                cnt:        { data_name: cnt }
55                value:      { data_name: value }
56        filters:
57            columns:
58                period:
59                    type: orocrm_period_filter
60                    data_name: period
61                    options:
62                        populate_default: false
63                        field_options:
64                            choices:
65                                monthPeriod:    Monthly
66                                quarterPeriod:  Quarterly
67                                yearPeriod:     Yearly
68                cnt:
69                    type: number
70                    data_name: cnt
71                    filter_by_having: true
72                value:
73                    type: currency
74                    data_name: value
75                    filter_by_having: true
76                    options:
77                        data_type:    Oro\Bundle\FilterBundle\Form\Type\Filter\NumberFilterType::DATA_DECIMAL
78                closeDate:
79                    type:        date
80                    label:       orocrm.report.datagrid.columns.close_date
81                    data_name:   o.closeDate
82                createdAt:
83                    type:        date
84                    label:       orocrm.report.datagrid.columns.created_date
85                    data_name:   o.createdAt
86            default:
87                period: { value: monthPeriod }
88        options:
89            entityHint: report data
90            export: true

The definition of a data grid consists of the following sections:

pageTitle

The report headline, you can use labels for translations here.

source

The source property describes which data need to be fetched from the database to collect all data needed for the report. As you can see, you are able to use all the features that you already know from the Doctrine query builder. The acl_resource specifies the ACL a user has to fullfil to be able to access the data grid.

See also

You can learn more about other data source types and how to implement your own adapter in the datasource documentation.

properties

totals

Here you configure for which columns of the grid you want to display total values for the currently shown page (total) and for all existing entries (grand_total). You can also specify custom expressions that will be executed to calculate the actual value being shown (e.g. to display the total revenue, all existing values will summed up.

columns

The columns option configures which columns will be visible in the data grid. As you can see, you can either refer to values that are produced by the source (like cnt or value) or to a kind of virtual column (like period) which can be defined through custom filters (see below).

sorters

This option configures which columns can be used to sort entries by the time they are displayed. You can refer to the columns that you defined before.

filters

The filters option allows you to provide the user interface to filter the report to display a subset of all available entries only. In the example above, the period column which was used in other options before lets the user select from a list for which period entries should be shown. The available choices directly refer to the fields that where selected with the source configuration. Additionally, the monthPeriod will be taken by default if the user doesn’t make a choice to the default option:

1default:
2    period: { value: monthPeriod }

The filter_by_having option used for the cnt and value columns is used to filter for entries that exactly have the value entered by the user. For the closeDate and createdAt columns, the user will be presented a date widget which they can use to select an interval that reduces the set of entries being shown.

options

Additional options that describe how the report will be presented. In the example above, reports will be exportable.

See also

This example is taken from ReportBundle which is part of OroPlatform. Refer to it for more examples.

You can also find more information on data grids in the DataGridBundle documentation.

Access the Report

To be able to access the new report, you can add a custom item to the Reports & Segments menu in a configuration file named navigation.yml that is located in the Resources/config directory of your bundle:

 1# src/Acme/DemoBundle/Resources/config/oro/navigation.yml
 2menu_config:
 3    items:
 4        account_opportunity:
 5            label: Accounts by opportunity
 6            route: orocrm_report_index
 7            routeParameters:
 8                reportGroupName: opportunities
 9                reportName:      won_by_period
10
11    tree:
12        application_menu:
13            children:
14                reports_tab:
15                    children:
16                        account_opportunity: ~

The configuration of your new menu items is grouped under the oro_menu_config key. First, under the items key you create a new menu item which will be shown in the backend as Accounts by Opportunity. The report to be shown is selected by using the reportGroupName and reportName options in the routerParameters which refer to the report name as configured in the example above. Of course, you can simply add additional items if you have more custom reports.

Then, under the tree key you add the newly created item to the Reports & Segments tab of the application menu.

Segments

A segment is a representation of some dataset and is based on an entity and a set of filters. It is filtered data of the provided entity type.

There are two types of segments:

  1. Static (is also called On demand)

  2. Dynamic

The difference is that the dynamic segment displays real-time data, and static segment has a set of snapshots. It filters data in the same way as the dynamic one and stores the state in a service table (oro_segment_snapshot). So, even if the data is no longer correspond to the filtering criteria in real-time, it will still exist in the dataset of the static segment.

So, a static segment is a snapshot of the filtered data at some point of time.

Also, both segment types have a table representation of data. It can be configured from the segment management pages.

Frontend Implementation

A frontend part of the segment management is based on condition builder that comes from OroQueryDesignerBundle. See the Condition Builder Component topic for more details. A segmentation filter roots from AbstractFilter of OroFilterBundle and provides the ajax-based autocomplete field, which, in turn, is based on the JQuery.Select2 plugin.

Backend Implementation

Entities

Segment entity is descendant of the AbstractQueryDesigner model that comes from OroQueryDesignerBundle.

Basically, this entity contains an entity name (based on), a json encoded definition and service fields such as created/updated, owner etc. SegmentType is a representation of possible segment types. Default types are loaded by the data fixture migration mechanism. SegmentSnapshot is a service entity. It contains snapshots data for static segments. It also contains a link to the segment that it belongs to, the entityId field that is linked to the entity of the type that the segment is based on, and the date when this link was created.

Query Builders

As described before, static and dynamic segments have different ways of applying a filtering tool. There are two strategies, the DynamicSegmentQueryBuilder and StaticSegmentQueryBuilder correspondent.

Datagrid

For a table representation of the segment, use OroDataGridBundle. A grid configuration comes from the segment definition in Oro\Bundle\SegmentBundle\Grid\ConfigurationProvider.

It retrieves the segment identifier from the grid name and passes the loaded segment entity to SegmentDatagridConfigurationBuilder. The datagrid configuration does not process filtering to encapsulate filtering logic in SegmentFilter. So, for those purposes, two proxy classes, DatagridSourceSegmentProxy and RestrictionSegmentProxy, were created.

DatagridSourceSegmentProxy provides the definition to segment filter only. So, the datagrid configuration builder receives the definition for segment filter.

SegmentQueryConverter uses RestrictionSegmentProxy to decline converting definition of the columns, as the query builder needs only one field in the SELECT statement, which is an entity identifier.

Usage Examples

The query is retrieved using the following code:

1if ($segment->getType()->getName() === SegmentType::TYPE_DYNAMIC) {
2    $query = $this->dynamicSegmentQueryBuilder->build($segment);
3} else {
4    $query = $this->staticSegmentQueryBuilder->build($segment);
5}

A $query variable contains instance of DoctrineORMQuery. Add it to the statement of any doctrine query in the following way:

 1/** @var EntityManger $em */
 2$classMetadata = $em->getClassMetadata($segment->getEntity());
 3$identifiers   = $classMetadata->getIdentifier();
 4
 5// SOME QUERY HERE
 6$qb = $em->createQueryBuilder()->select()
 7    ->from($segment->getEntity());
 8
 9$alias = 'u';
10// only not composite identifiers are supported
11$identifier = sprintf('%s.%s', $alias, reset($identifiers));
12$expr       = $qb->expr()->in($identifier, $query->getDQL());
13
14$qb->where($expr);
15
16$params = $query->getParameters();
17/** @var Parameter $param */
18foreach ($params as $param) {
19    $qb->setParameter($param->getName(), $param->getValue(), $param->getType());
20}