Lay out crosstab or flat query results

By default, query results are flat and unsorted. To help compare and analyze data, you can:

• Sort the results in ascending or descending order, based on one or more fields.
• Use a cross-tabulation—or crosstab—layout to group the results horizontally and vertically based on dimensions you choose and apply aggregation values—such as COUNT or MAX—to rows or columns.

For example, a query's results provide sales data in a flat, tabular layout by default:

With crosstab results, you can lay out and summarize the data to compare each region's total sales during each fiscal year:

Lay out crosstab results

To ease the analysis of categorical data, you can lay out the results as a cross-tabulation, with rows, columns, and values in either Builder or SQL .

1. In the query, add the fields to include in the results:
   • In Builder, add the source fields and calculations to the Fields tab.
   • In SQL, compose the query's SQL, and verify no syntax errors occur. For each field in the SELECT statement, use an AS statement to specify its header in the crosstab layout, even if the same as the field's name. For example, SELECT field_name1 AS "alias", field_name2 AS "field_name2" FROM source_name.
2. From the Crosstab panel, select Enable Crosstab, and specify how to display the fields in the results:
   
   
   • For values to display vertically, such as Region in our example, leave its field under Rows, and select whether to sort its data in ascending or descending order.
   • For values to display horizontally, such as Fiscal Year in our example, move its field from Rows to Columns, and select whether to sort its data in ascending or descending order.
   • To apply a calculation to a value within the comparison, move its field from Rows to Values, and select the aggregation to use such as DISTINCTCOUNT or SUM. To display the value as a grand total, in Totals, select whether it should appear for rows or columns.

   Note: To remove a row from the results, delete its field from the query.

3. To preview the results in the crosstab layout, click Run Query .
4. Click Save .

Lay out flat results

By default, query results appear in a flat layout. To return crosstab results to a flat layout, deselect Enable crosstab from the Layout panel.

Sort flat results

By default, query results appear unsorted. In either Builder or SQL, you can sort the results in ascending or descending order according to one or more fields.