Cascade Reporting in SpotlightXL

Cascade Reporting is a method of report design that eases ongoing maintenance by letting you indicate that you want a dimension and all its children, or all its members, rather than specifying each member explicitly in the report. When members are added, deleted, or moved in the dimension hierarchy, the changes take effect the next time the report is run.

Cascade Reporting uses relationship filters (such as children of a selected member), which minimizes report maintenance when new members are added to the dimension hierarchies. You can set up one or more rows of data to be displayed for each cascaded dimension member.

For example, you add a newly acquired organization to the Company dimension, which is cascaded in a report. The new organization is automatically included the next time the report is run. This is done by specifying the dimension to cascade from, defining a grouping of rows, called a block, that will be repeated for each cascaded member, and specifying a relationship filter that defines how to cascade the dimension. For each dimension member that meets the criteria in the filter, the block of rows is repeated in the report at run-time.

Cascade dimensions can be defined only on row dimensions. We do not support cascaded column dimensions.

Cascade Options and Filters

Cascade Reporting works well on dimensions with member hierarchies that change or have new members added regularly, such as a Product dimension. Using filters, you can precisely specify which members of the cascaded dimension should appear in the report.

How to Use Cascade Filters?

1. In SpotlightXL, go to Report > Design. Select a desired report.
2. Select all cells in the report and click Design Manager.
3. On the Cascaded dimension, click the Selection icon, then select Filters.

The following section provides information on the Cascade options.

How to Create a Cascade Report?

To create a cascade report, start by opening a view that includes a specific dimension on the row axis.

1. In SpotlightXL, navigate to Report > Design and select a desired report.
2. Select the entire spreadsheet as shown below.
   Note:

   Select the entire spreadsheet before using the Design Manager to ensure accurate dimension representation.
3. Click Design Manager.
   
   
4. On the Design Manager window, you can cascade row dimensions by selecting the Cascade checkbox for the dimension you want to apply cascading to.
   Note:

   Once a dimension on the row is selected for cascading, all other row dimensions will also be cascaded automatically.
   
5. Click the Selection icon to specify the cascade options.
6. Click Select Member.
   
   
7. The member selection window appears. Select the desired member and click Select.
   
   
8. Click the Selection icon again and select Filters.
9. Select the desired filter option.
   
   
10. Click Update. Notice that the selected dimension is preceded with Cascade. This indicates that it is a cascading dimension.
11. Apply formatting, such as highlighting and font size, as desired.
12. Click Save.
    
13. Enter a name for the report and click Save.
    
14. Go to Repot > Run to execute the report and apply cascading functionality.

Adding Grouping Functionality to a Cascade Report

Add row and column groupings within the Cascade Block of a Cascade Report. The Cascade Block includes rows from the cascaded dimension to the innermost dimension member. When you save the report with groups collapsed or expanded, this setting is preserved. Upon refresh, the groups will appear as defined in the report design. For instructions on grouping rows, click here.

How to Ungroup Rows in SpotlightXL?

1. In SpotlightXL, select the rows you want to ungroup.
2. Click the Data tab.
3. Click the Ungroup option.
4. Return to the SpotlightXL tab.

See Also: Cascade Reporting

This feature does not work when you have a 1-row cascade report design.

Updating a Cascade Report

Click the Report task and the Design subtask, then select the report you want to update. Select the entire worksheet and click Design Manager. Update as needed and click the Update button. When returned to the worksheet, Updated indicators are displayed.

You can have one report with Cascade dimensions and each dimension member can have a different filter. In this example, the Cascade dimension is Company, parent, and children are applied to DecisionWorks Golf, children are applied to DW Golf Eliminations, and None is applied to North America, Europe, and Asia-Pac.

You can also select several Cascade dimensions for one report as shown below.

Indent Row Members

You can indent all inner block dimension members in a Cascade report based on the levels defined in the dimension. This functionality indents the inner block members based on their levels defined in the hierarchy.

You can select or deselect the Indent Row Members option available in the Selection menu of the Design Manager for a Cascade Report. This option is selected by default. If you deselect this option, the dimension members are not indented.

How to Select the Indent Row Members Option?

1. In SpotlightXL, go to Report > Design and select the desired report.
2. Select the entire spreadsheet.
   Note:

   It is important when using Cascade Reports to select the entire spreadsheet before selecting the Design Manager to ensure the dimensions are represented correctly if the cascade option is used.
3. Click Design Manager
4. For the dimension that you want to use Cascade, click the Cascade checkbox.
5. On the Cascaded dimension, click the Selection icon and select or deselect Indent Row Members.

Repeat Outer Dimension Members Automatically in Cascade Reports

In SpotlightXL, you can repeat outer dimension members automatically in cascade reports by selecting the Repeat option available in the Selection menu. When you select the Repeat option while creating a Cascade report, you can view the outer dimension members repeating automatically for all inner dimension members.

The following image shows the Repeat option:

Let us take the following example to understand the details.

Suppose you have a Report containing 2 dimensions on rows(ex:- Furniture_Type and Season dimensions). Home living and Furniture as outer dimension members and different Seasons as inner dimension members. For this report, when you create a cascade report and select the Repeat option, then the outer dimension members, Home & Living, and Furniture will repeat automatically for all inner dimension members.

The image below shows the outer dimension members repeating themselves for all inner dimension members:

Remove Inner Block Rows

If you use the "Suppress Rows" feature in a Cascade report, any rows that don't have any data will be hidden. However, if there is any data in a row, the entire block will be visible, including any cells that contain formulas but don't have data. This means that those cells will not be suppressed and will be displayed as intended.

You can select or deselect the Remove Inner Block Rows option available in the Selection menu of the Design Manager for a Cascade Report. This option is selected by default. If you deselect this option, the rows are hidden and not deleted.

Report Output when Blanks and Rows are Hidden: They are not deleted. This will ensure that any dependent Excel formulas will not impact the overall report.

Report Output when Blanks and Rows are Included: This is applicable when the inner block does not have any dependent Excel formulas in the inner block that might be affected.

How to Select the Remove Inner Block Rows Option?

1. In SpotlightXL, go to Report > Design and select the desired report.
2. Select the entire spreadsheet.
   Note:

   It is important when using Cascade Reports to select the entire spreadsheet before selecting the Design Manager to ensure the dimensions are represented correctly if the cascade option is used.
3. Click Design Manager
4. For the dimension that you want to use Cascade, click the Cascade checkbox.
5. On the Cascaded dimension, click the Selection icon and select or deselect Remove Inner Block Rows.

How to Select the Repeat Option in Reports?

1. In SpotlightXL, go to Report > Design and select the desired report.
2. Select the entire spreadsheet.
   Note:

   It is important when using Cascade Reports to select the entire spreadsheet before selecting the Design Manager to ensure the dimensions are represented correctly if the cascade option is used.
3. Click Design Manager.
4. For the dimension that you want to use Cascade, click the Cascade check box.
5. On the Cascaded dimension, click the Selection icon and select Repeat.