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:
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:
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.