on 10-01-2014 7:26 AM
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.
Once again your help is greatly appreciated during this initial few days of me on B1. Thanks heaps !
Rahul.
Hi Rahul,
see the below answers for your questions
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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.
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
Hi,
If you got answer, please close this thread by marking correct/helpful answer.
Thanks & Regards,
Nagarajan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
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
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
a) some of the report and its fields are hard coded. Its very difficult to find in system.
Thanks & Regards,
Nagarajan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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.
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..
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,
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.
User | Count |
---|---|
94 | |
11 | |
11 | |
6 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.