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 sheet.
  2. Optional: 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 Extensions menu, select Smart View for Google Workspace, and then select Ad Hoc.
  4. Select Analysis and then select Cascade.
  5. Under Cascade, select the required option:
    • Cascade - Same Spreadsheet to cascade all reports in the current spreadsheet
    • Cascade - New Spreadsheet to cascade all reports in a new spreadsheet
    • Cascade - Different Spreadsheets to cascade all reports in different spreadsheets

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

  6. In the Cascade panel, click Ellipses button next to each dimension to launch the Member Selector dialog.
  7. Select the members for the dimensions for which you want to create reports, and then click Done.

    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.

  8. Click Done to begin cascading.

    You will see temporary sheets getting created until all the reports are cascaded. The resulting reports are created on separate sheets in either the current spreadsheet, a new spreadsheet, or different spreadsheets based on your selection. Each sheet is named as per the dimension and member data it contains for easy identification. Click a sheet to view the report.

    Note:

    After the temporary sheets are created, if you do not see the final cascaded reports, check the pop-up blocker settings on your browser and make sure you allow pop-up windows to be launched for cascading reports.
  9. 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.