sales order gross profit report

[former_member229757]

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

[Johan_H]

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

[Johan_H]

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]

[former_member229757]

While executing the above query its throwing an error

Thanks

Vinoth

[Johan_H]

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

[former_member229757]

Hi

Thanks for your reply

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

Thanks

Vinoth