Use the crosstab layout to arrange data in rows and columns and summarize multiple value fields for easier comparison and analysis. If your dataset contains a category field along with multiple value fields, you can use the crosstab layout to organize the categories and measures in a matrix format.
The crosstab layout is useful for large datasets where a summarized view makes it easier to identify trends, relationships, and outliers without reviewing individual records.
Use the crosstab layout to:
To enable the crosstab layout, make sure the table meets the following requirements:
You can configure the primary key as an Identity Column only when you configure the table, as shown in the following image.
Similarly, to configure a column as Required at the database level, select Required when you configure the table.
This section explains how to create the crosstab layout to organize and summarize data in a matrix format. In this example, you create a crosstab view for a Sales Performance table and organize sales data by category and date.
The sample table contains the following fields: Sales Entry ID, Category, Sales Date, Revenue, Cost, Profit, and Units Sold. You can organize Sales Date into a Year → Quarter → Month hierarchy and aggregate the value fields for comparison.
Configure columns that you want to assign to the Values field as nullable so they can accept blank values.
In the PowerTable tab, select Layout > Crosstab. The Create Crosstab View window opens.
Assign the required Rows, Columns, and Values fields to configure the crosstab layout.
Each field in the crosstab configuration determines how PowerTable groups, displays, or aggregates the data.
| Field | Purpose |
|---|---|
| Row | Groups data by categories, such as Product or Region. |
| Column | Groups data by categories or time periods. |
| Values | Displays and aggregates the measures you want to summarize, such as Revenue or Cost. |
To create the Sales Performance table by categories across the time period, assign the following columns to these fields:
Row: Category
Column: Sales_Date
Values: Profit, Units Sold
After selecting the columns in the Columns field, select the Settings icon next to it to configure the column hierarchy. Use the available options to define how you want to create, group, and display the hierarchy, as shown in the following image.
Interval Type
Use this option to select how you want to group the data in columns. Choose Date, Week, Month, Quarter, or Year to group the data by day, week, month, quarter, or year, respectively.
Start of the week
Select the day on which you want the week to start.
Select Range
Use the date picker to select the date range that you want to include and display in the crosstab layout. Both the dates and data outside this range aren't displayed.
Start of Financial Year
Select the month on which you want the financial year to begin in the layout.
Show Aggregated Values
Enable Show Aggregated Values to display the summarized value for the selected interval.
For example, when Interval Type is set to Quarter:
Editing data:
To learn more, see edit cells and enter values.
Enable drill-down levels
Under Enable drill-down levels, select the levels that you want to display and navigate within the column hierarchy, such as Year, Quarter, Month, Week, and Date.
Based on the selected Interval Type, configure the level at which the hierarchy starts and the lower levels that users can navigate through. For example, if you select Month as the Interval Type, you can display Month directly or create a hierarchy such as Year → Month, Year → Quarter → Month, or Quarter → Month.
Drill-down settings
Select the Settings icon next to a drill-down level to specify its display format. For example, select YYYY or YY for years and MMM or MM for months.
Values are aggregated to Sum by default. To modify, select the vertical ellipsis (⋮) next to the required Values field and choose an aggregation type, such as Min, Max, Average, and Count. This action aggregates or summarizes the columns on each hierarchy.
Select the Settings icon next to the Values field, and then select or clear the options to show or hide Subtotals and the Grand Total.
Select Save. The crosstab layout is created as shown in the following image.
If the table is empty, navigate to a time range that contains data.
The Crosstab layout doesn't distribute a value entered at an aggregated level across the lower levels. When you enter a value at an aggregated level, Crosstab stores it at the first period within the interval, which is the first date of the month in this example.
Use the toolbar to find and organize specific data.
To modify the existing crosstab layout and configure a new one, go to Layout > Manage Layout. Select the layout, and then reset or reconfigure the properties.
