Working with Grids and Pivot Grids

Grids, Grid Charts, Pivot Grids, and Pivot Grid Charts are available through the Query Analyzer Window as tabs (when enabled in the Main Application Toolbar) for any queries on any database Aqua Data Studio can connect to. Grid Results are the most common method of displaying query results, and they are also the recommended method of displaying large results sets (100,000 rows or more) as memory consumption has been highly optimized. Other features of the Grids Results include sorting, column rearrangement, quick filtering displayed results, aggregate functions on selected cells, Script to Window for Aqua Commands, Save As, View in Excel Viewer, Grid Printing, and Charting. It is also possible to save charts as AquaScripts within AquaProjects by right-clicking on them.

Grid results display data from tables and queries within a grid that can be sorted and rearranged. Column data can be Sorted in descending or ascending order by left-clicking on column headers. If the Control key (CTRL) is pressed during sorting, multiple columns can be sorted in sequence. Resorting columns can also change how the chart displays.

Columns can be Rearranged left to right by dragging their column headers from one location to another. Resorting columns also changes how the chart displays. Hide and Unhide Table Columns with a right-click in a Grid Results Column Header and choose which Table Columns to display in the Results. This makes it much easier to pare down displayed results without having to change the SQL statement and re-execute.

Quickfilter - There is a Quickfilter on the upper right corner of the grid with which you can focus on a particular field and display only those results with matching text or wildcards. To the left of the filter numbers (2/15) indicate the number displayed out of the total number of query results. When you type in the quick filter, an "X" appears at the end of the filter that erases the text entered when it is selected. You can also produce a chart of the filtered data the same way that you produced a chart of the whole block of information. Any charts associated with the results of the query change dynamically based on the filtered results, as shown below. The Quickfilter is also available to narrow down the Fields displayed in the Fields List within Pivot Grid Results.

Aggregate Functions -

Aggregate Functions allow users to calculate Count, Count Numbers, Sum, Min, Max, Average, and Standard Deviation functions on selected cells in the grid data. Once data has been highlighted in the grid, right-click on the Aggregate Functions button at the top right of the grid. Choose the desired function and its results are displayed within the button itself. If the Grid Results Pane is too narrow, the Aggregate functions may collapse into an expandable box adjacent to the quick filter input form. Clicking on the collapsed box displays its contents in an overlay at the top of the grid results. Pivot Grids Results also allow Aggregate Functions on selected data.

Pivot Grid Custom Filters

- If your Pivot Grid contains a date field as a Filter Field, then the Custom Filter drop-down for your date field contains a set of conditions that allow you to filter "on" the days of the week.

The Script to Window menu option automatically generates Aqua Commands for the pivot grid/pivot chart for exporting by right-clicking within the grid view. This generated Aqua Command will include all of the settings of the grid/graph display. If these settings are stored with a query as a file, users can re-execute and generate the same grids and graphs again as many times as needed. As data changes the chart dynamically changes with each re-execution of the query. Pivot Grids Results also have the Script to Window feature for generating Aqua Commands scripts.

After generating a script, using "Save As" from within the query toolbar makes it possible to save the query and Aqua Commands together in a shared file or directory so that multiple users of Aqua Data Studio can access the layout and results.

Selecting View in Excel Viewer after selecting cells, will display selected data from the grid in Excel or Excel Viewer such as Open Office. A button in the Grid Results toolbar allows you to either View as Spreadsheet, or Email as Excel Attachment (with email SMTP settings entered in File > Options > Email) the selected cells and rows in the grid. Excel 2007 file formats are supported and you can set which version of Excel is used as the default in File > Options > General > General > Default Spreadsheet Format.

Right-clicking within a grid and selecting Print allows printing of grid results alone. The Print Preview displays margin, page orientation, and individual page previews for each page printed if the grid is wider than a single page width.

Grid and Grid Chart Process

Below is a step-by-step example of generating Grid Results and then creating charts from those results.

1. Connect to the Database Server and Open a Query Analyzer
2. Type in the Query for which you want to view the Charts, in the Query Analyzer and Execute the Query
3. Click on Grid Tab, to enable the Chart View Click on Show Charts Icon near records(s) [Fetch MetaData: /ms] [Fetch Data: /ms]
4. The Chart Display Area is blank
5. Select a Series in the Chart Display Area within the Chart's Toolbar
6. Select a Category in the Chart Display Area within the Chart's Toolbar
7. A chart is generated. Select desired Chart Options and Advanced Chart Options - Series such as series color, Legend Display, and other options
8. Maximize the Chart to check if its contents are what is needed. The view can be freely rotated in three dimensions by left-clicking and dragging the mouse across the chart. To zoom in and out, hold down shift and left-click and drag up and down across the chart. To reposition the chart, Ctrl + drag moves the chart within the view
9. Change Chart Type if needed by selecting Column Chart, Stacked Chart, Bar Chart, Area Chart, Pie Chart, Line Chart or Surface Chart. The Chart Type can be set per series by clicking on the legend and choosing a different type
10. View the list of Chart Types and swap if needed
11. The Chart changes immediately upon choosing a new Chart Type
12. Select a different Chart Theme: Apex, Civic, Concourse, Equity, Flow, Foundry, Median, etc.
13. The 2D 3D button swaps the chart from a 2D chart to a 3D chart
14. Right-click on the Chart and save it as an Image
15. You can Script to Window, creating an Aqua Command script as part of the query
16. Saving this script, with the charting Aqua Command allows sharing of the chart with others who need to run the same query
17. Right-click on a chart to Generate an AquaScript
18. Indicate a name and AquaProject to save it in
19. The AquaScript Editor will open in a tab with that AquaScript. See the AquaScripts and AquaProject section for more information.

Pivot Grid and Pivot Grid Chart Process

Pivot Table concept and Aqua Data Studio's Pivot Grids - Pivot tables, discussed as Pivot Grids within Aqua Data Studio, allow the grouping and summarizing of selected columns and rows of data to obtain a desired report. They are particularly useful with large amounts of data, helping a user to classify data by categories, and obtaining summaries without the time-consuming search that it would normally take to look through all of the data. For more information on this see Wikipedia article about Pivot Tables.

Pivot Grid configuration is persistent over multiple runs of the same query in the same query panel, i.e., if a user configures a pivot grid and then re-runs the same query, this pivot grid will maintain its configuration. As with the Grid Results, sort  Ascending or Descending after a right-click in data sorts it.  Pivot Grids also allow sorting by Data Row Fields names. For example, all of the states of the U.S. were listed under an expandable row tab within the pivot. Sorting of either kind immediately affects the Chart.

Pivot Grid configurations can be saved as XML layouts with a right-click within a Pivot and those layouts can be loaded back into existing pivot tables. Saving as XML Layout does not include a Pivot Grid Chart's settings, it is only for the Pivot Grid. A  full discussion of Aqua Data Studio's Charting Features can be found here.

Below is a step-by-step process for creating Pivot Grids with Charts. 

1. Connect to the Database Server and Open a Query Analyzer. Execute the Query. Click on the Pivot Grid Tab and click on Show Chart
2. Drag Fields from the Fields list on the right into the Row Field area in the Pivot Grid's left side where it displays "Drag Row Fields Here"
3. Drag Fields from the Fields list on the right into the Column Fields area at the top of the pivot Grid where it displays "Drag Column Fields Here"
4. In the Chart display Area, select Column Chart Type. Change Chart Type if needed by selecting Column Chart, Stacked Chart, Bar Chart, Area Chart, Pie Chart, Line Chart or Surface Chart. The Chart Type can be set per series by clicking on the legend and choosing a different type
5. Edit the Column Fields as needed. CardType is the Column Field and in the drop-down, only two items from CardType are selected so that the columns are returned on the basis of this filter.
6. Select the Chart Type in the Chart Display Area. The Chart view can be freely rotated in three dimensions by left-clicking and dragging the mouse across the chart. To zoom in and out, hold down shift and left-click and drag up and down across the chart. To reposition the chart, CTRL+drag moves the chart within the view
7. Review the Aqua Commands script this chart would create within the Chart Options Script tab. If this script were shared with Aqua Data Studio users, they would see an identical chart layout. Just like the Grid Results Charts, a right-click on the Chart allows saving as an image which can also be emailed
8. Right-click and select View in Excel Viewer to see how the Pivot Grid displays in Excel
9. View the Pivot Grid viewed within Excel
10. To save the Pivot Grid and the associated Pivot Grid Chart as an Excel Worksheet, right-click in the Pivot and select Save Results or click the Save Results icon
11. To generate Aqua Commands to script both the Pivot Grid layout and Pivot Grid Chart, right-click in the Pivot Grid and select Script to Window. Saving the Query Analyzer Window contents as a file preserves the layout and settings for later use
12. Select Pivot Grid Options as needed, any changes take place immediately in the chart. Executing the contents of the Query Analyzer will update the chart and grid with any data that has changed since the previous execution
13. Aggregate Functions like Sum, Count, Min, Max, Average, Standard Deviation are available for data selected from the Pivot Grid
14. Just like the Grid Results Charts, a right-click on the Chart allows saving as an image
15. Save the Query and Aqua Commands script for generating the Pivot Grid and Pivot Grid Chart with the layout for sharing or reuse
16. Right-click on a Pivot Chart to Generate an AquaScript.
17. Indicate a name and AquaProject to save it in.
18. The AquaScript Editor will open in a tab with that AquaScript.  See the AquaScripts and AquaProject section for more information.

Pivot Grid in Query Builder results - The Query Builder results also have a tab presenting the results as a pivot grid. The number of pivot grids corresponds to the number of result sets obtained when running the query. The pivot grids are displayed on a multi-level split-pane providing full control over the sizes and configurations of each pivot grid. See the Query Builder section for more information. The Pivot Grid within the Query Builder has exactly the same functionality as the Pivot Grid launched from the Query Analyzer.