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.