Business Intelligence

How to Group and Ungroup Reports with SAP Analysis for Microsoft Office

In SAP Analysis for Microsoft Office, not only can you link individual dimensions, you can also group multiple reports so that they look like one report.

 

You can navigate in the merged report as you would in a normal report. This function opens up new possibilities in data analysis.

 

When you group two reports together, these reports are merged in the workbook so that no empty space exists between the two reports. The row axis is the common axis of the grouped report. The crosstab from which you execute the grouping serves as the master table and determines the structure of the common report. The dependent table provides the additional axes.

 

Note that only the rows can form a common axis. For this reason, the crosstabs must have similar row structures so that the grouping can be carried out meaningfully. You can group several reports, but at least two reports will be required.

 

The figure below shows an example of a merged report. This report is visually indistinguishable from an ordinary report but has some limitations in its functionality.

 

Grouped Report

 

You can add new dimensions or remove existing dimensions from the drilldown report only in the master table. Filtering dimensions is also only possible in the master table.

 

Furthermore, you cannot add new lines to input-ready crosstabs. Unfortunately, you cannot insert diagrams on the basis of the grouped crosstabs. If you’ve created conditional formatting rules for your dependent crosstab, this formatting won’t be displayed in the common axis. In addition, you cannot link reports across systems or using different dimensions. For example, if the first crosstab contains a particular dimension and the second crosstab contains the same InfoObject as a navigation attribute with a different technical name, no link is possible. You cannot link column axes either.

 

After grouping, the dependent tables adopt the settings of the master table. If you adjust the settings in the master table, they automatically apply to all dependent crosstabs. The respective navigational state is also adopted.

 

If, for example, you select the display option Compact Display in Rows in the master table, this formatting is also applied to the dependent crosstabs. These settings are retained even after the grouping has been removed.

 

Now, let’s walk through grouping and ungrouping reports.

 

Grouping Reports

Let’s demonstrate the grouping function using an example. As shown in the figure below, the workbook has two reports: one for the quantity sold and one for the sales of the relevant product groups by company code. While the key figures in the reports are different, the dimensions in the row axes are identical. Therefore, these reports are perfect for combining into one report.

 

Two Separate Reports

 

To merge these reports, first select a cell in the report that is to serve as master. Then, in the Build menu group of the Analysis Design tab, go to the Combine dropdown list, click on Group Crosstab, and select the crosstab you want to append, as shown here:

 

Group Crosstab

 

Alternatively, you can also use the context menu. Select the Group Crosstab option and select the crosstable with which you want to group the already selected crosstab, as shown in this figure:

 

Group Crosstab via Context Menu

 

Finally, you can also choose the path via the Design Panel. First, go to the Components tab and select the desired crosstab. Then, select the Group Crosstab option from the context menu, as shown below.

 

Group Crosstab via the Design Panel

 

The crosstabs are now merged into a single report, as shown in the next figure, which can be used for data analysis as usual.

 

All crosstabs have a name, such as SAPCrosstab1, SAPCrosstab2, and so on, which you can use in your Visual Basic for Applications (VBA) macros. The name of the grouped crosstable is then SAPContainer1.

 

Grouped Report

 

Under the Components tab of the Design Panel, the crosstabs are marked with different icons, as shown in the next figure. The master table is marked with the icon, while the dependent crosstabs are marked with the icon.

 

Grouped Crosstabs Highlighted with Different Icons 

The settings for the grouped crosstab can only be made via the master table and apply to all crosstabs in the merged report. No settings can be changed in the dependent crosstabs. As shown below, all options are grayed out.

 

Inability to Change Settings in the Dependent Crosstab

 

If you move the master table to a different position in the worksheet using the Move To option, the entire grouped crosstab is moved. The grouped table thus behaves like a single report, as shown here:

 

Changing the Position of the Master Crosstab

 

You can also use the Components tab to highlight the individual tables in the worksheet, which facilitates orientation in worksheets with many reports. Simply use the context menu of the relevant cross-classified table. For the master table, select the Highlight Master option, as shown below.

 

Highlight Master Option

This step highlights the master table in the worksheet. Below shows an example.

 

Highlighted Master Table

 

Similarly, you can select the Highlight Dependant option (1) from the context menu to highlight dependent tables, as shown in the next figure. You can also use the Highlight Grouped Crosstabs option (2) to select the entire grouped crosstab.

 

Highlight Dependent Option

 

Ungrouping Reports

To ungroup crosstabs, you can use the menu. Select the master table and choose the Group Crosstab option from the Combine dropdown list, as shown below. Then, remove the checkmark next to the crosstab you want to remove from the grouping.

 

Ungrouping Crosstabs

Alternatively, you can select the dependent crosstab and ungroup it using the Ungroup Crosstab option from the Combine dropdown list, as shown here:

 

Ungrouping Dependent Crosstabs 

You can also select a dimension in the master table itself and choose the path via the context menu. Below shows an example.

 

Ungrouping Crosstab via Context Menu

 

For a dependent crosstab, you can select the Ungroup Crosstab option in the context menu, as shown here:

 

Ungrouping Dependent Table Using Context Menu

 

Of course, you can also use the Components tab in the Design Panel. Select the master table and select the Group Crosstab option from the context menu. Then, deselect the checkbox next to the table that you want to ungroup, as shown in the next figure.

 

Ungroup Crosstab in the Components Tab

 

For the dependent crosstab, select the Ungroup Crosstab option from the context menu, as shown here:

 

Ungrouping a Dependent Crosstab via the Components Tab

 

After ungrouping, the crosstabs are not brought to their original position, but are displayed side by side. Each crosstab has its own row axis. An example is shown below.

 

Ungrouped Crosstabs Displayed Side by Side

 

Note, however, that the dimensions of the two crosstabs are still linked. If required, you can unlink them manually.

11

Editor’s note: This post has been adapted from a section of the book SAP Analysis for Microsoft Office—Practical Guide by Denis Reis.

Recommendation

SAP Analysis for Microsoft Office—Practical Guide
SAP Analysis for Microsoft Office—Practical Guide

Managing your SAP data in Microsoft Excel? This is your guide to using SAP Analysis for Microsoft Office! Get started with the basics, from creating your first workbook to navigating through reports. Then, follow step-by-step instructions to process data, analyze data, develop planning applications, customize reports, and work with tools such as formulas and macros. Including details on troubleshooting, UI customization, and more, this book is your all-in-one resource!

Learn More
SAP PRESS
by SAP PRESS

SAP PRESS is the world's leading SAP publisher, with books on ABAP, SAP S/4HANA, SAP CX, intelligent technologies, SAP Business Technology Platform, and more!

Comments