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.

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].

Sparkline charts 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.

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 Relationship Profitability and Pricing System 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

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 series will use a different color. Typically, Waterfall charts only use one series.Each sparkline chart uses a single series in the data source.

[XValueName]

This row contains the names of each XValue column in the chart. These names will display along the primary axis of the chart (the X-axis for most charts; the Y-axis for bar charts).

Column tags

The data source wizard only adds the [SeriesName], [XValue], and [Kind] columns. If you want to use any of the other columns, you must manually add them to the data source.

[SeriesName]

Defines the name of each series in the chart. These names will be displayed in the chart legend, if the chart is configured to show a legend (as defined in the component settings).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.

For waterfall series, the first XValue column determines the starting value. Each subsequent XValue column must then contain a positive or negative value to be added to or subtracted from the cumulative running total. For example, if the starting value is 250 and you want to show that the next value is increased by 45 (reaching a total value of 295), then the second XValue column should contain 45 instead of 295. See the data source example at the end of the section for more information.

Additionally, you can use the following special keywords in XValue columns, to display total bars in the chart:

  • RunningTotal: Displays the sum of all items since the last RunningTotal point. For example, if the previous three columns were the values for Jan-March, you could use a RunningTotal to display the total change for Q1.
  • Total: Displays the sum of all previous items. For example, this could be the last XValue item in the chart, to display the total of all previous values.
[Kind]

Specifies the kind of each series in the chart: Area, Bar, Column, Line, Waterfall, or HorizontalWaterfall. If omitted, then all series in the chart will use the Default Series Kind as defined in the component settings. If a data source contains multiple kinds of series then it is known as a combination chart (for example, one or more column series combined with a line series).

If you want to use the HorizontalWaterfall kind, it must be entered in the Kind column with no space between the words. If the words have a space, the entry is interpreted as invalid and then the Default Series Kind applies.

Specifies the kind of each series in the chart: Line or Column. Any other XYChart series kind listed here will render as Line.

[Color]

Optional. Specifies the color assignment for each series. If omitted, then colors will be dynamically determined based on the style or skin (in that order). See Specifying chart colors.

For Waterfall Charts, you can use additional optional columns to specify different colors for total columns and running total columns.

[RunningTotalColor]

Optional. Specifies the color assignment for columns that use the RunningTotal keyword. If omitted, these columns will use the same color as regular value columns.

[TotalColor]

Optional. Specifies the color assignment for columns that use the Total keyword. If omitted, these columns will use the same color as regular value columns.

[Axis]

Optional. Specifies the Y-axis scale for each series. This column is only required if the chart has both a primary and secondary Y-axis. If omitted, the primary Y-axis scale is assumed. See Using two Y-axis scales with combination XYCharts.

[VisibleinLegend]

Optional. Specifies whether a particular series is shown in the chart legend (True/False). If omitted, all series are shown. This setting only applies if the chart is configured to show a legend (as defined in the component settings).

[ShowMarkers]

Optional. Specifies whether markers are shown on the line to indicate each specific data point in the series (True/False). If omitted, markers are shown. The marker is a circle with no fill color; the marker shape and fill are not configurable.

[LineStyle]

Optional. Specifies the style of the line as one of the following. If omitted, the Normal style is used. Only applies to Line series.

  • None: No line is displayed; only markers are shown to represent the data points. [ShowMarkers] must be enabled or else the series will not display at all. This option is primarily intended for use in combination charts—for example, multiple bar series combined with a marker-only line series.
  • Normal: A straight line is drawn from point to point.
  • Smooth: A curved line is drawn from point to point.
  • Step: The line "steps" from one point to another. The lines between points are flat, with a vertical line up or down to indicate the differential at each point.
[DashType]

Optional. Specifies the type of dash as one of the following. If omitted, the Solid style is used. Only applies to Line series.

  • Dash: The line is drawn in dashes. The length of the dash is fixed and cannot be configured.
  • DashDot: The line is drawn as a dash-dot-dash repeating series.
  • Dot: The line is drawn in dots. The size of the dot is fixed and cannot be configured.
  • Solid: The line is drawn as a solid line.
[PlotNullValues]

Optional. Specifies whether null values are plotted on the line (True/False). Only applies to Line series.

If omitted or False, then null values will result in a gap between line segments. If True, then the missing value will be interpolated, so that the line will continue from the last plotted point to the next plotted point.

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.