Skip to Content

sales order gross profit report

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Apr 04, 2017 at 05:50 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 04, 2017 at 08:37 AM
    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]
    Add comment
    10|10000 characters needed characters exceeded

  • Apr 04, 2017 at 09:51 AM

    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

    Add comment
    10|10000 characters needed characters exceeded