Full Version: SQL report, can't filter
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
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 
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

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?
try eliminating the order by clause, that is not supported there if you want to do filtering (I think)
(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?
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?
activate the debug report query global variable and look at the SQL being generated
OK, trying to catch up to some things.. let me get back to you