Reports: Ability to specify filters, run-time filters, labels, etc...

[theBuzzyCoder]

Right now,

A user do not have ability to specify above mentioned fields in Reports for internal reports.

Requirements:
1) Ability to load M:N table fields in Columns. If one entity has N related entities, then in report there should n entries. For m:n table, for M entities if n entities are related, m * n entries should be loaded.
2) Ability to pre-defined filter and runtime filters using fields from M:N table
3) Ability to specify which internal class to execute using Enum
4) Ability to specify labels for exported csv or xlsx header row


Use Case:

1) A custom report is required with user defined filters. The custom report is generated based on specific code which loads data from different tables which user is not able to load from UI (Usually an M:N relation tables).
2) A filter should defined by espocrm user and sent to the internal class.


What am I doing right now is:

I have two tables that I need report generated from:

Leads and SomeEntity

Relationship: M Leads has N SomeEntities

In Reports:

Entity Type: SomeEntity
Type: List

Sample Code:

class MailExporter extends ReportBase
{
    protected function getServiceFactory()
    {
        return $this->getContainer()->get("serviceFactory");
    }

    protected function getRecordService($name)
    {
        if ($this->getServiceFactory()->checkExists($name)) {
            $service = $this->getServiceFactory()->create($name);
            $service->setEntityType($name);
        } else {
            $service = $this->getServiceFactory()->create('Record');
            if (method_exists($service, 'setEntityType')) {
                $service->setEntityType($name);
            } else {
                $service->setEntityName($name);
            }
        }

        return $service;
    }

    public function run($where = null, array $params = null)
    {
        $data = new \stdClass;

        // Don't put leads.name as it is a link field to Person type link
        $data->columns = array(
            "someEntityName", "leads", "leads.firstName", "leads.lastName", "leads.referenceId", "leads.numberOfTimesExported",
            "someEntityFieldname"
        );
        foreach ($data->columns as $column) {
            $data->columnsData[$column] = array(
                "align" => "left",
                "link" => false,
                "notSortable" => false,
                "width" => null
            );
        }
        $data->columnsData["name"]["link"] = true;

        $response = $this->getRecordService("Report")->getListReport("SomeEntity", $data);
        foreach ($response['collection'] as $item) {
            $item->set('leads_referenceId', $item->get("leads_numberOfTimesExported") + 1 . $item->get("leads_referenceId"));
        }
        return $response;
    }
} 

Note: You can use Opportunity and Contact relationship to emulate the use case.

Get report from contact and opportunity table with Entity Type as Contact

[theBuzzyCoder]

tarasm tanya Any updates on this.