· 

Analysis for Office Table Design, API and Design Rules

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

SAP Analysis for Office Table Design
SAP Analysis for Office Table Design

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

 

Now in Analysis for Office 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 for 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.

SAP Analysis for Office Design Panel
SAP Analysis for 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.

SAP Analysis for Office Design Rules Ruletype Text
SAP Analysis for Office Design Rules Ruletype Text

The style is an ID that is unique for each rule. The description is automatically set by the Analysis for 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.

SAP Analysis for Office Design Rules Move Rule
SAP Analysis for 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.

author.


I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.

SAP Analysis for Office - The Comprehensive Guide
The book SAP Analysis for Office - The Comprehensive Guide by Tobias Meyer is a pdf book about SAP Analysis for Office. It is based on Analysis for Office 2.8 and contains 346 Pages.
45,00 €
SAP Analysis for Office - The Comprehensive Guide
SAP Analysis for Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis for Office. It is based on Analysis for Office 2.7 and contains 299 Pages.
37,00 €
SAP Analysis for Office - The Comprehensive Guide
SAP Analysis for Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis for Office. It is based on Analysis for Office 2.6 and contains 272 Pages.
27,00 €

Write a comment

Comments: 0