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.
Reports
Once you click in the menu REPORT->Reports manager it will be appear a list with all the reports, and from here we can select a report to edit or delete.
Create Report
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:
General information
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
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
FROM @sales_flat_quote
- : (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
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
Chart of the report. We can choose between those option:
- Linear
- Areas
- Bars
- Multibars
- Horizontal bars
- Cake
- Donut
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
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 columns
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:
Export format
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.
Report Edit
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.
Filters
When you click in the menu FILTERS->Filters manager will show us a list with all the filters, from here we will be able to select a filter to edit it or delete it.
Create Filter
When you click in the menu FILTERS ->Add filter will show us the screen to create a filter and the next values will have to be filled up.
- Parameter: Associated parameter, this parameter will be associated in the SQL sequence, been replaced by its value when a value is selected in the display module. The value has to be a text without spaces.
- Title: Tittle of the filter.
- Type: Type of the filter which can be:
- Date: Date selector. In the display module you will be able to select a date
- Select: SQL sentence. It is used when the values are directly taken from the database. The output result has to be two columns; the first one being the value that will be a sign to the parameter and the second one a description of the value. In the SQL sentence you can also use the tag @ in front of the tables and to be replaced by the prefix of the Magento tables
The list that will be shown in the display module can be of three types:
- Date: Date selector. In the display module you will be able to select a date
- Select list unique value: A dropdown will be shown in which you can only select one value.
- Select list unique value with search: A dropdown will be shown in which you can only select one value and a field appears to search among the possibilities.
- Radio unique value: All the values will be shown with a selector to the left and only one value could be chosen.
- Checkbox multiple values: All the values will be shown and several of them could be selected. The value list will be assigned separated by coma, each value in quotation marks and with parentheses at the beginning and end mostly used with an IN
- Text: Free text field. In the display module a field where we can put the text we want will be shown. The value will be replaced with quotation marks
- Set: List of static values. For the list where we don’t want the values the picked up by the DDBB because we want to set them up manually, we will go one by one through all the values indicating in the table which value we want to replace in the parameter and the description. To add a new value to the table we just have to press tabulation in the last value and a new row will be added to the table. Just like in the filter type “select” we can indicate have we want the values to be shown (Select list unique value, Radio unique value, Checkbox multiple value)
- Evaluated: For more experienced Magento users, it is possible to indicate a PHP expression for the result of the expression to be assigned to the parameter. The filter won’t show up on the display module and it will not possible to change it in that module. For example if we just want a report to my show us a list of products depending on the user that is logged in, we can insert the expression
Mage::getSingleton('admin/session')->getUser()->getUsername()
The value of the expression will be replaced by the parameter, so if the result, we will have to put quotation marks between the parameter in the SQL
Once we have set up all the data of the filters we press the bottom “Save” and a message saying “combo added” will appear, and we will now be able to use that filter in the reports.
Filter Editing
When you click in FILTERSà Filter Manager will Shaw us a list of the filters
To edit a filter, we will just have to click in the filter that we want to edit and a similar window to the add filters are will be shown, but now with the following options:
- Back: Goes back to the filter list.
- Save: Saves the changes.
- Delete: Deletes the filter.
Groups
We can gather u the reports in groups, using the option GROUPàGroup manager, we can create, edit and delte groups, once we enter the section the list of avariable groups will appear.
Create Group
To create a group in the section GROUP->Group manager we press the button “Add Group”, a new line will appear in the group table, there we will fill up the description and then we will push the tick button
.
The group will be able already to be selected in the reports.
Edit Group
To edit a group in the section GROUP->Group manager we press the pencil button 
next to the group that we want to edit, we will only be able to edit the group description, when we finish the change, we press the tick button and the “Save” button, and a message saying “Groups successfully saved” will appear.
Links
When the data of a report is shown on the display module, by default if all the data is of the correct type a link to send an email will be inserted:
It is possible, that link in the example where data from orders and products is shown, when you click on a row, you can see that order or when you push on the product ID, you can see the product.
Because of that, they are two kinds of links:
- Row Links: To from the URL, they can use data from several columns and the link is done when you click over the whole row.
- Column Links: To from the URL, it picks up the value of each cell, this way the link is done over each cell like with the email.
Row link
To add a row link, we go to LINK ->Link manager, where we can see the list with the row links.
We click on the button “Add row link” and a new row will show up on the table, where we fill up:
- Description: Link’s description, it will also appear as the title of the link when you leave the cursor on the row. You can create new parameters for the tittles to look nice.
- URL: Link’s route. If a parameter is needed for the route like the ID of the product or the order, put the value of the order between curly brackets, for example, “adminhtml/catalog_product/edit/id/<PRODUCT_ID>”
To add the link, we click on the save button 
.
To edit a link, we click over the value that we want to edit, the field will enter edit mode, to exit you just have to click anywhere outside and the link will be saved.
In the display module, when a report has a row link associated, if we click on any part of the row, it will take us to the page that we had set up, so in the example if we click on the row with order_ID 44, it will take us to the edit screen of that order.
Column Links
To add a column link, we go to LINKà Link manager, where we can see the list of column links.
We click on the button “Add column link” and a new row will appear on the on the table, where we will fill up:
- Description: Link’s description. It will also appear as the tittle of the link when you leave the cursor on the row.
- URL: Link’s route.
To add the link we click on the save button .
To edit a link, we click on the value that we want to edit, the field will enter edit mode, to exit you just have to click anywhere outsides and the like will be saved.
In the display module, when a report has a column link associated, if we click on the value of the cell, it will take us to the page that we had set up.
Plugin Setup
They are certain parameters that are set up by default, like the Excel formats and certain configurations that we might want to change.
Also if we want to create an intelligent permission system, in which one report con only be seen by a few users, it can be done in the permissions.
Parameters
To set up the default plugin parameters, we have to click on SYSTEMàAdmin, it will show the parameters sorted by groups
- General Parameters
- Prefix Parameter: prefix that identifies a parameter in a SQL.
- Prefix Table: Prefix that identifies the name of a table, the prefix will be replaced by the prefix of the Magento tables.
- Date mask: Format of the dates, both in date filters and in SQL.
- Default rows per page: Number of rows to show when a report are executed.
- Export parameters
- Decimal separator: Decimal and integer numbers separator when the data is exported.
- CSV Column delimiter: Character separator of the columns when the SVN is exported.
- CSV export header: We will give the value “true” to allow the names of the columns to be exported and “false” to get the opposite result.
- CSV text qualifier: Character that is put at beginning and end of a text when is exported in CSV.
- Excel Header format: Format of the header when is exported by Excel,
- Excel Odd Row format: Format of the impair rows.
- Excel Even Row format: Format of the pair rows.
- PDF Logo: Width and height of logo. The logos’s url by default are the email logo, that ir configured in SystemàGeneralàDesignàTransactional EmailsàLogo Image, if you want another image, set the path of logo image.
- PDF Title: Title of PDF, right of logo.
- PDF Description: Description of report, bottom of logo and title.
- PDF Filters: You can select if the filters setted will be show on PDF.
- PDF Table Header: Header of table data.
- PDF Table Data: Data of table data.
- PDF Table Footer: Footer of table data.
- PDF Footer: Page footer. You can use the parameter @@CURRENT_PAGE and @@TOTAL_PAGES.
- Chart Parameters
- Height default: Chart height on display mode.
- Link Parameter
- Target: indicates how the will open when a report has rows links or column links, set up with 4 different options (Black, Self, Parent, Top).
- Database connection: In cases in which the reports are to be run on another database, specify connection parameters. You can test the connection by pressing the button “Test Connection” where a message will show whether it has been connected or in error, the error message.
Permissions
For each Magento’s role you can set up which options, it can visualize, so you can create roles with permissions of visualization for only some pre-defined reports.
By default, all the roles have whole permission.
To restrict the permissions of a role, we will choose that role in the selector “Role” and “Resource Access” we’ll choose “Custom”, it will show us a list of all the reports with the options.
: The role can see the report on display module.
: The role cannot see the report on display module.
: The role can edit the report.
: The role cannot edit the report.
When we choose “Custom” by default the role is set up not to have permissions for any report.
When we have already set up the role with the permissions we want, press the “Save” button to save the changes.
Export and Import
On EXPORT/IMPORT menu we can export reports, its setup or the associated filters and links just like import reports.
Export
To expert a report, we will go to the EXPORT/IMPORT->Export, and the list of every report will be shown.
If we press any of them, a new window will appear to download the file .json with the report setup; the name of the file will be its code.
Import
To import a report that must have been previously exported from the plugin we will go to EXPORT/IMPORT->Import, this window will ask which file we want to import.
We select the file and press the button “Import”, and it will show a list with all the actions that it has done.
If you are importing a filter, a link, or a report that already exists, the filter/link/report will not be imported leaving the configuration of the one that already exists.
Market
From the market, we have access to a report repository where we can install new reports and install updates. When we go we all reports appear grouped by groups.
For each report the market, we can install the new reports, update reports with new versions or delete the report from our system.
New reports
When there is a report on the market that you do not have in your system, it appear in the Actions column the “Install” button, when you pressed, the report will be installed on your system
Update reports
When there is a report with a higher version installed that on your system, It appear in the Actions column the “Update” button, when you pressed, the report will be updated.
Unchanged
For market reports that already are installed, it appear in the Actions column the “Delete” button which when pressed, the report will be deleted from your system.
Notifications
When we enter the market, if there are changes since the last time you enter, we will appear in orange the name of the groups to which the report belongs and on new report title image of “New” appears.
