Logic and design tools behind SAP B1 Reports

[Former Member]

Dear all,

I have got a small scale process improvement and new Reports creation assignment with one of my clients who run on SAP B1.

Basically my skills lie in SAP ERP (enterprise and retail components) space and not in SAP B1 at all.

highly appreciate if you could please help me in this very initial days to get my head around with my following basic queries in relates to understanding the architecture behind this SAP B1 reporting.

Client uses SAP B1 version 9.0.

When I run few customized reports (such as daily production sheet, delivery run sheet etc , etc) system pops up with selection criteria for the report( such as date, business partner, product category etc ) to run and when executed it shows me the report outcome on PDF form.

1. how do I see the relevant SQL statement executed to get me this results set in PDF form ? I need this underlined SQL statement to understand the business logic behind the report.
2. how do I know which development framework/tools that the client has used to generate these reports, whether its SAP crystal reports or, .net or Query manager  and query generator or any other tool ?. because I need to change the layout of some reports and few changes to some report logic as well.
3. how do I know the data sources that these PDF reports uses to connect to SAP B1 ?

Once again your help is greatly appreciated during this initial few days of me on B1. Thanks heaps !

Rahul.

[former_member184146 · Accepted solution]

Hi Rahul,

            see the below answers for your questions

1. If you want to know about the SQL Statement and the logic behind that report then first you should have some understanding of crystal and SQL Queries once you get the basic idea about the queries then you can try your hand on SQL statements.

check this link below for SQL Tips and Tricks

http://wiki.scn.sap.com/wiki/display/B1/SAP+B1+SQL+Tips+and+Tricks

http://wiki.scn.sap.com/wiki/display/B1/SAP+B1+SQL+Contributors'+Tips+and+Tricks

http://wiki.scn.sap.com/wiki/display/B1/SAP+B1+SQL+Suggested+Standards+-+Basic+Outline+of+the+SQL

for crystal check these links below

http://www.pioneerb1.com/wp-content/uploads/2012/04/How-to-work-with-Crystal-Reports-8.8.pdfhttp://scn.sap.com/thread/3226349

http://www.mepa.ch/images/documentation/HowTo_Work_with_CR_90.pdf

2 & 3>if the reports are generating in the PDF format then they are from Crystal Reports and you can export and import these  these reports and can check the format of report, if the format is .rpt then they are crystal and data source for that pdf is Crystal reports tool check the above links then you will get the idea how to import and export these reports.

Regards,

Manish

[Former Member · Accepted solution]

Hi Manish, Thanks heaps for your valuable docos. my sincere apologies for delay in respond. was on a completely different project on ECC. also I had an issue login in the scn forum on windows env, not sure why but it is still on.

I have some knowledge on SQL and little on crystal reports too, as I was a software developer before I step into SAP/ERP

so, my issue is where should I look into find these SAP non standard(customised)  SQL/program to see the source code. for example, I am creating a delivery/pack list in B1 through my picking list which was generated based on my sales orders. When I saved my delivery, B1 creates a excel file/CSV file in background (with shipping details to be passed to an external interface for parcel tracking) and saved it is a windows folder.

so, I want to find out this custom program name (program that runs in background to creates this csv file) where it is located in the server, and the SQL statements in this program to understand the logic).

thanks a lot.

Rahul.

[former_member184146 · Accepted solution]

Hi Rahul,

               First check is there any add-on there which is creating this csv file.

--Manish

[Former Member · Accepted solution]

Thanks for the prompt reply Manish. I believe I need to go through the 'Adminitration' function in menu path to verify these add-ons, unfortunately there's no any technical B1 support guys here to get this clarified for me  : (

[former_member184146 · Accepted solution]

Yes check under Admin>Add-on >Add-on manager >Installed addons Tab.

--Manish

[Former Member · Accepted solution]

Yes, I can see only a one add-on which is from a third party vendor for barcode scanning.

I'm still interested to understand this technical framework ( as per my original query)

1. how do I know which program triggers the calling of the above add-on ?

2. for custom developed reports - how do I see the SQL program source codes and where (which folder in my SQL server) it is stored for reports. please note this is only for my clients customized reports. not the standard SAP reports/queries. my requirement is to make a slight modification (column heading change to the report) to an existing customized reports.

Thanks.

[Former Member · Accepted solution]

Hi Rahul,

It might be done through Stored Procedure. You may check under Programmability -  Stored Procedure on SQL server to find out.

Thanks,

Gordon

[Former Member · Accepted solution]

I am really glad to see your respond Gordon. yes that answered my first question above.

waiting for a response from someone to my second question too.

Btw, I just ordered your book on B1 SQL.which should reach here in 2 more days and I do hope I can gain more knowledge on this space of B1..

Thanks once again.

Rahul.

[Former Member · Accepted solution]

Hello Manish,

The Links are really helpful. Thank you for sharing.

[former_member184146 · Accepted solution]

Hi Bijaya,

am glad it helped you.

--Manish

[Former Member · Accepted solution]

🙂 I tried to send you a direct message. Can we share email addresses please?

[malcolm_lamour]

Hi

The way I tried to see which programs are called is to save a trace for about an hour on a typical day and then to run a query as below. change the folders and name per your setup.

SELECT * INTO ##temp_trc

FROM fn_trace_gettable('D:\Trace\Untitled.trc', default)

Where TextData like '%[@PROMO%]%'

you can then query on whatever you want to.

regards

Malcolm

[Former Member]

Thanks for your valuable input Malcolm. would you be able to help me to find a solution for my second question ?

2. for custom developed reports - how do I see the SQL program source codes and where (which folder in my SQL server) it is stored for reports. please note this is only for my clients customized reports. not the standard SAP reports/queries. my requirement is to make a slight modification (column heading change to the report) to an existing customized report.

Thanks.

[kothandaraman_nagarajan]

Hi,

If you got answer, please close this thread by marking correct/helpful answer.

Thanks & Regards,

Nagarajan

[Former Member]

Hi Rahul,

I was wondering if you ever got the answer on viewing the SQL statement from a standard report?  I'm in the same situation with my client.

Thank you

Lori

[malcolm_lamour]

Hi Lori

These reports run off encrypted Stored Procs and you will not be able to see the SQL statements even if you try and run SSMS Profiler.
Your best bet it to see what sql codes there are in the forum and combine them into code that gives you what you require.

regards

Malcolm

[Former Member]

Thank you Malcolm!

Lori

[Former Member]

Hi Lori, sorry for the delay in respond. in fact what I want to see is not the SQL statements behind the standard programs, all I want to see is the SQL statements/ programs for customised reports for my client and where are they located in the server with its names.

thanks

Rahul.

[kothandaraman_nagarajan]

Hi,

1. how do I see the relevant SQL statement executed to get me this results set in PDF form ? I need this underlined SQL statement to understand the business logic behind the report.

a) System reports are designed from B1 tables. For example, OJDT and JDT1 being used for most of Financial reports

b) OINM table is used for inventory reports.

c) OINV &  ORIN tables are for sales analysis

1. how do I know which development framework/tools that the client has used to generate these reports, whether its SAP crystal reports or, .net or Query manager  and query generator or any other tool ?. because I need to change the layout of some reports and few changes to some report logic as well.

a) B1 reports are designed by PLD and crystal report. For PLD, please refer below link. In general, if we want add new field in report, you have copy and edit from standard PLD report

http://www.pioneerb1.com/wp-content/uploads/2012/04/HowTo_PrintLayouts_PLD_88.pdf

1. how do I know the data sources that these PDF reports uses to connect to SAP B1 ?

a) some of the report and its fields are hard coded. Its very difficult to find in system.

Thanks & Regards,

Nagarajan

[Former Member]

Hi Nagarajan, thanks for your quick respond and attempt to help me .

as indicated in my thread above my questions are related to customized PDF reports at my client site, not regarding any standard SAP B1 reports.

1. how do I see the relevant SQL statement executed to get me this results set in PDF form ? I need this underlined SQL statement to understand the business logic behind the report.

     I completely understand these reports are based on SAP tables. just reiterating my question in a      simple way again, question is how can I see the written SQL query or program logic which given me the result of these "customized PDF reports"

2.  how do I know which development framework/tools that the client has used to generate      these reports, whether its SAP crystal reports or, .net or Query manager  and query      generator or any other tool ?. because I need to change the layout of some reports and few      changes to some report logic as well.

     thanks for your respond here Nagarajan, if that is the case, how would I verify whether my client's these 'customized PDF reports" are based on PLD or crystal reports ? where should I look in to in B1 to verify it please ?

3 .how do I know the data sources that these PDF reports uses to connect to SAP B1 ?

once again my question is about customized reports and not about SAP standard reports, (these reports have been developed by another third party company). I do not concern about standard SAP reports at all at this stage.

thanks and awaiting for a respond from you.

[kothandaraman_nagarajan]

Hi,

1. You can view the field information and its related table by editing customized PLD.

2. You can view the type of report under Report and layout manager

3. I did not get your requirement.

Thanks & Regards,

Nagarajan

[Former Member]

Dear Nagarajan, thanks a lot. please find my comments for your answers. please note that as mentioned I'm absolutely a beginner for B1. infact its my second day on B1

"2. You can view the type of report under Report and layout manager"

would you please show me the path to access 'report and layout manager' in B1 ?

eg, menu--->production--->production order etc

"3. I did not get your requirement"

what I meant by data sources was. in order to extract data from B1 database (eg, Microsoft SQL server database) for reporting we need to connect to database using data drivers (eg, ODBC driver, JDBC driver etc etc ). so my question was , how do I know whether my clients customized PDF reports uses data sources if so how do I verify It.

apologies, if my question was not clear.

Kind regards..

[Former Member]

Dear Nagarajan, thanks a lot. please find my comments for your answers. please note that as mentioned I'm absolutely a beginner for B1. infact its my second day on B1

"1. You can view the field information and its related table by editing customized PLD".

what I meant by SQL statement in my first question was i.e, SELECT * FROM OITM WHERE xxx >0 ...

so, by seen this in my clients customized PDF reports , I can understand the business logic used to generate report.

"2. You can view the type of report under Report and layout manager"

would you please show me the path to access 'report and layout manager' in B1 ?

eg, menu--->production--->production order etc

"3. I did not get your requirement"

what I meant by data sources was. in order to extract data from B1 database (eg, Microsoft SQL server database) for reporting we need to connect to database using data drivers (eg, ODBC driver, JDBC driver etc etc ). so my question was , how do I know whether my clients customized PDF reports uses data sources if so how do I verify It.

apologies, if my question was not clear.

Kind regards,

[kothandaraman_nagarajan]

Hi,

Please advice your reports are PDF or PLD. Is it possible to post same PDF report here?

Thanks & Regards,

Nagarajan

[Former Member]

Dear Nagarajan,

to be honest, I do not know how to check whether these reports are developed on PDF or PLD, All I know is when I executed the report it shows me a PDF output (with "PDF Complete" software).

Thats exactly why I indicated in my original post,

"When I run few customized reports (such as daily production sheet, delivery run sheet etc , etc) system pops up with selection criteria for the report( such as date, business partner, product category etc ) to run and when executed it shows me the report outcome on PDF form".

Due to confidentiality reasons I'm unable to post the PDF report output as it is on public forums. but I can share few sample reports if you can get me your email by any chance. I can assure that the outcome of the report is a PDF output, but I'm not sure what are the other tools that the client has used internally to get that output. may be . Net, Crystal reports, stored procedures, MS SQL etc etc. so that's exactly my question to you as indicated the the original post under questions one requesting you how to check it in B1.

By the way, thanks heaps for your help so far and really looking forward for your further assistance.

Kind regards,

Rahul.

[kothandaraman_nagarajan]

Hi,

If you want, I will check your reports through TV. If its OK, send ID and password after 45 minutes.

Thanks & Regards,

Nagarajan

[Former Member]

Thats absolutely fine Nagarajan, but I have no idea how does it works. (please note that I'm based in Sydney, Australia and not sure it works here )

thanks and let me know.

[kothandaraman_nagarajan]

Hi,

We will discuss it through TV by tomorrow morning.

Thanks & Regards,

Nagarajan

[Former Member]

Thanks and looking forward to your call.

[kothandaraman_nagarajan]

Hi,

Follow on this forum to get direct communication between us.

Thanks & Regards,

Nagarajan