Sql2report User Manual
Contents
1. cae ee zea gt REPORTS gore sonar Add report co as eae e Back Goes back to the report list window e View Opens the report display module with the selected report e Delete Deletes the report it doesn t delete the filters nor the links associated the report e Duplicate Loads the add report window filling up the blank with the save data of the current report e Export Generate a file json so it can be imported or do a backup e Save Save the changes 3 1 2 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 Dashboard Sales Catalog Customers Promotions Newsletter CMS Reports System Get help for this page Sql2Report admin Manage Filters REPORTS ES pS Add report ET COMBO CODE COMBO PARAMETER COMBO TITLE COMBO TYPE aaa CBC_USER_NAME USERNAME User name evaluated cbo_evaluated EMAIL Email evaluated eee CBO_TEXT TEXT Texto text Nae CB_CUSTOMER_GUEST TYPECUSTOMER Customer or guest set Soup manager CB_ENDDATE ENDDATE End Date date a Enl CB_STARTDATE STARTDATE Start Date date Link manager STORE_SELECT STORE_ID Show Report For select gt SYSTEM Admin pe ecco gt EXPORTIMPORT Export a Ee A RARAS 3 1 2 1 Create Filter When you click in the menu FILTERS gt Add filter will show us the screen to create a filter and the next values will have to be filled up A2. JTags Search Terms J Statistics Sales _ Reviews Customers C Shopping Cart Products 4 Sqi2report 4 Sqi2report Manager my System y Mobile E Global Search Once the new role is configured we create it clicking Save Role Version plugin 0 4 1 Page 32 37 User manual Document Sql2report User Manual docx Creation of the user Now we have to create a user that can only see and set up report for that we will go to System gt Permissions gt Users and we press the button Add new User In the information section we create a user with these parameters e User Name sql2report e First Name User e Last Name Sql2report e Email sql2report test com e Password sql2report Dashboard Sales Catalog Mobile Customers Promotions Newsletter CMS Reports System O Get heip for this page User Information New User lt Back User Info ti n _RESTRoe UserName sarepor First Name User Last Name Sal2report Email sqi2report test com Password seeseeeeee Password Confirmation eeoeeensso This account is Active v In the roles section we assigned the sole that was just created ROLE_SQL2REPORT Dashboard Sales Catalog Mobile Customers Promotions Newsletter CMS Reports System Get help for this page User Information New User Back User Info 2 AS 2 T Searc User Role a Page 1 of 1 pages View 20 w perpage Total 2 records found Reset Fi
3. SQL2report User Manual User manual Document Sql2report User Manual docx Content of the document 1 VES e E E A E E come A E E E E E E T E A 3 2 A A E O E E E 5 3 B Maca 8 ds OPON M N O T oaa a aaa 8 s APO e r eS ll PS E E E E 8 lA PU EE E E E E E E A IE OE EE EO E eects 14 Sla A E E E eee art tiene at 18 A eee ne PP ee eee ee E eee eee 19 A A oe eee ee 22 A a A E N N 24 des QUEDO VICW EN utilidades 28 3 2 1 Load and execute a TepDOlto ooonnnnccccconccnnccccnononncnononcnnnnnonancnnnononancnnnnnnnnnrnnnonnnnnrnnnns 28 A A E ASA 30 4 O A O e AA A A E A E E 32 4 1 Configure a user with only permission to run repottS ococoooocononccononoconononnnnnnnnnnnnonannnoos 32 4 2 Magento CRON ACUIVO iii 34 LO E e E TODO EEE E E A E 35 Version plugin 0 4 1 Page 2 37 User manual Document Sqgl2report User Manual docx 1 Versions Version control Ss 23 10 2013 Original Version 26 10 2013 e BUGFIX Dynamic links to images using Mage getBaseUrl 02 10 2014 e BUGFIX Handle database with prefixes e NEW FEATURE When changes are made a save icon is shown on the menu in case you forget to save NEW FEATURE Permissions to see edit by roles NEW FEATURE Informs with charts 0 2 0 03 04 2014 NEW FEATURE Import and export reports CHANGE In informs and filters the description is now the tittle NUCLEO Code ordinance cleanliness and improves updating system BUGFIX Exportation of reports without filter
4. gt 9 Cron Y Active When p10 Reset Minute Hour DayofMonth Month Day of Week Every Minute Everynminutes Each Selected Minute 04 05 14 15 24 25 a 35 44 45 54 55 Filters value Show Report For admin v Customer or guest Customers amp guest r Email Subject Test SOL2Report To Email nfo domain com Attach fie ExcelXML Y Content Test at 10 00 Version plugin 0 4 1 Page 35 37 User manual Document Sql2report User Manual docx In the example we have set the CUSTOMER_GUEST report to run every day at 10 am will generate an Excel report that will be sent to the e mail info domain com We have also indicated that the generated file is not deleted from the server When we have completed all the data the report and we will keep this message Report saved Cron activated correctly Enable Autorun in magento by AOE Scheduler Thanks to AOE Scheduler plugin we can see the execution and planning begins You have to go to System gt Scheduler gt Schedule Configuration ons Newsletter CMS Reports System output Chck here to go to Cac anac ssssssssseesessseossessseesssssssssssesees O Get help for his page Available tasks Seet Al Unselect Al Select Vetie Urseect Vetie O tems selected Actons Submit Code Cron Expression Model aggregate_sales_report_bestselers_data 00 sulesiobserver aggregateSalesReportBestrelersData aporegate_sa
5. gt LINK Link manager gt SYSTEM NA see ceccecccceccesceccscsccescescscescssessuccusesssscuscesscsescessuscsceh Catalog Customers Promotions Newsletter CMS Reports System O Gethelp for this page Import report Select File to Import Ningun archivo seleccionado We select the file and press the button Import and it will show a list with all the actions that it has done Import report import Settings Select File to Import tutorial json Group Quotes GR_QUOTE imported A The link Link to order edit LK_TR_ORDER_EDIT already exists The link will not be imported import Results O Report Tutorial Report basura imported 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 3 1 6 3 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 Version plugin 0 4 1 Page 26 37 User manual Document Sql2report User Manual docx Dashboard Sales Catalog Customers Promotions Newsletter CMS Reports System Gethelp for this page Sql2Report admin Market gt REPORTS SSeS Add report gt FILTERS Title Version Description Action Filte by Hour 1 Sales Information number of requests number of products subtotal taxes transportation costs Delete EST Sana
6. 879 40 64 90 39 50 0 00 983 80 USD 2013 05 29 03 28 25 2 7 1255 09 90 57 12 52 15 00 1343 18 USD Total of 5 Rows 8 74 18267 53 168 67 165 86 3629 14973 06 If the has assigned a chart it will appear a new section called Chart with a chart drawing Each type of chart is interactive we could show and hide result without setting up the report again The graph displays the data on the current page to display the graph of all data select all in the filter rows per page Down the Filter section we can see a square with the data In the tables header it will show the names of the columns being able to press on them to order them on that field under the name it will show boxes to filter data by that column At the bottom of the table it will appear a totals row where we can see the total of the rows and if the values of the column are numbers it will show the total of the values of the column We can export the data in two different formats e CSV Plan files with values separated by character e Excel XML Excel file To export the data we will choose the exportation type and press the button Export We can navigate through the pages of results by clicking on the arrows also can change the number of rows per page Page 1 of 3 Pages View 20 v perpage Total 55 records found Version plugin 0 4 1 Page 31 37 User manual Document Sql2report User Manual docx 4 HOWTO 4 1 Configure a user with
7. Manager Refresh Statistics 3 1 Sql2report Manager In this unit 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 Dashboard Sales Catalog Customers Promotions Newsletter CMS Reports System Get help for this page Sql2Report admin Manage Report gt REPORTS Reports manager Add report gt FILTERS BN BERR Filters manager Quotes iiiki IO Tutorial Report bl Sales General GROUP by Coupon code Group manager by Hour gt LINK by Country Link manager by Day of Week gt SYSTEM Ordered Inventory Contol re Admin snes Product Bestsellers Permissions Customers Customers amp guest list gt EXPORT IMPORT Alerts Rate equals 0 Export Import Market 3 1 1 Reports Once you click in the menu REPORT gt Reports manager it will be appear a list with all the reports and from here we can select a report to edit or delete Version plugin 0 4 1 Page 8 37 User manual Document Sql2report User Manual docx 3 1 1 1 Create Report Once you click in the menu REPORTS gt Add report will display a screen to create a report the screen is grouped by the most important parts of a report Dashboard Sales Catalog Sql2Report admin v REPORTS Reports manager aaa sar cee FILTERS Filters manager eee a GROUP Group manager LINK Link manager SYSTEM AAA EXPOR
8. Refresh Statistics Sql2Report Report Viewer Sql2Report Manager Reports manager Version plugin 0 4 1 Page 6 37 User manual Document Sql2report User Manual docx We ll select this option Reports gt Sql2Report Manager As we enter for this first time in the manager screen this system will create as tables as it needs and will import the example reports indicating if everything went as expected by a message instalation plugin Sqi2Report correctly Sqi2Report admin Manage Report REPORTS mam Add report gt FILTERS Be RESON Quotes Quotes list Filters manager dacevenesecveveccensoccccosevesceseseccoesoescocescnesccscesecevesoscoseah by Hour Add filter by Coupon code P Sales a O A Sales General gt SYSTEM by Zip Code be a by Day of Week Products Bestsellers Customers Customers amp guestlist Alerts Rate equals 0 We ll have the plugin installed and ready to be used Version plugin 0 4 1 Page 7 37 User manual Document Sql2report User Manual docx 3 Use Manual Sql2report has two main units e Sql2report Manager Plugin creator where you can create reports filters groups links export import and configure the plugin parameters e Sqgl2report Viewer Report Viewer where you can execute a report to see the data its chart and export data to Excel or CSV files To enter in each one of the units in Mangento s Backend you will see both options in Report menu A ql2Report
9. 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 Type set v STORE VIEW List type Select list unique value Show result like checkbox or select box Select list unique value with search Radio unique value Checkbox multiple value VALUE DESCRIPTION ALL Customers amp guest CUSTOMER Customers 69 GUEST Guest o 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 gt getUser gt getUsername Code CBC_LUSER_NAME STORE Ha de ser unico Parameter USERNAME STORE VIEW Title User name STORE VIEW Type evaluated Y STORE VIEW Expression Mage getSingleton admin session gt getUser gt php statement to evaluate The value of the expression will
10. 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 po Zip Code 1 Sales Information number of requests number of products subtotal taxes transportation costs discounts and total grouped by zip code i 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 Version plugin 0 4 1 Page 27 37 User manual Document Sql2report User Manual docx sales General 2 Sales Information number of requests number of products subtotal taxes transportation costs discounts and total grouped by day 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 Delete Sales Information number of requests number of products subtotal taxes transportation costs discounts and total grouped by day ofthe week by Day of Week 1 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
11. of New appears Titie Version Description Action by Hour 1 po Zip Code 1 Sales Information number of requests number of products subtotal taxes transportation costs discounts and total grouped by hour ofthe day Sales Information number of requests number of products subtotal taxes transportation costs discounts and total grouped by zip code fo sar 3 2 Sql2report Viewer On this module you can see and execute reports Dashboard Sales Catalog Customers Promotions Newsletter CMS Reports System Gethelp for this page Choose Report Quotes list Select report SQL 0 Mensaje No report loaded 3 2 1 Load and execute a report Once you enter the module a pull down menu will show the reports list To load a report choose the report that you want to load and press the button GO Once the report is loaded it will show two sections Version plugin 0 4 1 Page 28 37 User manual Document Sql2report User Manual docx Customers Promotions Newsletter CMS Reports System Get help for this page Dashboard Sales Catalog Choose Report Quotes list 7 Quotes list stow Report KA SQL O Show Values 1 A store_id MN A O 0 00 y dh UN Le Lu A js ps ps A coupo 3 FROM Esales flat_q 14 WHERE D DATE _FORMAT A created_at Y m d as Created date DATE_FORMAT A updated_at Y m d as Updated date CONCAT ROUND A base_ subtotal A base_to_global_
12. only permission to run reports Occasionally we need that certain users of our shop have only access to see one file and create reports without having access to Magento s private area In this tutorial l m going to show you configurate a user that only will have permissions inside the sql2report plugin Creation of the role First thing we have to do is to create a role in Magento For that we go to System gt Permissions gt Roles and we press the button Add New Role On the page that is shown we put the name of the role ROLE_SQL2REPORT Dashboard Sales Catalog Mobile Customers Promotions Newsletter CMS Reports System Get help for this page Role Information Add New Role lt Back Role Info E ET Role Name ROLE_SQL2REPORT In the Role Resources section we select only the sections e Reports gt Sql2report to be able to see the reports e Reports gt Sql2report Manager to be able to setup reports Depending on the level of permissions that we want we select one the other or both Dashboard Sales Catalog Mobile Customers Promotions Newsletter CMS Reports System O Get help for this page Role Information Add New Role mn Role Resources Resource Access Custom Resources Ey Jj Sales E External Page Cache E Dashboard E Catalog E Customers E Promotions Gy Newsletter G CMS E Ei Reports e R Ge Sey ew BE eu Ex ER
13. unique value A dropdown will be shown in which you can only select one value Show Report For All Websites y SQ e 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 Version plugin 0 4 1 Page 15 37 User manual Document Sql2report User Manual docx ee Show Report For All Websites admin lage 1 of 3 Pages View default Type Customer id ae Customer a Soman TY a e Radio unique value All the values will be shown with a selector to the y y left and only one value could be chosen Show Report For All Websites admin default C french C german e 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 Show Report For All Websites Jj admin default jfrench german o 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 Texto PY P Version plugin 0 4 1 Page 16 37 User manual Document Sql2report User Manual docx o 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
14. 22 on d errors Carpeta de flcdmpe 0 10000 2522 s fen de includes Carpeta de flcdmpe 0 10000 2522 F A adir archivos a la cola E de ib Carpeta de ficdmpe 0 10000 2522 Abrir A pkginfo Carpeta de flcdmpe 0 10000 2522 de shell Carpeta de flicdmpe 0 10000 2522 Edici n de skin Carpeta de flcdmpe 0 10000 2522 ern e downloader Carpeta de ficdmpe 0 10000 2522 eed di maintenance Carpeta de flcdmpe 0 10000 2522 Borrar 13 archivos y 20 directorios Tama o total 44 583 bytes Renombrar direcci Archivo remoto Tama o Prioridad Estado On this step we ll update Magento s cache so that it can detect the new plugin and we will start up the plugin for the first time 1 Flush magento cache for that we ll go to System gt Cache Management menu in Magento s backend Version plugin 0 4 1 Page 5 37 User manual Document Sql2report User Manual docx Reports Configuration We will press Flush Magento Cache and Flush Cache Storage Flush Magento Cache Flush Cache Storage lt will appear a message telling that the Magento cache storage has been flushed The Magento cache storage has been flushed Cache Storage Management Flush Magento Cache 3 Flush Cache Storage 2 Execute the plugin for the first time Once the plugin is installed in the backend of Magento appear two new submenus in the Reports menu ql2Report Manager
15. L o Prefix Table Prefix that identifies the name of a table the prefix will be replaced by the prefix of the Magento tables o Date mask Format of the dates both in date filters and in SQL e Export parameters o Decimal separator Decimal and integer numbers separator when the data is exported o SVN Column delimiter Character separator of the columns when the SVN is exported o SVN 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 o SVN text qualifier Character that is put at beginning and end of a text when is exported in SVN o Excel Header format Format of the header when is exported by Excel o Excel Odd Row format Format of the impair rows o Excel Even Row format Format of the pair rows e Chart Parameters o Height default Chart height on display mode e Link Parameter o 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 e 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 3 1 5 2 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 on
16. T IMPORT O General information information that identifies the reports which values are General information Customers Add Report General information Code Title Group SQL Filters Chart Links Excel export format Q S Promotions Newsletter CMS Reports Must be unique Quotes v STORE VIEW STORE VIEW STORE VIEW EXON Get help for this page Add Report O y Code Title Group QUOTE_LIST 4 Must be unique Quotes list Quotes v STORE VIEW STORE VIEW STORE VIEW O SQL 1 BELECT A store_id 2 DATE_FORMAT A created_at Y m d as Created date 3 DATE_FORMAT A updated_at Y m d as Updated date 4 5 6 7 A customer_id 8 A customer_email g A customer_firstname 10 A customer_middlename 11 A customer_lastname 12 A coupon_code o Code Unique code of report its value is a text without spaces o Title Report s tittle it will appear on the reports list o Group Where the report belong SQL SQL sentence of the report in which you can use the following metatags 13 FROM sales_flat_quote A 14 WHERE DATE_FORMAT A created_at Y m d BETWEEN STARTDATE AND ENDDATE 15 AND STORE_ID ALL OR A store_id STORE_ID 16 ORDER BY 1 2 3 4 CONCAT ROUND A base_subtotal A base_to_global_rate 2 A global_currency_code as base_subtotal CONCAT ROUND A base_subtotal A base_t
17. TERS Se Prefix Table Prefix that identifies a table in an sql statement required when magento s tables have prefix Filters manager A AA AR EN A A ik aao ona a Add filter Date mask nelly gt GROUP Path Cron file CiwamplwwwisqRreportimedialserpini_sqlreportier Default path where the generated files by cron will be stored Group manager gt LINK Export Parameters A Link manager Decimal separator Decimal separator ASISTEN CSV Column delimiter When you export to CSV separator character data Admin CSV export header true v When you export to CSV identifies if in the first line the column names are exported as J When you to CSV identifies the character that includes a text dat EXPORT MPORT CSV text qualifier you export to er a text data Export Excel header format Aria vlo vi Se sBl gt lA BD 7411 Import Excel Odd Row format Arial v s ViE BBI amp o A BHI Market Excel Even Row format Arial vVii8 vYEZ3EBI1I 6 laA OFERI Chart Parameters Height default 400 Height of the chart in the report Link Parameters Target l Self Opens the linked document in the same fr Y The target sttribute specifies where to open the linked document Database connection If reports have to be run on a different database insert here the connection settings Host Database Name Username Password Test Connection e General Parameters o Prefix Parameter prefix that identifies a parameter in a SQ
18. Totals in reports consults when a report is launched if the data of a column are numbers at the bottom of the report an addition with all the values is shown e BUGFIX The codes of the reports and the filters allow again capital letters e BUGFIX The table s prefix parameter is replaced before the rest of the filter 0 3 1 29 09 2014 parameters e BUGFIX Solution to the problem that didn t allow to create new filters Version plugin 0 4 1 Page 3 37 User manual Document Sql2report User Manual docx NEW FEATURE Possibility of configure a styles format when you are going to export an excel data being allowed to configure the header style pair and impair rows Columns now are auto adjustable with respect to the width of the data 0 4 0 12 05 2015 NEW FEATURE Periodically auto execution of reports sending by email the exportation selected Excel or CSV NEW FEATURE Association of columns with equal values from left to right NEW FEATURE Report market NEW FEATURE Pagination of results NEW FEATURE Filter results NEW FEATURE New filter select with search option 17 08 2015 NEW FEATURE Improvements in cron log sending email per row and new parameters to insert the values of the results NEW FEATURE Running Reports in another database Version plugin 0 4 1 Page 4 37 User manual Document Sql2report User Manual docx 2 Installation After purchasing the plugin you will obtain zip file with a name lik
19. _USER_NAME USERNAME User name evaluated CBO_TEXT TEXT Texto text CB_CUSTOMER_GUEST TYPECUSTOMER Customer or guest set Y Add Combo f Remove Combo COMBO CODE COMBO PARAMETER COMBOTITLE COMBO TYPE ACTIONS STORE_SELECT STORE_ID Show Report For select T J CB_STARTDATE STARTDATE Start Date date 11 CB_ENDDATE ENDDATE End Date date 11 e Chart Chart of the report We can choose between those option o Linear Areas Bars Multibars Horizontal bars Cake Donut O O O 0 0 Chart X value 11 Column number Y value 12 Column number 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 e Links Links on the report data There are two insert options Version plugin 0 4 1 Page 10 37 User manual Document Sql2report User Manual docx Row link 1D DESCRIPTION URL LK_TR_CUSTOMER_EDIT Link to customer edit adminhtml customer edit id FCUSTOMER_ID gt LK_TR_ORDER_EDIT Link to order edit adminhtml sales_order view order_id s lt ORDER_ID gt LK_TR_PRODUCT_EDIT Link to edit lt PRODUCT_NAME gt adminhtml catalog_product editid lt PRODUCT_ID gt Column links Column 1D DESCRIPTION URL LK_TD_CUSTOMER Link to customer admin
20. be replaced by the parameter so if the result we will have to put quotation marks between the parameter in the SQL 18 19 WHERE email USERNAME i Version plugin 0 4 1 Page 17 37 User manual Document Sql2report User Manual docx me v 18 19 WHERE email admin 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 Sql2Report admin Add Filter Back REPORTS Reports manager Addreport Code FILTER_ATRIBUTES_PRODUCTS STORE VIEW 3 1 2 2 Filter Editing When you click in FILTERS gt Filter Manager will Shaw us a list of the filters Dashboard Sales Catalog Customers Promotions Newsletter CMS Reports System Gethelp for this page Sql2Report admin Manage Filters gt REPORTS ma Add report gt FILTERS COMBO CODE COMBO PARAMETER COMBOTITLE COMBO TYPE p CBC_USER_NAME USERNAME User name evaluated Filters manager CBO_TEXT TEXT Texto text Add filter CB_CUSTOMER_GUEST TYPECUSTOMER Customer or guest set mee CB_ENDDATE ENDDATE End Date date Soup managen CB_STARTDATE STARTDATE Start Date date gt LINK STORE_SELECT STORE_ID Show Report For select Link manager gt SYSTEM Admin deanenencccscncescescscuscencecescesnencnsesscssusessessascsncescscssncseh To edit a filter we will just have to click in the filter that we want
21. cute 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 Version plugin 0 4 1 Page 11 37 User manual Document Sql2report User Manual docx Cron Y Active When Minute Hour DayofMonth Month Day of Week Every Minute Every n minutes Each Selected Minute Every 10 minutes Filter s value Show Report For Bn o 2 Customer or guest Customers amp guest r Email Subject Dear C5C C6C To C3C cc BCC Attach file No attach v No rows send O Ative if you don t want the email to be send if report don t return rows Email per row 4 Ative if you want send an email for each result row you can use the parameters CXC where X is the number of the column Content lt 2 gt Dear lt strong gt CSC C6C lt p gt gt lt strong gt our email is lt strong gt C3C lt p gt gt lt strong gt Street gt C7C lt Ip gt lt p gt lt strong gt City lt strong gt C8C lt p gt lt p gt lt strong gt Telephone gt C1IC lt p gt Z File generated Path C wamp wwwisql2report media serpini_sqlreport er Save a No Save O Save Log A Select All Unselect All Actions v Submit Date To Cec BCe Subject Content Status 2015 05 19 18 36 41 linda example com Dear Linda Wood a O 2015 05 19 18 36 39 donaki example com Dear Donak Hertz 3 A o When When w
22. d Continue Edit Product Information D a General Create New Attribute L BIR I General te w gt Name Classic Hardshell Suitcase 29 STORE VIEW eee Description Water resistant hard polycarbonate shell All direction STORE VIEW _ O ORT spinner wheels Zip closure Locking retractable metal 392 Images handle Cross strap and self repairing zipper interior 386 Padded side handle for lateral carry 29 x 20 x 13 oa 400 O ca per TE OI O ra TO ar 42 456 elsea Tee KOOS 409 Elizabeth Knit Top wbk012 421 52 100000052 1 Apr 4 2013 jill example com Houston Travel Wallet abl004 374 NoLlta Cami wbk002 417 3 1 5 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 3 1 5 1 Parameters To set up the default plugin parameters we have to click on SYSTEM gt Admin it will show the parameters sorted by groups Version plugin 0 4 1 Page 22 37 User manual Document Sql2report User Manual docx Sql2Report admin System admin 0 4 1 gt REPORTS Reports manager General Parameters A sept la Prefix Parameter Prefix that identifies a parameter in an sql statement gt FIL
23. dd filter Code STORE VIEW Ha de ser unico Parameter STORE VIEW Title l STORE VIEW Type l v STORE VIEW Version plugin 0 4 1 Page 14 37 User manual Document Sql2report User Manual docx e Code Unique code of the filter it has to be written without spaces e 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 e Title Tittle of the filter e Type Type of the filter which can be o Date Date selector In the display module you will be able to select a date Start Date ES End Date o 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 Type select y STORE VIEW Listtype Select list unique value Show result like checkbox or select box Select list unique value with search Radio unique value Checkbox multiple value 1 SELECT ALL A11 Websites FROM dual ALL 2 UNION 3 ie store_id code FROM core_store 4 The list that will be shown in the display module can be of three types e Select list
24. e Serpini_Sqlreport X X X tgz For being able to unzip that file it is necessary to use an unzip program such us WinRAR from the company Rarlab Install Files The Serpini_Sqlreport X X X tgz Zip file has every plugin file To install it you must copy all the files in the root of the installation of Magento 1 Unzip the file in a folder Nombre Tipo Tama o app Carpeta de archivos del js Carpeta de archivos media Carpeta de archivos skin Carpeta de archivos E package xml Archivo XML 4 KB Y Serpini_Sqlreport 0 1 0 tgz WinRARarchive 104KB 2 Upload the file to the server We will take this step with a ftp client such as FileZilla and we will connect to Magento installation root We ll only upload folders we don t have to upload neither the zip file nor the package xml file Archivo Edici n Ver Transferencia Servidor Marcadores Ayuda Nueva versi n disponible ERA 68 Cl AAA vliaarta Servidor Nombre de usuario Contrase a Puerto gt Conexi n r pida Respuesta 150 Opening ASCII mode data connection for MLSD Respuesta 226 Transfer complete Estado Directorio listado correctamente packagexml B Serpini_Sqlreport 0 1 0 tgz 4 directorios seleccionados Servidor Archivo local Configure Magento Tama o d Tipo de archivo Nombre de archivo Tama o d Tipo de arc Permisos Propietario A Carpeta de arc L app Carpeta de flcdmpe 0 10000 25
25. elect Al Select Vetie Urseiect Vatie O tems selected Atoms LA Submit Code Cron Eapresson Model Status G aggregate_saies_report_bestseliers_data 00 sulesobserver aggregate Sale ReporntBestselersData aggregate_saies_report_coupons_data Reports From the table we select our task in the Code field and see two executions one SUCCESS that is what we have executed to test PENDING works and other programming that is next run Dashboard Sales Catalog Customers Promobons Newesietier CMS Reports System O Get help for this page Scheduled tasks Page 1 of i pages View 20 v perpage Total 2 records found Select Al Unselect Al Select Visible Unseiect Visbie 0 tems selected Code Created Scheduled i Executed Finished Any Y sqkeport_CUSTOMER_GUEST salreport_CUSTOMER_GUEST 07 05 02 07 05 02 salreport_CUSTOMER_GUEST 07 04 42 07 04 42 07 04 42 07 04 42 Version plugin 0 4 1 Page 37 37
26. ere is a bug in the file cron php file does not detect this so it can activate the function or edit the cron php file and add a line when disabled features are detected for it if the original file has this part umask disabledFuncs explode ini_get disable functions isShellDisabled is _array disabledFuncs in_array shell exec disabledFuncs true Add the line in bold umask disabledFuncs explode ini_get disable functions disabledFuncs array_map trim disabledFuncs isShellDisabled is _array disabledFuncs in_array shell exec disabledFuncs true There is a plugin very useful and necessary to check the scheduled tasks and magento know what time they are to run called AOE Scheduler which you can find on this page http www magentocommerce com magento connect aoe scheduler html Version plugin 0 4 1 Page 34 37 User manual Document Sql2report User Manual docx 4 3 Active cron of a report When we want to run a report automatically we follow these steps Config email and cron parameter by sql2report In the section Cron when you are editing a report you have to fill data Filter s Value if the report has filters Email and File and active the tick Active a o ve Code CUSTOMER_GUEST STORE MEW a Must be unique Tiie Customers amp guest ist STOREVIEM Group Customers o STORE VIEW Filters Chart Links Excel export format
27. et 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 3 1 6 Export and Import On EXPORT IMPORT menu we can export reports its setup or the associated filters and links just like import reports Version plugin 0 4 1 Page 24 37 User manual Document Sql2report User Manual docx 3 1 6 1 Export To expert a report we will go to the EXPORT IMPORT Export and the list of every report will be shown Dashboard Sales Catalog Customers Promotions Newsletter CMS Reports System Gethelp for this page Sql2Report admin Manage Report gt REPORTS Reports manager E list O Add report T gt FILTERS PY PERAR Quotes list Filters manager Quotes T A N AAO TOEA Tutorial Report Add filter by Coupon code gt GROUP by Hour Group manager i by Country ales gt LINK by Day of Week Link manager Sales General gt SYSTEM Ordered Inventory Contol be CA Products Bestsellers Permissions Customers Customers amp guest list gt EXPORT IMPORT Alerts Rate equals 0 Export Import Market If we press any of them a new window will appear to download the file son with the report setup the name of the file will be its code Dashboard Sales Catalog Customers Promotions Newsletter CMS Reports System Get help for this page Guardar como Sql2Report admin Export report Ov amp Equipo Disco local C 4 B
28. go r discounts and total grouped by hour of the day Add filter Ho Zip Code 1 Sales Information number of requests number of products subtotal taxes transportation costs gt GROUP discounts and total grouped by zip code Group manager by Country 1 Sales Information number of requests number of products subtotal taxes transportation costs gt LINK discounts and total grouped by country Link manager sales General 2 Sales Information number of requests number of products subtotal taxes transportation costs orar discounts and total grouped by day gt SYSTEM by Day of Week 1 Sales Information number of requests number of products subtotal taxes transportation costs Je ci A discounts and total grouped by day of the week Permissions by Coupon code 1 Sales Information number of requests number of products subtotal taxes transportation costs gt EXPORTIMPORT discounts and total grouped by coupon code Export E Market Title Version Description Action Quotes list 1 Orders that are in carts and have not made the payment reached Products O Title Version Description Action Bestsellers 1 Sales Information Product Name Total sales number of sales grouped by product name Customers A Title Version Description Action Customers amp guest list 1 Information of all clients and guests Alerts A Title Version Description Action Rate equals 0 1 Alert conversion rates between currencies equal to 0 For each report the
29. html customer editiid _ LK_TD_PRODUCT Link to product adminhtml catalog_producteditid LK_TD_PRUEBA Link prueba lt VARIABLE gt admin id lt VARIABLE gt o 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 Row link i LKTRCUSTOMEREDT Description Link to customer edit Url ladminhtml customer editid lt CUSTOMER_ID gt CUSTOMER_ID 1 Column number o 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 e Excel export format Excel format generated when you export from the display module You can use the default setting that can be modified in SYSTEM gt Admin gt Export Parameters Excel export format nm by Default Customized Header Arial vj 10 v Odd row Arial vwii8 v Even row m 0 il a f 4 E E Mi Mi ili io Pr b O A Arial vw 8 v I 1 ili bp E EH e Cron Periodical auto execution of the report You can set up a report to execute periodically So if we want a report to exe
30. ill 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 o Filters value If the report has any associated filters here we will indicate of each one of them lf you do click on button the parameter insert into email part the value of de parameter will be replaced in the content of the email o 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 o 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 Version plugin 0 4 1 Page 12 37 User manual Document Sql2report User Manual docx it and Save this file will indicate if we want to store or deletion The file format is generated name codigolnforme_YYYYMMDDHHmm o Log At the end of the section Cron we can see the history of past executions executed by cron File generated Path Cwamplwwwisql2reportmedialserpini_sqlreportk Save No Save Save Select All Unselect All Actions Date To Cc BCc Subject Content Status O 2015 05 19 18 36 41 linda example com Dear Linda Wood a O O 2015 05 19 18 36 39 donald example com Dear Donald Hertz fr A O 2015 05 19 18 36 38 john example com Dear John Fernandiz ia A Once we have set up all the sections we will
31. les_report_coupons_data ata salesrule observer aggregateSalesReportCouponsData aggregate_sales_report_invoiced_data 00 sales observer aggregateSalesReportinvoicedData ENABLED agoregate_sales_report_crder_data 00 sales observer aggregateSalesReportOrderD ata aggregate_sales_report_refunded_data Ut sales observer aggregateSalesReportRefuncedData ENABLED aggregate_sales_report_shipment_data per sales observer agogregateSalesReportShipmentData ENABLED aggregate _sales_report_tax data 00 lavobserver aggregateSalesReportT axData acescheduler_heartbeat tallas a0e_scheduler heartbeatT ask run ENABLE O NAILED ENABLED ENASLEO ENABLED ENAMIO NAILED ENASLED ENASLED ENASLEO ENABLED f stemap_generate sqireport_CUSTOMER_GU Select the task select Run now on Actions and click Submit the report will run and send a mail to the address you have configured and check that the execution is correct Version plugin 0 4 1 Page 36 37 User manual Document Sql2report User Manual docx Test SQL2Report Recibidos x Serpini Magento 15 56 hace 0 minutos Ah iv para mi v Test at 10 00 X customer_guest y Now we schedule executions for it in the same window we select our report in the example sqlreport CUSTOMER_GUEST in actions select Schedule Now and click on Submit we see the following message Scheduled sqireport_CUSTOMER_GUEST Available tasks Select Al Uns
32. lter REST Role Assigned Role Name Any v O Administrators ROLE_SQL2REPORT Once the new user is configured we created it clicking Save User Acceso con el nuevo usuario If we Access now with the new user credentials we will only have Access to the following sections on the menu reports section and the sub sections Sql2report y Sqi2report Manager Version plugin 0 4 1 Page 33 37 User manual Document Sql2report User Manual docx Reports Get help for this page Sql2Report A A Sql2Report Manager i v Select report SQL 0 Mensaje No report loaded 4 2 Magento CRON active If you are going to use the auto running reports we must activate Magento cron for it depending on the server where we installed Magento will be made one way or another The key to activate the Magento Cron idea is that the cron php file in the root of Magento installation run every 5 minutes To do this we create a scheduled task depending on the server if a Linux create a task that runs this judgment usr bin php q var www vhosts domain com httpdocs magento cron php Obviously we will have to locate in our server the path where the file is If this error appears when you turn on cron in the system log ERR 3 Warning shell exec has been disabled for security reasons in var www vhosts domain com httpdocs magento cron php on line 67 This is because the server hasn t enabled the shell_ exec function but th
33. ly some pre defined reports By default all the roles have whole permission Version plugin 0 4 1 Page 23 37 User manual Document Sql2report User Manual docx Dashboard Sales Catalog Customers Promotions Newsletter CMS Reports System Gethelp for this page Sql2Report admin Permissions admin REPORTS Reports manager Roles Resources o Add report Role Administrators v gt FILTERS Resource Access All v Filters manager Group manager gt LINK Link manager gt SYSTEM Permissions gt EXPORT IMPORT sv eecececceccesuccucccccecsecsesuccecceccussecescesseccecusseccessessessuh 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 Permissions admin Roles Resources A Role Administrators y Resource Access Custom Resources 5 Quotes BOF Quotes list a Of Tutorial Report 33 Sales amp Sales General a 04 by Coupon code BO by Hour SO by Country 304 by Day of Week SQA Ordered Inventory Contol E 5 Products a 04 Bestsellers E sj Customers 204 Customers amp guest list amp Alerts a Of Rate equals 0 e The role can see the report on display module e The role cannot see the report on display module F The role can edit the report F The role cannot edit the report When we choose Custom by default the role is s
34. mer Name Haven Bangor 10000004 o Order Status Pending Email haven yahoo com 10000005 Comma Ey Purchased From Main Website Customer Group VIP Member Transact Madison Island English 1000008 Placed from IP 8 31 244 68 DE Haven Bangor Haven Bangor Massachusetts Ave Massachusetts Ave Cambridge Massachusetts 01964 Cambridge Massachusetts 01964 10000005 United States United States X a D JUU Madison LX2200 hde001 392 Herald Glass Vase hdd000 386 100000056 Mar 28 2013 mark yahoo com Florentine Satchel Handbag abl001 371 Compact mp3 Player hde013 400 100000057 Mar 28 2013 joe amail com Khaki Bowery Chino Pants mpd00338 456 100000058 Mar 28 2013 jack qmail com Slim fit Dobby Oxford Shirt msj005 403 Hana Flat Charcoal aws011 432 Apr 4 2013 joe example com Khaki Bowery Chino Pants mpd00338 456 3 1 4 2 Column Links To add a column link we go to LINK gt Link manager where we can see the list of column links Column link CQ Add column link 1D LK_TD_CUSTOMER LK_TD_ORDER LK_TD_PRODUCT DESCRIPTION Link to customer Link to order edit Link to product URL adminhtml customer editid ACTIONS E adminhtml sales_order view order_id G E adminhtmi catalog_product editid B We click on the button Add column link and a new row will appear on the on the table where we will fill up e ID Unique code of the link it has to be text without spaces e Description Link s description I
35. nd_ total A base_to_global_rate 2 ERG global_ currency_code as base_grand_total A customer_id A customer email A customer_firstname A customer_middlename A customer_lastname n _code A coupo 13 FROM sales _ Flat _quote A m e y AND STR_TO_DATE 3 3 15 m e y 14 oat DATE_FORMAT A created_at a m d BETWEEN STR_TO_DATE 3 3 14 D ALL OR A store_id ES 3 4 15 16 ORDER BY 1 2 Once the values of the parameters are shown pressing the button Show Params we will see the SQL sentence with the parameters Filter Here you can give value the report parameters all the parameters are compulsory Show Report For admin y SL Y Start Date 033 14 El End Date In the Select type filters they will appear two buttons besides the selector SQL Pressing this button the sentence SQL of the filter will appear Version plugin 0 4 1 Page 29 37 User manual Document Sql2report User Manual docx P Pressing this button it will take you to the setup module so that you can edit this filter In the Date type filters it will appear this button beside the text field e El On pressing this button it will appear a calendar so that you can choose one date Once the filter values are filled up to execute the report it is necessary to press the Show Report button Dashboard Sales Catalog Customers Promotions Newsletter CMS Reports System Get help for this
36. o_global_rate A base_subtotal_with_discount A base_to_global_rate 2 A global_currenc CONCAT ROUND A base_grand_total A base_to_global_rate 2 A global_currency_code as base_grand_total o 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 o 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 Version plugin 0 4 1 Page 9 37 User manual Document Sql2report User Manual docx 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 e 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 Filters 0 COMBO CODE COMBO PARAMETER COMBOTITLE COMBO TYPE CBC
37. page Choose Report Quotes list v Quotes list Show Report For admin TAUF Start Date 03 73 14 El End Date 0371815 Besides the Show Report button it appears the button and if you press it you will be taken to the setup module to edit the loaded report 3 2 2 Report Display Once a report is loaded it will show us the execution data Version plugin 0 4 1 Page 30 37 User manual Document Sql2report User Manual docx Dashboard Sales Catalog Customers Promotions Newsletter CMS Reports System Get help for this page Choose Report Sales General v Sales General 4 SQL Y Chart O O Grouped O Stacked Subtotal Tax Shipping Discount Total 14 493 0 12 000 0 10 000 0 8 000 0 6 000 0 4 000 0 2 000 0 _ i ee lll OD 2 000 0 3 604 0 2013 04 04 02 25 53 2013 04 25 19 41 25 2013 05 25 03 42 11 2013 05 28 02 07 47 2013 05 29 03 28 25 Filter A Show Report For All Websites Start Date onn El End Date oenans El Page f1 of 1 Pages View 20 v perpage Total S records found Ey Export to CSV v Period Number of Orders Items Ordered Subtotal Tax Shipping Discount Total Currency To ee To To to To O Si a 2013 04 04 02 25 53 2 10 1186 84 0 00 36 84 0 00 1223 68 USD 2013 04 25 19 41 25 1 52 14493 00 0 00 77 00 3604 00 10966 00 USD 2013 05 25 03 42 11 1 2 453 20 13 20 0 00 10 00 456 40 USD 2013 05 28 02 07 47 2 3
38. 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 lt PRODUCT_ID gt 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 Version plugin 0 4 1 Page 20 37 User manual Document Sql2report User Manual docx Dashboard Sales Catalog Customers Promotions Newsletter CMS Reports Choose Report Tutorial Report Tutorial Report SQL Order id Order Number 41 42 43 44 Link to order edit 47 48 49 50 100000057 1 Magento Admin Panel Dashboard Sales Catalog Customers Promotions System Newsletter hrough this store wall not be honored or fulfilled Global Record Search Logged in as a CMS Reports System Get help for this page n Tuesday March 17 2015 Get help for this page Order View amp Order 100000083 Mar 28 2013 2 01 06 em E Seek ECO EEN EEN ECO MS E Information Credit Memos Order Date Mar 28 2013 2 01 06 PM Custo
39. press the button Add report and then a Report added message will be shown Dashboard Sales Catalog Mobile Customers Promotions Newsletter CMS Reports System Get help for this page A Sql2Report admin Manage Report Example report product list LO Back REPORTS Reports manager General information v Aid SQL O gt FILTERS 1 SELECT A entity_id attribute code 3 B Eronivend Tabel 3 1 1 2 Report Editing Once you click in REPORTS gt Report Manager will show us a list of the reports Dashboard Sales Catalog Customers Promotions Newsletter CMS Reports System Gethelp for this page Sql2Report admin Manage Report gt REPORTS Reports manager Add report gt FILTERS 22 BERERA Filters manager Quotes sa a E T A ER Tutorial Report A Sales General GROUP by Coupon code Group manager by Hour LINK i by Country Link manager by Day of Week gt SYSTEM Ordered Inventory Contol BS i E E Products Bestsellers Permissions Customers Customers amp guest list gt EXPORT IMPORT Alerts Rate equals 0 Export Import Market 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 Version plugin 0 4 1 Page 13 37 User manual Document Sql2report User Manual docx Dashboard Sales Catalog Customers Promotions Newsletter CMS Reports System Get help for this page
40. rate 2 A global_ currency code as base_subtotal CONCAT ROUND A base_ subtotal A base_to_global_ rate a base subtotal_with_discount A base_ to _global_ rate 2 CONCAT ROUND A base_grand_total A base_to_global_rate 2 A global_currency_code as base_grand_total A customer_id A customer_ email A customer firstname A customer_middlename Ak customer_lastname n_ code uote A ORMAT A created_at Y m d BETWEEN STARTDATE AND ENDDATE ID A global_currency_code as base discount 15 AND STORE ID ALL OR A store_ id STORE_ 16 ORDER BY 1 2 3 4 Show Report For l All Websites TUF Start Date El End Date El B Expor to csv v Mensaje SQL Select combo values SQL Here you can see the SQL of the report Pressing the button Show Values you will see the SQL sentence with the replaced parameters if the report has been executed previously If it is the first time the report is loaded the parameters will be replaced for an empty value Show Params 1 paien a q id FORMAT A created_at Y m d as Created date DATE EORMATIA updated_at Y m d as Updated date gt CONCAT ROUND A base_ subtotal A base_to_global_rate 2 A global_currency_code as base_subtotal pe TI base_subtotal A base_to_global_ rate a base_ subtotal_with_discount A base _to global rate 2 A global_currency_code as base discount CONCAT ROUND A base_gra
41. s e NEW FEATURE Import zip 9 2 1 12 04 2014 e BUGFIX Installation Script e NEW FEATURE Type of evaluated filter where you can put a PHP and the result of this evaluation will be replaced by the parameter chosen For example you can use the username registered with the sentence Mage getSingleton admin session gt getUser gt getUsername and use it in the parameter USER_NAME in a SQL sentence e NEW FEATURE Type of visualization with list of filters Filters such as select or set where shown in the view reports as pull down menu and you could only choose one Now it is possible to configure it as a list if you want to also as a radio buttons or by check with multiples choices e NEW FEATURE Links It is possible create links to drive the reports data to webpages depending value For example if a report is displayed products 0 3 0 21 08 2014 can be set up so that if you link to a product go to the page of Magento editing of that product you can configure row links as well as column links and also choose if you want the link to open in new windows or in the same one e NEW FEATURE SVN parameter text qualifier New parameter in export parameter where it is possible to indicate when data is exported in SVN and which symbol includes the text e NEW FEATURE SQL visualization when the data of a report is seen Before to see the SQL button beside the Show Report bottom Now is shown in a pull down bar e NEW FEATURE
42. s 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 e 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 e 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 3 1 4 1 Row Links To add a row link we go to LINK gt Link manager where we can see the list with the row links Sql2Report admin Links manager gt REPORTS Ronee mentee ROW eel aaa repon gt FILTERS E 10 DESCRIPTION URL ACTIONS ilters manager seseneneecesscessnsnensnenens z ere LK_TR_CUSTOMER_EDIT Link to customer edit adminhtml customer editid FCUSTOMER_ID gt 0 a LK_TR_ORDER_EDIT Link to order edit adminhtml sales_orderiview order_id lt ORDER_ID gt 5 pi LK_TR_PRODUCT_EDIT Link to edit lt PRODUCT_NAME gt adminhtml catalog_producteditid lt PRODUCT_ID gt 3 Group manager amp AN 5 gt LINK Link manager We click on the button Add row link and a new row will show up on the table where we fill up e ID Unique code of link it has to be text without spaces e 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 e URL Link s route If a
43. t will also appear as the tittle of the link when you leave the cursor on the row e URL Link s route El 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 Version plugin 0 4 1 Page 21 37 User manual Document Sql2report User Manual docx Dashboard Sales Catalog Customers Promotions Newsletter CMS Reports System Gethelp for this page x a Arc J A Classic Hardshell Suitcase x y tud gt D ww sql2report es index php admin catalog_product edit id 377 key 7f73454bdf413bfbe4cc3a5291 0 M Gmail Q 2 Query Otros Magento J GNF Otros marcadores This is a demo store Any orders placed through this store will not be honored or fulfilled show report DA 5 Regex Tester Global Record Search Logged d Monday March 23 2015 Ey Exportto csv Magento Admin Panel Global Record Search Mee EI Dashboard Sales Catalog Customers Promotions Newsletter CMS Reports System Get help for this page 377 409 RIRI Choose Store View Classic Hardshell Suitcase 29 Accessories Default Values vO Back Reset Delete Duplicate Save O Save an
44. to edit and a similar window to the add filters are will be shown but now with the following options Sql2Report admin Manage filter STORE_SELECT Back gt REPORTS Reports manager Add report Code STORE_SELECT STORE VIEW gt FILTERS 4 Ha de ser unico Filters manager Parameter STORE_ID STORE VIEW e Back Goes back to the filter list e Save Saves the changes e Delete Deletes the filter 3 1 3 Groups We can gather u the reports in groups using the option GROUP gt Group manager we can create edit and delte groups once we enter the section the list of avariable groups will appear Version plugin 0 4 1 Page 18 37 User manual Document Sql2report User Manual docx Dashboard Sales Catalog Customers Promotions Newsletter CMS Reports System Gethelp for this page Sql2Report admin Manage Groups REPORTS gt Reports manager n aa gt FILTERS GROUP ID DESCRIPTION ACTIONS a GR_QUOTE Quotes 11 40 i pa o ae i Eee GR_CUSTOMERS Customers 11 20 GRUNUR GR_ALERTS Alerts t IAG LINK Link manager gt SYSTEM Admin 3 1 3 1 Create Group To create a group in the section GROUP gt Group manager we press the button Add Group a new line will appear in the group table there we will fill up the group code which has to be unique and without spaces the description and then we will push the tick button Y GR_ALERTS Alerts i 1 20 11140 The group will be able alread
45. uscar Disco loca C p gt REPORTS i Organizar v Nueva carpeta g Reports manager hapni 9 B z 3 e Add report E Im genes 2 Nombre Fecha de modifica Mus GROUP REPORT a M sica W Archivos de programa 86 06 03 2015 10 30 Quotes li de eclipse 02 12 2013 12 55 Filters manager Quotes hee E Tutorial Report k Intel 29 11 2013 16 21 r i Java 27 01 2015 16 21 by Day of Wee E Equipo GROUP ine l Disco local C e oracle 11 12 2013 13 47 Group manager gt Ji app e PerfLogs 14 07 2009 5 20 gt LINK Le Archivos de programa de TEMP 23 03 2015 13 20 OE mana pr Sales General UN Actes dde programs G80 Y tivemp 11 12 2013 11 40 by Coupon cod Bh eclipse e Usuarios 29 11 2013 16 24 SYSTEM by Country Intel Ji wamp 29 01 2015 16 34 he A Ordered Inventgi J Java Je win64_11gR2_ client 29 11 2013 16 33 Permissions y From Magento q D oracte de Windows 30 01 2015 12 37 gt EXPORT IMPORT Products Bestsellers Li Perfloas lll eee Export Customers Customers amp gif Nombre quote _list json met ste a Tipo a Ocultar carpetas 3 1 6 2 Import To import a report that must have been previously exported from the plugin we will go to EXPORT IMPORT gt Import this window will ask which file we want to import Version plugin 0 4 1 Page 25 37 User manual Document Sql2report User Manual docx Dashboard Sales Sql2Report admin gt REPORTS Reports manager Group manager
46. y to be selected in the reports 3 1 3 2 Edit Group To edit a group in the section GROUP gt Group manager we press the pencil button Es 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 Sql2Report admin Manage Groups Save gt REPORTS a le ee Se gt FILTERS GROUP ID DESCRIPTION ACTIONS OS GR_QUOTE Quotes 2 11140 A o MD Saco Calas 8 AZA 3 1 4 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 Version plugin 0 4 1 Page 19 37 User manual Document Sql2report User Manual docx Tutorial Report Show Report ig SAL a Export to ev EZ Product Name Ordered Sku product_id Order id Order Number Order Date 41 100000049 Mar 15 2013 Classic Hardshell Suitcase 29 abl007 377 42 100000051 Mar 28 2013 Chelsea Tee mtk004 409 Elizabeth Knit Top wbk012 421 43 100000052 Mar 28 2013 Houston Travel Wallet abl004 374 NoLita Cami wbk002 417 44 100000053 Mar 28 2013 Modern Murray Ceramic Vase hdd001 387 388 431 hdd002 awsnNnft Ann Ankle Root rack amail com 100000055 Mar 28 2013 It is possible that link in the example where data from orders and product
Download Pdf Manuals
Related Search