2012-06-26

Email SAP SQVI reports.

Many years from now I was involved in creating a client-server application, in those days this was a new concept, clients was Windows PC computers, and server was a backend computer of some kind. Often these apps didn’t work due to bad communications and buggy comms protocol those days you actually had to be a rocket scientist to successfully connect PCs to servers and make multi update apps work. It was 1990 and the app was an ordering system, retailers sent purchase orders to a wholesaler and received an acknowledgement in return. The twist with this system was the backend it was a DB2 system in an IBM mainframe. How the f-k should the Windows client connect to the mainframe DB2 system?. I came up with a solution, use mail. The dominant mainframe mail system MEMO (developed by the car manufacturer Volvo!) had a Windows client that had a reliable connection to the mainframe MEMO system.  I thought why not use a mail system for sending messages, it is designed for that. So I created an interface between MEMO and DB2. When a mail arrived I picked up the mail, translated it into appropriate SQLs and sent back an email with an order acknowledgment as an attachment that the Windows client software picked up from the mailbox in the PC. This system worked well, which is more than I could say about other Windows client-server apps I had seen at that time.
I have written a post on extracting data from SAP using SAP RFC and PHP . SAP RFC is a very reliable and fast and definitely the first choice for extracting (large) data from SAP. The problem with RFC communication you need an RFC enabled ABAP program or a BAPI on the SAP end. What if you do not have an RFC enabled program in SAP that give you the information you need? It is not a simple task to create an RFC enabled ABAP program.

In this post I  will schedule and send an SQVI   report by the mail to a target system.

SQVI is a user friendly report-generator in SAP. It is a simple, well relatively simple, thing to knock up an SQVI report. If you are not an ABAP programmer this is a more passable way to route data out of SAP, just bear in mind this is not the most efficient way to extract data from SAP.  
Automatic workflow exporting SQVI reports from SAP into MySQL
This is a picture of the workflow I have in mind. Create an automated workflow from the SQVI report into The Business Intelligence application the Data Warehouse. We start with an SQVI report. (I will not explain how you create SQVI reports, I barely know this myself).
The first thing we need to create is a ‘mail printer’, we use this printer to print the report.

SPAD

Use SAP transaction SPAD  to create a new printer.
Here are screenshots from creating the printer  LassesMailPrinter .
1. Fire up SPAD and display output Devices.
2 press Create or Create using template (of you have a suitable printer definition to copy)
When the mail printer is created we put it into use.

SQVI

I select report RUNE and then press Execute (you have to create a report of your own first)
Then I select Execute in Background  and fill in all parameters
Then I choose my mail printer LassesMailPrinter
That’s it. SAP will now create the report and print/send it to your specified email address.
This is the first part of my workflow, the way out of SAP. The second part is to bounce the mail to my FTP server. For that i need some help, we use Lotus Notes for mail and I do not talk Lotus Notes and there is also some security concerns about bouncing emails, to cut it short ‘I can not write this routing so I got help from a colleague.
But now I’m off for vacation and if I recall this when I’m coming back I will create the last part of my workflow from the FTP server into the Data Warehouse.  (It’s the green part of the picture in the beginning).

3 comments:

  1. Lars

    Very interesting post.

    I'm interesting in building a SQVI (the definition selecting tables/LDB's programatically).

    Can you help me in this subject (do you know of Bapi's or RFC's that can invoque the definition/creation of an QuickView object)?

    Thanks

    Jose Hernandez

    ReplyDelete
  2. Hi Jose,
    Thanks for you comment. I didn't have time to do more than what's in the post.
    I do not know of any such BAPIs, I scheduled my 'predeveloped' SQVIs as background jobs.
    I'm on a new job now so I no longer work with 'extracting info' from SAP.
    But I'm willing to share ideas, code etc.

    ReplyDelete
  3. Hi again Jose,
    This morning on my way to the office it struck my mind maybe this post can help you http://remoteopensql.codeplex.com/

    The post is about issuing dynamic SQL against SAP. I do not know if you can address LDB in there, but it's worth a try. Please post me about the result if you try it out.

    ReplyDelete