Option to group repeating cells in reports produced in Excel 2007 format
Report outputs to Excel 2007 format are easier to read and look more like other output formats when repeating values are grouped. If further analysis of the data is required within Excel or if the outputs are used to provide data to another tool, it is often preferable for repeating values to be populated in each row or column to which they apply.
By default, repeating cells are merged in Excel 2007 output. For example, Product line is a grouped column in a list. The values for Product line, such as Camping Equipment and Golf Equipment, appear once in a merged cell in Excel output. When repeating cells are not grouped, the values for Product line appear in each repeating cell. The option to merge repeating cells in Excel output is controlled by selecting or clearing the Group repeating cells when exporting to Excel check box in the Report Properties dialog box.
Crosstabs
In general, grouped data item values appear in each repeating cell. For example, a crosstab contains Product line and Product type as rows. When repeating cells are ungrouped, the label for each product line value is rendered in each repeating row cell.
If a cell on a column edge spans multiple worksheet rows, the cell label is not repeated in all rows. If a cell on a row edge spans multiple columns, the cell label value is not repeated in multiple worksheet columns. For example, a crosstab contains Product line, Product type, and Product as rows. A summary is added for Product line. The product line summary cell spans Product line, Product type, and Product. In Excel, repeating cells are produced for the summary row, but the label for the summary appears only in the first cell.
Repeating cells that are produced from crosstab headers follow this behavior. For example, Product line and Product type are inserted as rows in a crosstab. Headers named Product line and Product type are created, and each header spans two columns. In Excel, the header labels appear only in the first cell. The repeating cells are empty.
If a layout object, such as a table or image, is inserted in the crosstab corner, the size of the object might cause the crosstab corner column edge to span multiple rows and row edges to span multiple columns. When this situation occurs, column labels repeat only in the column span and row labels repeat only in the row span.
The following figure shows a crosstab that contains Year as columns and Order method type as rows. A three by three table is inserted in the crosstab corner. In Excel output, the size of the table produces repeating cells in the columns and in the rows. Year labels, such as 2010, repeat only in the columns and not in the rows. Order method labels, such as E-mail, repeat only in the rows.
Lists
In lists, grouped data values are repeated in ungrouped cells. For example, a list contains Product line and Product type, and Product line is grouped. When repeating cells are ungrouped in Excel, the label for each product line value is rendered in the repeating cells.
Group header and list page header labels are not repeated. These labels appear only in the first cell, and repeating cells are empty.
Nested data containers, images, and charts
Nested data containers, images, and charts in a report can produce merged cells. For example, when a crosstab is inserted in a list, other columns in the list appear as merged cells in Excel 2007 output. The following rules are applied when merged cells are split.
- If a cell is merged as a result of a data item grouping, then data values are repeated in the split cells.
- If a cell is merged as a result of a nested data container, image, or chart, then data values are not repeated in split cells.
For example, a list contains Product line, Product type, Quantity, and an image as columns. The Product line column is grouped. The image produces merged cells in Excel output because its size spans more than one row. When the report is run with the option to ungroup repeating cells, Product line values are repeated, but Product type and Quantity values are not.
Bookmarks
When you produce Excel 2007 output with ungrouped repeating cells, clicking an entry in a table of contents brings you to the first row that contains that entry. For example, a report contains a list with a table of contents. Product line values are repeated in the list in Excel output. When you click Camping Equipment in the table of contents, you are brought to the first cell in the list that contains Camping Equipment.