MS Excel-Specific Formatting Options 

Use

All of the formatting options described here refer exclusively to planning layouts for which you have chosen Microsoft Excel as the display tool. These options are not available for the display with the SAP List Viewer.

You can use the following formatting options:

·        Formatting Cells

·        Controlling Column Width

·        Moving Cells and Creating New Ones

·        Creating New Sheets

·        Influencing System Behavior with Macros

Prerequisites

Microsoft Excel display tool

In the second editing step you selected Microsoft Excel as the display tool.

MS Excel certificate

The macros in the central layout template for the SAP MS Excel master template are certified. To be able to use the certificate you have to accept it.  You only have to do this once. When you open an MS Excel document (in the Layout Builder, in the third processing step, for example, or when you start manual planning) choose the option Always trust macros from this source and then Enable Macros in the Security Warningdialog box. You will not be asked again whether macros should be executed.

If you are using the certificated templates with MS Excel97 problems occur because of a Microsoft error. The functionality of the certificate is not recognized in this version. This causes a visual basic error to occur when you execute a layout in manual planning. You can find more information on resolving this problem in SAP Note 611090.

If you are using your own template instead of the central layout template delivered by SAP, the certificate for the SAP MS Excel master template is still valid as long as no changes are made to the macros. All newly created layouts are also certified as copies of the certified template. 

The SAP certificate is no longer valid if you change or insert macros in your own planning layouts or in the template.  In this case SAP recommends that you purchase the license for an appropriate certification software and certify the Excel files for your enterprise.  You can find more information on certificates for MS Excel documents on the Internet on the Microsoft homepage.

Functions

Formatting cells

For every planning layout that you create using Microsoft Excel, BW-BPS administers a list of styles. Every style represents a complete record of predefined formatting settings (for example font settings, colors, alignment, frames) for a certain cell range. Using this function you can use all the formatting possibilities in the planning layouts which Microsoft Excel also offers in stand alone operation.

The possibilities offered by Microsoft Excel for the presentation of decimal places and units are overwritten in planning layouts by the appropriate settings. For more information, see Configuring Data Columns.

The styles are contained in the SAP MS Excel master template layout template which is stored centrally. This serves as a copy template when you are creating a new planning layout. It also contains two predefined macros that you can use for special purposes (see below in the section “Influencing System Behavior with Macros”).

You can decide whether one of your desired changes is to apply to one concrete planning layout or whether the change is to be adopted system-wide in all planning layouts that are created in the future (such as inserting the company logo in the layout). For more information, see Editing Styles and Editing Layout Templates.

The system generates a style for the following layout areas:

·        Header area

·        Column title for lead column

·        Column title for data columns

·        Values of lead column (not ready for input)

·        Values of data columns (not ready for input)

·        Ready for input cells

·        Two different subtotal rows/hierarchy levels

·        Totals rows/upper most hierarchy level

In addition, you can define further styles and assign certain cell ranges, for example in order to format every data column individually. If subtotals are to be displayed the subtotal format is prioritized in the columns. Subsequently, formatting is automatically continued with the style for the columns.

Controlling column width

Using the  Column Width function you can determine if the width of the lead or data columns is to be automatically fitted to accommodate the largest value or the text length of the column heading. If this accommodation is active (default) column values and headings are always visible in their full length. As this can lead to columns becoming extremely wide you are also able to deactivate this function.

SAP recommends that you deactivate the automatic adjustment of column width if a column with a long heading is not followed by other columns. In this case the text is automatically distributed over the available space; the column width itself remains unchanged.

Exactly how the text is distributed and displayed is determined by the underlying MS Excel functionality. The BW-BPS function described here serves simply to activate or deactivate this functionality in general.

This setting always affects all the columns in a layout (independent of whether certain columns are selected or not). You cannot determine the width of individual columns.

Moving cells and creating new ones

You can move or regroup the automatically generated cell ranges. You can also create cells with your own content (for example with formulas that refer to the plan data and perform further calculations). Note the following points:

·        BW-BPS manages the automatically generated cells in several related, named cell ranges and these may only be moved in one piece. If you happen to break up such a cell range by moving individual cells, BW-BPS will rearrange the range if necessary and undo the changes you have made. The following elements of the layout are managed as related cell ranges:

¡        Each row in the header area

¡        Each column heading

¡        Each column including the cell that belongs to the totals row If both the technical name and a name or attribute are also given for the lead column these appear in abutting columns and are treated as one cell range.

When moving cell ranges, you must only use the Windows standard functions for cutting, copying, and pasting of selected ranges (Ctrl+X and Ctrl+V or Shift-Del and Shift-Ins). You can also use the mouse to move selected cell ranges to different locations (Drag&Drop). If you use different procedures there is a danger that the relationship between the column heading and the columns that belong with it will be distorted or lost. The same applies to a named range and the range where the data is actually stored. In such a case, the planning layout cannot be used and it must either be restored or newly constructed.

If you have made changes in the layout by mistake and these could damage the relationship between the cell ranges, you can return the layout to the one which BW-BPS had generated as a default setting using the  Restore Standard Positions function.

·        When you open the planning layout for data entry, BW-BPS automatically generates 20 new blank rows under the last row where data was entered. If you have placed anything in these rows the content is overwritten by the blank lines without any warning.

If you have entered content below the data entry range, you can protect it from accidental deletion by inserting a sufficient number of blank rows between the data entry area and your content and then hiding these blank rows. This way, your content appears close to the data entry area but cannot be overwritten by automatically generated data entry rows.

When BW-BPS opens the planning layout for data entry, the plan data is inserted in the positions that you defined when creating the layout. When saving the data is read from the same positions without further checks and is written to the data basis. Therefore avoid executing actions in data entry that change the positions of the cell ranges in the layout (inserting or deleting rows or columns or sorting columns).

Creating new sheets

For each planning layout you create, BW-BPS generates a Microsoft Excel workbook with exactly one sheet. This sheet is filled with the various layout areas according to your settings. This standard worksheet is called SEM-BPS 1. If you need additional complex calculations, want to create detailed comments or want to save other data related to the plan data, you can create new worksheets in the workbook and use them as you wish. Note the following:

·        The data transfer between the data basis and the planning layout always refers to the standard worksheet SEM-BPS 1. Changes that you make in this worksheet are written in the data basis when saving, but are also overwritten when reading data again from the data basis. On the other hand, all data in further worksheets created by you is not taken into consideration when saving, however, it remains unchanged when reading again from the data basis.

·        In order to save data that you have calculated or changed in additional worksheets in the data basis, it is therefore necessary that this data is transferred to the standard worksheet SEM-BPS 1 immediately before saving (and after reading from the data basis from there to the worksheet that you have created). You can achieve this by using macros to ensure that whenever data is exchanged between the system and the worksheet, the available worksheets contain the data required according to the named request.

Influencing system behavior with macros

You have the facility to influence the system behavior in the context of Microsoft Excel whenever data is exchanged between the workbook and the system. You do this by running the macros you have created for this purpose. Using the  Excel Settings function you can determine if further operations are to be carried out when data is exchanged. You can plan operations for the following times:

·        The time just after BW-BPS has inserted data from the data basis into the layout (predefined macro name: Modul1.SAPAfterDataPut)

·        The time just before BW-BPS transfers data from the layout into the data basis (predefined macro name: Modul1.SAPBeforeDataGet)

Every Microsoft Excel template for a planning layout already contains the macros SAPBeforeDataPut and SAPBeforeDataGet in module Modul1. With the  Excel Settings function you can determine which of the two macros is to be called by the system. Implement your macros, preferably in a module of your own, and insert just one call up of your macros in the specified places in SAPBeforeDataPut or SAPBeforeDataGet.

The macros that you can activate are started only at the specified times and then only in conjunction with the functions Display Plan Data or Enter Plan Data (however, not with Display Layout or Change Layout). Test your macros in the VBA development environment.

Example

Here is an application of the "Influencing system behavior with macros" facility described above:

You want to carry out special formatting so as to adhere to your enterprise guidelines for designing forms. One specification is that blank spaces are to be inserted in certain places in order to improve clarity. This cannot be achieved with an automatically generated layout. You also want to use formulas in a spreadsheet to further process the plan data presented in the layout.

Therefore, you decide to move the presentation of the plan data to a new worksheet that you have inserted. You need a solution for the requests described above in the section "Creating new sheets". Proceed as follows:

...

       1.      Ensure that the layout generated by BW-BPS contains all the data that you require for further processing.

       2.      Insert a new worksheet.

       3.      Activate the macros for both times using  Excel Settings.

       4.      Choose Tools  → Macro → Visual Basic Editor. Open the predefined VBA module Modul1.

       5.      Insert your code in the SAPAfterDataPut macro where indicated. When this macro is executed your code ensures that the plan data taken from the data basis is transferred from the automatically generated layout SEM-BPS 1 into the worksheet you have created. It positions the data according to your requirements, formats it and uses predefined (or newly created) formulas to calculate it.

If the macro become too complex due to your code, you can share the required functionality with other macros that you create and then call them up from the predefined macro. Note that your own module cannot begin with SAP_.

       6.      Enter SAPBeforeDataGet statements in the predefined macro that copies the plan data from the worksheet you created back into the SEM-BPS 1 worksheet with the automatically generated layout. This allows them to be transferred into the database again from there.