Cascade Reporting Support

Cascade Support for Excel Formulas

Excel formulas in the report are honored in a Cascade report in the following circumstances:

• The formulas are inside the block.

• The formulas are on the row immediately following the block.

When the report is run, the formulas are calculated using the data retrieved from the model. The formulas are retained.

The following example shows Excel formulas inserted in the row below the data.

When the report is run, the formulas are calculated using the data retrieved from the model. The formulas are retained.

Cascade Support for Subtotals

Subtotals are available for cascaded dimensions. When you insert a Subtotal, a row is added to the report with subtotals of the data in the columns. The Subtotal disregards the rollup operators on the members and simply does a sum of all the numbers in each column.
To add the Subtotal, select the Subtotal row in the Design Manager, use the Selection icon, and click Insert Subtotal.

After you click Update, you see the Subtotal row added to the report.

After you run the report, you see Subtotals under each block of cells.

To delete the Subtotal, select the Subtotal row in the Design Manager, use the Selection icon, and click Delete.

Cascade Support for Saving Data, Saving Data and Running a Calculation, and Breakback

Cascade reports support saving data, whether through data entry or Breakback operations. You can use the standard Calculation on Save options, including saving data in multiple models, running a calculation after saving the data, and running a calculation without saving data. As with non-cascade reports, data can be saved only on leaf-level cells. Dimension security is honored.

For general information on enabling Data Save options, see Enabling Menu Items in Views and Reports: Save Data and Breakback.

In the following example, a report has leaf members on the page axis and column axis. This ensures that leaf-level data appears in the report. Report Properties allow data to be saved and a calculation to be run. Additionally, note that Suppress Rows and Columns are set to None since the user may want to enter data into those cells.

When the report is run, the Save Data and Breakback buttons are provided on the menu ribbon. The user can enter data and click Save Data when complete.

Cascade Support for Drill-Through

When creating a report using data from a Master model, you can use Drill-Through from any of the data cells in a cascading report.

Cascade Support for Suppress Rows and Columns

Cascaded dimensions automatically have blank rows suppressed. You may want to also specify to suppress Zero rows in the report properties.

In the following example, the highest-level parent in the Account dimension has no data because it was defined with a null rollup operator (). The account is a cascaded dimension defined as Sel+ Children of Account.

If Suppress Rows and Suppress Columns are set to None, when the report is run, you see blank data for Account and #DIV/0 for the percentage calculations.

If Suppress Rows is set to Suppress Both - Zero and Blank, when the report is run, you no longer see the #DIV/0 errors, but you also lose the top row of each block because the Account is blank.

The solution is to specify that the Account dimension be cascaded as Children of Account so that the Account member is not included. With Suppress Rows set to Suppress Both - Zero and Blank, when the report is run, you see a complete set of data.

How to Add a Dimension Member to the Cascade Report?

1. In SpotlightXL, go to Report > Design and select the desired report.

2. Click Design Manager, select the dimension member to add, and click Update.

3. Select the entire spreadsheet.

4. For the added dimension, click the Cascade check box and click Update.

5. After saving, run the report by going to the Report > Run.

Cascade Support for Leaf Levels with Data Only

Cascade reporting provides functionality similar to Zoom In to Data Leaf Levels in Views.

When a cascade report is run, you can see only rows that have data at the leaf level. Additionally, rows that do not have data are removed from the report so that they do not consume excess data analysis cells unnecessarily. (The maximum number of Analysis Cells is specified in Application Settings.)

How to Set Up the Cascade Report to Show Data at Leaf Levels?

1. In SpotlightXL, go to Report > Design.

2. Open the report with cascaded dimensions on the row axis.

3. Select all cells in the report, then click Design Manager.

4. Set the cascaded dimensions to a filter of 'Leaves of' the dimension then click Update.

5. Click Report Properties and set Suppress Rows to Blank, then click Save.

Now you can run the report by going to Report > Run. For information on Zoom In to Data Leaf Levels in Views, see Dynamic Planning: How to Find Data Cells Quickly.

Cascade Support for Attributes

Cascade Reporting supports attributes in two ways:

• An attribute can appear on the page or column axis in a report with cascaded dimensions on the rows.

• An attribute can be cascaded on the row axis.

In the following example, Region is an attribute of the Company dimension. The region appears in the row axis and is cascaded.

Formatting Tips in Cascade Reporting

When designing a cascading report, here are some formatting tips.

• You can rename the Subtotals cell to be more descriptive, and use cell formatting on the data cells.

• You can add merged cells in the heading and additional text above the block. Any text cells on the right side of the block will not be repeated with the block. Think of the block as the box from column A to the rightmost column of data cells with the red cell-comment indicators. Anything to the right of the box is ignored.

When the report is run:

When starting with a view and then creating a report, it is a best practice to remove excess rows from the report design and let the Cascade feature fill in the rows.

How to Add a Dimension Member to the Cascade Report?

1. In SpotlightXL, go to Report > Design and select the desired report.

2. Click Design Manager, select the dimension member to add, and click Update.

3. Select the entire spreadsheet.

4. For the added dimension, click the Cascade check box and click Update.

5. After saving, run the report by going to the Report > Run.

Why Data May Save Incorrectly in Cascaded Reports During Structural Updates

When entering data in a cascaded report, clicking Save triggers a refresh of the report to reflect the latest hierarchy and structural changes. Once the refresh is complete, the inputs are saved to the model.

If structural changes (like hierarchy reordering or report layout updates) occur simultaneously with data entry, the report refresh may realign the structure, causing values to be saved to unintended intersections. This can cause a misalignment between the combinations that the user entered values against versus the combinations that the values were saved to.

Examples of Misalignment Scenarios

Example 1

A user enters sales data for Region 3. At the same time, an Admin re-sorts the region hierarchy (from Region 1, Region 3, Region 2 → Region 1, Region 2, Region 3).

Upon saving, the input is mistakenly written to Region 2 instead of Region 3 due to the refreshed hierarchy.

Example 2

A user modifies data in a web report, while a Power User edits the same report in SpotlightXL—such as rearranging rows.

After saving, the data is written to the new row intersection, not the one currently shown in the web interface.

This issue occurs when:

• A report refresh happens during the save process.

• The structure (like rows or hierarchy order) has changed.

• The report has not been refreshed by the user before saving.

Best Practice to Prevent Data Misalignment

Always refresh cascaded reports before saving inputs, especially if there’s a chance that structural updates (e.g., hierarchy changes, report design updates) are occurring at the same time.