Charts - Documentation

Last updated: 09-Dec-2021

Overview

Charts are data visualization tools to display data in visual formats ie. tables, line charts, bar charts, pie charts, doughnut charts, geo maps, tiles, horizontal bar charts and more.

Charts also provide a RESTful JSON API to fetch chart data and configurations from any client that supports REST.

Charts are created when you want to provide an interface to visualize data in your database.

To create a chart, go to "Developer tools" in the main menu and click on "Charts", then click on "Add".

Details

ItemDescription
Chart nameThe display name of the chart. Will be used in Sphere ERP UI elements such as the menu and window titles. Will also be available in Sphere ERP API responses to chart requests.
CategoryFor organisational purposes. Is used to categorise chart menu items in Sphere ERP UI. Will also be available in Sphere ERP API responses to dashboard requests. Can also be used as a filter item in the list of chartS to quickly navigate to a desired chart configuration.
For branchesThe branches to which the chart will be displayed in the menu. If a branch is set here, then this chart will only be displayed on the menu if a user naigates to the particular branch in Sphere ERP UI or Sphere ERP API. If you wish to have the chart available in all branches, enter "all"
Chrt uriA unique identifier of the chart. It is used in API requests for the chart.

NOTE:Use only lowercase letters and underscores (_). Do not start with an underscore.

EnabledIf checked, then the chart will be able to process requests. If it is not checked then any requests to the chart data will return an error message of id "chart_disabled".
ListedIf checked and the chart is enabled, then a menu item for the chart will be available in Sphere ERP UI under "Modules" (also see "For branches" above) and dashboard Sphere ERP API requests
Tutorial videoYou can upload a tutorial video here for users to learn how to use a chart. It will be displayed when a user clicks on the info icon in a chart view.
DescriptionThis is a description of the chart and will be displayed when a user clicks on the info icon in a chart view.
ConfigA JSON object with properties to define a chart elements. See config below.

Config

The properties of a chart are configured using JSON. Different configurations can be defined for different access levels, users, user groups or staff positions.

NOTE: Chart "filter_inputs" values are stored in session and can be access in a query, for example, by the tag "{current.< chart origin >-filter_inputs-< input name >} . Replace all occurances of "|" with "-" in < chart origin >"

{
    "< config level (chart group name if writting 'charts,detail_bottom_charts,detail_top_charts' configs in resource OR dashboard charts) >": {
        "cstacked": "< 1 or not set >",
        "link": "< resource uri >",
        "d_title": "< string or SQL query that returns a value >",
        "data_options_label": "< string >",
        "vid": "< (uri of video file) string >",
        "uri": "< (chart uri) string >",
        "res": "< (chart literal name) string >",
        "description": "< string >",
        "filter_inputs": {
            "< input name >": {
                "type": "< all valid html input types|lookup|autocomplete|checkmark > (default=text)",
                "default": "< string or SQL query that returns a value >",
                "source": "< key-value pairs list or query that returns key-value pairs (for lookup)> OR < table name >-< value column >-< label column > (for autocomplete)"
            }
        },
        "< chart name>": {
            "columns_def (for 'type' of 'table')": {
                "< column name >": {
                    "inline_input": {
                        "type": "lookup|checkmark|autocomplete|any valid html input (default=text)",
                        "source": "< key-value pairs list or query that returns key-value pairs (for lookup)> OR < table name >-< value column >-< label column > (for autocomplete)"
                    },
                    "link_type": "< resource OR chart >",
                    "link_id": "< column in current dataset > default=current column",
                    "resource": "< chart or resource URI >",
                    "tip": "< descriptive text for column >"
                }
            },
            "inline_actions (for 'type' of 'table')": {
                "label": {
                    "condition": "< string, column name in current data set OR SQL query >, < string or SQL query >, < operator (possible values=|=,>,<,!=,<=,>=|) (default='=') >",
                    "data": {
                        "0": "< list of strings or column names in current data set OR SQL queries >"
                    }
                }
            },
            "remove (for 'type' of 'table')": "< coma separted list of columns to remove >",
            "stack": "< stack name >",
            "data_conn": {
                "host": "< database server uri >",
                "user": "< database user name >",
                "pass": "< database password >",
                "db": "< database name >"
            },
            "dataset_list": "< SQL query that returns a list >",
            "type": "table|line|bar|pie|doughnut|map|tile|horizontalBar|infotile|polarArea|radar|scatter",
            "data": "< SQL query that returns key-value pairs or mutliple columns for 'table' chart type >",
            "data (alternative)": {
                "< tab option >": {
                    "type": "",
                    "data": ""
                }
            },
            "summary (for 'type' of 'table')": "< SQL query that returns one row for data summary >"
        },
        "drills": {
            "< drill name >": "< chart config >"
        }
    }
}

configlevel (@chart config)

The level to which this config applies

Possible values

A user name, a user group name, a staff position string, or any string/integer used in the 'resource_access_levels' configuration of a user or user group. If no matching configuration is found in the order of priorities, the configuration labelled as '0' will be applied.

Access session chart filter values in a query

Current chart: {filter_inputs.< input name >}

{current.resource-< resource name >-< chart name >-filter_inputs-< input name >}

{current.resource--detail_top_charts-< resource name >-< chart name >-filter_inputs-< input name >}

{current.resource--detail_bottom_charts-< resource name >-< chart name >-filter_inputs-< input name >}

{current.dashboard-< chart group >-< chart name >-filter_inputs-< input name >}

{current.sys_charts-< chart uri >-filter_inputs-< input name >}