Sales Order Discount Summery Report

[Former Member]

Hi Experts,

I wand to generate a 'Sales Order Discount Summery Report' to know discount report of outlets.

In my company (Separate Database) all outlets are adding Sales Order and they are giving discount based on total amount of invoice.

Now am using the following query to get this report.

SELECT T1.[DocNum] AS 'Order No:', T1.[DocDate] AS 'Posting Date', T1.[NumAtCard] AS 'Reference No.', T1.[CardCode] AS 'Customer Code', T1.[CardName] AS 'Customer Name', T0.[OpenSum] AS 'Invoice Amount', T1.[DiscPrcnt] AS 'Discount (%)', T1.[DiscSum] AS 'Discount Amount', T1.[VatSum] AS 'Tax Amount', T1.[DocTotal] AS 'Net Amount (After Discount)' FROM  [dbo].[RDR1] T0  INNER  JOIN [dbo].[ORDR] T1  ON  T1.[DocEntry] = T0.[DocEntry]   WHERE T1.[DocDate] >= (CONVERT(DATETIME, '[%0]', 112) )  AND  T1.[DocDate] <= (CONVERT(DATETIME, '[%1]', 112) )

But in this query am not getting a summery report.

Please any one can help me to get a summery report of each order.

Thanks & Regards,

Abdul Rasheed

[Former Member · Accepted solution]

Hi Abdul,

You are not able to get summary as you have taken Row Level Field in your Query that's why Query result is showing another line for same document due to no of rows in docuemnt.

Please remove T0.OpenSum from your query and Include T1.DocTotal then you will get Summary of Query result.

Please check below Query-- Just Changed T0.OpenSum to T1.DocTotal.

SELECT T1.[DocNum] AS 'Order No:', T1.[DocDate] AS 'Posting Date', T1.[NumAtCard] AS 'Reference No.', T1.[CardCode] AS 'Customer Code', T1.[CardName] AS 'Customer Name', T1.[DocTotal] AS 'Invoice Amount', T1.[DiscPrcnt] AS 'Discount (%)', T1.[DiscSum] AS 'Discount Amount', T1.[VatSum] AS 'Tax Amount', T1.[DocTotal] AS 'Net Amount (After Discount)' FROM  [dbo].[RDR1] T0  INNER  JOIN [dbo].[ORDR] T1  ON  T1.[DocEntry] = T0.[DocEntry]   WHERE T1.[DocDate] >= (CONVERT(DATETIME, '[%0]', 112) )  AND  T1.[DocDate] <= (CONVERT(DATETIME, '[%1]', 112) )

Hope this helps

--

--

Regards::::

Atul Chakraborty

[Former Member · Accepted solution]

Hi Sir,

In this query report am not getting a summery report.

If one more items in sales order document, this query generating one more rows in report.

I wand to get a summery report including following columns.

___________________________________________

Order Number

Posting Date

Customer Reference Number

Customer Code

Customer Name

Invoice Amount Before Discount

Discount %

Discount Amount

Tax Amount

Invoice Amount After Discount

__________________________________________

Only document discount is giving.

Thanks & Regards,

Abdul Rasheed

[Former Member · Accepted solution]

Hi Abdul,

Please check below Query.

SELECT DISTINCT T1.[DocNum] AS 'Order No:', T1.[DocDate] AS 'Posting Date', T1.[NumAtCard] AS 'Reference No.',

T1.[CardCode] AS 'Customer Code', T1.[CardName] AS 'Customer Name', T1.[DocTotal] AS 'Invoice Amount',

T1.[DiscPrcnt] AS 'Discount (%)', T1.[DiscSum] AS 'Discount Amount', T1.[VatSum] AS 'Tax Amount',

T1.[DocTotal] AS 'Net Amount (After Discount)'

FROM  [dbo].[RDR1] T0 

INNER  JOIN [dbo].[ORDR] T1  ON  T1.[DocEntry] = T0.[DocEntry]  

WHERE T1.[DocDate] >= (CONVERT(DATETIME, '[%0]', 112) )  AND  T1.[DocDate] <= (CONVERT(DATETIME, '[%1]', 112) )

Hope this helps

--

--

Regards::::

Atul Chakraborty

[Former Member · Accepted solution]

Hi Sir,

This query is OK..

But in this query report, Before discount amount & After discount amount are showing same result value.

I wand following columns in my report

Order Number

Posting Date

Customer Reference Number

Customer Code

Customer Name

Invoice Amount Before Discount

Discount %

Discount Amount

Tax Amount

Invoice Amount After Discount

Thanks & Regards,

Abdul Rasheed

[Former Member · Accepted solution]

Hi Abdul,

Please check below Query now.

SELECT DISTINCT T1.[DocNum] AS 'Order No:', T1.[DocDate] AS 'Posting Date', T1.[NumAtCard] AS 'Reference No.',

T1.[CardCode] AS 'Customer Code', T1.[CardName] AS 'Customer Name', SUM(T0.[LineTotal])  AS 'Invoice Amount',

T1.[DiscPrcnt] AS 'Discount (%)', T1.[DiscSum] AS 'Discount Amount', T1.[VatSum] AS 'Tax Amount',

T1.[DocTotal] AS 'Net Amount (After Discount)'

FROM  [dbo].[RDR1] T0 

INNER  JOIN [dbo].[ORDR] T1  ON  T1.[DocEntry] = T0.[DocEntry]  

WHERE T1.[DocDate] >= (CONVERT(DATETIME, '[%0]', 112) )  AND  T1.[DocDate] <= (CONVERT(DATETIME, '[%1]', 112))

GROUP BY T1.[DocNum] , T1.[DocDate] , T1.[NumAtCard], T1.[CardCode] , T1.[CardName],

T1.[DiscPrcnt], T1.[DiscSum] , T1.[VatSum],  T1.[DocTotal]

Hope this helps

--

--

Regards:::

Atul Chakraborty

[Former Member · Accepted solution]

Thanks Mr.Atul Chakraborty.

Regards,

Abdul Rasheed

[kothandaraman_nagarajan]

Have you tried my last query?

Any difference between this query and Mr.Atul's query?

[kothandaraman_nagarajan]

Try:

SELECT T1.[DocNum], T1.[DocDate], T1.[NumAtCard], T1.[CardCode], T1.[CardName], sum(T0.[OpenSum]), T1.[DiscPrcnt], T1.[DiscSum], T1.[VatSum], T1.[DocTotal] FROM RDR1 T0  INNER JOIN ORDR T1 ON T0.DocEntry = T1.DocEntry WHERE T1.[DocDate] >= (CONVERT(DATETIME, '[%0]', 112) )  AND  T1.[DocDate] <= (CONVERT(DATETIME, '[%1]', 112) ) group  by T1.[DocNum], T1.[DocDate], T1.[NumAtCard], T1.[CardCode], T1.[CardName],  T1.[DiscPrcnt], T1.[DiscSum], T1.[VatSum], T1.[DocTotal]

Thanks & Regards,

Nagarajan

[Former Member]

Hi Abdul,

Try:

SELECT T1.[DocNum] AS 'Order No:', T1.[DocDate] AS 'Posting Date', T1.[NumAtCard] AS 'Reference No.', T1.[CardCode] AS 'Customer Code', T1.[CardName] AS 'Customer Name',  T1.[DiscPrcnt] AS 'Discount (%)', T1.[DiscSum] AS 'Discount Amount', T1.[VatSum] AS 'Tax Amount', T1.[DocTotal] AS 'Net Amount (After Discount)'

FROM  [dbo].[ORDR] WHERE T1.[DocDate] >= (CONVERT(DATETIME, '[%0]', 112) )  AND  T1.[DocDate] <= (CONVERT(DATETIME, '[%1]', 112) )

Thanks,

Gordon

[kothandaraman_nagarajan]

Hi,

Remove reference field from query  and then try.

Thanks & Regards,

Nagarajan

[Former Member]

Hi Abdul,

Where do you apply the discounts? on Row Level or Document Header?

Regards,

Lean