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.

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.

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.

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

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.

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.

These posts might also be interesting:

• Analysis for Office SAPInsertLine
• Define default Workbook in Analysis for Office
• Analysis for Office Data Analysis Advanced Calculations
• Rename and delete an Analysis for Office Workbook

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.