Edit Report:PSELL - Product Line Ranking Report
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- PSELL - Product Line Ranking Report
-- Product Line Ranking Report
-- ^inventory
-- VARIABLE: {
-- name: "range",
-- display: "Report Range",
-- type: "daterange",
-- default: { start: "first day of last month", end: "last day of last month" }
-- }
-- VARIABLE: {
-- name: "productline",
-- display: "Product Line",
-- type: "select",
-- database_options: { table: "productlines", display: "line_description", column: "line_field", order:{ column: "line_description" , 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`,
(select round(price,2) from prices p where p.stockid = stocktrans.stockid and p.typeabbrev = 'A' and p.startdate <= NOW() and p.enddate >= NOW() limit 1) as `Salon Pr` from stocktrans
where trandate between DATE('{{ range.start }}') AND DATE('{{ range.end }}')
{% if productline|first != '' and productline is not empty %}
and lineid in (
{% for pl in productline %}
{% if pl != '' %}
'{{ pl }}'
{{ 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 stocktrans.stockid
{% else %}
group by 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 %}