Back to How-Tos

Put one slice per detail record in a Pie Chart

Introduction

Original Author: Mieke Zuurman

When you create a Pie Chart and select a detail table as its data source, the chart will show one slice for each field in the first detail record. What if you wanted the Pie Chart to have one slice per detail record for the field of your choice?

Assume you have the following data.

The 5 records in the detail table detailOrders each contain the number of orders in a certain year. You want a Pie Chart that visualizes the number of orders per year. Here's how to add such a chart.

Add a chart

First, add a Pie Chart: click the Pie Chart button, for example. You can leave the settings as they are; click Next. Make sure to set Input Type to Data table and select your detail table from the Detail Table dropdown; click Finish. A Pie Chart will be added to the template, as well as a script that provides the data for the chart. For a look behind the scenes, select the chart and switch to the Source tab. The selected element is a div element hat has an ID ('pie', by default) and a data-amchart attribute. The value of that attribute is a JSON string that contains all the properties of the chart. The Pie Chart script adds the data for the chart to that attribute, in a dataProvider property. Connect then passes the JSON string to the integrated Amcharts library.

The Pie Chart that appears in your template doesn't visualize the desired data. To achieve that you will have to change the script.

Edit the script

Hover over the scripts in the Scripts pane until you find a script that highlights the chart you just added. Double-click to open it. The script window looks a lot like one of the windows in the Pie Chart Wizard, but it has an Expand button. Click that button.

Delete all the code and replace it with the following code:

let json = JSON.parse(results.attr("data-amchart"));

// Specify the detail table, and the fields that contain the value and title for a slice
    let detailTable = "detailOrders";
    let valueField = "Orders";
    let titleField = "Year";

// Put them in the JSON string and add the dataProvider property to it
    json.valueField = valueField;
    json.titleField = titleField;
    json.dataProvider = [];

//Loop over records in the detail table
    for( let index in record.tables[detailTable] ){
     let rec = record.tables[detailTable][index];
     let data = {};
 //Copy the record's value and title into a data object
     data[valueField] = rec.fields[valueField];
     data[titleField] = rec.fields[titleField];
 // If no theme, base color or color array has been specified,
     if (!json.theme && !json.baseColor && !json.colors) {
 // assign a color to the slice by setting the data.color property
 // e.g. data.color = "#FF0000";
 // Note: Specify a different color for each slice.
    }
 // Add the data object to the dataProvider property in the JSON string
     json.dataProvider.push(data);
}
// Put the extended JSON string back in the data-amchart attribute
    results.attr("data-amchart", JSON.stringify(json));

All you need to do is specify the name of your detail table and of the fields that contain the value and title for a slice. After saving the script, the Pie Chart looks like this:

The chart now visualizes the desired data. To make it look better, change the chart's properties (see Business Graphics in the Online Help).

About colors

Maybe you've noticed: this script didn't specify a color for any of the slices. So where do these colors come from? To color slices in a Pie Chart, you can:

  • Use a base color. Right-click the chart, select Chart... and on the General tab, specify a base color. This is the color of the first slice. Subsequent slices will be one shade lighter or darker. Use Brightness Step to set the intensity of the color change per slice.
  • Use a theme. Right-click the chart, select Chart... and go to the Source tab. Specify the theme, e.g. "theme": "light". See: Using themes in the Online Help.
  • Specify a color array. Right-click the chart, select Chart... and on the Source tab, specify the colors to use. For an example see https://docs.amcharts.com/3/javascriptcharts/AmPieChart#colors.
If none of the previous has been done, you can assign a different color to each pie slice in the chart script; the above example indicates where to do that. You could also define no color at all, neither in the chart properties nor in the script, as in this example. In such a case, the Amcharts library uses its own default set of colors.