SAP and MS Office Integration Architecture

Integration between SAP and Microsoft products hinges upon the use of OLE, a common and standard technology for transferring and sharing information among applications. With OLE, you can take data out of your SAP system and place it into another system, all the while maintaining the format and integrity of the data. For example, you can view data residing in any number of SAP database tables as a series of columns and rows in Microsoft Excel—an easy way to view and manipulate data otherwise trapped in the SAP database.

The SAP Assistant is the OLE interface used for calling SAP functions and transactions from other non-SAP applications. The SAP Assistant exposes both ActiveX controls and OLE object classes, for logging in to SAP, managing data and tables, calling functions and transactions, and more. SAP systems are therefore designed today so that you can share data with any OLE-compatible application. Sample OLE-compatible applications include

• Microsoft Office, including Visio and other products

• Corel Office, including Paradox

• Star Office

• Lotus Smart Suite

• Many Web Server development environments, such as Microsoft FrontPage

Additionally, nearly all modern application development languages in use today support OLE. This includes the old-school C++ programming language as well as the latest and greatest Microsoft .NET Visual Basic offerings, to IBM’s WebSphere Information Integrator and PowerBuilder by Sybase. In this way, the developer of a non-SAP application can create objects that can access information in SAP.

Using %pc to Download Data

There are many ways to share data between SAP and Microsoft. As I briefly mentioned in previous hours, executing %pc in the transaction dialog box is an excellent and expedient way of moving data from SAP lists into other formats. Transactions that display their data via OCX controls—in various panels, each with its own data sources, constructs, and so on—are generally not good candidates for %pc. That is, OCX-based output typically does not lend itself to being downloaded into a PC format such as XLS, RTF, and so on. But transactions that are displayed in list format—most of them, to some degree—are excellent candidates for this method of downloading SAP data into Microsoft Office–based formats.

To save SAP list-based output to a file on your desktop or the network, enter the characters %pc in the transaction dialog box and then press Enter. A print window pops up (see Figure 22.1), defaulting to saving the screen’s contents in an unconverted file format. Choose the format most appropriate for your immediate needs, press Enter, browse to the desired directory path, type the name of the output file you want to create, and then press Save to save the list data to the filename you specified.

Exporting SAP Data to Microsoft Excel

Microsoft Excel provides a user-friendly format and helpful tools to assist you in the process of analyzing and presenting data. To get your SAP data into Microsoft Excel, you can employ several methods. The most basic method involves the System List function, which enables you to save lists displayed on your SAP screen.

You can also use the SAP Query tool to export data to Microsoft Excel, as follows:

1.	Execute an SAP Query, as discussed in Hour 20, “Reporting Tools in SAP (SAP Query, InfoSet Query, Ad Hoc Query, and QuickViewer).”
2.	The options listed on the selection screen enable you to designate the type of output you want for your report. For a basic transfer to a Microsoft Excel spreadsheet, select the Display As Table radio button.
3.	From here, select the List, Save, Local File to download this table into Microsoft Excel. A Save As box appears, enabling you to select the download file format. Be sure to select the spreadsheet option.
4.	After the download is complete, start Microsoft Excel and open the data you have just saved (see Figure 22.2). Figure 22.2. Your Microsoft Excel spreadsheet containing your SAP Query data looks the same as the data in your original query output. [View full size image]
5.	Return to your SAP Query output screen displaying your table.

Creating SAP Form Letters in Microsoft Word

SAP has a great interface for creating form letters using Microsoft Word. This tool has endless possibilities for your company. For an example, let’s assume you need to output SAP Human Resources employee data into Microsoft Word so that you can create a form letter to all employees.

1.	Select a query to execute.
2.	From the selection screen, use the Display As Table option and then execute your report.
3.	When the output appears, rather than saving this file to Microsoft Excel, select the Word Processing button at the top of your Query Output. Doing so opens the Word Processor Settings dialog box shown in Figure 22.3. Press Enter to continue. Figure 22.3. SAP’s Word Processor Settings dialog boxes enable you to download your SAP data into Microsoft Word.
4.	The dialog box that is displayed presents you with a number of options. You can designate whether you want to create a new Word document, use a current Word document (one that is currently “open” on your system), or use an existing Word document (one that is saved on your computer). Click the green check mark to begin the merge between SAP and Microsoft Word. Upon execution, SAP opens Microsoft Word (see Figure 22.4). Figure 22.4. A Microsoft Word application launches with a new document. [View full size image]
5.	An important thing to note is that your Microsoft Word application now contains a new mail merge toolbar that gives you the capability to insert your SAP fields into your Microsoft Word form letter. In Microsoft Word, press the Enter key to begin at a new line, and then select the Insert Merge Field button on the toolbar. In the drop-down list (or the Insert Merge Field pop-up window, in the case of Microsoft Office 2003), shown in Figure 22.5, you see all the SAP fields contained in your original SAP Query. Figure 22.5. The Microsoft Word Insert Merge Field button contains the names of your SAP fields from your SAP Query.
6.	As appropriate for your needs, select one of your SAP fields. It appears in brackets in your Microsoft Word document. Press Enter and insert another SAP field. Type some text into your Microsoft Word document, and then insert another SAP field (see Figure 22.6). Figure 22.6. Your Microsoft Word form letter contains the inserted fields from your SAP Query in addition to any text you typed manually. [View full size image]
7.	To preview the output of your form letter, click the ABC (View Merged Data) button from the mail merge toolbar, shown in Figure 22.7. Figure 22.7. A sample Microsoft Word form letter containing the SAP fields from your SAP Query, in the View Merged Data view. [View full size image]
8.	Use the Record selectors (forward and backward) buttons on the mail merge toolbar to view the various records.

Exporting SAP to Microsoft Access

As you have seen, exporting SAP data to Microsoft Excel and Word is useful when it comes to performing further offline manipulation of your data, for creating reports and graphs, or for drafting form letters. Exporting data to a Microsoft Access database is quite useful, too, when it comes to general reporting.

The initial steps to export data into Microsoft Access are the same as the steps to download a file into Microsoft Excel—the idea is to get the data into the Excel XLS format. Verify this is the case before proceeding.

Importing SAP into Microsoft Access

After the XLS file resides on your local system or an accessible file share, you need to import this file into Microsoft Access (the following steps assume Access has been installed on your system; given that Access is not included with all versions of Microsoft Office, this might not be the case by default):

1.	Launch Microsoft Access on your system.
2.	From this initial window, select the Blank Database option, and then click OK. You are prompted to create a name and to select a location for your database. In this example, I selected the C:My Documents directory and named the database MySAP.mdb, as shown in Figure 22.8. Figure 22.8. The Microsoft Access File New Database window prompts you to create a new database file. [View full size image]
3.	Click the Create button; you then see the main Microsoft Access window, which appears in Figure 22.9. Figure 22.9. The Microsoft Access database main window displays the different database elements.
4.	To bring the SAP data into Microsoft Access, use the Microsoft Access menu path File, Get External Data, Import. You are then prompted with a window similar to the one shown in Figure 22.10. This is where you have to input the location and filename of the output file you saved earlier. By default, the Files of Type box lists Microsoft Access (*.mdb). You have to change this to Microsoft Excel (*.xls). Figure 22.10. Select your import file location in the Microsoft Access Import window. [View full size image]
5.	After changing the Files of Type box and selecting your file, click Import. Just as in the Microsoft Excel import, in Access you are presented with an Import Spreadsheet Wizard similar to the one shown in Figure 22.11. Figure 22.11. The Microsoft Access Import Spreadsheet Wizard assists you in importing your file.
6.	On the first screen of the Import Spreadsheet Wizard, click the Next button to continue. On the second screen, it asks whether you want to create a new table or add the data to an existing table. To create a new Access database table containing your SAP data, click Next. The next window, shown in Figure 22.12, gives you an opportunity to name each of your fields. Figure 22.12. The Microsoft Access Import Spreadsheet Wizard field enables individual field specification and more. [View full size image]
7.	By selecting each column (use your mouse to do so), you can type a field name for each. After you have named all your fields, click Next.
8.	The following screen enables you to assign a unique identifying number for each of your records, to be used as a primary key (primary keys are discussed in Hour 3, “Database Basics”). Click the Next button to continue.
9.	The last screen asks you to provide a name for your table. Type MySAP and click Finish. Microsoft Access then presents you with a confirmation window similar to that shown in Figure 22.13. Figure 22.13. The Microsoft Access confirmation window declares that your data has finished importing. [View full size image]
10.	Click OK in the final Import Spreadsheet Wizard confirmation window; you are returned to the Microsoft Access main window, and your new table is now listed under the Table tab.
11.	To take a look at your table, select it and then click the Open button. Your SAP list now appears as a Microsoft Access table (see Figure 22.14); it includes an additional primary key field as well. Figure 22.14. Your SAP data now appears in a Microsoft Access database table. [View full size image]

This process is certainly a few steps longer than exporting your SAP data into Microsoft Excel. However, Microsoft Access is a sound reporting tool used by a large number of SAP customers as their primary reporting tool—especially when other applications or tools like SAP Business Warehouse, Strategic Enterprise Management, Cognos, or Crystal Reports are unavailable. Its prevalence also speaks to the fact that Microsoft Access is popular across many companies regardless of size.

Another reason for its popularity is that Microsoft Access is simple to use. It leverages simple report wizards to guide you step-by-step in creating comprehensive reports. Now you will create one sample report using a Microsoft Access Report Wizard.

The Microsoft Access Report Wizard

Creating reports in Microsoft Access is easy using a tool called the Microsoft Access Report Wizard. The use of reports wizards simplifies the layout process of your fields by visually stepping you through a series of questions about the type of report that you want to create. The wizard walks you through the step-by–step creation of a report, while behind the scenes Access is formatting, grouping, and sorting your report based on selections you make.

Instead of having to create a report from scratch, Microsoft Access provides a number of standard report formats. Some of these, like tabular and columnar reports, mail-merge reports, and mailing label formats, lend themselves to meeting basic reporting requirements. Reports created using the Microsoft Access Report Wizard can also be customized to fit your needs. To use the Report Wizard, perform the following steps:

1.	Close any open Access databases by using the menu path File, Close.
2.	In the main Microsoft Access database window, click Reports.
3.	From here, click the New button to launch the Microsoft Access Report Wizard (or choose the option to create a report in Design view).
4.	Assuming you are running the Report Wizard, select the Report Wizard option in the top box and your table name in the second box. Click OK to proceed.
5.	Next, you are presented with a field selection screen. From this screen, you can select which fields are output to your report. Select a field by highlighting it with your mouse, and then use the Next button to include it in the report. In the example, I selected the Employment Status field, as shown in Figure 22.15. Figure 22.15. The Microsoft Access Report Wizard field selection window enables you to specify which fields you want to include in your report output.
6.	After you click Next, the Report Wizard asks whether you want to add any grouping levels to your report. This is a helpful step when you are creating a report where you might want to group and subtotal portions of the output. For this example, you don’t need grouping or subtotaling, so click the Next button to continue.
7.	Now identify your sort order criteria. In the example, shown in Figure 22.16, I have sorted according to Employee Name. Figure 22.16. The Microsoft Access Report Wizard enables you to select multiple sorting criteria.
8.	The Report Wizard enables you to specify formatting criteria. The orientation of the report (portrait or landscape) and the layout of the report (columnar, tabular, or justified) are designated on this screen. After making a selection, click Next.
9.	You can choose from a selection of predefined formats for your report. After making a selection, click Next.
10.	The last step asks you to type a name for your report. Do so, and click Finish to complete the creation of your report.

Microsoft Access is a great reporting tool that enables users with minimal Microsoft Access skills to create reports. Using Access, you can also include graphics in your reports, or you can create graphs and charts of your SAP data. If you take a few minutes to investigate the types of reports you can create using Microsoft Access, I’m sure you will discover the value of this reporting tool for SAP.