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:
|Region||Sales Amount||Fiscal Year|
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 .
- 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
SELECTstatement, use an
ASstatement 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.
- 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.
- To preview the results in the crosstab layout, click Run Query .
- 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.
- On the Sort tab, move each field to sort by from Unsorted to Sorted, and select whether to sort its data in ascending or descending order.
- If you sort according to multiple fields, under Sorted, arrange them in the order of how to sort their data. For example, you may want to sort first in ascending order according to one field, then in descending order according to another.
- Click Save .
When you compose the query's SQL statement, use an
ORDER BY keyword to specify the field to sort in ascending (
ASC) or descending (
DESC) order, such as
ORDER BY field_header ASC.
To sort according to multiple fields, separate each with a comma, such as
ORDER BY field_header1 ASC, field_header2 DESC.