Sales Agent Name Report

ERPNext Reports
1

hello , so far i’ve done this for this report

Screenshot%20(5)
Screenshot%20(5)792×327 38.9 KB

this is my query , the only thing i’m missing is combining the 2 rows with same sales invoice number into 1 row, example :
SINV-05717 should have totalht 254.75 , vat 53.55, shipping 13 and totalttc 321.3

that’s my query:

    select
    `tabSales Invoice`.full_name as `salesagentname`,
    `tabSales Invoice`.`name` as `invoicenumber`,
    `tabSales Invoice`.`customer` as `customername`,
    `tabSales Invoice`.`posting_date` as `date`,
    `tabSales Invoice`.`total_ht` as `totalht`,
    CASE WHEN `tabSales Taxes and Charges`.account_head = '4427 - V.A.T on Sales - EUSF' THEN `tabSales Taxes and Charges`.tax_amount END as `vat`,
    CASE WHEN `tabSales Taxes and Charges`.account_head = '7310-CHARGES ON SALES - EUSF' THEN `tabSales Taxes and Charges`.tax_amount END as `shipping`,
    `tabSales Invoice`.`grand_total` as `totalttc`
    from
    `tabSales Invoice`, `tabSales Taxes and Charges` 
    where `tabSales Invoice`.`name` = `tabSales Taxes and Charges`.parent and `tabSales Invoice`.full_name IS NOT NULL

if i put group by tabSales Invoice.`name
it does only show me the shipping and put all the vat as null
any idea how to fix this one ??

2

Okay here’s the problem. If you check your image there’s NULL present in the sheet and you haven’t included an ELSE condition hence mysql fetches whatever is present. Also use Inner Join, it is better than your method. Also since you are grouping by the Invoice, you should be using the SUM function over these CASE WHEN to ensure your values are added.

Try this query:

SELECT `tabsales invoice`.full_name      AS `salesagentname`,
       `tabsales invoice`.`name`         AS `invoicenumber`,
       `tabsales invoice`.`customer`     AS `customername`,
       `tabsales invoice`.`posting_date` AS `date`,
       `tabsales invoice`.`total_ht`     AS `totalht`,
SUM(CASE WHEN 
       `tabsales taxes and charges`.account_head = '4427 - V.A.T on Sales - EUSF' 
     THEN
       `tabsales taxes and charges`.tax_amount
ELSE 0  END)  AS `vat`,
SUM(CASE
     WHEN `tabsales taxes and charges`.account_head =
              '7310-CHARGES ON SALES - EUSF' THEN
    `tabsales taxes and charges`.tax_amount
     ELSE 0 END) AS `shipping`,
       `tabsales invoice`.`grand_total`  AS `totalttc`
FROM   
       `tabsales invoice`
        INNER JOIN
       `tabsales taxes and charges`
        ON  `tabsales invoice`.`name` = `tabsales taxes and charges`.parent
WHERE 
       `tabsales invoice`.full_name IS NOT NULL

Also, in case you haven’t used joins here’s a good link:

1 Like
3

Thanks for your help!! worked perfectly , i used SUM before but inside the CASE , so that was my mistake not on the case , in addition i wasn’t putting anything in ELSE so it was filling it with null as you said ,now i get how it works. Thanks again!

1 Like