Goods Receipt PO Entry Day Wise Pivot Report

[Former Member]

Dear All,

We need a report like;

(1) Vendor Name (From PO)       (2) PO No.         (3) Item Description (From PO)       (4) Qty (From PO)     (5) Unit Price (From PO)   (6) Opening Balance Qty (Beginning of the Month)   (7) 01-Jan ( GRPO Qty Received by Date Wise)      02-Jan           03-Jan ................. 31-Jan  (8) Total Receipt of the Month        (9) Balance (Opening - Total Receipts)

Can anyone plz help me on this?

Regards,

SP Samy

[Former Member · Accepted solution]

Hi,

To create day wise report, it is better use something like SSRS or Excel pivot table. Query is not the tool for this job.

Thanks,

Gordon

[kothandaraman_nagarajan]

Hi,

If don't have further questions, please close this thread.

Thanks & Regards,

Nagarajan

[kothandaraman_nagarajan]

Hi,

Why do you need day by day report for purchasing? Better get report by weeks  easy to check and write a query.

Thanks & Regards,

Nagarajan

[former_member188586]

hi

try with bellow      Query

SELECT CardName,[ItemCode],[ItemName], [4] as 'Apr-PO', [5] as 'May-PO', [6] as 'Jun-PO', [7] as 'Jul-PO',  [8] as 'Aug-PO', [9] as 'Sep-PO', [10] as 'Oct-PO', [11] as 'Nov-PO', [12] as 'Dec-PO', [1] as 'Jan-PO', [2] as 'Feb-PO', [3] as 'Mar-PO',  [4] as 'Apr-GRPO', [5] as 'May-GRPO', [6] as 'Jun-GRPO', [7] as 'Jul-GRPO',  [8] as 'Aug-GRPO', [9] as 'Sep-GRPO', [10] as 'Oct-GRPO', [11] as 'Nov-GRPO', [12] as 'Dec-GRPO', [1] as 'Jan-GRPO', [2] as 'Feb-GRPO', [3] as 'Mar-GRPO'

from

( select CardName,T0.[ItemCode],T2.[ItemName],sum(T0.[Quantity]) as Quantity, month(T1.[DocDate]) as Month FROM PDN1 T0 INNER JOIN OPDN T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T0.ItemCode = T2.ItemCode  and (T1.[DocDate] between '2014/04/01' and '2015/03/31')  WHERE (T0.ItemCode LIKE '%%[%0]%%' OR '[%0]' = ' ')

GROUP BY CardName,T0.[ItemCode],T2.[ItemName],T1.DocDate)S

Pivot

(sum(Quantity) FOR Month IN ([4],[5],[6],[7],[8],[9],[10],[11],[12],[1],[2],[3])) P

union all

SELECT CardName,[ItemCode],[ItemName],  [4] as 'Apr-PO', [5] as 'May-PO', [6] as 'Jun-PO', [7] as 'Jul-PO',  [8] as 'Aug-PO', [9] as 'Sep-PO', [10] as 'Oct-PO', [11] as 'Nov-PO', [12] as 'Dec-PO', [1] as 'Jan-PO', [2] as 'Feb-PO', [3] as 'Mar-PO',[4] as 'Apr-GRPO', [5] as 'May-GRPO', [6] as 'Jun-GRPO', [7] as 'Jul-GRPO',  [8] as 'Aug-GRPO', [9] as 'Sep-GRPO', [10] as 'Oct-GRPO', [11] as 'Nov-GRPO', [12] as 'Dec-GRPO', [1] as 'Jan-GRPO', [2] as 'Feb-GRPO', [3] as 'Mar-GRPO'

from

( select CardName,T0.[ItemCode],T2.[ItemName],sum(T0.[Quantity]) as Quantity, month(T1.[DocDate]) as Month FROM POR1 T0 INNER JOIN OPDN T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T0.ItemCode = T2.ItemCode  and (T1.[DocDate] between '2014/04/01' and '2015/03/31')  WHERE (T0.ItemCode LIKE '%%[%0]%%' OR '[%0]' = ' ')

GROUP BY CardName,T0.[ItemCode],T2.[ItemName],T1.DocDate)S

Pivot

(sum(Quantity) FOR Month IN ([4],[5],[6],[7],[8],[9],[10],[11],[12],[1],[2],[3])) P

The Above Query got got Already Discussed Thread but i saved in my DB ...Check Once and Update The Status

Thanks&regards

AndakondaRamudu

[Former Member]

Hi Andakonda Ramudu,

Thanks for reply!! we need day wise pivot instated of Month Wise.

Regards,

SP Samy

[former_member188586]

hi

are else try with this simple way

SELECT T0.[DocNum],T0.[DocDate],T0.[TaxDate],T0.[CardCode], T0.[CardName], T1.[Dscription], T1.[FreeTxt],T1.[Quantity], T1.[Price], T0.U_TRPSHT "Trip Sheet",T0.U_StockEntry "Stock Entry", T0.[DocStatus] FROM OPDN T0  INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry WHERE T1.[BaseType] = 22 and  T1.[BaseRef]  =[%0] and T0.[DocDate] >='01/04/2010'