Re: new report

2019-09-14T21:28:02Z

Can you attach file after modification report and steps to run
I try but not work
Thanks

Re: new report

2019-09-14T06:22:51Z

You will either need to use a user defined Pivot function / procedure or make a php function to assemble a sql like it and then create a FA report to display it.

You want Item Category, User Sum for each user across the page.

A nice report indeed - An Item category Wise Customer Sales listing. Start by modifying Item Sales Summary Report - rep309.php.

Specify if you want Qty, Inv Amount or both to be summed.

Raw SQL (ST_SALESINVOICE=10 as defined in includes/types.inc):

SELECT dd.stock_id
     , dd.description
     , dt.debtor_no
     , dm.`name` AS debtor
     , SUM(dd.quantity) AS TotQty
     , SUM(dd.quantity * dd.unit_price) AS TotAmount
FROM 1_debtor_trans_details dd LEFT JOIN 1_debtor_trans dt
     ON (dd.debtor_trans_no = dt.trans_no AND dd.debtor_trans_type = dt.`type`)
   LEFT JOIN 1_debtors_master dm USING (debtor_no)
WHERE dd.debtor_trans_type=10
GROUP BY dd.stock_id, dt.debtor_no;

Sample output (debtor name omitted for space, date filters can be incorporated in the WHERE clause, adjust table prefix as appropriate):

stock_id    description    debtor_no    TotQty    TotAmount
101         iPad Air 2 16GB     1          21    6300
102         iPhone 6 64GB       1           5    1250
102         iPhone 6 64GB       2           1    222.62
103         iPhone Cover Case   2           1    44.52
202         Maintenance         1           5    0
26138763    Bucket              1           3    18
301         Support             1           3    240
95006000    Billiard Balls      1           5    54

The real PIVOT sample SQL for the training company will be:

SELECT IFNULL(stock_id, 'Totals') AS stock_id
     , description
     , IF(debtor_no=1, TotQty, 0) AS `Qty - Donald Easter LLC`
     , IF(debtor_no=1, TotAmount, 0) AS `Amt - Donald Easter LLC`
     , IF(debtor_no=2, TotQty, 0) AS `Qty - MoneyMaker Ltd.`
     , IF(debtor_no=2, TotAmount, 0) AS `Amt - MoneyMaker Ltd.`
FROM ( 
SELECT dd.stock_id
     , dd.description
     , dt.debtor_no
     , dm.`name` AS debtor
     , SUM(dd.quantity) AS TotQty
     , ROUND(SUM(dd.quantity * dd.unit_price),0) AS TotAmount
FROM 1_debtor_trans_details dd LEFT JOIN 1_debtor_trans dt
     ON (dd.debtor_trans_no = dt.trans_no AND dd.debtor_trans_type = dt.`type`)
   LEFT JOIN 1_debtors_master dm USING (debtor_no)
WHERE dd.debtor_trans_type=10
--  AND dt.trans_date between '2018-05-10' and '2019-01-21'
GROUP BY dd.stock_id, dt.debtor_no
) a GROUP BY stock_id
WITH ROLLUP;

new report

2019-09-13T13:35:54Z

i need this report in FA same like in img
is explain sale total by category users

https://cdn1.imggmi.com/uploads/2019/9/13/138334b0240f403521831a9774642dbf-full.png

can you help me for this report
Thanks

FrontAccounting forum — new report

Re: new report

Re: new report

new report