Maximizing SAP and Third-Party Solution Reports by Using Access


Creating basic reports, charts, graphs, labels, and so on is easy, thanks to the Access Report Wizard. Using Access with SAP is especially helpful when you want to report on data from two different computer systems. Table 23.1, earlier in this chapter, includes sample data from an SAP HCM system. What if you wanted to create a report with data from SAP plus data from a separate third-party system, such as a building's security access system? You could do so if both systems shared a common key. The following sections explain how.

The Fictional Data Source for a Third-Party Database

Table 23.1, earlier in this chapter, showed the SAP HCM module report data used in the earlier example. Table 23.2 shows a report run from non-SAP third-party building security software, UCGI Access System. To create a report that contains data from SAP (for example, name and address) plus data from the security system (for example, access level and schedule access), you could use Access.

Table 23.2. UCGI Access System Data (Non-SAP Third-Party Technology)

pers num

access level

schedule access

date accesses approved

1254587

Level 1

Weekday

June 20, 1967

1254591

Level 2

Weekend

August 25, 1969

1254595

Level 3

Weekday

June 9, 2006

1254599

Level 1

Weekday

August 9, 1999

1254603

Level 3

Weekday

September 26, 1997

1254607

Level 4

Anytime

January 11, 2005

1254611

Level 1

Weekday

February 1, 2006

1254615

Level 2

Weekday

December 5, 2004


The key requirement for being able to create a report that contains fields from each of these separate databases within the same integrated report is that you have to have a unique common field between the two. You need a field that exists in both data sources, such as pers num, that you can use to link the two sources together. The following sections describe how to create a single Access report that contains integrated data from these two different computer systems.

Getting Third-Party Computer System Report Data into Access

Earlier in this chapter, I explained how to import SAP report data into Access. This example builds on that earlier one. You will be adding additional data to your existing Access database so that you can report on both within the same report. To get your third-party computer solution report data into your existing Access database, follow these steps:

1.

Open your third-party computer solution (whatever it may be) and download your report to an Excel file on your computer. Save the file (for example, as c:\UCGI_security_file.xls). After saving your report in Excel, close and exit your third-party solution.

2.

Launch Access and open the existing Access database you created earlier (that is, c:\my documents\database1.mdb). Click OK to continue to the main screen of your Access database, which should now contain at least one table (Employee Address Table, from your SAP system).

3.

To bring in your third-party computer system (for example, UCGI) report data that you saved in step 1, select File, Get External Data, Import.

4.

In the dialog box that appears, locate the file you saved in step 1 (for example, c:\UCGI_security_file.xls). Then click the Import button. The Import Spreadsheet Wizard appears, to walk you through the import of your third-party database data into Access.

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. The Import Spreadsheet Wizard appears, to walk you through the import of your SAP data into Access.

5.

Look at the list of worksheets on the wizard (for example, Sheet 1, Sheet 2, and so on). Select the worksheet that contains your third-party report data, and then click the Next button.

6.

In the next screen, select the check box to indicate that your data source contains column headers, and then click the Next button.

7.

The next screen has two options: You can either merge the data into an existing table or create a new table. Select the option Place It in a New Table, and then click the Next button.

8.

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.

9.

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 this basic example, allow Microsoft to create one for you, which is the default option. Click the Next button to proceed.

10.

Give your table a name (for example, UCGI Security Access Table) and then click the Finish button. A dialog box appears, indicating that your table was successfully imported.

11.

Click OK. If you had any errors in importing the data, you are alerted of that at this time. Your database now contains two separate tables: one called Employee Address Table and another called UCGI Security Access Table.

Creating an Access Query to Link Data from Two Different Computer Systems

This section explains how to combine data from two different tables (from two different database computer sources) into a single report. Using the fictional data sources in Tables 23.1 and Table 23.2 earlier in this chapter, you need to now create a single report that includes data from both, as shown in Table 23.3.

Table 23.3. Sample Combined Report: SAP HR Report of Associate Addresses and UCGI Access System Report of Associate Access Levels

pers num

first name

last name

street address 1

city

state

postal code

access level

schedule access

1254587

Jack

Shepard

1 Walkmon Lane

Wantagh

NY

12345

Level 1

Weekday

1254591

Kate

Lilly

16 Treaty Avenue

Weddington

NC

23456

Level 2

Weekend

1254595

Charlie

Pace

1423 George Washington St

San Diego

CA

34567

Level 3

Weekday

1254599

Hurley

Reyes

922 Overlook Mtn Way

Poplin Oats

OH

45678

Level 1

Weekday

1254603

Walt

Llyod

66 Ninety Tine Court

Chicago

IL

56789

Level 3

Weekday

1254607

Sayid

Jarrah

9453 Belmont Lane

Cortland

NJ

67890

Level 4

Anytime

1254611

John

Locke

345 86th Street

Winston

CT

78901

Level 1

Weekday

1254615

Michael

Dawson

25 Oceanview Ave

Easton

PA

89012

Level 2

Weekday


To create an Access query that links data from the two existing tables, follow these steps:

1.

Launch Access and open the Access database you created earlier (that is, c:\my documents\database1.mdb). Click OK to continue to the main screen of your Access database, which should now contain two tables (Employee Address Table, from your SAP system, and UCGI Security Access Table, from the third-party computer solution).

2.

Select the Query navigation tab on the left side of the window, and then click the New button on the Application toolbar.

3.

When the New Query dialog box appears, click OK. The dialog box changes to a box labeled Show Table that lists all the tables that exist in the database.

4.

Select each table and insert it into your query by clicking the table name and then clicking the Add button. After adding both tables, click the Close button. The screen should now look like the one shown in Figure 23.7.



Figure 23.7. Access links the two tables by the ID field because it has the same name in both tables.


5.

Notice that the two tables are linked with a line. Access assumes that because the two tables contain a field called ID, the data in them is the same. As in this situation, that may not always be the case. Select the line between the two fields and then press Delete to delete it.

6.

One field in the two tables contains the same data: the pers num field (refer to Tables 23.1 and 23.2). Therefore, you want to create a relationship between the two tables based on the pers num field. To do so, place your cursor on the pers num field in the first table and drag it to the pers num field in the second table. A line linking the two appears.

7.

Double-click the linking line to see the properties of the join, as shown in Figure 23.8. The default option on the Join Properties screen is the one you want, so click OK.

Figure 23.8. Two tables that contain the same unique value (in this case, pers num) can be joined.


8.

Now that the two tables are joined, select the individual fields from the specific tables you want to include in your query (report). You do so by selecting a field from a table at the top of the screen and dragging it to the bottom of the screen.

9.

To see the finished, combined data source, click the View button (see Figure 23.9). The finished query is shown in Figure 23.10. Click the Save button on the Application toolbar and then give your new query a name (for example, Query 1).

Figure 23.9. The Query Design view in Access lists each table and field name.


Figure 23.10. The Query Preview view of Access looks the same as a table view in Access.


Using the Access Report Wizard to Create a Report of SAP and Third-Party Query Data

To use a Microsoft Access wizard to create a report with your newly created query that contains data from your two imported tables (from SAP and from the third-party computer system, UCGI), follow these steps:

1.

Select the Reports link 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 query from the drop-down box at the bottom of the screen. Then click the OK button.

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 the pers num, first name, last name, access level, and schedule access fields, and then click the Next button.

4.

On the next screen, select a grouping level, if desired, and then click Next.

5.

On the next screen, indicate your sorting (ascending or descending) preference. For this example, sort on the pers num field (ascending) followed by the last name field (ascending). Then click the Next button.

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 styles for you to choose from for your report. By default, the Corporate format is selected, but you can preview each by selecting it. Select the Casual option and then click the Next button to change the title of the report. Then click the Finish button to see the finished report (see Figure 23.11).

Figure 23.11. Access reports created with the Report Wizard have special formatting and design based on the template selected (in this example, the Casual template).





SAP Query Reporting
SAP Query Reporting
ISBN: 0672329026
EAN: 2147483647
Year: 2006
Pages: 161

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net