Charts & Pivot turns the result of the console's last completed run into a chart or a pivot table. You drag fields into place and the Editor calculates the figures, so you do not write a GROUP BY clause yourself. Use it to see a result at a glance while you are still working in the console.
Charts & Pivot works on the result of the last completed run in the console. Run a query first, for example by following Run your first query. Until a query has run, the panel shows No Query Results and "Run a query to use Charts & Pivot".
If the Results Panel is hidden, press Alt+2 to show it.
| Chart type | Drop zones |
|---|---|
| Bar, Line | X-Axis (one field), Series (Y-Values), Legend (one field) |
| Pie | Category (one field), Values |
| Radial | Category (one field), Values (one field) |
| Radar | Category (one field), Series (Y-Values) (one field) |
| Combined | X-Axis (one field), Bar Series (Left Y-Axis), Line Series (Right Y-Axis) |
| Pivot | Rows, Columns, Values |
An empty zone reads Drop here, and a full one reads Max fields reached. To find a field quickly, type in Search fields... above the list; when nothing matches, the list reads No fields match.
Before you place any fields, the chart area reads Configure chart fields with "Drag fields from Available Fields to create a chart". Once fields are in place it reads Click Execute to generate chart, and while the calculation runs it reads Aggregating data....
The pivot table appears as a grid. A header pairs a column value with the value field in brackets; when it carries several keys they join with -, and a missing value shows as (null). If you run it with nothing in Values, a notification No value fields asks you to add at least one field to Values.
Before you place any fields, the pivot area reads Drag fields to create a pivot table with "Drop fields into Rows, Columns, or Values to begin". Once a value field is in place it reads Click Execute to run aggregation, and while the calculation runs it reads Running aggregation....
Charts show a limited number of points by default. When your result has more points than that, the chart shows a badge such as Showing 20 of 57. Hover over the badge to read "Chart limit reached. Showing the first 20 of 57 data points."
To show more points, click a number next to Limit. When the result has more than one page of points, use the arrows beside Limit to move between pages. The counter shows the current page, for example 2/3. On later pages the badge shows a range, such as Showing 21-40 of 57.
Double-click a field in its drop zone and type the name you want. The name changes as you type; press Enter to keep it, or Escape to cancel. If another field already uses the name, the text turns red. Hover over it to read "A field with this name already exists". Enter does not finish until the name is unique. If you click away while the name is still a duplicate, the field goes back to its last valid name.
To return a field to its column name, click the small reset icon, Reset custom name, beside the field. To take a field out of a zone, click the small X on it.
The calculation names an aggregated column after the operation and the field, such as sum(amount). A custom field name replaces that label.
The buttons on the right edge of the chart panel do the following:
| Button (tooltip) | What it does |
|---|---|
| Execute chart aggregation (play) | Runs the calculation. |
| Stop chart aggregation (square) | Replaces the play button while a calculation runs. It cancels the calculation and shows Aggregation cancelled. |
| Reset chart | Clears the placed fields and the chart, and sets Display Mode back to Line. The chart type stays. |
| Hide chart controls or Show chart controls | Hides or shows the column with Chart Type and Available Fields, so the chart uses the full width. |
The calculation keeps running on the server when you switch tabs or consoles. When you come back, the chart shows the stored result, with the area reading Restoring aggregation result... while it loads. Use Stop chart aggregation to end a calculation early.
| Setting | What it does | Default |
|---|---|---|
| Chart Type | Sets the kind of chart, or the pivot table. | Bar |
| Display Mode | For a Line chart, draws lines or a filled area. | Line |
| Aggregation | How a field is combined. Numeric fields offer Sum and Average. | Sum for a field in a series area; Count for a field in Values (Pie, Radial and Pivot) |
| Limit | How many points a chart shows before you page. | Bar 20, Line 100, Pie 10, Radar 8, Radial 12, Combined 20. Pivot has no limit. |
The Limit choices are: Bar and Combined 20, 30, 40, 80 or 100; Line 50, 100, 150, 200 or 300; Pie 10, 15, 20, 30 or 50; Radar 8, 12, 16, 20 or 30; Radial 12, 20, 30, 40 or 60.
Charts draw with the legend along the bottom, data labels switched on and gridlines on. If a chart has many legend entries, the legend scrolls. Hovering a slice, a series or a legend entry highlights it and dims the others; a pie shows the total in its centre until you point at a slice, and then that slice's share. Large values are shortened, such as 1., and tooltips use thousands separators, leaving out any series entry with no value.
Charts & Pivot needs no permission of its own. You can use it wherever you can open the Editor and run a query. For who can open the Editor, see Editor.
A run's results are kept on the server for only a few minutes after it finishes. After that, Execute chart aggregation fails with Aggregation failed. Run the query again, then click the play button.
All rows of the query result. The calculation runs over the whole result on the server, not only the page on screen. The chart then shows a limited number of points, which you can change with Limit.
No. The calculation runs the SQL of the console's last completed run, not the text you have in the editor now. If you change the query, run it before you calculate again. Filters you apply in the data grid do not change what the chart uses.
A chart is drawn only after you click the play button, Execute chart aggregation. Until then the panel reads "Click Execute to generate chart".
Bar charts show 20 points by default. Choose a higher number next to Limit, or use the arrows to page through the rest.
Legend works only when Bar or Line has at most one Series (Y-Values) field, and it takes one field. Remove the extra series fields first.
A new run clears the placed fields, but keeps the chart type and display mode. Drag the fields in again.
Yes. The calculation runs on the server and keeps going while you switch tabs or consoles. When you come back, the chart loads the stored result.
No. Average and Sum are offered only for numeric fields. Text fields offer Min, Max, Count and Distinct.
Choose Pivot, drag at least one field into Values, and optionally drag fields into Rows and Columns. Then click the play button, Execute chart aggregation.
Yes. Choose Combined. Bar series use the left Y-axis and line series use the right Y-axis.
The calculation could not finish. Read the message under the heading. Results are kept for only a few minutes after a run, so if the run is older than that, run the query again and click the play button.