Analysis Office Table Design, API and Design Rules

Since Analysis Office 2.0 you have the option Table Design. You can now create new empty rows and columns.

Analysis Office Table Design
Analysis Office Table Design

So you were able to use own Excel formulas in a Crosstab. But in Analysis Office 2.0 the formulas disappeared when you refreshed a query. Since AO 2.2 the Excel formulas survive a refresh. This is a really nice function. You can read this in the post "Analysis Office: How to work with Excel formulas".


Now in AO 2.2 SP3 the Table Design API got new rather advanced features like SAPSetText or SAPInsertLine. SAPInsertLine has now two new options for the parameter PositionBy:

  • DimensionGroup
  • Tuple

With SAPInsertLine you can add a new row or column via VBA. For Example:

Dim RuleId as String

RuleId = Application.Run("SAPInsertLine", "NewLine1", "DS_1", "After", "Dimension", "0CALYEAR")

With this command you add a new columns after the dimension 0CALYEAR. Be careful the Help file declares lResult as Long but this is wrong! To explain all SAPInsertLine parameter I will write an own post.


You can use SAPSetText to add texts to inserted cells. You can also rename measures in the crosstab. At the moment I haven't got any more information about this function. In Analysis Office 2.3 you get a new feature, called Design Rules tab. You find the Design Rules on the 4th tab of the Design Panel.

Analysis Office Design Panel
Analysis Office Design Panel

On the Design Rules tab you can see and edit the rules created with Table Design. The rules are displayed per DataSource and rule type. First you select the DataSource and then the rule type. You can actually choose between four rule types:

  • Format
  • Formula
  • Text
  • New Lines

The accessible rule types are displayed in a Dropdown Box for the selected DataSource. The first column show you if the rule is active or deactivated. If you see a square the rule is active, if you see a diamond the rule is deactivated. If you deactivate a NewLine-Ruletype the line disappear from the table design.

Analysis Office Design Rules Active Rule
Active Rule
Analysis Office Design Rules Deactivate Rule
Deactivate Rule

The value column show for example the content of a text rule.

Analysis Office Design Rules Ruletype Text
Analysis Office Design Rules Ruletype Text

The style is an ID that is unique for each rule. The description is automatically set by the Analysis Office for formats, formulas and texts. NewLines haven't got a description.


The priority of a rule can be changed by the arrows above the list. You can easily move the rules up and down.

Analysis Office Design Rules Move Rule
Analysis Office Design Rules Move Rule

You can edit only formats and new line-rules directly from the Design Rules tab. The other rules can only be deleted, activate or deactivate in the Design Rules tab.


Note: You can only edit new line rules after you change the height of a new line once manually.


If you have a large report with many design rules you love the feature "Keep selection in synch with crosstab selection" because if you select a cell in the crosstab, the associated rule is highlighted in the text.


Note: It is highlighted only if you are in the right list. For example if you click on a formula, but your list is on new line, nothing happened.


If you want to remove all Table Design modifications, switch to the components tab and choose Reset Data Source in the context menu.

Analysis Office - The Comprehensive Guide
Analysis Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis Office. It is based on Analysis Office 2.3 and contains 227 Pages.
29,99 € 1
Analysis Office - The Comprehensive Guide 2nd edit
Analysis Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis Office. It is based on Analysis Office 2.4 and contains 243 pages.
39,99 € 1

Write a comment

Comments: 0