AX1724

Displaying charts in KPI Panels in web reports

Each KPI in a KPI Panel component can include an optional chart. There are two options to display a chart:

Bullet Chart: To display a bullet chart in the KPI box, complete the ChartTarget, ChartActual, and ChartMax columns in the KPI table.
SparklineChart: To display a sparkline chart in the KPI box, first create an XYChart data source to define the data for the sparkline. Then, complete the SparklineData column in the KPI table.

Each KPI can use either a bullet chart or a sparkline chart, but not both. If both ChartMax and SparklineData are completed in a KPI table, the bullet chart takes precedence.

Both kinds of KPIs (Basic and Status) can display charts. In Basic KPIs, the chart displays at the bottom left of the KPI box. In Status KPIs, the chart displays in the middle of the KPI box, next to the status indicator.

In Basic KPIs, the chart and Value 4 display in the same location, so only one or the other can be used per KPI. If both are defined, the chart takes precedence. This is not an issue for Status KPIs because the chart displays in a different location.

Displaying bullet charts in KPIs

To display a bullet chart in a KPI, complete the following columns in the KPI table.

ChartTarget: A value that defines the target line for the bullet chart.
ChartActual: A value that defines the actual line for the bullet chart.
ChartMax: The maximum value of the bullet chart. The chart target and actual values are represented in relation to this maximum value.

For example, if the actual value is 100 and the maximum value is 1000, then the actual bar will only take up 1/10 of the bullet chart. But if the maximum value is 150, then the actual bar will take up 2/3 of the bullet chart.

If the Style of the KPI is set to white, blank (transparent), or S1, then the actual bar of the chart displays in red or green, depending on the value of the Sign column. Otherwise, it displays in white.

NOTE: Tooltips display on bullet chart values within the KPI. However, these values display as unformatted numbers. When using a KPI table, there is no option to define numeric formatting for these values.

Displaying sparkline charts in KPIs

To display a sparkline chart in a KPI when using a KPI table, you must use special syntax to save the contents of a specified series in an XYChart data source to the SparklineData column of the table during a save-to-database.

The save-to-database file that you use to save KPI data to the table must be set up as follows:

The file must contain an XYChart data source that is configured to display sparkline data. This data source is set up as normal, on any sheet of the file. See Creating an XYChart data source for a Sparkline chart.
When setting up the Save Type 1 in the file, the contents of the SparklineData column must contain the following special syntax to specify the XYChart data source and series: [Datasource=DataSourceName;Series=SeriesName].

For example, you can create an XYChart data source named Sparkline, with two series. One is a line series named LineSeries and the other is a column series named ColumnSeries.

Example XYChart data source

To save series data to a KPI table, you must place the special syntax in the SparklineData column for the save-to-database, as shown in the following screenshot:

Example save-to-database using special syntax to save sparkline data

When the save-to-database is executed, Axiom Software finds the designated XYChart data source, and converts the contents of the specified series into an XML string. That XML string is then saved to the SparklineData column in the KPI table. When the KPI table is used with a KPI Panel component, the XML string is used to render the sparkline chart on the KPI box.

The SparklineData column in KPI tables can only accept the special data source syntax when saving to the database using Save Type 1. If any other contents are present in the SparklineData column within the sheet (even the resulting XML syntax), an error occurs when saving. If you want to modify and save the other columns in the table without modifying the SparklineData column, then the SparklineData column must be omitted from the save.

The following example KPIs show how a line and column sparkline chart appear in the KPI box:

Example KPIs with sparkline charts

NOTES:

Sparkline charts in KPIs do not display using colors. They are black when the KPI box uses a light color and white when the KPI box uses a dark color.
Tooltips display on sparkline chart values within the KPI. When using a KPI table, the tooltips display the unformatted numeric value.

Creating an XYChart data source for a Sparkline chart

The tags for the XYChart data source are as follows when using it to define a sparkline chart for a KPI. When using the data source with a KPI table, the data source must be created in the save-to-database file that is being used to populate the table, instead of in the form source file.

Primary tag

[XYChart;DataSourceName]

The DataSourceName identifies this data source so that it can be assigned to a KPI. Data source names must be unique within a file and must start with a letter. Names can only contain letters, numbers, and underscores. Names are validated when the file is saved; an invalid name will prevent the save.

The placement of this primary tag defines the control column and the control row for the data source.

All column tags must be placed in this row, to the right of the tag.
All row tags must be placed in this column, below the tag.

Row tags

[Series]: Each row flagged with this tag defines a series of data to be displayed in the chart. Each sparkline chart uses a single series in the data source.

Column tags

[SeriesName]: Defines the name of each series in the chart. The name identifies this series so that it can be assigned to a KPI.
[XValue]: Each column of data to be displayed in the chart must be marked with an XValue tag.
[Kind]: Specifies the kind of each series in the chart: Line or Column. Any other XYChart series kind listed here will render as Line.

NOTES:

The primary tag must be placed in the first 500 rows of the sheet.
Formulas can be used to create the tags, as long as the initial bracket and identifying keyword are whole within the formula.
Negative numbers in a data source must use the minus symbol or parentheses to indicate the negative value. Alternative negative formats such as red number text are not recognized and will display as positive values in the chart.

When using Create Axiom Form Data Source on the right-click menu, there is no separate option for Sparkline. Instead, you should select Line Chart or Column Chart to create an XYChart data source. You can modify the Kind column as needed to specify Line or Column for each series.