CoreBOSBB
SQL report, can't filter - Printable Version

+- CoreBOSBB (https://discussions.corebos.org)
+-- Forum: Support (https://discussions.corebos.org/forumdisplay.php?fid=17)
+--- Forum: Administrator Support (https://discussions.corebos.org/forumdisplay.php?fid=8)
+--- Thread: SQL report, can't filter (/showthread.php?tid=1392)

SQL report, can't filter - Guido1982 - 02-01-2019

I have this query that I used to produce an SQL report:
PHP Code:
SELECT vtiger_salesorder.salesorder_no AS 'SalesOrder_SalesOrder_No', 
                 vtiger_salesorder.pl_net_total AS 'SalesOrder_Net_Total_(aGD)', 
                 vtiger_salesorder.subject AS 'SalesOrder_Subject', 
                 CONCAT(vtiger_users.first_name, ' ', vtiger_users.last_name) AS 'Users_User_Name', 
                 DATE_FORMAT(crment_so.createdtime, '%d-%m-%Y') AS 'SalesOrder_Created_Time', 
                 vtiger_products.productname AS 'Products_Product_Name', 
                 vtiger_products.generalledgers AS 'Products_General_Ledger_Accounts', 
                 vtiger_inventorydetails.quantity AS 'InventoryDetails_Quantity', 
                 vtiger_inventorydetails.extnet AS 'InventoryDetails_ExtNet', 
                 vtiger_inventorydetails.cost_gross AS 'InventoryDetails_Cost_Price', 
                 vtiger_account.accountname AS 'Accounts_Account_Name', 
                 vtiger_inventorydetails.inventorydetailsid AS 'LBL_ACTION' 
                 FROM vtiger_inventorydetails 
                 INNER JOIN vtiger_salesorder ON vtiger_inventorydetails.related_to = vtiger_salesorder.salesorderid 
                 INNER JOIN vtiger_account ON vtiger_salesorder.accountid = vtiger_account.accountid 
                 INNER JOIN vtiger_crmentity crment_so ON vtiger_salesorder.salesorderid = crment_so.crmid 
                 INNER JOIN vtiger_crmentity crment_id ON vtiger_inventorydetails.inventorydetailsid = crment_id.crmid 
                 INNER JOIN vtiger_users ON vtiger_users.id = crment_so.smownerid 
                 INNER JOIN vtiger_products ON vtiger_inventorydetails.productid = vtiger_products.productid 
                 AND (
                         (
                             CAST(SUBSTRING(vtiger_products.generalledgers, 1, 4) AS SIGNED) > 8099 AND 
                             CAST(SUBSTRING(vtiger_products.generalledgers, 1, 4) AS SIGNED) < 8200
                         ) OR 
                         CAST(SUBSTRING(vtiger_products.generalledgers, 1, 4) AS SIGNED) = 8997
                 ) 
                 AND CAST(SUBSTRING(vtiger_products.generalledgers, 1, 4) AS SIGNED) != 8198 
                 AND crment_so.deleted = 0 
                 AND crment_id.deleted = 0 
                 AND crment_so.createdtime > '2016-01-01 00:00:00' 
                 ORDER BY vtiger_salesorder.salesorder_no ASC 
It produces the report all right, but filtering doesn't work. I tried filtering the created time but ended up with either an empty report or an unfiltered one. I created the report both on SalesOrders and on InventoryDetails as the main module. Is this a query too complicated?

RE: SQL report, can't filter - joebordes - 02-01-2019

try eliminating the order by clause, that is not supported there if you want to do filtering (I think)

RE: SQL report, can't filter - Guido1982 - 02-05-2019

(02-01-2019, 09:57 PM)joebordes Wrote: try eliminating the order by clause, that is not supported there if you want to do filtering (I think)

Hm, no. Think I need to investigate this a bit more and try to improve the mechanism, create some PR's. I believe this functionality is still kind of in beta?

RE: SQL report, can't filter - joebordes - 02-05-2019

This functionality hasn't been tested much but last time I tried it was working. Have you selected the main module correctly when creating the report?

RE: SQL report, can't filter - joebordes - 02-05-2019

activate the debug report query global variable and look at the SQL being generated

RE: SQL report, can't filter - Guido1982 - 03-01-2019

OK, trying to catch up to some things.. let me get back to you