need help in identifying fields of sd report creation

[Former Member]

Hi Experts,

I need to create report in sd from manual report .The fileds in report are taken from Quickbooks.so inorder to create report in sap I need to identify fields in sap first.we dont had functional guy so i need help in identifying fields in sap for below mentioned report.

Report: invoice distribution report-service billing for all states.

Fields:customer number,customer name,city state,SAP Document number,Invoice date,period(month,year)invoice number,invoice type,currency,invoice amount in usd.

Please help me in suggesting tcodes or table names from where i can fetch these fields.

Thanking you in advance.

Regards,

narasimha.

[Former Member · Accepted solution]

Hi,

Please find the fields from where you can fetch the data

CUSTOMER NUMBE --- VBAK-KUNNR

CUSTOMER NAME--- Get the ADRNR from VBPA table with sales order number and then goto Table ADRC to get the NAME 1 which is customer name

CITY --get it from ADRC table from the field CITY1

STATE - get if from the ADRC table from the field REGION ( depends on your requirement)

INVOICE DATE---VBRK- FKDAT

PERIOD---there is no direct way to get,use the FM to get the month and period by passed the Invoice date

INVOICE NUMBER---if it is accounting number get it from *VBRK-BELNR* , if it is billing document nmber get that from *VBRK-VBELN*

INVOICE TYPE-- *VBRK-FKART*

CURRENCY-- VBRK-WAERK

NET AMOUNT--VBRK-NETWR

please revrt if u need more details

thanks

santosh

[Former Member · Accepted solution]

Hi Santosh,

could you send me table name for SAP document number.

Thanks&Regards,

narasimha.

[Former Member · Accepted solution]

SAP Document number - VBAK - VBELN

[Former Member · Accepted solution]

Hi ,

I created view with santosh mentioned field& table names.Unfortunately i didn't get the results as expected.how can i related these table in view.Please give some hint.

Thanks& Regards,

narasimha.

[Former Member · Accepted solution]

hi Could you please tell what will be ur input parameters to generate report

or selection parameters

[Former Member · Accepted solution]

Hi wish,

Thanks for your response.i created view with base tables VBAK,VBRK,ADRC and specified the fields required in report and executed the report.But i didnt get the results as expected.

Regards,

narasimha.

[Former Member · Accepted solution]

you will get all fields from the tables VBAK, VBRK and and KNA1 and ADRC

To get customer name,city, state you need to pass Customer no to KNA1 get the ADRNR and pass this ADRNR to ADRC you will get details

I WANT TO ASK YOU TO GENERATE REPORT WHAT WILL BE YOUR INPUT PARAMETERS

are you trying to generate report base don Sales document no / invoice no or based on something else?

[Former Member · Accepted solution]

Hi,

Client need report based on city,posting period to get servive billing on state wise.

regards,

narasimha.

[Former Member · Accepted solution]

My last question is'on which field i can relate all these tables.

Regards,

narasimha.

[Former Member · Accepted solution]

You need to pass CITY / STATE to ADRC table and get all customers for that CITY / State

Get all customers and pass it to VBAK table and all Sales Document for that Sold-to party

Pass this Sales Document no to VBRK, you will get

Invoice date - VBRK - FKDAT

period(month,year) - VBRK - POPER

invoice number - VBRK - VBELN

invoice type - VBRK - FKART

currency - VBRK - CMWAE

invoice amount - VBRK - NETWR

To display customer number,customer name,city state, you can use the ADRC table as told earlier

BUT as thease are not PRIMARY KEYS, it might give you performance issue

[Former Member]

Hi Experts,

problem was finally solved by taking fieds from vbrk,vbrp and kna1.duplicates that i got are nothing but billing line items.instead of taking netamount from vbrk ,i have taken netwr from vbrp.

thanks&regards,

narasimha.

[Former Member]

Hi Experts,

Finally with all above suggestions i have chosen tables vbrk(billing document header data),kna1(customer header),vbrp.

1.I created view to find the output of report with conditions as follows:

tables:vbrk,kna1,vbrp

vbrk-vbeln(invoice number)

vbrk-fkart(invoice type)

vbrk-fkdat(invoice date)

vbrk-waerk(currency)

vbrk-netwr(netvalue)

vbrk-kunag(customer number)

kna1-name1(customer name)

kna1-ort01(customer city)

kna1-regio(customer state)

join condition:

kna1-mandt=vbrk-mandt

kna1-kunnr=vbrk-kunag.

when i execute the report i am getting same value for lot of times.

i am confused how to include sales document number.I hope sales document number and invoice are different.

2.i have also added vbrp-aubel and join condition as

vbrk-mandt = vbrp-mandt

vbrk-vbeln = vbrp-vbeln.

but when i execute the report i am getting lot of duplicated with same value and unknown invoice numbers.

please let me know where i went wrong.

is there any standard adhoc report so that i can manipulate it with above requirement.

vf05n is somewhat useful .is it possible to add customer details in this??

Thanks&Regards,

narasimha.

[Former Member]

Some suggestions regarding SD reports:

1. Try to use the standard SD link table, VBFA. This is updated and has consistent data. Of course, you can use also the reference document from VBRK, but is not updated every time if you delete the predecessor.

2. I try first the connections between tables in SQVI Quick viewer. Itu2019s the easiest way to check if a report will be correctly updated.

3. In SD a very useful tool is LIS u2013 SIS (Sales information system). Try to u201Csqueezeu201D the LIS tables for information. You can find a lot of documentation regarding LIS.

4. VF05N has already almost all informations. Try to reuse this report.

BR!

Edited by: Marian Solomon on Oct 13, 2010 9:25 AM

[Former Member]

VBAK-VBELN - Sales order number

VBRK-VBELN - Invoice number

Conection between SO and Invoice (or other releated documents): usualy VBFA table.

[Lakshmipathi]

Please check tables VBRK for header related data and VBRP for item related data

thanks

G. Lakshmipathi