Any hint how we can make profit and loss as per below print format?
Hi @ushashmi,
you could define a query report that will return the content and then apply a print format to it. I have found database views to be very helpful to gather the data, then process in a query report.
Hope this helps.
@lasalesi the whole P&L can be generated with query report along with budget figures and year to date data?
Can you plz provide some guidance how we can make?
Hi @ushashmi,
you should have something like this (this is an example that pulls a sales report on the basis of delivery notes in 7 days, 1 month and full year)
SELECT `item_group` AS `Product group`, `item_code` AS `SKU`, `item_name` AS `Description`,
`weight_per_unit` AS `Packaging`, `weight_uom` AS `Unit`,
/* LAST 7 DAYS */
(SELECT IFNULL((
SELECT (SUM(`qty`))
FROM `tabDelivery Note Item`
WHERE
`tabDelivery Note Item`.`item_code` = `tabItem`.`item_code`
AND `docstatus` = '1'
AND `creation` > DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY `item_code`), 0) * `weight_per_unit`) AS `Sold last 7 days kg/L`,
(SELECT IFNULL((
SELECT (SUM(`net_amount`))
FROM `tabDelivery Note Item`
WHERE
`tabDelivery Note Item`.`item_code` = `tabItem`.`item_code`
AND `docstatus` = '1'
AND `creation` > DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY `item_code`), 0)) AS `Revenue last 7 days`,
/* ROLLING 12 MONTHS */
(SELECT IFNULL((
SELECT (SUM(`qty`))
FROM `tabDelivery Note Item`
WHERE
`tabDelivery Note Item`.`item_code` = `tabItem`.`item_code`
AND `docstatus` = '1'
AND `creation` > DATE_SUB(NOW(), INTERVAL 12 MONTH)
GROUP BY `item_code`), 0) * `weight_per_unit`) AS `Sold 12 Mt kg/L`,
(SELECT IFNULL((
SELECT (SUM(`net_amount`))
FROM `tabDelivery Note Item`
WHERE
`tabDelivery Note Item`.`item_code` = `tabItem`.`item_code`
AND `docstatus` = '1'
AND `creation` > DATE_SUB(NOW(), INTERVAL 12 MONTH)
GROUP BY `item_code`), 0)) AS `Revenue 12 Mt`,
/* FULL YEAR */
(SELECT IFNULL((
SELECT (SUM(`qty`))
FROM `tabDelivery Note Item`
WHERE
`tabDelivery Note Item`.`item_code` = `tabItem`.`item_code`
AND `docstatus` = '1'
AND YEAR(`creation`) = YEAR(CURDATE())
GROUP BY `item_code`), 0) * `weight_per_unit`) AS `Sold PY kg/L`,
(SELECT IFNULL((
SELECT (SUM(`net_amount`))
FROM `tabDelivery Note Item`
WHERE
`tabDelivery Note Item`.`item_code` = `tabItem`.`item_code`
AND `docstatus` = '1'
AND YEAR(`creation`) = YEAR(CURDATE())
GROUP BY `item_code`), 0)) AS `Revenue PY`
FROM `tabItem`
ORDER BY `Product group` ASC, `Description` ASC;
You can include additional columns also from your budget data, or amend this to cover other data fields. Building your exact report will take a few minutes 
Feel free to PM.
Hope this helps.