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.generalledgers14) AS SIGNED) > 8099 AND 
                             
CAST(SUBSTRING(vtiger_products.generalledgers14) AS SIGNED) < 8200
                         
) OR 
                         
CAST(SUBSTRING(vtiger_products.generalledgers14) AS SIGNED) = 8997
                 

                 AND 
CAST(SUBSTRING(vtiger_products.generalledgers14) AS SIGNED) != 8198 
                 
AND crment_so.deleted 
                 
AND crment_id.deleted 
                 
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