You are here: PHP Products > ChartBuilder > Transforming Data for ChartBuilder

ChartBuilder for PHP

Transforming Multiple Columns for ChartBuilder Series

In some cases your data source may have individual columns containing values that you want to view as series in your chart.  ChartBuilder expects a single value column, and series column.  This page will describe how you transform your data using a View.  For example, if your table looks like this:
Date Maintenance Operations Sales Manufacturing
1/1/13 100 2.494 2394 1294
1/3/13 193 128 392 384
2/2/13 2 39 4 392

After following the steps below, your data will be in the format necessary to be rendered in a chart as shown below:
Date seriesfield valuefield
1/1/13 Maintenance 100
1/3/13 Maintenance 193
2/2/13 Maintenance 2
1/1/13 Operations 2394
1/3/13 Operations 128
2/2/13 Operations 39
... ... ...
Step 1: Create a new view

Create a new view on your server.

Step 2: Design a union query
Design your query using a similar approach to below:
SELECT Date, ‘Operations’ as seriesfield, Operations as valuefield FROM YourTableName UNION SELECT Date, ‘Manufacturing’ as seriesfield, Manufacturing as valuefield FROM YourTableName UNION SELECT Date, ‘Operations’ as seriesfield, Operations as valuefield FROM YourTableName UNION SELECT Date, 'Sales’ as seriesfield, Sales as valuefield FROM YourTableName
Copyright © 2017 OpenGate Software Inc. All Rights Reserved.

MS Partner