odoo-support-kpi | My Website

Module

The Orchid Dynamic Dashboard is a powerful and configurable dashboard solution designed for Odoo. It provides users with a centralized platform to monitor critical business metrics, track performance, and make data-driven decisions.

Available Versions : Odoo 16, Odoo 17, Odoo 18

GitHub repository for Developers: Orchid Home Dashboard

Configuration

Please watch the video for configuration of Orchid KPI Dashboard

Color Codes for Graphs

You can use the below color codes for graphs.

Autumn Harvest - #e06666, #d4a5a5, #c99162, #7b4e33, #503b31, #3e4c6b, #2a74a6, #2167c5, #0f5e8b, #98c4c3, #ffb830, #fcaa1d
Serene Ocean - #4b9fa3, #317679, #236a4e, #1b4c36, #4a6e4f, #2f6a8b, #57a7be, #91d0b7, #5f4b8d, #8e2a5c, #fc9d42, #f1c869
Electric Pop - #2c81d9, #3c6b6f, #b03949, #ba4720, #5baf66, #29c1b4, #8c4cd9, #cd4776, #4e93d3, #fa7532, #fdcd3b, #ffee4a
Tropical Sunset - #fe4c6f, #f26646, #ef7e42, #d78a5a, #9d8c4f, #67b4b1, #60c9a2, #3b78c7, #2e4f88, #f4ba47, #ffca45, #fa8773
Frosty Twilight - #7d90a3, #a2b5d1, #8a9aba, #5a7486, #69778b, #395c6d, #4e779e, #426d7d, #4f98a0, #8ba39e, #c9c9db, #dde4f3

SQL Queries

You can use the below SQL queries for graphs and List and make sure Custome SQL Enabled.

:: CRM

Revenue By Salesperson ::

SELECT t3.name AS salesperson, t1.user_id, SUM(t1.expected_revenue) AS revenue
FROM crm_lead t1
LEFT JOIN res_users t2 ON t1.user_id = t2.id 
LEFT JOIN res_partner t3 ON t2.partner_id = t3.id 
WHERE 1=1
AND t1.expected_revenue IS NOT NULL
GROUP BY t3.name, t1.user_id
ORDER BY revenue DESC;

Stage By Salesperson ::

SELECT t3.name AS salesperson, t1.stage_id, COUNT(t1.id) AS leads_count
FROM crm_lead t1
LEFT JOIN res_users t2 ON t1.user_id = t2.id 
LEFT JOIN res_partner t3 ON t2.partner_id = t3.id 
WHERE t1.stage_id IS NOT NULL
GROUP BY t3.name, t1.stage_id
ORDER BY leads_count DESC;

Leads By Country::

SELECT t3.name AS salesperson, t5.code, SUM(t1.expected_revenue) AS total_revenue
FROM crm_lead t1
LEFT JOIN res_users t2 ON t1.user_id = t2.id 
LEFT JOIN res_partner t3 ON t2.partner_id = t3.id 
LEFT JOIN res_partner t4 ON t1.partner_id = t4.id 
LEFT JOIN res_country t5 ON t4.country_id = t5.id 
WHERE t1.expected_revenue IS NOT NULL
GROUP BY t3.name, t5.code, t1.expected_revenue
ORDER BY t1.expected_revenue DESC;

:: Sale Order

Revenue By Salesperson ::

SELECT t3.name AS salesperson, t1.user_id, SUM(t1.amount_total) AS revenue
FROM sale_order t1
LEFT JOIN res_users t2 ON t1.user_id = t2.id 
LEFT JOIN res_partner t3 ON t2.partner_id = t3.id 
WHERE 1=1
AND t1.amount_total IS NOT NULL
GROUP BY t3.name, t1.user_id
ORDER BY revenue DESC;

Count By Salesperson ::

SELECT t3.name AS salesperson, t1.user_id, COUNT(t1.id) AS orders_count
FROM sale_order t1
LEFT JOIN res_users t2 ON t1.user_id = t2.id 
LEFT JOIN res_partner t3 ON t2.partner_id = t3.id 
WHERE t1.user_id IS NOT NULL
GROUP BY t3.name, t1.user_id
ORDER BY orders_count DESC;

Sales By Country::

SELECT t3.name AS salesperson, t5.code AS country_code, SUM(t1.amount_total) AS total_revenue
FROM sale_order t1
LEFT JOIN res_users t2 ON t1.user_id = t2.id 
LEFT JOIN res_partner t3 ON t2.partner_id = t3.id 
LEFT JOIN res_partner t4 ON t1.partner_id = t4.id  
LEFT JOIN res_country t5 ON t4.country_id = t5.id 
WHERE t1.amount_total IS NOT NULL
GROUP BY t3.name, t5.code
ORDER BY total_revenue DESC;

:: Customer Invoice

Customer Invoice ::

SELECT t2.name AS customer, 
TO_CHAR(t1.date, 'MM') AS month,
SUM(t1.amount_total) AS revenue
FROM account_move t1
LEFT JOIN res_partner t2 ON t1.partner_id = t2.id
WHERE t1.amount_total IS NOT NULL
AND t1.move_type = 'out_invoice' 
GROUP BY t2.name, TO_CHAR(t1.date, 'MM') 
ORDER BY revenue DESC;

Odoo KPI Dashbaord

{ Orchid Home Page }