In this tutorial, we teach you to create a group where we store our reports, create multiple filters, two select (from a SQL statement) and another type set (list of values) and create a sample report using the new filters as well see how SQL errors are displayed, seeing the statement that was executed and failed.

In the example, we will create a report that shows the information of products, where you can choose the type of product and the information to display.

Go to menu Reports -> SQL2Reports admin,

Create a group

In SQL2Report control panel admin, go to the Group Manager option.
We will see a list of all groups, we proceed to create a group called “Group example”.
Click the button “Add Group” and we will be able to put two box for group id and description.
Fill the box with the value GROUP ID “GR_EXAMPLE”, this value must be unique, in the DESCRIPTION field put the group title with value “Group example” and click the green tick to save.
For the changes are saved and the group is added, click the Save button and we will get a message that the changes were made correctly.

Create a filter

We will create three filters, the first to create one type “select” with a sentence that gives us different types of products, the second type filter will create a “set” with a static list that has values return the first filter, so we have a filter with dynamic data and other static data, the third is the type “select” which returns all possible attributes with the products.

Dynamic filter types of products
The SQL statement that will be used in this filter is as follows:

SELECT type_id AS CODE,type_id AS DESCRIPTION 
FROM catalog_product_entity
GROUP BY type_id

If we execute this sentence in a program management databases such as TOAD, we will return the following data.
The SQL statement must return two columns, the first is the value that will replace the parameter and the second the description to display in the dropdown.

In SQL2Report admin panel, go to “Add filter” and fill in the details with these settings:

Code: FILTER_TYPE_PRODUCT (Unique filter code)
Parameter: TYPE_ID (parameter replaced with the value selected in the filter)
Description: Type id (label displayed in the filter)
Type: Select (Select type indicating that collect values from a SQL statement)

Completed all the fields, click the “Save” button and displays a message saying it has saved.

Static filter product type
We might know the list of values that will always return the sentence or want to show a list of static values, for this type create a filter set. We will go to the “Add filter” and fill the data with the following values.
To fill the different values, just press the tab key, click the button “Save” to create the static filter.
We realize that both filters use the same parameter to replace, this is possible and useful to reuse parameters.
Dynamic filtering of possible attributes of a product
The SQL statement that will be used in this filter is as follows:

SELECT attribute_code,
       CASE IFNULL (frontend_label,attribute_code) WHEN '' THEN attribute_code ELSE IFNULL (frontend_label,attribute_code) END AS DESCRIPTION
FROM eav_attribute B
WHERE B.entity_type_id=(SELECT `entity_type_id`
      FROM eav_entity_type
      WHERE entity_type_code = 'catalog_product'
      )
ORDER BY 2

If we execute this sentence in a program management databases such as TOAD, we will return the following data.
We will go to the “Add filter”, fill the data with the following values and click the button “Save”.

You have created the 3 filters, 2 for parameter TYPE_ID (static and dynamic) and one for ATTRIBUTE_CODE.

Create a report

Created the group and filters, we will create a report that to us return the data depending on the type of products we select and returning the information that we select. We will use this SQL statement:

SELECT A.entity_id,
       B.attribute_code,
       B.frontend_label,
       A.value,
       C.type_id,
       c.sku
FROM catalog_product_entity_varchar A
INNER JOIN eav_attribute B
 ON A.attribute_id = B.attribute_id
INNER JOIN catalog_product_entity C
  ON C.entity_id = A.entity_id
WHERE B.entity_type_id=(SELECT `entity_type_id`
      FROM eav_entity_type
      WHERE entity_type_code = 'catalog_product'
      )
  AND B.attribute_code = :ATTRIBUTE_CODE
  AND C.type_id = :TYPE_ID
ORDER BY A.entity_id,B.attribute_id

As we see, makes use of two parameters :ATRIBUTE_CODE and :TYPE_ID, these parameters are filled with the value you select in the filters.

In the SQL2Report Admin going to “Add report” and fill the fields in the “General Information” to this data:

Code: EXAMPLE_PRODUCT_LIST (Unique code)
Description: Example report product list (Description of the report will appear in the list of reports).
Group: Group example (Group where insert the report)

We click on the “SQL” and insert the SQL statement.

In the “Filters” select the filters you want to display and fill SQL parameters. It will show the list of all possible filters.

And select the filters FILTER_TYPE_ID_SETS FILTER_ATRIBUTES_PRODUCTS and click the button “Add Combo”, we will see how these combos are added to the bottom which is the list of report filters.

Click the “Add report” button and it will show a message saying if it has been saved.

To go directly to test your report, you click the button “View” and will lead to the SQL2Report reporting panel.

Select this values:

Type id: simple
Atribute of product: Name

Click “Show Report” button.

Oops! We have an error when running the SQL statement, not panic, to see what sentence was executed press on the “SQL” next to “Show Report”.

It shows the SQL statement without replacing the parameters, to see how replaces click on “Show Values”.

Now we see why the replacement was performed directly without putting quotation marks, we can see that it has replaced.

AND B.attribute_code = :ATRIBUTE_CODE
AND C.type_id = :TYPE_ID

AND B.attribute_code = name
AND C.type_id = simple

when it should be

AND B.attribute_code = 'name'
AND C.type_id = 'simple'

We must put quotation marks before and after the parameters, press the button next “Show Report” and it will open the SQL2Report Admin to edit the report.

We edit the SQL, putting a quotation mark before and after running the SQL parameters as follows:

SELECT A.entity_id,
       B.attribute_code,
       B.frontend_label,
       A.value,
       C.type_id,
       c.sku
FROM catalog_product_entity_varchar A
INNER JOIN eav_attribute B
 ON A.attribute_id = B.attribute_id
INNER JOIN catalog_product_entity C
  ON C.entity_id = A.entity_id
WHERE B.entity_type_id=(SELECT `entity_type_id`
      FROM eav_entity_type
      WHERE entity_type_code = 'catalog_product'
      )
  AND B.attribute_code = ':ATTRIBUTE_CODE'
  AND C.type_id = ':TYPE_ID'
ORDER BY A.entity_id,B.attribute_id

Click the button “Save” to save the changes.

Now if you press the button “View” and choose the same values of the filters, and now that will show the data.

Config a Chart

Yout cant configure various types of charts

Line
Stacked areas
Discrete bar
Multi bar
Horizontal Bar
Pie
Donuts

To do this, select a report on “Report Manager” in the last panel configuration is the “Chart”

In the X cell value you have to put the number of the column containing the values for X, Y value in the cell you have to put the number of the column containing the Y-axis values, in the graphic lines, stacked Areas Multi bars and horizontal lines you can add more columns for the Y axis by clicking the button .

Example

In the “Sales by Hour” report, select the graph lines in the X-axis value 1 (Column hours) and the X axis the value 8 (Column total) and you’ll see a chart like this:

If now we select the graph Areas Apliadas and configure these settings:

X value: 1 (Hours)
Y value: 4 (Subtotal)
Y value: 5 (Tax)
Y value: 6 (Shipping)
Y value: 7 (Discount)
Y value: 8 (Total)

Captura1

You get

SQL2REPORT

plugin for magento

Create a group

Create a filter

Create a report

Config a Chart

Example