Details about this thread
  • State Not Answered
  • Replies 2 replies
  • Subscribers 315 subscribers
  • Views 511 views
  • Users 0 members are here
Related content from around SugarClub

Advanced Reports Queries

Ada Napierala

Hi, 

I am creating a new Advanced Report to track changes of certain field on opportunity - Signature Date, Probability, New ARR. 

From the documentation, I was able to start with this: 

SELECT IFNULL(opportunities.id,'') opportunity_id

,IFNULL(opportunities.name,'') accounts_name

,IFNULL(opportunities_audit.date_created,'') `date_created`

,IFNULL(opportunities_audit.created_by,'') `created_by`

,IFNULL(opportunities_audit.field_name,'') `field_name`

,IFNULL(opportunities_audit.before_value_string,'') `before`

,IFNULL(opportunities_audit.after_value_string,'') `after`

FROM opportunities

LEFT JOIN opportunities_audit opportunities_audit ON opportunities.id = opportunities_audit.parent_id

WHERE (((opportunities_audit.date_created >= DATE_SUB(CURDATE(), INTERVAL 14 DAY)))) AND opportunities.deleted=0;

I wanted to add my filters and conditions to that query: 

SELECT IFNULL(opportunities.id,'') opportunity_id

,IFNULL(opportunities.name,'') accounts_name

,IFNULL(opportunities_audit.date_created,'') `date_created`

,IFNULL(opportunities_audit.created_by,'') `created_by`

,IFNULL(opportunities_audit.field_name,'') `field_name`

,IFNULL(opportunities_audit.before_value_string,'') `before`

,IFNULL(opportunities_audit.after_value_string,'') `after`

FROM opportunities

LEFT JOIN opportunities_audit opportunities_audit ON opportunities.id = opportunities_audit.parent_id

WHERE (((opportunities_audit.date_created >= DATE_SUB(CURDATE(), INTERVAL 14 DAY)))) AND opportunities.deleted=0;

AND opportunities.probability BETWEEN 5 AND 95  

AND  opportunities.opportunity_type = 'New Business'  

 AND  opportunities_audit.field_name IN ('date_closed', 'probability_c', ‘calculated_new_arr_c'); 

Unfortunately this query is not working. Is there a mistake in the SQL? Or is the SQL limited and I cannot add more conditions?

Thank you for your help!

Ada

  • 0 Sugar Employee Badge

    Hi Ada,

    I took a look at the second SQL query and found a couple of things. 

    First one, you have a " ; " after the first WHERE clause 

    WHERE (((opportunities_audit.date_created >= DATE_SUB(CURDATE(), INTERVAL 14 DAY)))) AND opportunities.deleted=0;

    The second one is on the last where clause

    AND  opportunities_audit.field_name IN ('date_closed', 'probability_c', ‘calculated_new_arr_c');

    Here, you are filtering by "probability_c" field. Did you create a custom probability field? 

    If I remove this last where clause and the mentioned " ; " the query runs and shows data (in a demo environment).

    I hope this helps but let me know if you have any additional questions.

    Best,

    Francesc

  • 0

    If you copy your last condition 

       AND   opportunities_audit . field_name   IN  ( 'date_closed' ,  'probability_c' , ‘ calculated_new_arr_c  ');    

    to an UTF-8 editor like Notepad++ or SQL editor in DBeaver 

    you see that the apostrophs including the  calculated_new_arr_c are textual apostrophs and not the needed  simple apostrophs '. The strings are colored in green in DBeaver. caclulated_new_arr_c is not seen as a string.

    Just replace the apostrophs with the right ones like this: