AX1712

About KPI tables

KPI tables store key performance indicators (KPIs) for your organization. These KPIs can be referenced in web reports and in Axiom forms using the KPI Panel component.

KPI tables have a predefined structure that maps to the properties used by the KPI Panel component. Once you assign the table to a KPI Panel component, the component automatically formats and positions the KPI data in the table into a series of KPI boxes. You can apply an optional filter to limit the KPIs shown in a particular component.

Creating KPI tables

KPI tables have a special editor in the Web Client Table Manager, designed to make it easy for table administrators to create new KPI tables.

Currently, KPI tables can only be created in the Web Client. Once a table has been created, additional columns can be added as needed using the standard Edit Table feature in the Desktop Client General term for using either the Excel Client or the Windows Client, both of which are installed to the user's desktop..

To populate a KPI table with KPI values, you can use any of the standard features for populating tables, such as Save Type 1 and Open Table in Spreadsheet. In most cases, you would set up a Save Type 1 report that queries data and calculates KPIs, then saves that data back to the KPI table. The report can be scheduled to run periodically to update the table with the latest KPI data as needed.

Column structure for KPI Tables

KPI tables have a predefined column structure that corresponds to the values used by the KPI Panel component. All of the following columns are created by default when the KPI table is created, and cannot be deleted. All string columns are non-Unicode.

Name	Column Properties	Description
Name	String (100) Key Column	Defines a code that uniquely identifies each KPI in the table. The code can be anything you want, as long as it is unique. For example, you might use a meaningful code format like KPI_ExpenseVar or KPI_ExpenseVar_D42000 (where KPIs are stored by department).
Kind	String (32)	(missing or bad snippet)
Title	String (250)	(missing or bad snippet)
TitleIcon	String (64)	(missing or bad snippet) To look up valid icon names, you can use the symbol chooser available for Formatted Grid components. In a form-enabled file, right-click a cell and then choose Insert Formatted Grid Tag > Symbol. Then use the [...] button to the right of the Symbol box to open the Choose Symbol dialog. You can hover your cursor over an icon to see its name.
Subtitle	String (250)	(missing or bad snippet)
Value1	Numeric	(missing or bad snippet) The number format of this value is determined by the value specified in the NumericType column.
Value2	Numeric	(missing or bad snippet) The number format of this value is determined by the value specified in the NumericType column.
Value2Label	String (100)	(missing or bad snippet)
Value3	Numeric	(missing or bad snippet) The number format of this value is determined by the value specified in the NumericType column.
Value3Label	String (100)	(missing or bad snippet)
Value4	Numeric	(missing or bad snippet) The number format of this value is determined by the value specified in the NumericType column.
Value4Label	String (100)	(missing or bad snippet)
ChartTarget	Numeric	(missing or bad snippet)
ChartActual	Numeric	(missing or bad snippet)
ChartMax	Numeric	(missing or bad snippet)
Delta	Numeric	(missing or bad snippet) The number format of this value is determined by the value specified in the DeltaNumericType column.
Sign	String (8)	(missing or bad snippet)
StatusSign	String (8)	(missing or bad snippet)
Style	String (32)	(missing or bad snippet)
Tooltip	String (250)	(missing or bad snippet)
Command	String (1023)	(missing or bad snippet) The valid entries for the Command column are the same that can be defined for the Command column in the KPIMenu data source. See Executing commands from KPI Panels in Axiom forms or Executing commands from KPI Panels in web reports.
NumericType	String (16)	Specifies the numeric type of the values in the value columns (1-4): `Number` `Currency` `Decimal` This determines the format of these values within the KPI box. If blank, Number is assumed. The Numeric Type defined in the column properties for the individual value columns is not used.
DeltaNumericType	String(16)	Specifies the numeric type of the value in the Delta column: `Number` `Currency` `Decimal` This determines the format of the Delta value within the KPI box. If blank, Number is assumed. The Numeric Type defined in the Delta column properties is not used.
MenuData	String(Max)	Optional. Stores the contents of a KPIMenu data source as an XML string. The XML string is used to render one or more menu items on the KPI box. In order to generate a valid XML string, the save-to-database file that populates the KPI table must contain a KPIMenu data source, and the MenuData column in the save must contain the syntax `[Datasource=DatasourceName]`. When the save occurs, Axiom Financial Institutions Suite reads the contents of the designated KPIMenu data source, creates the XML string, and saves it to the MenuData column in the table. For more information, see Executing commands from KPI Panels in Axiom forms or Executing commands from KPI Panels in web reports.
SparklineData	String(Max)	Optional. Stores the contents of a specific series in an XYChart data source as an XML string. The XML string is used to render a sparkline chart in the KPI box. In order to generate a valid XML string, the save-to-database file that populates the KPI table must contain a XYChart data source, and the SparklineData column in the save must contain the syntax `[Datasource=DatasourceName;Series=SeriesName]`. When the save occurs, Axiom Financial Institutions Suite reads the contents of the designated series in the XYChart data source, creates the XML string, and saves it to the SparklineData column in the table. For more information, see Displaying charts in KPI Panels in Axiom forms or Displaying charts in KPI Panels in web reports.
Hidden	Boolean	Specifies whether a KPI row is omitted from KPI Panel components (True/False). The default value is False. You can set this to True in order to temporarily hide a KPI, or to archive a KPI without deleting it.
CalculatedDateTime	DateTime	Optional. Specifies the date/time of the KPI calculation. If defined, this value displays on the tooltip for the KPI title. This is not an automatically calculated value, because the applicable date/time of the KPI may be different than the date/time the value was saved to the table. If you want to use this value, it must be explicitly defined and saved to the KPI table along with the other values.

The main column properties of KPI columns cannot be modified, such as column name, data type, key column status, etc. Only minor column properties such as read-only status can be modified.

The following diagram shows how the major KPI properties defined by the columns map to the KPI Panel component:

Using KPI tables

The primary purpose of KPI tables is to provide data for KPI Panel components. You can configure the component to use a KPI table, and then that component is automatically populated with the KPIs in the table.

KPI tables can be used KPI Panel components in Axiom forms and in web reports.

In web reports, KPI Panel components are required to use a KPI table in order to provide KPI data to the component.
In Axiom forms, KPI Panel components can use either a KPI table or a data source defined within the spreadsheet.

When configuring a KPI Panel component, you can optionally specify a filter to determine the KPIs that display in the component. For example, if you have added a Dept or Entity column to the KPI table, you can filter the component to only show KPIs relating to a particular department or entity. Any KPI with Hidden set to True is automatically hidden from all KPI Panel components.

KPI table restrictions and limitations

KPI tables follow the same general rules of reference tables, with some additional restrictions. In addition to the pre-set column structure, the following limitations and restrictions apply to KPI tables:

KPI tables cannot be plan code tables.
KPI tables cannot have column hierarchies, column sequences, or calculated fields.

When filtering a list of tables by table classification, KPI tables will display along with the other reference tables, unless they are being omitted from the list as invalid selections.

Generally speaking, whenever a feature says a reference table is valid for use, a KPI table can be used as well. Any exceptions are noted in the specific feature documentation.

KPI table security

By default, non-admin users do not have access to the data in KPI tables. When a new KPI table is created, you must configure read-only or read / write access to the table as needed (unless the KPI table is assigned to a table type to inherit permissions).

Note the following considerations for KPI table security:

KPI tables can belong to table types, for purposes of easily granting full read or write access to a set of related tables. You can then grant access at the table type level, to be inherited by all tables in the table type. KPI tables can belong to any table type that uses the reference table classification; there is no special table type classification for KPI tables.
KPI tables can be created by administrators or by users with the Administer Tables permission. There is no special permission specifically for administering KPI tables. Once a KPI table is created, standard table and table type permissions apply.