Stacked Charts - For Showing Fixed Cost and Variable Cost as Constituents of Total Cost (dynamic data)

In this section we will show you how to create a Stacked Column Chart to show the costs incurred by a manufacturing company for all the four quarters of 2007. The stacked chart will display the fixed and the variable costs as constituents of the total costs.

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

Click here to download the database used to create this chart

The Costs table contains an entry for each cost encountered by the company; the cost is described by the following fields: a unique ID, the cost name, the type of cost (fixed or variable), the value of the cost and the date when the cost appeared.

The Costs table

The Costs table

In order to obtain the data needed to plot the costs encountered by the company, we will add two data series from the table column containing the information about the value of the costs. Following, we will filter each of the data series to display only costs of type “fixed”, or “variable” 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

The following steps will guide you through the process of creating a Stacked 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

  3. From the list labeled Connection, select stackedSeries (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.
  4. Select the connection and the plot type

    Select the connection and the plot type

  5. 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.
  6. Configure the category data

    Configure the category data

  7. From the Series Data tab, click the Add Series button to open the Add/Edit Series Form. Configure the Series options; select costs for the Data Source option, costs.CostValue for the Value Column option, sum for the Calculation option, costs.CostDate 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.
  8. Add a data series

    Add a data series

    Configure the data series

    Configure the data series

  9. Select the series and click the Set Filter button. Select the Add Simple Filter option and configure it to have: costs.CostType for Column, contains for Operator and fixed for Value. Click Save to set the filters on the data series.
  10. Set a filter on the data series

    Set a filter on the data series

    Configure the filter

    Configure the filter

  11. 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 variable.
  12. 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.
  13. Switch to the 'Chart Selection' screen and select a 'Column' chart option from the 'Stacked Charts' category.
  14. In the 'Titles & Axis Names' panel; enter 'Quarter' in the 'xAxisName' field and 'Total Costs' in the 'yAxisName' field.
  15. Switch to the 'General Options' tab and set the 'Chart Name' as 'myfirstStackedChart' and specify the desired width and height of the chart in their respective fields.
  16. In the Caption, Subcaption panel - provide the caption and sub caption for the chart in their respective fields. For this chart we will provide 'Quarterly Cost Analysis' as the caption, and 'For the Year 2008' as the sub caption..
  17. 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.
  18. Your chart is now ready; preview it using the 'Open Preview' button and then click 'Ok' to embed your chart into your web page.

You may now open the page in a browser to see your first stacked chart in action.