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

[former_member599281]

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

[Former Member · Accepted solution]

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

[kothandaraman_nagarajan]

Hi,

Please check this thread

Thanks

[Former Member]

Hi,

Refer this link it may helpful for you;

http://scn.sap.com/docs/DOC-47616

Regards,

SP Samy

[former_member599281]

Hi SP ,

can't we create query in standard without creating UDFs . 

[Former Member]

Which version you're using?

[Former Member]

Try this...

Select (select UPPER(ou1.U_NAME) from OUSR ou1 where ou1.userid = TBL1.usersign) [UserName],

CASE

WHEN TBL1.Obj_Type = '13' THEN 'A/R Invoice'

WHEN TBL1.Obj_Type = '14' THEN 'A/R Credit Memo'

WHEN TBL1.Obj_Type = '15' THEN 'Delivery'

WHEN TBL1.Obj_Type = '16' THEN 'Delivery Return'

WHEN TBL1.Obj_Type = '17' THEN 'Sales Order'

WHEN TBL1.Obj_Type = '18' THEN 'A/P Invoice'

WHEN TBL1.Obj_Type = '19' THEN 'A/P Credit Memo'

WHEN TBL1.Obj_Type = '20' THEN 'GRPO'

WHEN TBL1.Obj_Type = '21' THEN 'GRPO_Return'

WHEN TBL1.Obj_Type = '22' THEN 'Purchase Order'

WHEN TBL1.Obj_Type = '23' THEN 'Sales Qutotaion'

WHEN TBL1.Obj_Type = '24' THEN 'Incoming Payment'

WHEN TBL1.Obj_Type = '30' THEN 'Journal Entry'

WHEN TBL1.Obj_Type = '46' THEN 'Outgoing Payment'

WHEN TBL1.Obj_Type = '59' THEN 'Goods Receipt'

WHEN TBL1.Obj_Type = '60' THEN 'Goods Issue'

WHEN TBL1.Obj_Type = '67' THEN 'Inventory Transfer'

WHEN TBL1.Obj_Type = '69' THEN 'Landed Costs'

WHEN TBL1.Obj_Type = '202' THEN 'Production Order'

WHEN TBL1.Obj_Type = '203' THEN 'A/R Down Payment Invoice'

WHEN TBL1.Obj_Type = '204' THEN 'A/P Down Payment Invoice'

WHEN TBL1.Obj_Type = '1250000001' THEN 'Inventory Transfer Request'

WHEN TBL1.Obj_Type = '1470000113' THEN 'Purchase Request'

ELSE '-'

END  [Document],

TBL1.Counts,

TBL1.PIndicator

FROM (

--Purchase

select p1.UserSign,count(p1.DocNum) [Counts],p1.ObjType as 'Obj_Type', p1.PIndicator from OPQT p1

group by p1.UserSign,p1.ObjType,p1.PIndicator

union all

select p1.UserSign,count(p1.DocNum) [Counts],p1.ObjType as 'Obj_Type', p1.PIndicator from OPOR p1

group by p1.UserSign,p1.ObjType,p1.PIndicator

union all

select p1.UserSign,count(p1.DocNum) [Counts],p1.ObjType as 'Obj_Type', p1.PIndicator from OPDN p1

group by p1.UserSign,p1.ObjType,p1.PIndicator

union all

select p1.UserSign,count(p1.DocNum) [Counts],p1.ObjType as 'Obj_Type', p1.PIndicator from ORPD p1

group by p1.UserSign,p1.ObjType,p1.PIndicator

union all

select p1.UserSign,count(p1.DocNum) [Counts],p1.ObjType as 'Obj_Type', p1.PIndicator from ODPO p1

group by p1.UserSign,p1.ObjType,p1.PIndicator

union all

select p1.UserSign,count(p1.DocNum) [Counts],p1.ObjType as 'Obj_Type', p1.PIndicator from OPCH p1

group by p1.UserSign,p1.ObjType,p1.PIndicator

union all

select p1.UserSign,count(p1.DocNum) [Counts],p1.ObjType as 'Obj_Type', p1.PIndicator from ORPC p1

group by p1.UserSign,p1.ObjType,p1.PIndicator

-- Sales--

union all

select s1.UserSign,count(s1.DocNum) [Counts],s1.ObjType as 'Obj_Type', s1.PIndicator from OQUT s1

group by s1.UserSign,s1.ObjType,s1.PIndicator

union all

select s1.UserSign,count(s1.DocNum) [Counts],s1.ObjType as 'Obj_Type', s1.PIndicator from ORDR s1

group by s1.UserSign,s1.ObjType,s1.PIndicator

union all

select s1.UserSign,count(s1.DocNum) [Counts],s1.ObjType as 'Obj_Type', s1.PIndicator from ODLN s1

group by s1.UserSign,s1.ObjType,s1.PIndicator

union all

select s1.UserSign,count(s1.DocNum) [Counts],s1.ObjType as 'Obj_Type', s1.PIndicator from ORDN s1

group by s1.UserSign,s1.ObjType,s1.PIndicator

union all

select s1.UserSign,count(s1.DocNum) [Counts],s1.ObjType as 'Obj_Type', s1.PIndicator from ODPI s1

group by s1.UserSign,s1.ObjType,s1.PIndicator

union all

select s1.UserSign,count(s1.DocNum) [Counts],s1.ObjType as 'Obj_Type', s1.PIndicator from OINV s1

group by s1.UserSign,s1.ObjType,s1.PIndicator

union all

select s1.UserSign,count(s1.DocNum) [Counts],s1.ObjType as 'Obj_Type', s1.PIndicator from ORIN s1

group by s1.UserSign,s1.ObjType,s1.PIndicator

union all

select s1.UserSign,count(s1.DocNum) [Counts],s1.ObjType as 'Obj_Type', s1.PIndicator from OWTR s1

group by s1.UserSign,s1.ObjType,s1.PIndicator

union all

select s1.UserSign,count(s1.DocNum) [Counts],s1.ObjType as 'Obj_Type', s1.PIndicator from OSCL s1

group by s1.UserSign,s1.ObjType,s1.PIndicator

union all

select s1.Owner,count(s1.ContractID) [Counts],'190', 'FY' from OCTR s1

group by s1.Owner

union all

select s1.UserSign,count(s1.DocNum) [Counts],s1.ObjType as 'Obj_Type', s1.PIndicator from OVPM s1

group by s1.UserSign,s1.ObjType,s1.PIndicator

union all

select s1.UserSign,count(s1.DocNum) [Counts],s1.ObjType as 'Obj_Type', s1.PIndicator from ORCT s1

group by s1.UserSign,s1.ObjType,s1.PIndicator

union all

select s1.UserSign,count(s1.OpprId) [Counts],'970', 'FY' from OOPR s1

group by s1.UserSign

) TBL1

where TBL1.UserSign <> '1'

order by TBL1.usersign,TBL1.Obj_Type

Regards

SP Samy