Need lead report when clicking the filter column it should go to respective leads

ERPNext Reports
,
1

Hi All,
i created a new Query report for Lead and i names it as Lead report i written the code
SELECT
lead_owner AS “Lead Owner:Link/User:150”,
custom_product_type AS “Product Type:Select:150”,
COUNT(name) AS “Total Leads:Int:100”,
COUNT(CASE WHEN status = ‘Converted’ THEN 1 END) AS “Converted Leads:Int:100”,
COUNT(CASE WHEN status != ‘Converted’ THEN 1 END) AS “Unconverted Leads:Int:100”,
COUNT(CASE WHEN DATEDIFF(CURDATE(), creation) <= 30 THEN 1 END) AS “Leads <= 30 Days:Int:150”,
COUNT(CASE WHEN status = ‘Converted’ AND DATEDIFF(CURDATE(), creation) <= 30 THEN 1 END) AS “Converted <= 30 Days:Int:150”,
COUNT(CASE WHEN DATEDIFF(CURDATE(), creation) > 30 AND DATEDIFF(CURDATE(), creation) <= 60 THEN 1 END) AS “Leads 31-60 Days:Int:150”,
COUNT(CASE WHEN status = ‘Converted’ AND DATEDIFF(CURDATE(), creation) > 30 AND DATEDIFF(CURDATE(), creation) <= 60 THEN 1 END) AS “Converted 31-60 Days:Int:150”,
COUNT(CASE WHEN DATEDIFF(CURDATE(), creation) > 60 AND DATEDIFF(CURDATE(), creation) <= 120 THEN 1 END) AS “Leads 61-120 Days:Int:150”,
COUNT(CASE WHEN status = ‘Converted’ AND DATEDIFF(CURDATE(), creation) > 60 AND DATEDIFF(CURDATE(), creation) <= 120 THEN 1 END) AS “Converted 61-120 Days:Int:150”,
COUNT(CASE WHEN DATEDIFF(CURDATE(), creation) > 120 THEN 1 END) AS “Leads > 120 Days:Int:150”,
COUNT(CASE WHEN status = ‘Converted’ AND DATEDIFF(CURDATE(), creation) > 120 THEN 1 END) AS “Converted > 120 Days:Int:150”
FROM
tabLead
GROUP BY
lead_owner
ORDER BY
lead_owner;
it is working but my problem is when the query report where clicking on the count values in the “Total Leads” column filters it should shows the respective leads,

Screenshot 2024-08-06 083949
Screenshot 2024-08-06 0839491294×450 38.7 KB

Thanks in Advance

2

Please apply it.

SELECT
    lead_owner AS "Lead Owner:Link/User:150",
    custom_product_type AS "Product Type:Select:150",
    CONCAT('<a href="/app/lead?lead_owner=', lead_owner, '">', COUNT(name), '</a>') AS "Total Leads:HTML:150",
    COUNT(CASE WHEN status = 'Converted' THEN 1 END) AS "Converted Leads:Int:100",
    COUNT(CASE WHEN status != 'Converted' THEN 1 END) AS "Unconverted Leads:Int:100",
    COUNT(CASE WHEN DATEDIFF(CURDATE(), creation) <= 30 THEN 1 END) AS "Leads <= 30 Days:Int:150",
    COUNT(CASE WHEN status = 'Converted' AND DATEDIFF(CURDATE(), creation) <= 30 THEN 1 END) AS "Converted <= 30 Days:Int:150",
    COUNT(CASE WHEN DATEDIFF(CURDATE(), creation) > 30 AND DATEDIFF(CURDATE(), creation) <= 60 THEN 1 END) AS "Leads 31-60 Days:Int:150",
    COUNT(CASE WHEN status = 'Converted' AND DATEDIFF(CURDATE(), creation) > 30 AND DATEDIFF(CURDATE(), creation) <= 60 THEN 1 END) AS "Converted 31-60 Days:Int:150",
    COUNT(CASE WHEN DATEDIFF(CURDATE(), creation) > 60 AND DATEDIFF(CURDATE(), creation) <= 120 THEN 1 END) AS "Leads 61-120 Days:Int:150",
    COUNT(CASE WHEN status = 'Converted' AND DATEDIFF(CURDATE(), creation) > 60 AND DATEDIFF(CURDATE(), creation) <= 120 THEN 1 END) AS "Converted 61-120 Days:Int:150",
    COUNT(CASE WHEN DATEDIFF(CURDATE(), creation) > 120 THEN 1 END) AS "Leads > 120 Days:Int:150",
    COUNT(CASE WHEN status = 'Converted' AND DATEDIFF(CURDATE(), creation) > 120 THEN 1 END) AS "Converted > 120 Days:Int:150"
FROM
    tabLead
GROUP BY
    lead_owner
ORDER BY
    lead_owner;

When clicking on “Total lead,” I will be redirected to the lead doctype with the lead_owner filter.

2 Likes
3

yeah it is working i need also for all filters like Converted Leads, Unconverted Leads, Converted <= 30 Days,Leads 31-60 Days etc it should redirect to the filters
Thanks in Advance

4

Provided the syntax, now you have to add it yourself. :wink:

2 Likes
5

Screenshot 2024-08-08 111456
Screenshot 2024-08-08 1114561317×443 36.3 KB

done but i need for example sales user if he open this report his creation only need to show only admin can see the all lead owners report
Thanks in Advance

6

everything is not possible in the query report :sweat_smile:

7

but how can we enable user wise can see their report and admin to see all lead owner report
SELECT
lead_owner AS “Lead Owner:Link/User:150”,
custom_product_type AS “Product Type:Select:150”,
CONCAT(‘’, COUNT(name), ‘’) AS “Total Leads:HTML:150”,
CONCAT(‘’, COUNT(CASE WHEN status = ‘Converted’ THEN 1 END), ‘’) AS “Converted Leads:HTML:100”,
CONCAT(‘’, COUNT(CASE WHEN status != ‘Converted’ THEN 1 END), ‘’) AS “Unconverted Leads:HTML:100”,
CONCAT(‘’, COUNT(CASE WHEN DATEDIFF(CURDATE(), creation) <= 30 THEN 1 END), ‘’) AS “Leads <= 30 Days:HTML:150”,
CONCAT(‘’, COUNT(CASE WHEN status = ‘Converted’ AND DATEDIFF(CURDATE(), creation) <= 30 THEN 1 END), ‘’) AS “Converted <= 30 Days:HTML:150”,
CONCAT(‘’, COUNT(CASE WHEN DATEDIFF(CURDATE(), creation) > 30 AND DATEDIFF(CURDATE(), creation) <= 60 THEN 1 END), ‘’) AS “Leads 31-60 Days:HTML:150”,
CONCAT(‘’, COUNT(CASE WHEN status = ‘Converted’ AND DATEDIFF(CURDATE(), creation) > 30 AND DATEDIFF(CURDATE(), creation) <= 60 THEN 1 END), ‘’) AS “Converted 31-60 Days:HTML:150”,
CONCAT(‘’, COUNT(CASE WHEN DATEDIFF(CURDATE(), creation) > 60 AND DATEDIFF(CURDATE(), creation) <= 120 THEN 1 END), ‘’) AS “Leads 61-120 Days:HTML:150”,
CONCAT(‘’, COUNT(CASE WHEN status = ‘Converted’ AND DATEDIFF(CURDATE(), creation) > 60 AND DATEDIFF(CURDATE(), creation) <= 120 THEN 1 END), ‘’) AS “Converted 61-120 Days:HTML:150”,
CONCAT(‘’, COUNT(CASE WHEN DATEDIFF(CURDATE(), creation) > 120 THEN 1 END), ‘’) AS “Leads > 120 Days:HTML:150”,
CONCAT(‘’, COUNT(CASE WHEN status = ‘Converted’ AND DATEDIFF(CURDATE(), creation) > 120 THEN 1 END), ‘’) AS “Converted > 120 Days:HTML:150”
FROM
tabLead
GROUP BY
lead_owner
ORDER BY
lead_owner;

8

You don’t want to understand what I want to tell you. Not everything is possible in query report. A server script report has to be created for that. I told you this before :thinking:

1 Like
9

can u please give me a script report for this

10

already provided the reference in your previous post: I need script report or query report for lead transaction - #6 by NCP

so learn it and develop it.