In the database-table method all data selection, filtering, grouping or changing by using various operations are configured from the FusionCharts interface. The advantage of this method is that you don’t need to write SQL queries, or use recordsets; the necessary SQL queries are generated automatically based on the configuration of the interface.
Database-table method
In order to use the database-table method, you need to:
The steps enumerated above are described in detail in the sections below.
Use the Connection option, select a connection to the database from which to retrieve the data you want to plot in a chart. If there are no connections available, you need to create one in Dreamweaver.
Select a database connection
In the Plot Type tab of the user interface, select one of the available options in which the category data will be grouped.
Select the Plot Type
Depending on the selected option, different configuration options will appear.
By using this grouping type, the data series will be displayed in the chart with respect to a specific period of time (hour, month, quarter and so on).
Group by date configuration
The category can be configured by using the following parameters:
Group By The data will be grouped with respect to the selected period of time. The available values are: Hour, Day, Month, Quarter, Semester and Year.
Display Format The selected period of time can have various appearances; for each of the Group By values, there are the following options available (an example for each option is available in the adjacent bracket):
Hour: HH (14), HH:MM (14:00), AM/PM (2:00 PM) and HH:MM:SS (14:00:30).
Day: DD (03), DD/MM (03/01), DD MMMM (03 Jan), DD Month (03 January), MM DD (Jan 03), MM/DD (01/03), MMMM / DD (Jan / 03), Month / DD (January / 03), Day of the week - Short (Mon) and Day of the week - Long (Monday).
Month: MM (01), MMMM (Jan), Month (January), MM YYYY (01 2009), MMMM YYYY (Jan 2009) and Month YYYY (January 2009).
Quarter: Short (Q1), Short + Year (Q1 2009), Long (Quarter 1) and Long + Year (Quarter 1 2009).
Semester: Short (S1), Short + Year (S1 2009), Long (Semester 1) and Long + Year (Semester 1 2009).
Year: YY (09), YYYY (2009).
Min Value The minimum value allowed to be displayed on the chart; the available values depend of the selected Group By option.
Max Value The maximum value allowed to be displayed on the chart; the available values depend of the selected Group By option.
Plot Complete Date information If enabled, retrieves from the database the full date information. For example, the month would be plotted as March 08 if the option is enabled, as opposed to March with the option disabled.
By using this grouping type, the data series will be displayed with respect to some number intervals.
Group by intervals configuration
The category can be configured by using the following parameters:
Min Value The minimum value of the category series.
Max Value The maximum value of the category series.
Step The step used to create the category items, by jumping through the interval from the minimum value to the maximum value.
By using this grouping type, the data series will be displayed with respect to a column in a linked database table.
Group by categories configuration
The category can be configured by using the following parameters:
Data Source The table which contains the data which will be used as the chart's category data.
ID Column The column which contains the unique ID's for the category data, to which the data series items have to be connected.
Label Column The column which contains the labels which will be displayed on the category axis.
By using this option, the category will be taken from the selected column of the selected database table.
Direct values configuration
Data Source The table which contains the data which will be used as the chart's category data.
In case you want to use more than one database table as the data source, add a Custom Data Source.
Category Column The column which contains the labels which will be displayed on the category axis.
In order to define the data series for you chart, you have to select a table from the database; following, select the table column which contains the data you want to plot as a data series. Furthermore, you can apply an operation on the groups obtained as a result of the selected grouping type.
Data Source The database table which contains the data which you would like to plot as a data series.
In case you want to use more than one database table as the data source, add a Custom Data Source.
Value Column The table column which contains the values which will be plotted on the chart as a data series.
Calculation The operation you want to apply on the data series, on the groups obtained as a result of the selected grouping type. The available operations are: Sum, Average, Count, Min and Max.
The Calculation option is not available for the Direct Values option.
Define the Series for your chart
Depending on the grouping type selected when the chart's category was defined, an extra option will appear, option based on which the series items will be separated into groups; the exception, for which no extra option will appear, is the Direct Values option.
Date Column The column which contains the dates for the items in the Value Column.
Date Column Format The SQL date format of the items in the Date Column; select an available option from the list, or insert the date format of your column, if no option corresponds to it.
Interval Column The column which contains the values based on which the items in the Value Column will be assigned to the interval groups.
Foreign Key The column which contains the foreign keys which point to the ID Column in the Category Table.
As you add data series, they will become available in the Series List, where each of the series can receive a custom name and a color. Moreover, if the sorting is done with respect with a data series, it has to be selected from the list by using the Ordering option. In order to filter the data series, use the Set Filter option corresponding to the the series.
The chart series list
Moreover, in order to limit the series elements which will be plotted, use the Set Filter option to filter the data by comparing it with a numeric value or a text. There are three filter types you can add:
Defined Filters list The filters which will be applied on the selected data series.
Add Simple Filter Adds a simple filter to the Defined Filters list; the filtering will be done using a column, an operator and a static value.
Add Dynamic Filter Adds a dynamic filter to the Defined Filters list. The difference with the simple filter is that the filter's value is dynamic; type the code to define the dynamic value in the Runtime Value box or use the adjacent button. Moreover, a static value must be inserted in the Default Value box to be used when previewing the data or when the dynamic data cannot be retrieved.
Delete Filters Deletes the selected filters from the Defined Filters List; use Ctrl+Click (Windows) or Command+Click (Macintosh) to select multiple filters.
Use Custom Filter Applies a custom filter on the selected data series;
is useful in cases when the filters in the Defined Filters list cannot be used to obtain the desired results. For example, when data has to be filtered to display only values outside a particular interval - the custom filter code would be (Table.Column < value) OR (Table.Column > value).
Custom Filter Contains Server Code Controls whether the code which defines the custom filter contains server code or note. An example for the custom filter is (Table.Column < $_POST[lowValue]) OR (Table.Column > $_POST[highValue]), where lowValue and highValue are dynamic data retrieved from a dynamic page.
Close Filtering Option Closes the Filters panel and applies the configured filters on the selected data series.
Filter panel
You can add more than one filter to the same data series.
| Symbol | Name | Definition |
|---|---|---|
| = | Equals | Extracts the results equal to the specified value |
| > | Greater than | Extract the results greater than the specified value |
| < | Less than | Extract the results less than the specified value |
| >= | Equal to or greater than | Extract the results equal to and greater than the specified value |
| <= | Equal to and less than | Extract the results equal to and less than the specified value |
| <> | Not equal to | Extracts the results that different than the specified value |
| N/A | Begins with | Extracts the results that begin with the entered value |
| N/A | Ends with | Extracts the results that end with the entered value |
| N/A | Contains | Extracts the results that contain the entered value |
In order to remove a data series from the chart, select it in the series list and click the Remove Series button.
The data based on which the chart will be plotted can be sorted in asceding order or in descending order based either on the chart data series or on the chart category.
Select a database connection
The sorting options can be found in the Series Data step of the data configuration wizard, and they are:
None Applies no sorting on the chart data.
Sort By Category The data will be sorted with respect to the chart's category.
Sort By Series The data will be sorted with respect to one of the chart's series, which can be selected in the series list.
Ascending Sorts the data starting with the lowest values.
Descending Sorts the data starting with the highest values.
The custom data source is useful when you want to use more than one database table as a data source. In order to put together data from two or more tables, use SQL joins. In order to add or edit a custom data source, use the option below:
Add Opens the Custom Data Source interface panel, where you can create the data source that you need.
Edit Opens the Custom Data Source interface panel, where you can edit the selected custom Data Source.
The Custom Data Source interface panels offers two methods in which you can configure the custom data: Basic and Advanced.
When using this method, a left join is used to put together the data in two tables, based on the relationship between their columns, set as primary key and foreign key. Following, another left join is used to put together the data in the resulting table with the data with a different table, and so on.
Finally, as the final data table is obtained , new columns can be added to it as the result as a calculation between other columns of the table (Table.Column * Table.Column). In order to give a new column a name, you can use an alias.
Basic custom data set
Insert the SQL code for the tables and the joins used in the Table Definition SQL box.
Finally, as the final data table is obtained , new columns can be added to it as the result as a calculation between other columns of the table (Table.Column * Table.Column). In order to give a new column a name, you can use an alias.
Advanced custom data set
You can preview the data which will be used to plot the chart by pressing the Preview Data button.
Preview data button