Cascading an Ad Hoc Grid to Multiple Sheets

Cascade members of one or more dimensions from an ad hoc grid to separate sheets automatically to manage multiple analysis based on different POV or member selections efficiently.

Cascade enables you to split an ad hoc grid into separate sheets based on the members of a selected dimension while preserving the original grid layout. Cascading automates the process of generating separate reports or sheets for each selected member. Cascading is useful when you want to produce consistent, member-level reports without manual duplication efforts. For example, you can create one sheet for each cost center, region, product, or time period without having to repeat queries manually.

Each cascaded sheet preserves the same layout and displays data specific to the selected member and dimension. Each sheet is named as per the dimension and member data it contains for easy identification. For example, from an ad hoc grid containing regional sales data, if you cascade data for Sales East and Sales West for FY24 and FY25, then the cascaded report sheets are named as Sales East - FY24, Sales East - FY25, Sales West - FY25, and Sales West - FY25.

Note:

The names of the cascaded sheets depend on your cascade selections, and have a limit of 30 characters. The first 30 characters of the selected dimension names are used, regardless of their length. Any characters over 30 are truncated. If there are more than 30 characters, then the first 28 characters are used and appended with ~n where n is a unique number (1,2,3,…).

To cascade members from an ad hoc grid to separate sheets:

  1. Open an ad hoc grid on the worksheet.
  2. From the provider ad hoc ribbon in the Analysis section, select Cascade Cascade button.

    The Cascade panel opens and displays the POV for the ad hoc grid.

    If you are working in a multiple-grid ad hoc sheet, then ensure that you select a cell in the grid from which you want to cascade members to separate sheets. This helps in displaying the correct POV in the Cascade panel.

  3. In the Cascade panel, click Ellipses button next to each dimension to launch the Smart View Member Selector dialog box.
  4. Select the members for the dimensions for which you want to create reports, and then click Apply.

    One report sheet will be generated for each member combination you selected. The number of cascaded sheets that will be created is displayed at the bottom of the Cascade panel.

  5. Click OK to begin cascading.

    The resulting reports are created on separate worksheets in the same workbook. Each worksheet is named as per the dimension and member data it contains for easy identification. Click a worksheet tab to view the report.

  6. Optional: While working in a multiple-grid ad hoc sheet, after cascading from the first grid, if you want to cascade from a second grid, then select a cell in the second grid and click Refresh in the Cascade panel. This helps to refresh and display the POV specific to the second grid. You can then follow the above steps to cascade members from the second grid to separate sheets.