In this area, you can configure the plugin, create reports, filters, groups, links, export and import reports.
Once you enter the unit, you can display the reports list and the menu on the left side.
Once you click in the menu REPORTS->Add report will display a screen to create a report, the screen is grouped by the most important parts of a report:
Information that identifies the reports, which values are:
- Title: Report’s tittle, it will appear on the reports list
- Group: Where the report belong.
- Description: Report’s description, it will show on viewing report and PDF.
SQL sentence of the report, in which you can use the following metatags.
- @ (at): It will be replaced by the prefix of the tables of Magento, for installations whose tables of Magento have prefix, it is good to put a @ in front of the names of the tables, for example
- : (two points): When you use SQL sentences with filters, it is necessary to place two points in front of the name of the filter’s parameter in case of for example “Start Date” filter has this parameter STARTDATE configured, in the SQL sentence it can be used this way
WHERE DATE_FORMAT (A.created_at,'%Y-%m-%d') BETWEEN:STARTDATE AND:ENDDATE
A recommendation to the hour of filter by dates is trunc the date, but it will also be filtered by hour and minute, this could cause that does not take the data of the day :ENDDATE.
Filters that you can use in the SQL sentence. To add a filter, we select it on the top list and press “Add Combo” button, and the filter will appear on the list below. To delete a filter we select it from the list below and press “Remove Combo”. You can also indicate the order in which display module will appear by clicking arrows.
Chart of the report. We can choose between those option:
- Horizontal bars
Once the type of chart is selected, pressing on its image, it will appear the option to indicate which data column will fill up the data of X axis and Y axis. You only have to put the column number starting by one. On charts that it’s possible to add more axis, such as areas or donut, we can add a new column pressing “Column number”.
Links on the report data. There are two insert options:
- Row link: Each report can only have one row link. We select a row link from the list of possible links. Once it’s selected we configure each link parameter, what column number includes the value. To unselect the created link, we press “unselect” and it’ll show the list of possible links.
- Column links: Each report can have as many links as columns it has. To use a column link, on the left of each link, indicate (in which column it will be used, in which the possible parameters will be filled up with the column value) and so you can use them in the URL or in the link title
Group header when you view a report. For each group, you must enter the number of columns and text, you can add more groups by clicking “Add group”. Ensure that the sum of the columns of groups, corresponding to the total of columns of the report.
When the report show con sql2report viewer, you can view the groups:
Excel or PDF format generated when you export from the display module. You can use the default setting that can be modified in SYSTEM->Admin->Export Parameters.
Scheduled Email Reports
Periodical auto-execution of the report. You can set up a report to execute periodically. So if we want a report to execute every day at 10 a.m and the result data to be sent to our email in Excel format. For that we will have to activate the Cron in the “active” square. And we’ll configure the following sections:
- When: When will the report be executed, will have to indicate the days of the week, the months, the days of the month, the hour and the minutes.
- Filter’s value: If the report has any associated filters here we will indicate of each one of them. If you do click on button “Add to Content”, the parameter insert into email part, the value of de parameter
will be replaced in the content of the email.
- Email: When the report is executed an email with the data will be sent. We will indicate if the data should be sent as a CSV or an Excel and the message body which can be in HTML. When you select and attach file, you can set the “offset” param, that indicate the number of the column where the data start exported.
- File generated: When the report is run, an Excel or CSV file is generated as we indicated in the previous section, the “Path” field will indicate where to build it and Save this file will indicate if we want to store or deletion. The file format is generated name codigoInforme_YYYYMMDDHHmm.
- Log: At the end of the section Cron, we can see the history of past executions executed by cron
Once we have set up all the sections, we will press the button “Add report” and then a “Report added” message will be shown.
Once you click in REPORTS -> Report Manager will show us a list of the reports.
To edit a report, we will click the report we want to modify and a similar window to the one for adding reports will appear, but now with the following options:Back: Goes back to the report list window.
View: Opens the report display module with the selected report.
Delete: Deletes the report, it doesn’t delete the filters, nor the links associated the report.
Duplicate: Loads the add report window filling up the blank, with the save data of the current report.
Export: Generate a file .json so it can be imported or do a backup.
Send to Market: Send a copy of report to market to be imported to market, so you contribute to the community sql2report.
Save: Save the changes.