There are many options in Microsoft Excel for formatting reports and queries downloaded from the SAP system. Applying (and re-applying) this formatting can become tiresome and repetitive for users who are required to regularly produce consistently formatted reports for wider publication. I demonstrate a simple way to save these fully formatted Excel templates within the SAP system and directly link them to the relevant reports and queries. This makes reproducing these reports on a regular basis easy.

System Prerequisites 

It goes without saying that Excel must be installed on your computer in order for the Excel Inplace view to work. Users might note slightly different system behavior depending on which versions of SAP and Excel are installed. Due to the many potential system variations it isn’t possible to cover every single combination in this article.

This article describes how to set up templates using Microsoft Office 2007. You might find there are minor differences in formatting, menu paths, and functionality if you’re using an earlier or a later version of Microsoft Office.

General instructions about how to work with SAP List Viewer (ALV) can be found here: https://help.sap.com/saphelp_470/helpdata/en/e3/41a138c4397a42e10000009b38f842/frameset.htm.

The following section gives detailed instructions on how to save custom Excel templates within your SAP ERP HCM reports.

Custom Templates

There are two different types of custom templates: client wide and layout specific. The difference between the two is that client-wide templates are available to all reports whereas layout-specific templates are restricted to one specific report.

To understand this better, let’s use the following business scenario. In this example, you are responsible for producing a number of regular printed reports in Excel that are exported from the SAP ERP HCM system. You would like to apply a consistent range of print settings to these exported reports, such as a standard company logo, header, footer, and page layout. To do this, you set up a client-wide template which is then used as the default template every time you produce a report from your SAP system. This ensures that when you print these reports they automatically have the same consistent look. Figure 1 shows a simple print layout formatted with standard company logo and heading data.

Figure 1

A client-wide template (Excel print preview showing company logo and header details)

Although you have created a custom client-wide template, you also know that one size doesn’t fit all and that some reports require additional format settings to be applied. For example, a report with a large number of columns needs to be printed in landscape orientation and the print size needs to be reduced to 90 percent so that it fits on one sheet of paper (in my example, a company birthday report). To achieve this, set up a layout-specific template and assign it to one specific report only (Figure 2).

Figure 2

A layout-specific template assigned to the birthday list report

Figure 2 shows such a report that has been run with a preformatted template that shows a custom header, page numbers, and company logo. This example is based on the SAP standard birthday list report. This report is accessed by menu path Human Resources > Personnel Management > Administration > Info system > Reports > Employee > Birthday List.

Step-by-Step Instructions for Setting Up New Excel Inplace View Templates

The following section shows how to create both client-wide and layout-specific Excel Inplace view templates. The same basic steps are followed for both types. A standard SAP template is downloaded. It is then customized, uploaded back into the SAP system, and then assigned to reports. Steps 1 through 4 are for client-wide templates, and steps 5 through 8 are for layout-specific templates.

Step 1. Download the Standard SAP Client-Wide Templates

To download a client-wide template you first access the SAP-standard templates by using a transaction called the Business Document Navigator. This allows you to create, change, copy, delete, and organize client-wide templates.

1. Navigate to the Business Document Navigator using transaction code OAOR.
2. Enter these values in the first box on the selection screen (Figure 3):
   • Class name: ALVLAYOUTTEMPLATES
   • Class type: OT
   • Object key: SAP_STANDARD_TEMPLATE
3. Click the execute icon.

Figure 3

Selection screen options for the Business Document Navigator

SAP delivers two standard templates for Excel in ALV: sap_om.xls and sap_mm.xls. It’s not a good idea to change the standard-delivered SAP objects as any changes would be overwritten by an upgrade. Instead, use these standard objects as the basis for your client-wide templates.

Select the SAP_om.xls template and click the download icon  to download the standard template document from the Business Document Navigator (Figure 4).

Figure 4

Select a standard Excel template in the business document navigator

A save-as dialog box is shown. Save the template to an easily-accessible local directory on your computer; for example, C:tempsap_om.xls.

Step 2. Customize the Standard SAP Client-Wide Templates 

The second step is to create a customized version of the template. In Excel, open the file you saved in the previous step. Before you make any changes to this file save it as a new workbook in the same directory with a name beginning with cus_, so that ALV recognizes it as a custom template; for example, C:tempcus_mytemplate.xls.

Once you open the file (Figure 5), you see that SAP has set up this template to split the elements of the report into different worksheets (e.g., Format, Header, Pivot, and RawData). At this point the template is completely blank so you can’t see what data or formatting ends up on each worksheet once the data has been exported from the SAP ERP HCM report.

Figure 5

Worksheets in the standard SAP template sap_om.xls

Here are three of the most important worksheets:  

• Format: This worksheet shows all the report data with formatting (e.g., subtotals or colors)
• Sub1 to Sub10: These worksheets display up to 10 levels of subtotals if these have been set up in the SAP ERP HCM report display variant
• RawData: This worksheet shows the report data without formatting (e.g., subtotals)

Within Excel you can now modify this template with your own formatting requirements. (I don’t go into more detailed instructions here as this article assumes you have a working knowledge of Excel and can modify a worksheet.)

Here are examples of three typical changes that users make:

• Hide the worksheets that you don’t want to be seen (e.g., Sub1 to Sub10 and Pivot)
• Set the appropriate print margins
• Add headers, footers, logos, images, dates, title, or page numbers, as required (Figure 6). (To do this, select the Format worksheet. Next, on the toolbar, Click View > Page Layout to get the print preview.)

Figure 6

A simple template with a custom header in the page layout view of Excel

Once you have added all your format settings, change the view back to the normal view and save your newly-customized template.

Step 3. Upload Custom Client-Wide Templates

To upload the template as a client-wide template follow these steps:

1. Navigate to the ABAP editor using transaction code SE38 or SA38 and start the upload report BCALV_BDS_UPLOAD.
2. On the selection screen, enter the Language and select the CUS STANDARD TEMPLATE radio button (Figure 7).

Figure 7

Selection screen for upload report BCALV_BDS_UPLOAD

3. Click the enter icon .
4. A file-open dialog box opens. Enter the path where your template is stored (e.g., C:tempcus_mytemplate.xls in my example, created in step 2) and click the Open button.
5. The report confirms the status by displaying the message Upload Successful.

Step 4. Assign Custom Client-Wide Templates to SAP ERP HCM Reports

Any report that displays the output using ALV can be used. This is the case for the majority of SAP ERP HCM and payroll reports and SAP queries. Custom-developed reports can also be coded to use this interface using fairly simple function modules or object-oriented methods and classes that are documented elsewhere. For example, to see a SAP standard demonstration report use transaction code SE38 or SA38 and view the sample code in report BCALV_TEMPLATE.

Make sure that the Output format SAP List Viewer radio button is selected on the selection screen of your report (Figure 8).

Figure 8

Select the SAP List Viewer output format option

Run the report as normal by choosing your selection options and clicking the execute icon. When the report has displayed the output, click the change layout icon .

The Change Layout dialog box is shown (Figure 9). Select the View tab. The usual default for the Preferred View field is SAP List Viewer. In the drop-down list change this to Microsoft Excel.

Figure 9

Selecting a custom template in the Change Layout view

Select the line for the custom template (cus_mytemplate.xls in my example) and click the determine default template icon . Then click the transfer icon  or press Enter. The report is now displayed in the SAP system using the Excel Inplace view with the formatting in your custom template (Figure 10).

Figure 10

Excel Inplace view of a report

Some of the formatting may not be visible yet. Save the file locally using Windows button > Save as > Excel Workbook, and the headers, logo, and any other custom formatting is visible when you print the workbook. See Figure 1 for a print preview of this document.

Instead of creating a client-wide template you may want to make a template available for one specific report. Follow steps 5 through 8 to create a layout-specific template. The screenprints are based on the example of setting up a custom template for the birthday list report. This report is accessed by following menu path Human Resources > Personnel Management > Administration > Info system > Reports > Employee > Birthday List.

Step 5. Download the Standard SAP Layout-Specific Templates 

Repeat step 1 to download and save a standard template from the Business Document Service (BDS) to an easily accessible local directory on your PC; for example, C:tempcus_my_template.xls.

Step 6. Customize the Standard SAP Layout-Specific Templates 

In Excel, open the file you saved in the previous step. Before you make any changes to this file save it as a new workbook in the same directory with a different naming convention to distinguish it from the client-wide templates; for example, C:tempz_birthday_template.xls. (Note that it can’t begin with SAP-* or cus_*.)

In Excel add the specific formatting changes that you want for this report. Typically the types of changes you would make are:

• Add custom header or footer for this report only
• Change the page orientation (to portrait or landscape)
• Change the zoom level
• Set the print area
• Hide the ruler, formula bar, grid lines, Excel rows, or column headings
• Minimize the ribbon

• Change to View > Page Layout so that the headers and logo appear in the Excel Inplace view within SAP (as in Figure 2)

Step 7. Upload Custom Layout-Specific Templates

Run the report as normal by choosing your selection options and clicking the execute icon. When the report has displayed the output in ALV, click the change layout icon.

The Change Layout dialog box is shown (Figure 11). Select the View tab. The usual default for the Preferred View field is SAP List Viewer. In the drop-down list change this to Microsoft Excel. On the View tab of the Change Layout dialog box, click the upload icon  to upload the template back into ALV.

Figure 11

Selecting a layout-specific template in the Change Layout view

Click the transfer icon or press Enter. The report is now displayed in the SAP system using the Excel Inplace view with the formatting from your custom template.

Step 8. Assign Layout-Specific Templates

Follow these steps to save the layout-specific variant.

As in the previous steps, first run the report as normal by choosing your selection options and clicking the execute icon. When the report has displayed the output in ALV click the save layout icon  to save the changed layout for future use.

Note
It is important to remember that this customization only needs to be done once and then can be saved as a variant. This means that in the future these reports can be run and will show the outputs in the same format without the need for further intervention.

A Save layout dialog box is shown. Give your customized layout a title and description (Figure 12).

Figure 12

Saving a customized layout

Finally, return to the selection screen of the report by clicking the back arrow. Add the layout name to the selection screen in the SAP List Viewer field (/Z_XL_INVIEW). Save this as a report variant for future use by clicking the save icon in the toolbar and giving the variant a name and description (Figure 13).

Figure 13

Saving a layout within a variant

Common Issues with Customizing Templates

There are two simple fixes that resolve the most common problems with the Excel Inplace view. Following are some examples of these issues and how you can fix them.

Blank Screen Display

One common issue is that Excel Inplace displays a blank screen. This can be caused by the default Excel security settings preventing the Excel Inplace view from working correctly and displaying data. In this case, do the following to resolve the problem:

• Click the Microsoft Office button and choose Excel Options
• Choose Trust Center and then click the Trust Center settings button
• Click the Macro Settings button
• Select the Disable all macros except digitally signed macros radio button and check the Trust access to the VBA project object model check box (Figure 14)

Figure 14

Excel Trust Center settings

Faulty Integration Between SAP and Desktop Office

Sometimes there is a problem with the installation and integration between SAP and Microsoft Office products. SAP provides a macro-enabled workbook that checks the integration, and displays any issues plus other useful information regarding settings, file paths, and registry entries. This Excel workbook is called doi_xls_check.xls and is usually found in the directory where the SAP GUI is installed (e.g., C:ProgramFiles(x86)SAPFrontEndSAPguiTesttools). Note that this exact path varies depending on your particular installation and operating system. While this step doesn’t resolve any issues directly, analyzing the output produced by the utility should indicate if there are problems with the integration that can be resolved by reinstallation, security, or tweaking the registry settings.

If any security warnings appear, you first must open the workbook and make sure that you have enabled macros to run before you can run this macro. Then run the macro by selecting menu path View > Macro > View Macros, selecting the macro that ends with CheckSettings, and clicking the Run button. The macro runs and creates a worksheet that shows all the integration settings data. (If more detailed instructions are required follow this link doi_xls_check.xls or see OSS Note 159499.)

Custom Template Limitations 

The first limitation to be noted is that ALV only allows templates to be stored in the Excel 97–2003 compatible workbook format (.xls), and not in the later, more format-rich, Excel 2007/2010 format (.xlsx). This means that some of the formatting functionality available in these later Excel versions can’t be stored in templates.

The second and more frustrating limitation is that any formatting applied to the main body of the spreadsheet (such as different fonts, font sizes, colors, or specific cell formats such as currencies and times) are not transferred from the template. Some of these shortcomings can be overcome by incorporating macros within the spreadsheet template. The details of how to achieve this are beyond the scope of this article.

Note
Here is a link to a helpful video about ALV functionality: 

Owen McGivney

Owen McGivney is a senior consultant at iProCon Ltd., part of the iProCon group, based in London, England. He has worked on implementing SAP HR and payroll systems since 1998. Owen has delivered UK, Irish, and multi-national payroll solutions for a wide range of private- and public-sector clients. He has a special interest in combining ABAP programming with configuration to create innovative and effective solutions.

You may contact the author at o.mcgivney@iprocon.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.