Working with Cross (Pivot) Tables

A cross or pivot table is a document table growing in the both directions – vertically and horizontally – depending on bound data. You can build a cross (pivot) table by nesting a table-column data band into a table-row data band as follows.

<<foreach ...>><<foreach ... -horz>> ...	...	...
...	...	...
...	...	... <</foreach>><</foreach>>

Note – It is not necessary to start (or end) an outer table-row data band and a nested table-column one within the same cell, but this case is also supported.

Let us consider concrete examples using years, an array of integers ranging from 2020 to 2023, and ds, a DataSet instance containing DataTable and DataRelation objects according to the following data model.

The most basic scenario is filling a document table with data in two directions. You can use a template like the following one to achieve this.

Managers	<<foreach [y in years] -horz>><<[y]>><</foreach>>
<<foreach [m in ds.Managers]>><<[m.Name]>>	<<foreach [y in years] -horz>><<[m.Contracts.where(c => c.Year == y).sum(c => c.Price)]>><</foreach>><</foreach>>

Note – Table-row and table-column regions cannot cross, that is why two table-column data bands bound to the same enumeration are required here.

In this case, the engine produces a report as follows.

Managers	2020	2021	2022	2023
James Atkins	545000	340000	620000	510000
John Lee	120000	320000	565000	495000
Thelma Green	310000	290000	485000	530000
Ted LeMark	0	110000	345000	380000

It is quite typical for cross (pivot) tables to contain totals for every row and column. You can add the totals by altering the template as follows.

Managers	<<foreach [y in years] -horz>><<[y]>><</foreach>>	Total
<<foreach [m in ds.Managers]>><<[m.Name]>>	<<foreach [y in years] -horz>><<[m.Contracts.where(c => c.Year == y).sum(c => c.Price)]>><</foreach>>	<<[m.Contracts.sum(c => c.Price)]>><</foreach>>
Total	<<foreach [y in years] -horz>><<[ds.Contracts.where(c => c.Year == y).sum(c => c.Price)]>><</foreach>>	<<[ds.Contracts.sum(c => c.Price)]>>

In this case, the engine produces a report as follows.

Managers	2020	2021	2022	2023	Total
James Atkins	545000	340000	620000	510000	2015000
John Lee	120000	320000	565000	495000	1500000
Thelma Green	310000	290000	485000	530000	1615000
Ted LeMark	0	110000	345000	380000	835000
Total	975000	1060000	2015000	1915000	5965000

Since cross (pivot) tables can contain large amounts of data, it is quite usual to group parts of information within these tables using merged cells, in order to simplify further search of necessary information. For this purpose, you can apply cellMerge tags (see “Merging Table Cells Dynamically” for more information) as shown in the following template.

Cites	Managers	<<foreach [y in years] -horz>>Years<<cellMerge -horz>>
		<<[y]>><</foreach>>
<<foreach [m in ds.Managers]>><<[m.Cities.Name]>><<cellMerge>>	<<[m.Name]>>	<<foreach [y in years] -horz>><<[m.Contracts.where(c => c.Year == y).sum(c => c.Price)]>><</foreach>><</foreach>>

In this case, the engine produces a report as follows.

Cities	Managers		Years
		2020	2021	2022	2023
Seattle	James Atkins	545000	340000	620000	510000
	John Lee	120000	320000	565000	495000
Ottawa	Thelma Green	310000	290000	485000	530000
	Ted LeMark	0	110000	345000	380000

Combining all the described approaches, you can create cross (pivot) tables of almost any complexity.

---

FAQ

1. Q: How do I define a cross (pivot) table in a Word template?
   A: Use a nested structure where a table‑row data band (<<foreach [m in ds.Managers]>>) contains a table‑column data band (<<foreach [y in years] -horz>>). The outer band iterates vertically (rows) and the inner band iterates horizontally (columns), producing a matrix of values.

2. Q: How can I add row and column totals to a cross table?
   A: Add an extra row and/or column that use the same LINQ expressions but call sum on the collection. For example, <<[m.Contracts.sum(c => c.Price)]>> gives the total for a manager, and <<[ds.Contracts.where(c => c.Year == y).sum(c => c.Price)]>> gives the total for a year.

3. Q: What does the -horz modifier do in a foreach tag?
   A: The -horz modifier tells the reporting engine to repeat the enclosed content horizontally, creating a new column for each iteration instead of a new row.

4. Q: How do I merge cells dynamically inside a cross table?
   A: Insert the cellMerge tag (or cellMerge -horz for horizontal merging) in the template where you want cells to be combined. The engine will merge the cells of the current row or column based on the tag’s position.

5. Q: Why do I get the error “Table‑row and table‑column regions cannot cross”?
   A: This occurs when a table‑row data band and a table‑column data band overlap inside the same cell. Each band must start and end in its own cell; move one of the bands to a separate cell to resolve the conflict.