Hierarchy in AO and Dynamics

In SAP Analysis for Office (AO), you can create a custom hierarchy to group periods into quarters, even if your data comes from SAP without a predefined quarterly hierarchy. Here's how you can do it:

1. Create a New Hierarchical Structure:

In Analysis for Office, open the report with period-level data.

Insert a new column next to the period data to define a quarter for each period.

Assign each period to its respective quarter (e.g., Jan - Mar as Q1, Apr - Jun as Q2, etc.).

2. Use a Custom Grouping or Calculated Dimension:

Some versions of AO support custom hierarchies and grouping within the workbook. You can create a calculated dimension that groups months into quarters based on period identifiers.

Go to the Structure tab (or Data Source context menu) and create a calculated dimension for the quarter. Use a formula or conditional statements to map periods to their quarters.

3. Filter and Display the Hierarchy:

Use the Filter and Hierarchy options in AO to display data at the quarter level.

By filtering, you can choose to view only quarters rather than all individual periods, giving you the summarized quarterly view.

4. Dynamic Hierarchy in AO (if applicable):

If your Analysis for Office version and SAP BW backend support dynamic hierarchies, you can use BW queries to create a hierarchy on the BW side that AO can recognize.

In this case, create a "Quarter" node in the SAP BW Query Designer or directly in your SAC model, defining each period under its appropriate quarter. AO will then recognize and apply this hierarchy when data is refreshed.

This approach allows you to generate quarterly reports by aggregating monthly data without changing the underlying SAP data model.