The Charting component of AQT gives you the ability to chart your data.

The Chart window can be invoked in a number of ways:

• from the Data Display window, click on Tools > Chart Data, or the Chart button in the toolbar

• from the Run SQL window, click on Run > Chart SQL

• from the Database Explorer window, click on a table then Table > Chart Table

When you are first building a chart for a table/query, it is highly recommended that you use the first of these options. This is because, in building a chart, AQT will frequently re-fetch the data. When building a chart from the Data Display window, AQT just re-reads the display grid. For the other methods, AQT will need to re-fetch the data from the database. This can be time-consuming, and will result in a greater load on the database server.

Charting is explained in one of the following topics:

• How Charting Works gives a good introduction to charting

• Using the Charting window gives you an overview of the use of the Charting window.

• Once you have developed some charts, you can view then in the Query Explorer window. Charts can easily be refreshed with the current data, making them a powerful way of developing a pre-build report. This is explained in more detail in the section Viewing Charts in the Query Explorer.

How Charting Works

A chart will display a graphical representation of the data in your table/query.

• there will one data point in your chart per row in your table/query.

  It's not recommended that your chart tables with a large number of rows: the charting will be very slow and the results may not be meaningful. You may wish to summarize your data before charting it. The Summarize Data window helps you summarize your table, and this links into the Charting window.

• to chart a table/query, you need to specify a Data Column. The values of this column determines the height of the data points on the vertical axis. The Data Column must be a numeric column.

  You cannot chart a table/query if it contains no numeric columns.

• you also specify a Label Column. The values of this determine how the data points in the chart will be labelled on the horizontal axis.

The data points are spaced evenly on the horizontal axis. The exception to this is when Scaling is used - in this case the position of the data points on the horizontal axis is determined by the value of the label column. Scaling can only be used when the label column is numeric or date.

The above chart is an example of a Bar chart. AQT can create other types of charts, such as Line, Area, Point and Pie.

Multiple Series

A set of data points for a column is called a Series. You can have multiple series - eg. you can can chart multiple columns.

Each Series:

• can be of different Type (eg. Bar, Line etc)

• can have different color, style and other attributes

• can use the scale on either left or right axis.

Using the Charting window

The following diagram shows you the Charting window, and the different components of it.

Building the Chart

To build the chart, use the tabs on the left:

• Columns specifies the Data Columns and Label Column for the chart

• Chart Type specifies the chart type (Bar, Line etc) plus other attributes of the chart layout

• Series allows you to specify attributes of a particular series. This includes:

  • the Type of each series (Bar, Line etc) - giving you the ability to have different types for each series

  • the attributes of each series (color, width etc).

  • which Axis the series is to use

  • the Title of the Series (this appears in the Legend)

• Labels. Allows you to specify the Labels that appear with each data point.

• Legend specifies the attributes of the Legend.

• Axes specifies the attributes of the Axes.

• Data. This tab:

  • shows you the details of the query that is run to produce the chart data (this query can be modified if required)

  • allows you to specify whether you want to chart all the data, or just some. While developing a chart, it is often useful to chart a subset of the data.

  • allows you to specify any Exclude values. This provides a simple way to remove unwanted or invalid data from the chart.

• Other. This allows you to specify other miscellaneous attributes:

  • colors for the chart plus background area

  • headers and footers, including the system-generated footer

Refresh

This refreshes the chart data from the data source:

• if the data comes from a display grid, the grid is re-read. This can be useful after you have refreshed the grid, or have sorted the data in a different order.

• if the data comes from a query, the query is rerun.

Saving a Chart

You can save your chart with File > Save, or the Save button on the toolbar. The chart will be saved to disk as a *.xcf (chart) file.

Once the chart has been saved, it will be visible in the Query Explorer. The Query Explorer provides a way of quickly seeing all the charts you have developed, and updating them with the current data. This is discussed more fully in Viewing Charts in the Query Explorer.

Commonly used charts can be defined as a Favorite.

Opening a Chart

You can open an existing chart with File > Open Chart for Viewing. The chart will be opened in the chart window in Locked mode. Locked mode is a safety mechanism to prevent you inadvertently making a change that affects the underlying data behind the chart. You will be able to make changes to the chart appearance, however you will not be able to Refresh the chart or change the Data Columns etc.

You can take the chart out of Locked mode with Chart > Locked.

Apply an existing Chart as a Template

This is a very powerful feature of AQT Charting. You can take any existing chart (xcf file) and apply it as a template to your current chart. This is done with File > Open Chart as Template.

To understand how this works, you should realize that an AQT chart consists of two parts:

• the definition / layout of the chart

• the chart data, and the definition of the query that is run to produce the data

When Open Chart as Template is used, AQT will open the selected chart and will apply the current data / query to that chart. Once you have developed a chart style, this allows you to apply that style to other charts.

Default Chart for a Table/Query

When you save the chart for a table or query, it will (by default) be saved with the name of the table/query. For instance, if you are charting table TEST.SALES_DETAILS, the default chart name will be test_sales_details.xcf.

The next time you chart the table, AQT will look for the default chart file. It is exists, AQT will apply that chart as a template to the existing table data.

This feature means that, once you have developed a chart for a table, the chart will automatically be re-displayed the next time you chart the table.

Specifying the columns to be charted

Specifying properties of the Series

Use the Series tab to specify more detailed properties of the chart Series (columns you are charting).

Other Properties

Cumulative

See Cumulative Series.

Use Additional Axis

The standard chart has two axes:

• the Left Axis

• the Right Axis

If you are charting more than two series, you can:

• chart the series against one of the left/right axes. This would be appropriate if the series contain data within a similar range of values

• chart the series against an axis specific for that series. You would do this by checking Use Addition Axis. By default, the axis will appear either to the left of the left axis. If Use Right Axis is also checked, the additional axis will be placed right of the right axis.

You can adjust the position and other properties of the Additional Axes on the Axes tab.

How Charting deals with Null values

When your Series has NULL values, this can cause a problem with some types of charts. In particular, Line and Area charts do not display correctly when some values are missing.

There are a number of options which specify how to deal with NULLs.

Exclude where all values NULL

This option is on the Data tab.

When this is specified, a point will not be charted if the value is Null.

If you are charting multiple series, a point will only be excluded if the value is NULL for all series.

Include NULL values (will be displayed as 0)

This option is on the Series tab, and applies just to the selected series.

When this option is specified, the value will be charted by giving it a value of zero. For bar, point and bubble charts, this can be an appropriate way of depicting a NULL value.

Include NULL values (use avg)

This option is on the Series tab, and applies just to the selected series.

When this option is specified, the value will be charted by averaging the nearest non-NULL values. This is an appropriate option for line and area charts as it creates a smooth line between values.

AQT just uses a simple interpolation of nearest values. It does not use a more sophisticated averaging method.

Specifying the type of Chart

AQT supports 5 different types of charts:

• bar chart

• line chart

• pie chart

• area chart

• point chart

• bubble chart

There is no "right" type of chart - you would use whichever one is most suitable to the type of your data.

Bar Chart

Line Chart

Pie Chart

Detaching values from the Pie chart

With the Pie chart, you can detach values / slices from the chart. This is useful for highlighting particular values.

Area Chart

Point Chart

Bubble Chart

Other styles of charts

You can specify a number of other styles of charts.

• scaling the x (horizontal) axis

• cumulative series

• stacked chart

• 3D chart

• multiple chart types

• horizontal chart

Scaling the x (horizontal) axis

By default, the values in the chart are evenly spaced horizontally. The label column is used to identify the values in the series.

However, if the label column is either numeric or date, you may wish to scale the x axis. When this is specifed, the values are spaced horzontally according to the values of the label column.

• you specify scaling on the Columns tab: Use this column to Scale the X axis

• this option is only available for line, point and area charts.

Cumulative Series

Sometimes it is useful to have cumulative values in the chart, rather than individual values.

You can specify this in two places:

• on the Chart Type tab, check Cumulative Values. This will apply to all series in the chart

• on the Series tab, check Cumulative. This will apply to only the selected series.

Stacked Chart

This style of chart applies when you have more than one series. The Stacked property specifies how the two sets of values are displayed in relation to each other. In the Chart Type tag, specify one of:

• none. Only one series is displayed.

• clustered. The two series are displayed separately. This is the default.

• stacked. The series are displayed "stacked" on top of each other.

• stacked percent. The series are stacked and shown as a percentage

3D Chart

Due to limitation of the Charting component, 3D charts are only available in 32-bit AQT

Scaling the z-axis

When you have a 3D chart, you can scale the z-axis.

Multiple Chart Types

When you have more than one series, you can use a different chart type for each series.

You cannot have a Pie chart in addition to other types.

Horizontal Chart

Specifying other properties of the chart

There are a number of other properties you can specify with a chart

• Legend

• Title

• Labels

• Axes

• Data

Customizing the Axes

You customize the Axes on the Axes tab

Specifying the Legend

The Legend is specified on the OLegend tab

Specifying Labels

The Labels for the Series are specified on the Labels tab

Specifying Titles

Chart Titles are specified on the Other tab

Specifying the Query which populates the chart

The Data tab shows the query that retrieves the data for the chart.

Using the Advanced Chart Editor

You can specify more advanced properties of the chart with the Advanced Chart Editor.

Using the Chart toolbar

The chart toolbar provides some useful functions for manipulating your chart.

Using Data Zoom and Pan

The Charting window allows you to zoom to a selected set of data. This is useful if you have charted a lot of data and want a closer view of a part of it.

To activate Data Zoom, click on the Data Zoom button in the toolbar.

Next, drag the mouse over an area of the chart to select an area of the chart you are wishing to see in more detail.

The chart will show the selected section of the chart.

Panning Your Chart

Once you have zoomed to smaller section of your chart, you can hold down the right mouse button and move the chart left/right and up/down. This allows you to move through your chart.

If you are charting multiple series, moving up / down will only move the series which is displayed on the primary vertical axis. The other series and the right axis will not be moved.

Returning to Full Chart Display

To return to the full display, click once in the chart.

Saving and Opening your Chart

Saving your Chart

Clicking on Save (Ctrl+S) will save your chart to disk.

The chart will be saved as an *xcf file (Xceed Chart File). This will include:

• the layout of the chart

• the data values

• the details of the query used to get the data for the chart

Opening your Chart

You can open a saved chart with File > Open Chart for viewing.

The chart will be opened in the chart window but will be Locked to prevent it being altered inadvertently.

If you wish to amend the chart, you can click on the Locked (Ctrl+L) button to unlock it.

Opening a chart as a Template

You can open a saved chart as a template with File >Open Chart as template.

When this is done, the styles and other formatting from the saved chart are applied to your existing chart. However the data from your existing chart is left unchanged.

This is useful way of developing a style for your charts, then applying this style to other charts.

Automatically Applying a template to your chart

When you save a chart, the default file name is based on the name of the table in your query. For instance, if you are charting table SALES.MONTLY_SUMMARY, the default filename will be sales_monthly_summary.xcf.

When you next chart this table, AQT will see if there is a chart file with this default name. If so, this chart file will be applied as a template to your new chart. This allows AQT to "remember" how a table was charted, and use the same style the next time you chart it.

Displaying your Chart

Once you have saved a chart, it will be visible in the Query Explorer or as a Favorite. This is discussed in the topic Viewing Charts in the Query Explorer.

Viewing Charts in the Query Explorer

Once you have saved some charts, you can view these charts in the Query Explorer.

This provides a very simple and quick way of viewing all the charts you have developed.

Note: that you can see the date / time the chart was created in the system-generated footer. You can also see this information by changing the drop-down at the top of the panel to Details of Chart.

Refresh Data

Clicking on Refresh Data will rerun the query to refresh the chart with the current data . Note that you need to be signed onto the database this query runs against before you can do this. You can then click on Save to update the saved-chart with this more recent data.

This provides a powerful mechanism for developing reports, and having the result displayed in graphical format.

Refresh Data (against this database)

Normally, Refresh Data runs the chart-query against the database the chart was defined as using. You may wish to run the chart against a different database. Some circumstances in which you may wish to do this are:

• you have multiple databases on which similar tables; the chart can run against any of these

• you have copied the chart to a different machine; the ODBC Datasource for the database has a different name on this machine

When you click on Refresh Data (against this database), the chart-query will be run against the current database.

If you then right-click the chart and select Save, the chart details will be amended to have the chart database being the current database.

Show Data Values

This will show you the Data Values which are behind the chart.

Deploying Charts to other Users

Once you have developed a chart, you can send them to others users for them to view and run. All the information for a chart is contained in the chart (*.xcf) file. You can send this file to another user; once they have copied it into their AQT query directory, they will be able to see it in their Query Explorer window.

Favorites

You can define a chart file as a Favorite.

Creating Charts in Batch

With AQT you can create charts in batch / unattended mode. This can be useful for creating regular charts with a script or in an overnight batch process.

To do this:

• build the chart in the Charting window

• click on File > Save Chart to save the chart as an *.xcf file.

  This chart file will be a template for the charts created by your batch script. Most of the information about your chart will be contained with the chart file. This includes information such as as chart type, series colors, legends, labels etc.

  When you run the chart in batch, you just need to provide information on the data for the chart - eg. the query text, the database the query runs against etc.

• on the Chart window, go File > View Chart Script. This will show you the script which defines your chart. Copy this script to the SQL window.

• you will need to amend the script to include either a saveas or exportfile parameter. You must specify one of these parameters when running the script in batch mode

  • the saveas parameter specifies the name of the resultant Chart (.xcf file)

  • the exportfile parameter specifies the name of an image file the chart is to be exported to. This can be of types jpg, bmp, png, gif, tif or wmf. Exporting to a pdf file is not supported in batch.

  • you can have both a saveas and an exportfile parameter.

• if you are exporting the chart to an image file you can also specify the dimensions of the image file. This is given with the exportimagesize parameter, example exportimagesize=(800,600). In this the image will have a width of 800 pixels and a height of 600 pixels.

• the script will have a Caption and Footer parameters which are not used when the script is run in batch mode; these parameters can be removed.

• you can run this script from the Run SQL window:

  • the chart will be created and written to the saveas file, and/or exported to the exportfile file.

  • you can view the saveas file by going to the Database Explorer window, clicking on View > Queries. The chart will shown in the list of queries. When you click on this you will be shown the chart in the right pane.

• once you have tested the chart script, you can incorporate it into a batch script using the procedure outlined in Unattended Batch Mode.

• when the chart is created, it will have a Footer which will give the date / time the chart was created. You may also wish to amend the saveas or exportfile filename and / or Header to be the different every time a chart is created.

Example:

--aqt chart,series=(col=Sum_Of_quantity_ordered,title=Total Ordered),labelcol=part_number,header="Status of Orders",layout=order_line.xcf,sql="select part_number, sum(quantity_ordered) as Sum_Of_quantity_ordered from Order_Line group by part_number",dbs=AQTDemo,saveas=order_line_20110525.xcf,exportfile=order_line_20110525.jpg,exportimagesize=(800,600)

If the saveas or exportfile file names are not fully qualified, they will be created in the Queries directory.