The goal of this functionality is to provide the ability to create custom queries to any entity in the application and select any data.

Very often, requests are received to make a selection from several entities at the same time and display data in one table, or to make some kind of summary report that displays data in a given format. All these tasks will help to solve the report designer.

Creating Report

To create new report page, go to Extension - Report Designer and click button Create.

In report form enter report name. If report assign to Entity then it will be displayed on record page and you can use [current_item_id] in mysql queries.

You can configure access to report for users group or single user.

On Filters tab enable available filters for report. If filter enable you can use [filter_by_date] in mysql queries.

Creating HTML Blocks

Once report created we have to add HTML blocks which will be used in report. Click on HTML Blocks link and then click to the button "Add" to add new block.

PHP code

This type of block allows you to embed your PHP code into the report. In the code, you can use database queries and output the results in your own format. See the example below.

i18n_date()

This is internal function that you can use in PHP code to display date in your language (which setup in app).

$output_value = i18n_date('F Y',strtotime('[filter_by_date]'));

Table (MySQL query)

This block type allows you to create your own database query and display the data in a tabular format.

Once Table block created click on it's name to add columns.

Note: in columns you can also use PHP code to add your own calculation in table. Variable $item contain all data that selected in query.

Report Body

Once all HTML blocks created, click on report name to prepare report body. You can use HTML editor or enter pure html code.

Filter settings

On the "Filter Panels" tab, you can enable the necessary filters on the report page. There are filters by date and user, as well as filters by entity and drop-down list.

The included filters can be used in MySql query or PHP code.

Note that the constant returns 0 if no filter value is selected. In this regard, I recommend the following sql example of using filters:

select *, if((field_157=[filter_by_list1] or [filter_by_list1]=0),1,0) as is_filter from app_entity_21 having is_filter=1

if((field_157=[filter_by_list1] or [filter_by_list1]=0),1,0) as is_filter - create a condition

having is_filter=1 - add filter