Table Design

[former_member192410]

TABLE  DESIGN

 

Table Design is a powerful feature in SAP Analysis for Microsoft Office. It brings more flexibility in designing and editing the crosstabs and enables formatting and layouting reports while keeping the ability for OLAP navigation. The changes made using Table Design persist after navigation steps that force a rebuild of the crosstab, such as a refresh or swapping axes.

The following Table Design options are available to edit the crosstabs:

• New lines


• Formats


• Formulas (also formulas to input-enabled planning data cells)


• Text

 

Every Table Design operation creates a rule to the Design Rules tab in the design panel, where you can edit them (exception: formulas added to planning data cells cannot be edited in the design panel).

 

New lines

With the Table Design feature, you can structure the report layout by adding new lines (i.e., rows and columns) into a crosstab, for example to visually separate report areas, in case you would like to have a separator between product data and result.

 



 

For the new lines you can define row height or column width, as well as apply formats.

 

Formats

You can assign predefined or custom created styles as format to single cells, rows and columns.

In this case possible options are available: standard Microsoft Excel cell styles, SAP cell styles and user-defined cell styles.

Custom style sets can be saved and shared with other users.

Please note: You cannot apply Table Design formats if Pause Refresh is active.

 



 

Depending on the selected cell, you can assign a different scope in the format dialog, for example, you have following options for:

 

Dimension Header:

• Dimension Header


• Dimension Header and All Members


• Dimension Members

 

On the picture below, you can see all scope options depending on selected cells.

 



 

 

 

Let us explain the options by an example of Dimension Header… For better understanding, the style will be selected to GOOD (green😞

• If we select only Dimension Header as scope, only the format for dimension will be changed.

 

 

 

• If we choose Dimension Header and All Members as scope, the format for the selected dimension as well as for all its members will be changed.

  

 

 

• If we select Dimension Members as scope, the format only for the selected dimension members will be changed.

  

 

 

Formulas

The newly inserted lines can more than just be used as layout elements: in new lines, you can use Excel syntax to define your own formulas or even complex calculations, for example including cells outside the crosstab or use Microsoft Excel reference functions (e.g. VlookUp).

You can apply formulas to whole lines by using the fill handling (autocomplete functionality)- this function works with relative references and absolute references ($).

It is also possible to a data cell of an input-ready planning data source in change mode.

 

Text

You can add texts to member cells of new inserted lines. Note that the text is assigned to the respective representation of the member. I.e., if you enter a text for the key representation and switch to text representation, the newly entered text is not visible anymore (but you can create a new text for text presentation again).

 

 

TABLE DESIGN RULES TAB

All existing Table Design modifications (rules) you can display and maintain on the Design Rules Tab in the design panel.

 



 

The rules are displayed per data source (RULES FOR) and rule type (RULE TYPE). There are 4 rule types: Format, Formula, Texts or New Lines. If you select a rule type, you will see how many of them are in your crosstab and can edit them directly from here.

The rule sequence can be easily changed by moving the rules up and down. It is very useful if you have multiple rules.

The rule on the top determines how the cell is displayed.

It is also possible to activate and deactivate the rules. If a rule is active or not applicable, this is shown by an indicator in the first column:

 



 



 

If you have a large report with many design rules, you will like to use the option Mark Rules Based on Crosstab Selection because the rule will be highlighted if you select a cell in the crosstab containing the rule.

Whereas the properties for formats and new lines are the same as in the corresponding dialogs for creating these rules, the properties for formulas offer more detailed options for redefining the formulas. Very helpful are here:

• Formula Location specifies which components are used to determine the cells in which the formula appears.

• Formula Definition: When you select a formula, the formula definition is directly displayed in the properties area. Please note, that you can only edit the members, the formula definition remains unchanged. You also see the location and the corresponding cell.

• Show Formulas in Crosstab: Using this feature, you can show and hide formulas in the crosstabs and also how all of them relate to each other. Showing formulas in the cells instead of their results can help you keep track of the data used in each formula and quickly check your calculations for errors.

 



 

Sometimes you need to use your created design rules for other data sources. This is possible with the feature “Copy All Rules To Clipboard”. Prerequisite is the same data presentation. After copying the rules, they are no longer connected. If you edit a rule, the copied one is independent from the source rule and can be changed individually.

All Table Design rules can also be deactivated or removed: You can select a rule and click on Deactivate  to deactivate it, if you don't want the rule to be applied temporarily. The deactivated rule has an icon displayed in front of the rule name. You can activate the rule again by clicking on (Deactivate Rule).

 



 

But if you don’t need a rule, you can remove it completely: It is possible to remove a single rule, but you can also remove all created rules using the multiselect option.

Just select the rule to remove and click on  (Delete Rule).

 



 

Sometimes it is needed to set the query back to the initial state. You can do this by choosing Reset Data Source. This option resets all navigational steps and takes the query back to the initial state and you can choose if you want to remove all available rules or if you want to keep them.

If the list of Rules is empty, a picture and the message text “No Rules Available“ will be shown.

 



 

Note:

• if you are using a release earlier than Analysis Office 2.6, not all mentioned features might be available


• for more information about all Table Design features, please refer to our product documentation.