Working with Grids and Pivot Grids (Revision #0)

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 run on any database Aqua Data Studio can connect to. Grid Results are the most common method of desplaying query results, and they are also the recommended method of displaying large results sets (100,000 rows or more) as memory consumption for them have been highly optimized. Other features of the Grids Results include sorting, column rearrangement, quickfiltering displayed results, aggregate functions on selected cells, Script to Window for AquaCommands, 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 displays 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 depressed during sorting, multiple columns can be sorted in sequence. Resorting columns also changes how the chart displays.

1. Click on Header	2. Rows Sort Ascending or Descending	3. Control Click to multiple Column sort

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 reexecute.

1. Drag Column Header	2. Release Column header to Rearrange

Quickfilter - There is a Quickfilter on the upper right corner of grid with which you use to 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 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 clicked on. 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 displaying in the Fields List within Pivot Grid Results.

1. Before Filtering	2. Typing Quickfilters 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 the quickfilter 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.

Select Data, Choose Aggregate Functions

Pivot Grid Custom Filters allow filtering on day if your Pivot Grid contains a date field as a Filter Field, then the Custom Filter dropdown for your date field contains a set of conditions that allow you to filter "on" the days of the week.

Pivot Grid Custom Filter	Pivot Grid Custom Filter Days

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 reexecute and generate the same grids and graphs again as many times as needed, and as data changes the chart dynamically changes with each reexecution of the query. Pivot Grids Results also have the Script to Window feature for generating Aqua Commands scripts.

1. Right Click select Script to Window	2. View resulting Aqua Commands

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.

Save As Aqua Commands script

Selecting View in Excel Viewer  after selecting cells and right clicking displays 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.

1. Select grid data	2. Right click select Excel Viewer

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.

ADS Printing Options

Grid and Grid Chart Process

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

1. Open the Query Analyzer	2. Execute a Query	3. Open Grid tab click Show Chart	4. Chart Display Area opens	5. Select Category in Display Area	6. Select Series in Display Area

7. Set Chart Options	8. Maximize Chart View	9. Column Chart	10. Chart Types	11. Column Chart	12. Chart Themes

13. Swap 3D to 2D	14. Save Chart as Image	15. Script the Chart to Window	16. Save and Share the Chart	17. Generate AquaScript	18. Save AquaScript to a Project

19. AquaScript in AquaScript Editor

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 to 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 particulary 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, if 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. Open Pivot Grid click Show Chart	2. Drag Fields into Row Field	3. Drag Fields into Column Field	4. Select Column Pivot Chart Type	5. Edit Column Fields	6. Line Pivot Chart selected

7. Review Aqua Command Script	8. View Pivot Grid in Excel Viewer	9. ADS Pivot Grid in Excel	10. Save as Excel Worksheet	11. Script Pivot Grid to Window	12. Pivot Grid Options

13. Pivot Grid Aggregate Functions	14. Save Pivot Grid Chart as Image	15. File saved as SQL with Aqua Commands	16. Generate AquaScript	17. Save AquaScript to a AquaProject	18. AquaScript in AquaScript Editor

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 2 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 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 splitpane providing full control over 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.