Dynamic Planning Cascade Reporting
  • 16 Minutes to read
  • Dark
    Light
  • PDF

Dynamic Planning Cascade Reporting

  • Dark
    Light
  • PDF

Article summary

Overview

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, 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.

ModelingImages401to450image406.png

ModelingImages401to450image407.png

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.

In Practice

  1. Select all cells in the report.

  2. Click Design Manager.

  3. On the Cascaded dimension, click the Selection icon, then select Filters.

    Note:
    Once you decide to cascade a dimension, you cannot remove the Cascade option.

    ModelingImages401to450image410.png

    ModelingImages401to450image411.png

    ModelingImages501to550image535.png

    The following table provides information on the Cascade options.

Action
Description

Select Member

Select a specific member to display in the report. For example, the dimension is Time and you have 5 members within Time (2012, 2013, 2014, 2015, and 2016). You want data only for 2015 displayed so you select that member.

Filters

Using Filters, you can precisely specify which members of the cascaded dimension should appear in the report. Select from the following options.

None – Only the member selected is displayed.

Children of <Member> – The children of the specified member are displayed in the report. The specified member is not displayed.

Sel + Children of <Member> – The children of the specified member are displayed in the report, as well as the specified member. The specified member is listed at the top of the report.

AllChildren of <Member> - The children, grandchildren, and all other descendants of the specified member are displayed in the report. The specified member is not displayed.

Sel + AllChildren of <Member> - The children, grandchildren, and all other descendants of the specified member are displayed in the report, as well as the specified member. The specified member is listed at the top of the report.

Leaves of <Member> - The leaf members in the hierarchy under the specified member are displayed in the report. The specified member is not displayed.

Sel + Leaves of <Member> - The leaf members in the hierarchy under the specified member are displayed in the report. The specified member is listed at the top of the report. Note that no other parent-level members under the specified member are included in the report.

Insert New Member

Above – Select to add an additional dimension member to the report above the cascaded rows. For example, you have a report with a cascaded dimension Time, and the filter None for selected member 2014. You want to show 2013 above 2014 in the report. By adding 2013 Above the cascaded dimension, it appears as another block above 2014.

Graphical user interface, text, application, email

Description automatically generated

Table

Description automatically generated

Below – Select to add an additional dimension member to the report and Below the existing selected dimension member.

Insert Subtotal

Adds a row to the report and provides a subtotal 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 delete the Subtotal, select the Subtotal row in the Design Manager, use the Selection icon and click Delete.

Delete

Delete the member from the report definition. Use this option to delete excess members listed under the cascaded dimensions.

Graphical user interface, text, application

Description automatically generated

Move

Top / Up – Move the dimension member up one place or to the top of the report.

Down / Bottom - Move the dimension member down one place or to the bottom of the report.

Creating a Cascade Report

To create a cascade report, begin by opening a View that has the dimension you want to cascade on the row axis.

In Practice

  1. Select the Analyze task and the Data subtask.

  2. Select the View.

  3. Select Design With > Report.

  4. Select the entire spreadsheet as shown below.

    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.

    ModelingImages401to450image408.png

  5. Click Design Manager.

  6. On the Design Manager page, Cascade is available for row dimensions. Select the Cascade check box for the Company dimension.

    ModelingImages401to450image409.png

  7. If Account was selected, Company would have automatically been selected as well because Account is nested (to the right of Company). Nested dimensions are referred to as inner dimensions. The outer dimension for this report is Company. Click the Selection icon to specify the Cascade options.

    ModelingImages401to450image4101.png

    ModelingImages401to450image4111.png

  8. For this example, click Select Member.

  9. The Member Selection page appears. Click on DecisionWorks Corporate and then click Select.

    ModelingImages401to450image414.png

  10. Click the Selection icon. Select Filters.

  11. Select Sel+ Children of DecisionWorks.

    ModelingImages401to450image415.png

  12. Click Update. Notice that the Company dimension is preceded with “Cascade”. This indicates that it is a cascading dimension.

    ModelingImages401to450image431.png

  13. Apply formatting. For example, highlight cells in green and make the font larger.

  14. Click Save.

  15. Enter a name for the report and click save.

  16. Select the Run subtask to execute the report and apply cascading functionality.

    Notice that the parent company (DecisionWorks Corporate) is at the bottom of the report. When including a parent member as done in step 11, the parent is always displayed at the bottom of the report.

    ModelingImages401to450image421602x518.png

    ModelingImages401to450image417602x495.png

    ModelingImages401to450image418.png

    ModelingImages401to450image419.png

    ModelingImages401to450image420604x471.png

Adding Grouping Functionality to a Cascade Report

Add row and column groupings; as long as the groupings are defined within the Cascade Block to a Cascade Report. The Cascade Block is comprised of the row which starts with a cascaded dimension to the ending innermost dimension member. For example, cell B3 through cell F23.

image1492zzzzz123234569012312345690122345678901236782.png

Let’s say you’ve design the following Cascade Report.

image1492zzzzz123234569012312345690122345678901236783.png

When you run the report, the output will look as follows in the image below.

image1492zzzzz123234569012312345690122345678901236784.png

If you save with the report with the groups collapsed or expanded, the Cascade Report will save along with the report definition. When you refresh, the group will either be expanded or collapsed based on what you define during report design.

Here is an example with a groups expanded design:

image1492zzzzz123234569012312345690122345678901236785.png

Here is an example with a groups collapsed design:

image1492zzzzz123234569012312345690122345678901236786.png

In Practice: Grouping Rows in SpotlightXL

  1. Select the rows you want to group.

  2. Click the Data tab.

  3. Click the Group option.

  4. Return to the SpotlightXL tab.

    image1492zzzzz123234569012312345690122345678901236787.png

In Practice: Ungrouping Rows in SpotlightXL

  1. 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 as shown below.

    image1492zzzzz123234569012312345690122345678901236788.png

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.

Note:
Remember to click Save before running the report.

ModelingImages401to450image422.png

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

ModelingImages401to450image423.png

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

ModelingImages401to450image424.png

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.

The following example shows Excel formulas inserted into columns showing the percentage of the total.

ModelingImages501to550image531.png

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

ModelingImages501to550image532.png

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

ModelingImages501to550image533.png

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

ModelingImages501to550image534.png

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 delete the Subtotal, select the Subtotal row in the Design Manager, use the Selection icon and click Delete.

ModelingImages501to550image536.png

ModelingImages501to550image537.png

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

ModelingImages501to550image538.png

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

ModelingImages501to550image539.png

Note:
You can change the text of the cell "Account:Subtotal" in Report Design mode.

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

Cascade reports support saving data, whether through data entry or through 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.

Note:
If the report contains attributes, we recommend that Save not be enabled.

In the following example, a report has leaf members on the page axis and column axis. The row axis has the dimension Product, and the filter selected is Leaves of Product. 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 is set to No since the user may want to enter data into those cells.

ModelingImages501to550image540.png

ModelingImages501to550image541.png

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.

ModelingImagesReportimage542.png

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.

ModelingImages551-600image553.png

ModelingImages551-600image554.png

ModelingImagesReportimage555.png

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 (). 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.

ModelingImages501to550image543.png

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 Account is blank.

ModelingImages501to550image544.png

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.

ModelingImages501to550image545.png

ModelingImages501to550image546.png

Remove Inner Block Rows

When you Suppress Rows in a Cascade report, if one of the data cells contains data, the inner block is not suppressed, and only the rows that do not contain any data are suppressed.

With this functionality, any cells containing formulas but not having any data are not suppressed and are displayed appropriately.

Note:
This functionality will work only if cascade report optimizations are enabled; that is, the Enable Cascade Enhancement flag is set to true.

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.

DynamicPlanningAug21Indent1.png

Report Design - If you deselect the Remove Inner Block Rows option.

Report Output in Run mode : Blank and Zero rows are hidden in the report. They are not deleted. This will ensure that any dependent excel formulas will not impact the overall report.

DynamicPlanningJuly21Casacade2.png

Report Design :- If you select the Remove Inner Block Rows option.

Report Output in Run mode : Blank and Zero rows are not included in the report. This is applicable when the inner block does not have any dependent excel formulas in the inner block that might be affected.

DynamicPlanningJuly21Casacade4.png

In Practice: Selecting the Remove Inner Block Rows Option

  1. In SpotlightXL, select the Analyze task and the Data subtask.

  2. Select the View.

  3. Select Design With > Report

  4. 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.
  5. Select all cells in the report.

  6. Click Design Manager

  7. For the dimension that you want to use Cascade, click the Cascade checkbox.

  8. On the Cascaded dimension, click the Selection icon and select or deselect Remove Inner Block Rows

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.)

In Practice: Setting up the Cascade report to show data at leaf levels only

  1. Run the report with cascaded dimensions on the row axis.

  2. Select Report and Design from the menu.

  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.

    ModelingImagesReportimage556.png

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

    ModelingImagesReportimage557.png

    Now you can run the report.

    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 on the Company dimension. Region appears in the row axis and is cascaded.

ModelingImages501to550image547.png

ModelingImages501to550image548.png

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.

DynamicPlanningAug21Indent11.png

DynamicPlanningJuly21Casacade6.png

In Practice: Selecting the Indent Row Members Option

  1. In SpotlightXL, select the Analyze task and the Data subtask.

  2. Select the View.

  3. Select Design With > Report

  4. 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.
  5. Select all cells in the report.

  6. Click Design Manager

  7. For the dimension that you want to use Cascade, click the Cascade checkbox.

  8. On the Cascaded dimension, click the Selection icon and select or deselect Indent Row Members

    Note:
    When including attributes on the report, we recommend that Save not be enabled.

Repeat Outer Dimension Members Automatically in Cascade Reports

In SpotlightXL, you can repeat outer dimension members automatically in cascade reports by selecting the new 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:

DynamicPlanningWinter21Repeat1.png

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:

DynamicPlanningWinter21Repeat2.png

In Practice: Selecting the Repeat Option

  1. In SpotlightXL, select the Analyze task and the Data subtask.

  2. Select the View.

  3. Select Design With > Report.

  4. Select the entire spreadsheet as shown below.

    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.
  5. Select all cells in the report.

  6. Click Design Manager.

  7. For the dimension that you want to use Cascade, click the Cascade check box.

  8. On the Cascaded dimension, click the Selection icon and select Repeat.

Delete a Member from Cascade Report and Re-Add

In SpotlightXL, when you delete any member from a cascade report, you can add the same member back to the report. When you add a deleted member back to the report, you have the Cascade check box available in the Design Manager window.

Let us take the following example to understand the details.

Suppose you have a cascade report containing Home & Living and Season as dimension members as shown in the image below.

DynamicPlanningWinter21CascadeReport1.png

For this report, you can delete the Season dimension member and add this member back to the report as shown in the image below.

DynamicPlanningWinter21CascadeReport2.png

While generating the report, you will have the Cascade check box available in the Design Manager window for the re-added member as shown in the image below.

DynamicPlanningWinter21CascadeReport4.png

You can select the Cascade check box for the Season dimension member and generate the cascade report.

The following image shows the generated cascade report:

DynamicPlanningWinter21CascadeReport3.png

In Practice: Adding a Deleted Dimension Member to the Cascade Report

  1. In SpotlightXL, select the Analyze task and the Data subtask.

  2. Select the required view.

  3. Select Design With > Report.

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

  5. Select the entire spreadsheet.

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

  7. Run the report.

Delete Blank Rows or Rows with Zeros from a Cascade Report

In a Cascade report, you can automatically delete all the blank rows and rows containing zeros when you Suppress Rows with blanks or zeros and run the cascade report.

Note:
This functionality will work only if cascade report optimizations are enabled. (i.e. Enable Cascade Enhancement flag is set to true ).

For example, if your cascade report has a blank row 12 and you run the report after selecting “Blank” as the value for the Suppress Rows property, then row 12 will be deleted, and the rows below it will be moved up by one place, instead of hiding the row 12.

DynamicPlanningWinter21DeleteSuppress2.png

Formatting Tips in Cascade Reporting

When designing a cascading reporting, 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.

    ModelingImages501to550image549.png

    When the report is run:

    ModelingImages501to550image550.png

  • 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. For example, you start with this view and then create a report from it.

    ModelingImages551-600image551.png

    When setting up the Cascading functionality, select all cells and then click Design Manager. Delete all the excess rows under Net Income and let Cascade Sel+ All Members display the hierarchy.

    ModelingImages551-600image552.png


Was this article helpful?