Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

User wise and Document wise query report for all the documents .

Hi Expert ,

I'm trying to get a report for user wise and document wise for daily purpose , please help me create the report .

scenario :

it should capture date , transaction Document (delivery , invoice and so on ) , numbering series (manual/primary), and total created document .

screen shot also attached here .

Thanks

report.PNG (11903 B)
replied

Hi Sarvesh,

Use this query

select 'PQ' 'Document',Convert (nvarchar(15),p1.DocDate,105) 'Date',n.Seriesname  ,count(p1.DocEntry )'No Of Entries' from OPQT p1

inner join nnm1 n on n.series=p1.series where p1.CANCELED='N' and Convert (nvarchar(15),p1.DocDate,105)=Convert (nvarchar(15),getdate(),105) group by  p1.DocDate ,n.Seriesname

union all

select 'PO' 'Document',Convert (nvarchar(15),p1.DocDate,105) 'Date',n.Seriesname  ,count(p1.DocEntry )'No Of Entries' from OPOR p1

inner join nnm1 n on n.series=p1.series where p1.CANCELED='N' and Convert (nvarchar(15),p1.DocDate,105)=Convert (nvarchar(15),getdate(),105) group by  p1.DocDate ,n.Seriesname

union all

select 'GRPO' 'Document',Convert (nvarchar(15),p1.DocDate,105) 'Date',n.Seriesname  ,count(p1.DocEntry )'No Of Entries' from OPDN p1

inner join nnm1 n on n.series=p1.series where p1.CANCELED='N' and Convert (nvarchar(15),p1.DocDate,105)=Convert (nvarchar(15),getdate(),105) group by  p1.DocDate ,n.Seriesname

union all

select 'Goods Return' 'Document',Convert (nvarchar(15),p1.DocDate,105) 'Date',n.Seriesname  ,count(p1.DocEntry )'No Of Entries' from ORPD p1

inner join nnm1 n on n.series=p1.series where p1.CANCELED='N' and Convert (nvarchar(15),p1.DocDate,105)=Convert (nvarchar(15),getdate(),105) group by  p1.DocDate ,n.Seriesname

union all

select 'AP DownPayment' 'Document',Convert (nvarchar(15),p1.DocDate,105) 'Date',n.Seriesname  ,count(p1.DocEntry )'No Of Entries' from ODPO p1

inner join nnm1 n on n.series=p1.series where p1.CANCELED='N' and Convert (nvarchar(15),p1.DocDate,105)=Convert (nvarchar(15),getdate(),105) group by  p1.DocDate ,n.Seriesname

union all

select 'AP Invoice' 'Document',Convert (nvarchar(15),p1.DocDate,105) 'Date',n.Seriesname  ,count(p1.DocEntry )'No Of Entries' from OPCH p1

inner join nnm1 n on n.series=p1.series where p1.CANCELED='N' and Convert (nvarchar(15),p1.DocDate,105)=Convert (nvarchar(15),getdate(),105) group by  p1.DocDate ,n.Seriesname

union all

select 'AP Credit memo' 'Document',Convert (nvarchar(15),p1.DocDate,105) 'Date',n.Seriesname  ,count(p1.DocEntry )'No Of Entries' from ORPC p1

inner join nnm1 n on n.series=p1.series where p1.CANCELED='N' and Convert (nvarchar(15),p1.DocDate,105)=Convert (nvarchar(15),getdate(),105) group by  p1.DocDate ,n.Seriesname

-- Sales--

union all

select 'SQ' 'Document',Convert (nvarchar(15),p1.DocDate,105) 'Date',n.Seriesname  ,count(p1.DocEntry )'No Of Entries' from OQUT p1

inner join nnm1 n on n.series=p1.series where p1.CANCELED='N' and Convert (nvarchar(15),p1.DocDate,105)=Convert (nvarchar(15),getdate(),105) group by  p1.DocDate ,n.Seriesname

union all

select 'SO' 'Document',Convert (nvarchar(15),p1.DocDate,105) 'Date',n.Seriesname  ,count(p1.DocEntry )'No Of Entries' from ORDR p1

inner join nnm1 n on n.series=p1.series where p1.CANCELED='N' and Convert (nvarchar(15),p1.DocDate,105)=Convert (nvarchar(15),getdate(),105) group by  p1.DocDate ,n.Seriesname

union all

select 'Delivery' 'Document',Convert (nvarchar(15),p1.DocDate,105) 'Date',n.Seriesname  ,count(p1.DocEntry )'No Of Entries' from ODLN p1

inner join nnm1 n on n.series=p1.series where p1.CANCELED='N' and Convert (nvarchar(15),p1.DocDate,105)=Convert (nvarchar(15),getdate(),105) group by  p1.DocDate ,n.Seriesname

union all

select 'Sales Return' 'Document',Convert (nvarchar(15),p1.DocDate,105) 'Date',n.Seriesname  ,count(p1.DocEntry )'No Of Entries' from ORDN p1

inner join nnm1 n on n.series=p1.series where p1.CANCELED='N' and Convert (nvarchar(15),p1.DocDate,105)=Convert (nvarchar(15),getdate(),105) group by  p1.DocDate ,n.Seriesname

union all

select 'AR DownPayment' 'Document',Convert (nvarchar(15),p1.DocDate,105) 'Date',n.Seriesname  ,count(p1.DocEntry )'No Of Entries' from ODPI p1

inner join nnm1 n on n.series=p1.series where p1.CANCELED='N' and Convert (nvarchar(15),p1.DocDate,105)=Convert (nvarchar(15),getdate(),105) group by  p1.DocDate ,n.Seriesname

union all

select 'AR Invoice' 'Document',Convert (nvarchar(15),p1.DocDate,105) 'Date',n.Seriesname  ,count(p1.DocEntry )'No Of Entries' from OINV p1

inner join nnm1 n on n.series=p1.series where p1.CANCELED='N' and Convert (nvarchar(15),p1.DocDate,105)=Convert (nvarchar(15),getdate(),105) group by  p1.DocDate ,n.Seriesname

union all

select 'AR Credit memo' 'Document',Convert (nvarchar(15),p1.DocDate,105) 'Date',n.Seriesname  ,count(p1.DocEntry )'No Of Entries' from ORIN p1

inner join nnm1 n on n.series=p1.series where p1.CANCELED='N' and Convert (nvarchar(15),p1.DocDate,105)=Convert (nvarchar(15),getdate(),105) group by  p1.DocDate ,n.Seriesname

union all

select 'Inventory Transfer' 'Document',Convert (nvarchar(15),p1.DocDate,105) 'Date',n.Seriesname  ,count(p1.DocEntry )'No Of Entries' from OWTR p1

inner join nnm1 n on n.series=p1.series where p1.CANCELED='N' and Convert (nvarchar(15),p1.DocDate,105)=Convert (nvarchar(15),getdate(),105) group by  p1.DocDate ,n.Seriesname

union all

select 'Outgoing Payment' 'Document',Convert (nvarchar(15),p1.DocDate,105) 'Date',n.Seriesname  ,count(p1.DocEntry )'No Of Entries' from OVPM p1

inner join nnm1 n on n.series=p1.series where p1.CANCELED='N' and Convert (nvarchar(15),p1.DocDate,105)=Convert (nvarchar(15),getdate(),105) group by  p1.DocDate ,n.Seriesname

union all

select 'Incoming Payment' 'Document',Convert (nvarchar(15),p1.DocDate,105) 'Date',n.Seriesname  ,count(p1.DocEntry )'No Of Entries' from ORCT p1

inner join nnm1 n on n.series=p1.series where p1.CANCELED='N' and Convert (nvarchar(15),p1.DocDate,105)=Convert (nvarchar(15),getdate(),105) group by  p1.DocDate ,n.Seriesname

0 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question