Crosstabs or Pivot Tables allow you to track two or more independent variables and see how another value aggregates based on these independent variables.
When you create a Crosstab Report, a similar wizard appears to the one we saw above for an Advanced Report.
- Start by specifying the name and folder.
- On the Categories tab, select Membership, then Members
- Optionally select Filters.
You will next see the Layout tab when you can specify the row and column values, and the aggregate value.
On the left is a list of columns in the selected data source. You can select a value and drag it to the Row Header Source, Column Header Source, or Tabulation Data Source sections. Each section can include more than one data source.
Click the Edit (pencil) icon for the Row or Column Header source to change the label and sort order. Click the Edit (pencil) icon for the Tabulation Data Source to change the aggregation formula and display value. For the tabulation, a label is generally not specified. The tabulation method is one of Sum, Count, Average, Min, or Max. The value can be the aggregate value or a percentage of the row or column.
Click the Options button to control how row headers are placed and whether you want totals at the row and column levels. Note that if you specify both, a total will also be calculated across all cells.
When you click Finish, you are taken to the Report Designer.
When you are working with a crosstab, only one row and one column are shown to represent multiple rows and columns in the final report. And because crosstabs show aggregate data, the whole report is defined in the Report Footer section.
Additional Notes
When you are tabulating text values, Count is generally the only aggregation that makes sense, and it’s important to pick an aggregation field that we know to be unique. In this example, we are using Member Number.
When you are tabulating numbers or currency, you can use any of Count, Sum, Average, Min, or Max. You can even show more than one value at the same time, such as Total Revenue (Sum) and Largest Purchase (Max).
When you are tabulating dates, Min and Max are generally the only aggregations that make sense.