Group By, Pivot and Charts

A query's results screen has four tabs. The first shows the rows as they came back; the other three reshape the same rows without going near the database again.

Tab	What it gives you
Results	The rows as returned — see Working with results
Group By	The same rows collapsed into groups, with totals per group
Pivot	A cross-tab: one field down the side, another across the top
Visual	A chart

Each tab keeps its own column choices, sorting and paging, so setting up a Group By does not disturb what you had on the Results tab.

Why the tab you are on matters

Save as Report and Save as Dashboard Item capture whichever tab you are looking at. Build the view you want first, then save it.

Group By

Choose one or more columns to group by, then choose what to work out for each group. The aggregates available are Sum, Count, Average, Min and Max.

Grouping by Teacher and summing Minutes, for example, turns a list of individual lessons into one row per teacher with their total teaching time.

Pivot

A pivot lays the data out in two dimensions. You choose three things:

• Rows — the field listed down the left
• Columns — the field spread across the top
• Values — the number filling the grid, and how it is aggregated

Instrument down the side and Term across the top, with a count in the middle, gives you at a glance how many of each instrument were taught in each term.

Visual

The Visual tab draws the results as a chart. You choose:

• Chart type — bar, line or pie
• Values — the number being plotted
• Aggregate — how those values are combined

Saving and printing a chart

Save as Image downloads the chart as a PNG. Print opens it in a print window.

Both work from the chart already on screen, so what you get is exactly what you can see.