AnsweredAssumed Answered

Trim new line , line breaks from report Query

Question asked by Caim Astraea on Mar 6, 2017
Latest reply on Mar 6, 2017 by Caim Astraea

Hello,

Is it possible to customize the reports in sugar 7.7 Pro to do filters on output fields?

Would need to remove the newlines from the accounts.shipping_address_city

 

The current query for the report is like

SELECT IFNULL(accounts.id,'') primaryid ,IFNULL(accounts_cstm.v_nom_groupe_recopy_c,'') ACCOUNTS_CSTM_V_NOM_GR62C97E ,IFNULL(accounts.shipping_address_street,'') ACCOUNTS_SHIPPING_ADDR7BFE69 ,IFNULL(accounts.shipping_address_postalcode,'') ACCOUNTS_SHIPPING_ADDR1496A1 ,IFNULL(accounts.shipping_address_city,'') ACCOUNTS_SHIPPING_ADDR4D329C ,IFNULL(l2.id,'') l2_id ,IFNULL(l2.name,'') l2_name ,IFNULL(accounts.account_type,'') accounts_account_type ,IFNULL(accounts_cstm.v_inactif_c,0) accounts_cstm_v_inactif_c FROM accounts INNER JOIN users l1 ON accounts.assigned_user_id=l1.id AND l1.deleted=0 LEFT JOIN crit_typegrp_accounts_c l2_1 ON accounts.id=l2_1.crit_typegrp_accountsaccounts_idb AND l2_1.deleted=0 LEFT JOIN crit_typegrp l2 ON l2.id=l2_1.crit_typegrp_accountscrit_typegrp_ida AND l2.deleted=0 LEFT JOIN accounts_cstm accounts_cstm ON accounts.id = accounts_cstm.id_c WHERE (((l1.id='13a73edc-c067-7936-c13e-55eec7c4440c' ) AND (accounts.account_type IN ('Customer','Prospect') ) AND ((accounts_cstm.v_inactif_c is null OR accounts_cstm.v_inactif_c='0') ))) AND accounts.deleted=0 ORDER BY ACCOUNTS_SHIPPING_ADDR1496A1 ASC

 

but there's no way to interact with it or change it.

Was thinking could run a trim on the field to remove the new line.

trim(BOTH '\n' from [field_name]) as field

Outcomes