As retrieving data from the database (based on which the chart will be plotted) has a great importance in the chart-creation process, we have included in the documentation a set of examples which will help you to better understand the database-table method. After reading the examples below, any problem you might meet when retrieving the data from the chart using the database-table method will be overcome.
As was presented in the Database-table method section of the documentation, when using this method there are three steps you need to cover in order to define the target data for your chart:
The first step will be common for all the examples presented in this section, as we will work on the same database tables.
The examples presented treat separately each available type in which to group the data on the chart (on the category axis) in step 2; also, how the series' configuration will be affected by the selected grouping type, configuration done in step 3. Finally, how to use a custom data source will be described in the direct values example.
In the examples presented in this section, we will use some tables from a database entitled "Car Sales", belonging to a company which sells used cars. We will use only two tables for our examples: CarsForSale and CarsSold.
The CarsForSale table contains information about: a unique ID for each car added to the table, the car manufacturer name, the car model name, the price of acquisition for the car, and the date when it was acquired.
CarsForSale database table
The CarsSold table contains information about: a unique ID for each car sold, the ID of the car from the CarsForSale table, the price for which the car was sold, and the date when the car was sold.
CarsSold table
In order to download the archive which contains the SQL database used for these example, click here. Following, import it to your server, configure the Dreamweaver database connection, and then configure the FusionCharts for DW corresponding option:
Select Database = Car Sales
In this example we will configure the data required to plot the expenses made while acquiring cars in the years 2007 and 2008, arranging the data on quarters.
Select the Data Grouped By Date option in the interface.
Configure the Category parameters as displayed below:
Group By = Quarter
Display Format = Quarter 1 2009
Min Value = 0
Max Value = 4
Plot Complete Date Informatiun = Disabled
In this example, we want to plot the total expenses on quarters, for two years - 2007 and 2008; in order to obtain the total expenses, we will apply Sum as the calculation method. As we want to plot two data series, one for 2007 and one for 2008, we will use filtering to separate the data. Configure the Series parameters as displayed below.
The first data series, Expenses 2007:
Select Add Series
Data Source = carsforsale
Value Column = carsforsale.acquisitionPrice
Calculation = sum
Date Column = carsforsale.dateAcquired
Date Column Format = YYYY-MM-DD
Select Set Filter to open the filtering panel and add a simple filter on the dateAcquired column to contain the string 2007
The second data series, Expenses 2008:
Select Add Series
Data Source =carsforsale
Value Column = carsforsale.acquisitionPrice
Calculation = sum
Date Column = carsforsale.dateAcquired
Date Column Format = YYYY-MM-DD
Select Set Filter to open the filtering panel and add a simple filter on the dateAcquired column to contain the string 2008
Select Preview Data to make sure that the data you want to plot is accurate.
Preview data
In this example we will configure the data required to plot the number of available cars for sale in the years 2007 and 2008, and arrange the data on acquisition price intervals.
Select the Data Grouped By Intervals option in the interface.
Configure the Category parameters as displayed below:
Min = 0
Max = 30000
Step = 5000
In this example, we want to plot the number of cars available for sale, for two years - 2007 and 2008; in order to obtain the number of cars, we will apply Count as the calculation method. As we want to plot two data series, one for 2007 and one for 2008, we will use filtering to separate the data. Configure the Series parameters as displayed below.
The first data series, Available Cars 2007:
Select Add Series
Data Source = carsforsale
Value Column = carsforsale.carForSaleID
Calculation = count
Interval Column = carsforsale.acquisitionPrice
Select Set Filter to open the filtering panel and add a simple filter on the dateAcquired column to contain the string 2007
The second data series, Available Cars 2008:
Select Add Series
Data Source = carsforsale
Value Column = carsforsale.carForSaleID
Calculation = count
Interval Column = carsforsale.acquisitionPrice
Select Set Filter to open the filtering panel and add a simple filter on the dateAcquired column to contain the string 2008
Select Preview Data to make sure that the data you want to plot is accurate.
Preview data
In this example we will configure the data required to plot the total sales and the number of cars sold, and arrange the data by manufacturer name.
Select the Data Grouped By Categories option in the interface.
Configure the Category parameters as displayed below:
Data Source = carsforsale
ID Column = carsforsale.carForSaleID
Label Column = carsforsale.manufacturerName
In this example we will plot two series: one for the total sales amount (we will apply Sum) and one for the number of cars sold (we will apply Count). Configure the Series parameters as displayed below.
The first data series, Total Sales:
Select Add Series
Data Source = carssold
Value Column = carssold.salePrice
Calculation = sum
Foreign Key = carssold.carForSaleID
The second data series, Number Of Cars Sold:
Select Add Series
Data Source = carssold
Value Column = carssold.carSoldID
Calculation = count
Foreign Key = carssold.carForSaleID
Select Preview Data to make sure that the data you want to plot is accurate.
Preview data
In this example we will configure the data required to plot the profit made by the car company, and arrange the data by car model. As we do not have a table column which holds the profit, we will add a custom data source, obtained by joining the two tables and adding a calculated column.
Select the Direct Values option in the interface.
As we will use a custom data source for this example, select the Add option adjacent to the Data Source option. The Custom Data Source panel contains two methods in which you can configure the data: Basic and Advanced; we will use both of them below to obtain the same data. You can solve the example by using one of the two examples.
Configure the custom source parameters as displayed below:
Enter a name for the custom source CustomTable
Link Table CarsSold
Click Add to join to another table; select CarsForSale, CarsForSale.carForSaleID, CarsSold. carForSaleID
Click Add to add a calculated column; insert CarsSold.salePrice - CarsForSale.acquisitionPrice, Profit
Click OK to add the custom data source
As this custom data source configuration method required to insert all the data source elements - tables, columns - we will limit the data source to the columns we are directly interested in to plot the chart for this example; hence, the custom data source will contain fewer columns than in the Basic data source configuration method above. Configure the custom source parameters as displayed below:
Enter a name for the custom source CustomTable
Column definition SQL CarsSold.carSoldID, CarsSold.carForSaleID, CarsForSale.modelName, (CarsForSale.acquisitionPrice-CarsSold.salePrice) as Profit
Table definition SQL CarsSold LEFT JOIN CarsForSale ON CarsSold.carForSaleID=CarsForSale.carForSaleID
Click OK to add the custom data source
Following, configure the Category parameters as displayed below:
Data Source = CustomTable
Category Column = CustomTable.modelName
In this example we will plot the profit made by the company; for this, we will use the custom data source we configured above. Configure the Series parameters as displayed below.
Select Add Series
Data Source = CustomTable
Series Column = CustomTable.Profit
Select Preview Data to make sure that the data you want to plot is accurate.