Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
SQL report, can't filter
#1
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?
Reply
#2
try eliminating the order by clause, that is not supported there if you want to do filtering (I think)
Joe
TSolucio
Reply
#3
(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?
Reply
#4
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?
Joe
TSolucio
Reply
#5
activate the debug report query global variable and look at the SQL being generated
Joe
TSolucio
Reply
#6
OK, trying to catch up to some things.. let me get back to you
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)