Edit Report:VSELL - Vendor Item Sales Ranking
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- VSELL - Vendor Item Sales Ranking
-- Vendor Item Sales Ranking
-- ^purchases
-- VARIABLE: {
-- name: "range",
-- display: "Report Range",
-- type: "daterange",
-- default: { start: "first day of last month", end: "last day of last month" }
-- }
-- VARIABLE: {
-- name: "vendor",
-- display: "Vendor",
-- type: "select",
-- database_options: { table: "purchdata", display: "supplierno", column: "supplierno", where: "supplierno != ''", order:{ column: "supplierno" , order: "ASC"} },
-- multiple:true,
-- empty:true,
-- }
-- VARIABLE: {
-- name: "location",
-- display: "Branches",
-- type: "select",
-- database_options: { table: "locations", display: "locationname", column: "loccode", order:{ column: "loccode" , order: "ASC"} },
-- multiple:true,
-- empty:true,
-- }
-- VARIABLE: {
-- name: "conbr",
-- display: "Consolidated or Branch",
-- type: "select",
-- multiple: false,
-- options: [{display: 'Consolidated', value:'C'},{display: 'Branch Detail', value:'B'}],
-- }
-- VARIABLE: {
-- name: "sortby",
-- display: "Sort by",
-- type: "select",
-- multiple: false,
-- options: [{display: 'Quantity', value:'Q'},{display: 'Sales $', value:'D'},{display: 'Profit', value:'P'}],
-- }
-- ROLLUP: {
-- columns: {
-- "Sales Total": "{{ sum }}",
-- "Cost Total": "{{ sum }}",
-- "Profit": "{{ sum }}",
-- "Qty Sold": "{{ sum }}"
-- }
--}
-- FILTER: {
-- filter: "class",
-- column: "Cost Total",
-- params: {
-- class: "right"
-- }
-- }
-- FILTER: {
-- filter: "class",
-- column: "Profit",
-- params: {
-- class: "right"
-- }
-- }
-- FILTER: {
-- filter: "class",
-- column: "Sales Total",
-- params: {
-- class: "right"
-- }
-- }
-- FILTER: {
-- filter: "class",
-- column: "Qty Sold",
-- params: {
-- class: "right"
-- }
-- }
-- FILTER: {
-- filter: "class",
-- column: "Salon Pr",
-- params: {
-- class: "right"
-- }
-- }
select {% if conbr == 'C' %} '99' {% else %} stocktrans.loccode {% endif %} as `Branch`, stocktrans.stockid as `Item Number`, description as `Description`, CAST(sum(discountedlinetotal * -1) as DECIMAL(10, 2)) as `Sales Total`,
CAST(sum(costtotal * -1) as DECIMAL(10,2)) as `Cost Total`, CAST(sum((discountedlinetotal - costtotal) * -1) as DECIMAL(10, 2)) as `Profit`,
CAST(sum(qty * -1) as DECIMAL(10, 0)) as `Qty Sold`, COUNT(*) as `LINES`, CAST((select price from prices where stockid = stocktrans.stockid and typeabbrev ='A') as DECIMAL(10, 2)) as `Salon Pr` from stocktrans
inner join purchdata on stocktrans.stockid = purchdata.stockid and preferred = 1
where trandate between DATE('{{ range.start }}') AND DATE('{{ range.end }}')
{% if vendor|first != '' and vendor is not empty %}
and purchdata.supplierno in (
{% for v1 in vendor %}
{% if v1 != '' %}
'{{ v1 }}'
{{ not loop.last ? ',' }}
{% endif %}
{% endfor %}
)
{% endif %}
{% if location|first != '' and location is not empty %}
and stocktrans.loccode in (
{% for loc in location %}
{% if loc != '' %}
'{{ loc }}'
{{ not loop.last ? ',' }}
{% endif %}
{% endfor %}
)
{% endif %}
{% if conbr == 'C' %}
group by purchdata.supplierno, stocktrans.stockid
{% else %}
group by purchdata.supplierno, stocktrans.stockid, stocktrans.loccode
{% endif %}
{% if sortby == 'Q' %}
order by `Qty Sold` desc
{% endif %}
{% if sortby == 'D' %}
order by `Sales Total` desc
{% endif %}
{% if sortby == 'P' %}
order by `Profit` desc
{% endif %}