Method

getReportData —  Filters and extract meaningful information from a report

Description

This method filters report data by passing filters and variables to a specific report.
Minimum number of arguments for this method is two and maximum number of arguments is four.

Structure of the method:

 getReportData(report id, user id, filters, variable) 

Parameters

report id — the ID of the report we are trying to extract information.

user id— the id of the user, logged in or from workflow

filters — this is an collection of objects that contain the filters. This is a key-value structure containing field alias, filter operator and field value.

variable — a variable object in a key-value format whose keys can be used inside the report enclosed by curly brackets.

Examples

In the examples below, we will show ways how to use getReportData method to extract report records. We create a new report and write a simple query to fetch the latest 20 Leads ordered by the “id” field.

SELECT 
  leads.id AS 'ID', 
  CONCAT(leads.first_name, ' ', leads.last_name) AS 'Lead', 
  leads.status AS 'Status', 
  leads.source AS 'Source', 
  DATE_FORMAT(leads.date_added, '%M %d, %Y') AS 'Date Added' 
FROM leads 
ORDER BY leads.id 
DESC 
LIMIT 20

The result set will be a five column table showing 20 Leads. Click on the small arrow in the top-right side.

Write the filters that you want to have for the report. We added three filters:

[   
    {
        "alias": "date_added",
        "label": "Date Added",
        "type": "datetime"
    },
    {
        "alias": "status",
        "label": "Status",
        "type": "select",
        "properties": 
        { 
            "options": 
            {
                "New": "New", 
                "Nurture": "Nurture", 
                "Pending": "Pending",
                "Qualify": "Qualify",
                "Trash": "Trash",
                "Watch": "Watch",
                "Closed": "Closed"
            }
        }
    },
    {
        "alias": "source",
        "label": "Source",
        "type": "select",
        "properties": 
        { 
            "options": 
            {
                "None": "None", 
                "Referral": "Referral",
                "Random": "Random",
                "Meeting": "Meeting",
                "Website": "Website"
            }
        }
    }
]

To filter on the Reports page, only Leads with Status equal to Qualify from the current results, click on Add Filter -> Status filter, choose the operator “equal” to “Qualify”.

To use filters to filter this report outside the Reports page we use the getReportdata method:

{% set report = getReportData(9, null, [{"alias":"status","operator":"eq","value":"Closed"}]) %} 

/*
** The first argument: The report id
** The second argument: The user id, we leave it null 
** The third argument: The filters 
*/ 

{{dump(report)}} // Output Leads with status "Closed"

After getting the desired results we can use scripting to manipulate and model our data.

Example with variables

To use variables inside a report we make use of the fourth argument of getReportData method. This is a specific way to use this method and very handful by using it inside workflows where we need to pass parameters.

When using variables, on the Report page we don’t see any result of the Report. Note this is only useful when reports are being used outside of the Reports page.

One example of the query:

SELECT 
  leads.id AS 'ID', 
  CONCAT(leads.first_name, ' ', leads.last_name) AS 'Lead', 
  leads.status AS 'Status', 
  leads.source AS 'Source', 
  DATE_FORMAT(leads.date_added, '%M %d, %Y') AS 'Date Added' 
FROM leads 
WHERE leads.source= '{{desired_source}}' AND leads.status = '{{desired_status}}'
ORDER BY leads.id 
DESC 
LIMIT 20 

{% set report = getReportData(9, null,null, {desired_source : source, desired_status : status}) %} 
{{dump(report)}} 

/* 
** This method can be used in Workflows and the value of the parameters 
** desired_source and desired_status will be passed to the Report query. 
*/

Report’s default variables

Another very important usage of reports is when we display their results on the Dashboards via different widgets.

In order to write query reports that are inclusive for the CRM users and can be used by all of them, we have some default variables that are specific for each logged in user.

Variables are user_id, group_id, role_id and timezone. The variables are specific for each user on Flexie CRM.

An example report to use those dynamic variables:

SELECT 
   COUNT(*) AS Total Leads 
FROM 
   leads 
WHERE 
   leads.owner_id = {user_id}

// Based on the logged in user, it will count only user's total leads

Override default variables

The second argument of the method getReportData is the user id. This second argument can override the default variable user_id described in the above section.

This means that when getReportData is used from workflows we can manipulate the report’s user_id variable and override by the one we pass to the method.

Example:

{% set report = getReportData(9, 17 ,null, {desired_source : source, desired_status : status}) %} 
{{dump(report)}} 

/*
** replace 9 with your report id
** replace 17 with the current user id you want override the {user_id} variable in report above. 
*/

Using filters as real-time query conditions

Applying filters to a report as described above will filter results from the records that are already extracted from the database. In order to perform a real-time filter then we have to insert them as a condition inside the query itself.

NOTE: When using real-time filters we need to specify the table name in the filter’s alias. An example how to filter with dates in Leads table below:

[   
    {
        "alias": "leads.date_added",
        "label": "Date Added",
        "type": "datetime"
    }
]

Information in the database is always added in UTC time. If we need to retrieve records between a given time, we need to convert the date field manually. An example of this conversion in the filters is shown below:

[   
    {
        "alias": "CONVERT_TZ(leads.date_added, 'UTC', 'Europe/Italy')",
        "label": "Date Added",
        "type": "datetime"
    }
]

An example of using real-time filters :

SELECT 
   COUNT(*) AS Total Leads 
FROM 
   leads 
WHERE 
   leads.owner_id = {user_id}
AND {{filters}}

// the filters will be added to the query in the moment they are applied when you see the report records. 

To stay updated with the latest features, news and how-to articles and videos, please join our group on Facebook, Flexie CRM Academy and subscribe to our YouTube channel Flexie CRM.