• From: Egypt, Cairo
  • Registered: 09/12/2019
  • Posts: 30

Topic: new report

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

Post's attachments

customerwise.png 6.5 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.
  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: new report

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;
Post's attachments

SQL1.png 10.6 kb, 1 downloads since 2019-09-14 

You don't have the permssions to download the attachments of this post.

apmuthu's Website

  • From: Egypt, Cairo
  • Registered: 09/12/2019
  • Posts: 30

Re: new report

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