Maximizing SAP Reports by Using Access

Access is a popular software solution used for all kinds of reporting. Access, like other Microsoft products, including Word, Excel, and Outlook, serves as a great complement to SAP R/3 reporting solutions. Access is a common solution used by many organizations that do not know how to create reports using the tools outlined in this book. Because Access is a standard software solution installed on many company computers, and because it is very easy for an end user who has no technical skills to use, it is commonly used for SAP reporting.

I have heard of organizations that run a daily or weekly download of information from their SAP solution, which they import into Access. They then do most of their reporting from Access. Although this may be helpful for organizations that do not know how to create reports in SAP, it does require some considerations, as outlined in the next section.

There are three general reasons why companies use Access in addition to their existing SAP reporting. The first is because Access and its wizards are very user-friendly; any user can follow along and create reports and so on. The second reason has to do with the volume of data: When working with data that is particularly large and cumbersome, it may be more time-efficient to work with that data outside the SAP environment, in Access. Finally, Access gives you the ability to cross-reference data from multiple systems and sources into a single report. An example of this is provided later in this chapter. All in all, Access can be a great complement to your existing SAP reporting solutions.

Considerations When Using Access for SAP Reporting

The dramatic difference between reporting directly within SAP and reporting using Access is based on two factors: security and timeliness. One of the most important considerations when using Access for reporting on SAP data is the appreciation that the data, once in Access, has no security limitations. I know this one sounds like a no-brainer, but sometimes a company does not realize the implications until the solution is in place. When the SAP reporting tools described in this book (and configured using the recommended guidelines) are used, security is not a concern. SAP security identifies who the user is and permits that user to see only the data that he or she is allowed to see (regardless of what type of access the person who created the report had). The concern with extracting your SAP data and placing it in Microsoft Access is that all data is downloaded, and any user can access it when it is outside the SAP R/3 environment.

The second consideration when using Access for SAP reporting is timeliness. This concern is the same with any non-SAP R/3 reporting solution, including Crystal Reports and SAP's Business Information Warehouse (BIW), where the reporting is first fed to a repository and then reported from. Reporting in this manner, as opposed to online reporting in the SAP Query tool, for example, does not occur in real time. In some cases, reporting does not need to be real-time, so a solution such as Crystal Reports, BIW, or Access would be a good fit.

Where tools such as BIW and Access are really useful is when you want to evaluate data from multiple sources. By using the SAP reporting tools described in this book, you can easily create thorough, detailed reports of data from your SAP R/3 system. However, if you want to report on data from your SAP R/3 solution plus data from an additional system (such as your distribution database), you can do so easily by using Access.

The Fictional SAP Report for a Basic Access Report

This fictional example uses a basic Human Capital Management (HCM) module report from SAP that contains information about associates, including their names, personnel numbers, and home mailing addresses. Table 23.1 shows a sample of the data in the fictional report.

Table 23.1. Sample SAP HCM Report of Associate Information

pers num	first name	last name	street address 1	city	state	postal code
1254587	Jack	Shepard	1 Walkmon Lane	Wantagh	NY	12345
1254591	Kate	Lilly	16 Treaty Avenue	Weddington	NC	23456
1254595	Charlie	Pace	1423 George Washington St	San Diego	CA	34567
1254599	Hurley	Reyes	922 Overlook Mtn Way	Poplin Oats	OH	45678
1254603	Walt	Llyod	66 Ninety Tine Court	Chicago	IL	56789
1254607	Sayid	Jarrah	9453 Belmont Lane	Cortland	NJ	67890
1254611	John	Locke	345 86th Street	Winston	CT	78901
1254615	Michael	Dawson	25 Oceanview Ave	Easton	PA	89012

Getting Your SAP Report Data into Access

To get SAP report data into Access, follow these steps:

1.	Open any report output screen that displays an SAP report. It can be an SAP query, a custom ABAP report, a standard SAP-delivered report, or any other format.
2.	You have multiple options for how to get your SAP data into Access, each of which varies depending on your installation version of SAP R/3 and your installation version of Access. Because there are multiple options for different versions, the menu path and buttons vary for each. It is a good idea to save your SAP report output in an Excel worksheet, which you will use as your mail merge data file. The most popular way to do this is to click the Excel button on your report output toolbar. Excel launches and displays the SAP report in an Excel worksheet. Next, save the report in Excel (for example, as c:\employee-data_file.xls). Close and exit SAP and Excel.
3.	Launch Access and create a new Access database by selecting the white page Create button on the Application toolbar and then selecting the link Create a Blank Database. A dialog box appears, asking you to name and save your database. Name and save your database, using a name such as c:\my documents\database1.mdb. Click OK to continue to the main screen of your newly created empty Access database.
4.	Import the SAP data into the empty Access database. To do so, select File, Get External Data, Import. A dialog box appears, giving you the opportunity to locate the file you saved in step 2.
5.	Select the file (for example, c:\employee-data_file.xls) and then click the Import button. The Import Spreadsheet Wizard appears, to walk you through the import of your SAP data into Access (see Figure 23.1). Figure 23.1. The Import Spreadsheet Wizard helps you import a data file by allowing you to simply pick and choose the information required. Helpful Hint By default, the Import dialog box looks for Access database files only. You need to change the File As Type box at the bottom of the dialog box to Excel Files in order for your file to appear.
6.	Look at the list of worksheets on the wizard (for example, Sheet 1, Sheet 2, and so on), and select the worksheet that contains your SAP data (depending on your installed version of SAP and your SAPGUI settings, it may be the last sheet in the list labeled Raw Data). Then click the Next button.
7.	In the next screen, select the check box to indicate that your data source contains column headers, and then click the Next button.
8.	The next screen has two options: You can either merge the data into an existing table or create a new table. Because this is an empty database that you created from scratch, select the option In a New Table and then click the Next button.
9.	On the next screen, specify information about each of the fields you are importing. For example, you can alter the column heading names and select to exclude some columns if you wish. Click the Next button to proceed.
10.	When the wizard prompts you to assign or select a primary key, do so. A primary key is a unique record in your data file that distinguishes all the records from each other. For basic SAP reporting, it is sufficient to allow Microsoft to create one for you, which is the default option (see Figure 23.2). Click the Next button to proceed. Figure 23.2. The Import Spreadsheet Wizard inserts a primary key at the start of your data file that contains a unique number that can be used to make a record in the report unique.
11.	Name your table (for example, Employee Address Table) and then click the Finish button. A dialog box appears, indicating that your table was successfully imported.
12.	Click OK. If you had any errors in importing the data, you are alerted of that at this time. Your database now contains one table called Employee Address Table, which you can view by double-clicking the table name listed on the screen. The table opens on the screen; it looks a good deal like an Excel worksheet, as shown in Figure 23.3.

Figure 23.3. This Access table and the one defined in Table 23.1 at the beginning of this chapter contain the same information.

Using the Access Report Wizard to Create a Report of SAP Data

One of the primary reasons that Access is a popular reporting complement to SAP is its availability to be used by casual users who have no technical skills. Access includes step-by-step wizards that can be used to easily create a variety of reports. To use the Access Report Wizard to create a report with your imported SAP data, follow these steps:

1.	Select the link Reports from the menu on the left side of the screen, and then click the New button on the Application toolbar. A New Report dialog box appears.
2.	In the New Report dialog box, select the option Report Wizard, and then select your table from the drop-down box at the bottom of the screen (see Figure 23.4). Then click the OK button. Figure 23.4. The Access Report Wizard makes it easy for end users with no technical training to create reports in Access.
3.	The first screen of the Access Report Wizard lists all the fields available in your table and gives you the option of adding some or all of these fields to your report by using the single selection (>) or all selections (>>) field indicators in the middle of the screen. Select all fields and then click the Next button.
4.	On the next screen, select grouping levels, if desired, and then click Next.
5.	On the next screen (see Figure 23.5), indicate your sorting (ascending or descending) preference. For this example, sort on the unique ID key (ascending) followed by the last name field (ascending). Then click the Next button. Figure 23.5. You have the option to sort in either ascending or descending order for up to four fields in this step of the wizard.
6.	On the next screen, specify the layout for your report. In this case, indicate that you want it to appear in landscape orientation and in tabular layout. Then click the Next button.
7.	The next screen displays at least six different predefined styles for you to choose from for your report. By default, the Corporate format is selected, but you can preview each by selecting it. Click the Next button, change the title of the report, and then click the Finish button to see the finished report (see Figure 23.6).

Figure 23.6. The selected formatting option enhances the look of the report with colors and fonts.

Besides the Report Wizard, Access also has wizards for creating charts, graphs, mailing labels, and so on. To see what other options are available for easy reporting, select the Reports link from the left side of the screen and then click the New button on the Application toolbar. The New Report dialog box appears, listing the various wizards.