Skip to Content
0

sales order gross profit report

Apr 04, 2017 at 04:47 AM

61

avatar image

Hi Experts

I want to generate a report of gross profit percentage value in sales order for one month.

Is there any specific table name available to generate the report.

while right clicking in sales order gross profit scree will open in that i want a field called purchase price,selling price, gross profit.

How to achieve that report.

Thanks

Vinoth

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Johan Hakkesteegt Apr 04, 2017 at 05:50 AM
0

Hi,

You only need one table: ORDR. Here is an example:

SELECT T0.[CardCode]
     , T0.[CardName]
     , SUM(T0.[DocTotal] - T0.[VatSum]) AS NettoSales
     , SUM(T0.[GrosProfit]) AS [Gross Profit]
     , SUM(T0.[GrosProfit]) / SUM(T0.[DocTotal] - T0.[VatSum]) AS [Gross Profit Percentage]
FROM ORDR T0 
WHERE MONTH(T0.[DocDate]) = MONTH([%0]) 
  AND YEAR(T0.[DocDate]) = YEAR([%0])
GROUP BY T0.[CardCode]
       , T0.[CardName]

Regards,

Johan

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi

Thanks for your reply

I want to include selling product code,unit price,quantity,Purchase unit price,Margin,Margin Percentage

Thanks

Vinoth

0
Johan Hakkesteegt Apr 04, 2017 at 08:37 AM
0
SELECT T1.[ItemCode]
     , T1.[Dscription]
 , T1.Price
 , T1.Quantity
 , SUM(T1.[LineTotal]) AS [Netto Sales]
 , T1.GrossBuyPr
     , SUM(T1.[GrossBuyPr] * T1.[Quantity]) AS [Line Gross Profit]
     , SUM(T0.[GrosProfit]) / SUM(T0.[DocTotal] - T0.[VatSum]) AS [Line Gross Profit %]
FROM ORDR T0
     INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
WHERE MONTH(T0.[DocDate]) = MONTH([%0]) 
  AND YEAR(T0.[DocDate]) = YEAR([%0])
GROUP BY T0.[CardCode]
       , T0.[CardName]
Show 1 Share
10 |10000 characters needed characters left characters exceeded

While executing the above query its throwing an error

Thanks

Vinoth

capture.png (68.4 kB)
0
Johan Hakkesteegt Apr 04, 2017 at 09:51 AM
0

Hi,

Sorry, that previous one was a bit of a mess. Please try this:

SELECT T1.[ItemCode]
     , T1.[Dscription]
     , T1.Price
     , T1.Quantity
     , SUM(T1.[LineTotal]) AS [Netto Sales]
     , T1.GrossBuyPr
     , SUM(T1.[GrossBuyPr] * T1.[Quantity]) AS [Line Gross Profit]
     , SUM(T1.[GrossBuyPr] * T1.[Quantity]) / CASE 
                                               WHEN ISNULL(SUM(T1.[LineTotal]), 0) = 0 THEN 1
                                               ELSE SUM(T1.[LineTotal])
                                              END AS [Line Gross Profit %]
FROM ORDR T0
     INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
WHERE MONTH(T0.[DocDate]) = MONTH([%0]) 
  AND YEAR(T0.[DocDate]) = YEAR([%0])
  AND T0.DocType = 'I'
GROUP BY T1.[ItemCode]
       , T1.[Dscription]
       , T1.Price
       , T1.Quantity
       , T1.GrossBuyPr
ORDER BY [Line Gross Profit] DESC
       , T1.[Dscription]

Regards,

Johan

Share
10 |10000 characters needed characters left characters exceeded