CrossTab Reports

The CrossTab component can be added to an Exo Clarity report to display grouped or aggregated data data in a spreadsheet-style format. CrossTab stands for Cross tabulation, a process by which totals and other calculations are performed based on common values found in a set of data (this style of spreadsheet is also called a pivot table).

Note: The General Ledger Account Summary report (GLaccSummaryCrossTab.clr) is an example of a report that uses a CrossTab to organise data.

How CrossTabs Work

Assume you have a set of data that describes the sales for a company. Each sale is represented by a row of data. Each row of data contains a customer name, company type, geographical area, sale date or period, and sale amount. If you want to know the total sales for each month by area, you could present the data:

Area: Sydney

Year: 2017

Total Sales: $2577

State: Auckland

Year: 2001

Total Sales: $3548

This format shows the data you need, but it makes a state-to-state comparison difficult. Another format could be:

	Region
Year	Auckland	Sydney	Total
2016	$7816	$5327	$13,143
2017	$10,500	$9750	$20,250

This format is easier to read and more compact - there is more information in less space. It is easy to make state-to-state and year-to-year comparisons. This format is a CrossTab. The Year and Region values are called "dimensions" because they orient the data in rows and columns. The values in the cells are the calculations created when the sales data is summarized and are sometimes referred to as measures.

The simple CrossTab above be taken a couple of steps further to create a very informative report. For example, if we wanted to know the sales by Customer Type within the region, as well as the total number of distinct sales per Customer Type, we could present this information by adding a Customer Type dimension to the columns and another calculation to the values (count of sales). The resulting CrossTab would look like the one below:

		Region
		Auckland					Sydney					Total
Year	Data	Dairies	Super-markets	Mini-marts	Take-aways	Region Total	Dairies	Super-markets	Mini-marts	Take- aways	Region Total
2016	Count Sales	43	27	19	103	192	23	24	12	83	142	334
	Sum of Sales	$2076	$1764	$1524	$1672	$7816	$1001	$1502	$1423	$1401	$5327	$13,143
2017	Count Sales	41	56	14	164	275	30	36	10	102	178	453
	Sum of Sales	$3084	$4500	$1029	$2097	$10,500	$2124	$4500	$1029	$2097	$9750	$20,250

Notice the new subtotal columns for each Region. This CrossTab shows all of the information of the initial CrossTab, plus more detailed information by Customer Type. You can see that CrossTabs can express a lot of information in a very small amount of space.