SQL report, can't filter

[Guido1982]

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?

[joebordes]

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

[Guido1982]

(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?

[joebordes]

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?

[joebordes]

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

[Guido1982]

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