|
| | | Advanced SQL Charts ToolIt’s always important to consider the nature of the data to be displayed when you choose a chart type. DatabaseSpy advanced styles and charting features empower you to create the most accurate and vivid graphical representation of your SQL query results. DatabaseSpy® 2012 extends charting functionality described on the SQL Charts Tool page with advanced chart types to graphically represent SQL query results:
DatabaseSpy Advanced SQL Charts Supported DatabasesDatabaseSpy® 2012 supports multiple database types and can generate charts from the results of SQL queries to the following databases:
Stacked Bar ChartsStacked bar charts are a variation on bar chart presentation and are especially useful when you need to illustrate multiple ranges of data. Stacked bar charts are also useful to more clearly display data in a smaller area. The images below show a SQL query for sales by region over two years, the query results, and the a stacked bar chart to illustrate the data.
Note that the combined height of each stack in the stacked bar chart represents the total sales over the two-year period for each Territory, since the sales for Last Year are added above the Year To Date numbers. Stacked bar charts complement regular bar charts and 3-D bar charts to offer users the greatest flexibility in illustrating SQL query results. If you prefer horizontal bars, a checkbox labeled Draw X and Y exchanged in the Change Appearance tab lets you select that orientation.
This orientation option is also available for other 2-D bar charts, line charts, area charts, and candlestick charts. Area ChartsArea charts are similar to line charts, with shading applied to make a more graphically appealing display. The area chart below shows a record of temperature and humidity changes by hour over the course of one day. Creative application of color can help you emphasize the point!
To successfully build an area chart, you should consider the values in each data category. As the area chart is constructed, each category forms an opaque layer on top of the layers for data retrieved previously. In the case illustrated above, Temperature was always a larger number than Humidity, so the SQL query was constructed to retrieve the Temperature value before Humidity to prevent Temperature from acting like a curtain to hide the Humidity data.
However, if your SQL query is constructed to return database columns in a sequence where the values arrive in increasing order, the last layer would overlap and hide all the preceding layers. In that case, the DatabaseSpy® 2012 chart heading titled Select Data lets you add and delete columns from the results to re-sequence the data correctly. The Select Data column also lets you edit the names assigned to each column on the X-axis label.
As an alternative solution, the Transparency option in the Change Appearance tab lets you adjust color levels to allow hidden layers to show through. Stacked Area ChartsAs implied by their name, stacked area charts layer the columns of a data set to illustrate the overall sum of a data series. Stacked area charts also eliminate the potential overlapping data problem that can occur with regular area charts. The chart below shows a table of air passenger revenue miles traveled by month, with individual regions for domestic and international travel.
The corresponding SQL query and partial results table are shown below. The Stacked Area chart creates a graphical representation of the total of Domestic and International miles, even though the total miles value was not returned by the SQL query. This is apparent at the top of the January entry, where the International region intersects the Y axis just below 600 (the original data showed 392 million Domestic miles and 181 million International miles, for a total of 573).
The stacked area chart provides a graphical representation of the total of Domestic and International miles, even though the total miles value was not part of the results set returned by the query. The top of the January International region representing the total intersects the Y axis just below 600 (392 Domestic miles + 181 International miles = 573). Candlestick ChartsCandlestick charts were originally developed by a wealthy Japanese businessman who began trading at the local rice exchange around the year 1750. He kept records of the local market psychology, learning to boost his profits by carefully monitoring prices and not rushing into trades. Today, candlestick charts are used to represent financial data such as stock prices over a period of time. Every day the market is open, each stock has four relevant data points that can be rendered in a candlestick chart: the price at market opening, the price when the market closed, the high price during the day, and the low price during the day. Investors and financial analysts like to view these indicators to gauge the stock’s performance over a period of time. In the candlestick chart below, each solid bar represents the range between the opening and closing price and the thin vertical line through each bar shows the extent of the high and low prices for the day.
In this version of the chart, following common convention, the color of each bar signals whether the stock was up or down for the day. If the bar is green, the stock was up for the day – it opened at the price indicated by the bottom of the bar and closed at the price indicated by the top. If the stock was down for the day, the bar is red and the symbolism is reversed – the stock opened at the price indicated at the top of the bar and closed at the price shown by the bottom. Numerous settings are available to define line and fill colors, the Y-axis range and values, and more. Originally candlestick charts used empty bars to indicate the price increased and solid bars to indicate price decreases. DatabaseSpy® 2012 lets you select this option, which is especially useful when the chart will be printed in black and white.
Another candlestick chart variation omits the opening price and simply illustrates the range with a vertical line and the closing price with a horizontal line. DatabaseSpy® 2012 automatically supports this format for SQL queries that only return the high, low, and closing prices.
Chart OverlaysThe overlays feature lets you combine multiple charts in a single image. Each overlay chart can even be generated from a separate database query. The image below shows two Select statements that retrieve price and volume data from historical stock market statistics, with two separate results tabs named Prices and Volume.
The results are easily combined to show a typical display of the stock’s performance, with the daily prices in a candlestick chart and daily volume in a bar chart created as an overlay.
Background Images and Color GradientsThe ability to specify background color gradients and background images gives you even more flexibility for creating customized, eye-catching charts. Overlaying one chart on another allows lets you illustrate multiple data sets with different Y-axes and types.
The Change Appearance dialog also lets you select a background image, as in the Winter Games chart above, or apply a background color gradient, as in the Summer 2010 chart below.
Select Position of X- and Y- Axis LabelsYou can even select the position of labels for the X- and Y- chart axes using any of the options shown below.
For instance, in the Winter Games chart above, the X-axis is labeled at a value, in this case, 10, and the Y-axis is positioned on the right-hand side of the chart. Whether you are a developer, business analyst, research professional, or other database user, the advanced charting functionality in DatabaseSpy® 2012 can generate sophisticated and attractive charts for your data reports – download a free 30-day trial today! | |
| | |||||||||||||||||||
| Company | Legal | Press | Partners | Careers | Sitemap | Contact Us | Altova Blog | Mobile | Full Site | |||
|
