Layouts Types in SAP Business Warehousing Continued

Totals and Automatic Calculation

Totals can be used in data columns and also in rows provided that you have chosen the type "key figures in data columns, rows individually defined" or the type "key figures in lead columns". When defining totals you can either list every column individually (e.g. C(1)+C(2)+…) or use ranges (e.g. C(1):C(3) which is equivalent to C(1)+C(2)+C(3)). The calculation for rows works accordingly. You can use this feature for example for calculating values for quarters i.e. totals over 3 periods .

You can use totals in totals. For calculating the grand total over the year you can sum up the totals over the quarters instead of summing up all the periods.Totals can be used for dynamic columns as well. If say the dynamic column in column 2 you can just write C(2). When the system replaces the dynamic column by the actual number of column then the system will also adapt the formula. If say the dynamic column is replaced by three columns then the system will replace C(2) by C(2)+C(3)+C(4) in every formula. All formulas that use columns "behind" dynamic columns (in our example a formula like C(3)) will be adopted accordingly (C(3) would be replaced by C(5) in all formulas).

Automatic Calculations in Excel

When using Microsoft Excel in the GUI you can use the automatic calculation functionality. It works for user defined totals (in columns or user defined rows) as well as for the totals in rows (set on the last screen of the layout builder). The system uses Excel macros for these calculations. The calculations are done as soon as the user enters a new value – the user does not have to press any button.

Automatic Aggregation: When changing a value in a non-total cell the system will calculate all totals in the layout automatically. Automatic Disaggregation: The totals are input on. When a user enters a value on a total the system will automatically distribute this value. There are different ways to determine the distribution key:

Same – use the current distribution as a distribution key
Equal" – distribute evenly use a reference column within the layout for the distribution key (refers to the planning function "distribute with reference data") If the layout uses totals rows and columns you can specify whether the values are first distributed to the rows and then to the columns or vice versa.You can lock (and unlock) cells against the automatic changes. If, for example, you have a layout with production data in 12 columns. You know exactly that you cannot go beyond the value you have already entered for say July as most of the workers are on annual leave.

Documents in Layouts

In SAP BW you can store documents to a characteristics (and Key figure) combination. Using such documents you comment single plan values directly in Manual Planning.For using this feature you have to mark all relevant characteristics as "document enabled" in the administrator workbench (flag "characteristic is document attr."). On the third screen of the layout builder you have to set the flag "use documents". You can create, change or delete documents of type Word, Excel, PowerPoint or pure text. If there is a document available for a cell in Excel then there is a small shape that can be clicked and that launches the document or a list of all available documents if there is more than one.

In SAP ALV every cell that contains a document is marked in blue.There are delivered Exit functions that can be used to copy documents and to delete documents. Have a look at function group UPFX.The system also uses the buffering mechanism for documents. When using Manual Planning in the Web (Web Interfaces) you can only create, change delete or view pure text documents. Any Word, Excel or PowerPoint document is not visible in the Web Interface.The document storage it the Knowledge Warehouse. Thus you can use the same documents throughout the entire BW including BW-BPS.

Documents in Planning Folders

The documents in layouts can be used in planning folders as well.In SAP ALV (in folders as well as in the framework) cells that have a document attaches have a blue background color. Documents can be created, changed and deleted.Additionally you can create an detail application "documents" in the framework that shows all documents within the package selection as a list. This feature can also be used in planning folders in the output area of a planning folder with input/output area. Documents can only be displayed when using this feature.

Special Design Features in Excel

When using Microsoft Excel for the layout you have additional design options that you can configure on the third screen of the layout builder.Within the areas in the layout the system creates ranges. These ranges are "named ranges" in the Excel and can be:

formatted individually using either one of the predefined styles or a new style,freely positioned on the Excel sheet (make sure that they do not overlap at execution time – e.g. when using dynamic columns the number of columns can change).

Each row in the header, each heading and each column (data column or key column) is a range. Range can only be moved as a whole. If for a characteristic in the key column you use text/description or description/text then the range is two Excel columns wide, otherwise it is only one column wide.The BPS used predefined delivered styles for formatting the ranges. For each range you can use one of these standard styles, you can modify the standard styles or create new styles. You simply format the entire range using the desired style. The system will read and store the name of the style.

There are some elements in the screen which used fixed style – you cannot set another style but you might modify these predefined styles. These elements/styles are:

every cell that is input on will be formatted with the style "SEM-BPS-input-on"
the grand total will be formatted with the style "SEM-BPS-total"
all subtotals (or sub-nodes in a hierarchy) will be formatted with the styles "SEM-BPS-sub1" or "SEM

Master Template and Data Storage in Layouts

When you create a layout that is using Excel then the system gets a physical copy from a "master template" and saves the Excel as a file in the BDS (Business Document Server) after you have done changes in the layout builder. Thus the BPS uses two different storage locations for changes you do in the third screen of the layout builder:

BPS Database tables: positions of the ranges, names of the used styles
BDS (Excel file): background, formulas, definition of styles, additional sheets, macros, …

If you do not like the delivered standards in the master template you can import your own master template (so-called custom master). The best way to do is to get a copy of the delivered SAP master template (report UPP_GET_MASTER_COPY), modify the Excel sheet (e.g. change the background, include a logo etc.), and check the file in as a custom master (report UPP_MASTER_CHECKIN). The system will not delete the delivered master. If you want to go back to the delivered master template run the report UPP_DELETE_CUTOM_MASTER. When creating a new master template make sure that the styles with the predefined names are still available.

When importing a new master template then all layouts that are created after that will have the new appearance. All older layouts will still have the "old" look. If you want to change this appearance you can run report UPP_LAYOUTS_UPDATE_WITH_MASTER. This report exchanges the saved Excel file in all selected layouts with the current master template. Changes in the files itself (like additional sheets, macros etc. ) will be lost.Remember that the Excel template is stored as a file on the server but has to be transferred and started on the PC when executing a layout. The larger the Excel file is (e.g. large pictures) the more time is lost for transferring the file to the PC and for starting it. So keep the Excel sheets at a reasonable size. In order to speed up the transfer process the system will store the Excel file in the temporary directory on the PC such that the Excel file only has to be transferred once.