Multi Series - Sales Chart (dynamic data)

In this section we will show you how to create a Multi-Series Column Chart to compare a company’s sales for the years 2007 and 2008.

As the target data is dynamic, it will be retrieved from a database table entitled Sales.

Click here to download the database used to create this chart

The Sales table contains an entry for each performed sale; the sale is described by the following fields: a unique ID, the surname of the customer, the value of the sale and the date on which the sale was made.

The Sales table

The Sales table

In order to obtain the data needed to compare the company’s sales for the two years, we will add two data series from the table column containing the information about the value of the sales. Following, we will filter each of the data series to display only sales which occurred in 2007, or 2008 respectively. The series will be grouped “by month” and displayed “by quarter“, and a sum will be applied on the data values for each quarter; all SQL queries needed to organize the data will be automatically generated based on a few options configured from the FusionCharts for DW user interface. The target data based on which the chart will be plotted is displayed in the table below.

Chart target data

Chart target data

Before you proceed to create your first Multi-Series chart, we suggest that you go through the 'Single Series - Sales Chart' section as some of the techniques that are needed for creating a multi-series chart have been described in detail in the 'Single Series - Sales Chart' section.

Following are the steps for creating a multi-series column chart:

  1. By default, the Data tab of the FusionCharts for DW user interface opens. Select Automatically Generate SQL Queries to us data from the tables in a database.
    2. Select the data source

    Select the data source

  2. From the list labeled Connection, select multiSeries (the connection to the database must already exist in order to do this step). Select the plot type as Data Grouped By Date, then click Next.
    3. Select the connection and the plot type

    Select the connection and the plot type

  3. From the Category Data tab, configure the Data Grouped By Date options; select Quarter for the Group By option and Quarter 1 for the Display Format option. Leave the Min Value and Max Value withe their default values, 0 and 4; finally, leave the Plot Complete Date Information option disabled. Click Next to configure the chart series data.
    4. Configure the category data

    Configure the category data

  4. From the Series Data tab, click the Add Series button to open the Add/Edit Series Form. Configure the Series options; select sales for the Data Source option, sales.SaleValue for the Value Column option, sum for the Calculation option, sales.SaleDate for the Date Column option and YYYY-MM-DD for the Date Column Format option. Finally, click the OK button to add the data series.
    5. Add a data series

    Add a data series

    Configure the data series

    Configure the data series

  5. Select the series and click the Set Filter button. Select the Add Simple Filter option and configure it to have: sales.SaleData for Column, contains for Operator and 2007 for Value. Click Save to set the filters on the data series.
    6. Set a filter on the data series

    Set a filter on the data series

    Configure the filter

    Configure the filter

  6. Repeat the two steps above to add another series and configure it exactly as before; set a filter on the series, and configure it as before, changing the Value to 2008.
  7. In order to see the data which will be plotted in the chart before configuring the chart options, use the Preview Data option. If you have any difficulties understanding the steps above, which refer to configuring the dynamic chart data, please read the Configuring Dynamic Chart Data section of the documentation.
  8. Switch to the 'Chart Selection' tab and select the 'Column' chart option from the multi-series chart category.
  9. Open the 'Titles & Axis Names' Panel, and enter the 'xAxiaName' and 'yAxisname' in the respective fields, for this chart we will set the x-axis name as 'Quarter' and the y-axis name as 'Sales'.
  10. Switch to the 'General Options' screen - and name the chart 'myfirst_ms_chart' and set the width and height to 400 pixels and 300 pixels respectively.
  11. In the 'Caption, Subcaption' panel - set the Caption as 'Sales' and Sub Caption as 'For 2007 and 2008'.
  12. Check the 'Show Advanced Options' check box which is located in the upper portion of the 'General Options' screen. Now, go to the 'Number Formatting and Scaling' panel and enter '$' into the 'Number Prefix' field.
  13. Use the 'Open Preview' button to preview the chart and then click the 'OK' button to embed the chart into your web page.

You are now ready to open your page in a browser and see your first multi series chart in action.