Dynamic Data Binding for TimeSeries Data Visualization Component UI Builder

ezhilzrec · ‎04-20-2023

I've created a data resource of type Transform in UI Builder to return the trend of closed incidents as JSON and im trying to bind this data resource to a Column Time Series Data Visualization Component.

However i'm not able to get it working as im not sure of the format of JSON that is to be used in the data, metric/TrendBy.

Is this even feasible thing to do and if so Can anyone share a working example?

Tagging @Nootan Bhat since he had responded on similar question. Would appreciate any pointers.

https://www.servicenow.com/community/developer-forum/ui-builder-single-score-make-filter-with-bind-d...

ezhilzrec · ‎04-21-2023

Tagging @Nootan Bhat since he had earlier responded on similar question. Would appreciate any pointers on this.

https://www.servicenow.com/community/developer-forum/ui-builder-single-score-make-filter-with-bind-d...

Nootan Bhat · ‎04-26-2023

Hi @ezhilzrec ,

When you select the Column Time series visualization, in Data sources options you can see an option "Define Data manually". if you enable it, you can pass the Jason value from the Transform script manually.

If you select this option, transform script should return the data for the visualization not the configuration.

format will be like below:

[
    {
        "data": [
            {
                "timestamp": "1638576000000",
                "value": "54",
                "forecast": "",
                "forecastRangeLower": "",
                "forecastRangeUpper": "",
                "trend": "53.93551912668431",
                "confidenceBandRangeLower": "51.42284371455968",
                "confidenceBandRangeUpper": "56.44819453880894",
                "predictionBandRangeLower": "43.766739658295705",
                "predictionBandRangeUpper": "64.10429859507292"
            },
            {
                "timestamp": "1638662400000",
                "value": "54",
                "forecast": "",
                "forecastRangeLower": "",
                "forecastRangeUpper": "",
                "trend": "53.997591924649896",
                "confidenceBandRangeLower": "51.54797899381162",
                "confidenceBandRangeUpper": "56.447204855488174",
                "predictionBandRangeLower": "43.844211125085835",
                "predictionBandRangeUpper": "64.15097272421396"
            },
            {
                "timestamp": "1638748800000",
                "value": "54",
                "forecast": "",
                "forecastRangeLower": "",
                "forecastRangeUpper": "",
                "trend": "54.05966472261548",
                "confidenceBandRangeLower": "51.67251995738005",
                "confidenceBandRangeUpper": "56.44680948785091",
                "predictionBandRangeLower": "43.921173795449995",
                "predictionBandRangeUpper": "64.19815564978097"
            },
            {
                "timestamp": "1638835200000",
                "value": "54",
                "forecast": "",
                "forecastRangeLower": "",
                "forecastRangeUpper": "",
                "trend": "54.12173752058084",
                "confidenceBandRangeLower": "51.796418707067694",
                "confidenceBandRangeUpper": "56.44705633409398",
                "predictionBandRangeLower": "43.997625424475615",
                "predictionBandRangeUpper": "64.24584961668606"
            },
            {
                "timestamp": "1638921600000",
                "value": "54",
                "forecast": "",
                "forecastRangeLower": "",
                "forecastRangeUpper": "",
                "trend": "54.18381031854642",
                "confidenceBandRangeLower": "51.91962263335074",
                "confidenceBandRangeUpper": "56.4479980037421",
                "predictionBandRangeLower": "44.0735638317481",
                "predictionBandRangeUpper": "64.29405680534474"
            },
            {
                "timestamp": "1639008000000",
                "value": "54",
                "forecast": "",
                "forecastRangeLower": "",
                "forecastRangeUpper": "",
                "trend": "54.245883116512005",
                "confidenceBandRangeLower": "52.042073914668116",
                "confidenceBandRangeUpper": "56.44969231835589",
                "predictionBandRangeLower": "44.14898690291459",
                "predictionBandRangeUpper": "64.34277933010942"
            },
            {
                "timestamp": "1639094400000",
                "value": "54",
                "forecast": "",
                "forecastRangeLower": "",
                "forecastRangeUpper": "",
                "trend": "54.30795591447736",
                "confidenceBandRangeLower": "52.16370897032699",
                "confidenceBandRangeUpper": "56.452202858627736",
                "predictionBandRangeLower": "44.22389259121703",
                "predictionBandRangeUpper": "64.39201923773768"
            },
            {
                "timestamp": "1639180800000",
                "value": "54",
                "forecast": "",
                "forecastRangeLower": "",
                "forecastRangeUpper": "",
                "trend": "54.370028712442945",
                "confidenceBandRangeLower": "52.28445786687307",
                "confidenceBandRangeUpper": "56.45559955801282",
                "predictionBandRangeLower": "44.29827891899012",
                "predictionBandRangeUpper": "64.44177850589577"
            },
            {
                "timestamp": "1639267200000",
                "value": "54",
                "forecast": "",
                "forecastRangeLower": "",
                "forecastRangeUpper": "",
                "trend": "54.43210151040853",
                "confidenceBandRangeLower": "52.404243679763034",
                "confidenceBandRangeUpper": "56.459959341054024",
                "predictionBandRangeLower": "44.37214397911963",
                "predictionBandRangeUpper": "64.49205904169743"
            },
            {
                "timestamp": "1639353600000",
                "value": "54",
                "forecast": "",
                "forecastRangeLower": "",
                "forecastRangeUpper": "",
                "trend": "54.494174308373886",
                "confidenceBandRangeLower": "52.52298181504507",
                "confidenceBandRangeUpper": "56.4653668017027",
                "predictionBandRangeLower": "44.44548593646276",
                "predictionBandRangeUpper": "64.54286268028501"
            },
            {
                "timestamp": "1639440000000",
                "value": "54",
                "forecast": "",
                "forecastRangeLower": "",
                "forecastRangeUpper": "",
                "trend": "54.55624710633947",
                "confidenceBandRangeLower": "52.640579299743585",
                "confidenceBandRangeUpper": "56.471914912935354",
                "predictionBandRangeLower": "44.518303029226175",
                "predictionBandRangeUpper": "64.59419118345276"
            },
            {
                "timestamp": "1639526400000",
                "value": "54",
                "forecast": "",
                "forecastRangeLower": "",
                "forecastRangeUpper": "",
                "trend": "54.618319904305054",
                "confidenceBandRangeLower": "52.75693405507166",
                "confidenceBandRangeUpper": "56.47970575353845",
                "predictionBandRangeLower": "44.590593570297315",
                "predictionBandRangeUpper": "64.64604623831279"
            },
            {
                "timestamp": "1639612800000",
                "value": "54",
                "forecast": "",
                "forecastRangeLower": "",
                "forecastRangeUpper": "",
                "trend": "54.68039270227041",
                "confidenceBandRangeLower": "52.871934173785135",
                "confidenceBandRangeUpper": "56.488851230755685",
                "predictionBandRangeLower": "44.66235594853083",
                "predictionBandRangeUpper": "64.69842945600999"
            },
            {
                "timestamp": "1639699200000",
                "value": "54",
                "forecast": "",
                "forecastRangeLower": "",
                "forecastRangeUpper": "",
                "trend": "54.742465500235994",
                "confidenceBandRangeLower": "52.98545723224112",
                "confidenceBandRangeUpper": "56.49947376823087",
                "predictionBandRangeLower": "44.733588629985846",
                "predictionBandRangeUpper": "64.75134237048614"
            }
        ],
        "metadata": {
            "eventData": {
                "indicatorSysid": "fb007202d7130100b96d45a3ce6103b4"
            },
            "dataSourceLabel": "Number of open incidents",
            "series": [
                {
                    "label": "Number of open incidents",
                    "id": "value",
                    "type": "value"
                },
                {
                    "label": "Forecast",
                    "id": "forecast",
                    "type": "forecast"
                },
                {
                    "label": "Forecast Range",
                    "lower": "forecastRangeLower",
                    "upper": "forecastRangeUpper",
                    "id": "forecastRange",
                    "type": "band"
                },
                {
                    "label": "Trend",
                    "id": "trend",
                    "type": "trend"
                },
                {
                    "label": "Confidence Band",
                    "lower": "confidenceBandRangeLower",
                    "upper": "confidenceBandRangeUpper",
                    "id": "confidenceBand",
                    "type": "band"
                },
                {
                    "label": "Prediction Band",
                    "lower": "predictionBandRangeLower",
                    "upper": "predictionBandRangeUpper",
                    "id": "predictionBand",
                    "type": "band"
                }
            ],
            "aggregate": {
                "fieldType": "decimal"
            },
            "format": {
                "unitFormat": "{0}",
                "frequency": "daily",
                "precision": 0
            }
        },
        "thresholds": [
            {
                "label": "More than",
                "value": 50,
                "type": "global"
            }
        ],
        "comments": [
            {
                "x": 1640995200000,
                "header": "Maint",
                "message": "Comment"
            }
        ]
    }
]

If you share the details on what data you are returning from transform script.

Let me know if it was helpful.

Thanks

Nootan Bhat · ‎04-26-2023

Or else, if you want to use the scripts in Data resources you can do below configurations:

Script for Data resources:

var data = [
                {
                    "isDatabaseView": false,
                    "allowRealTime": true,
                    "sourceType": "table",
                    "label": {
                        "message": "LABEL OF YOUR DV" // Proper label
                    },
                    "tableOrViewName": "TABLE_NAME", // Table name
                    "filterQuery": "YOUR_FILTER", //you can bind the state values here  
                    "id": "DVID" //proper ID name
                }
            ];
	return data;

For Metric:

var metric = [
                {
                    "dataSource": "DVID", // from data source
                    "id": "METRICID", // proper id
                    "aggregateFunction": "COUNT",
                    "aggregateField":"",
                    "numberFormat": {
                        "customFormat": false
                    },
                    "axisId": "primary"
                }
            ];
    return metric;

For Trend By:

var trend = {
      "trendByFrequency" : "date",
            "trendByMinuteInterval" : null,
            "trendByFields" : [ {
              "field" : "FIELD_NAME", //trend on which field
              "metric" : "METRICID" // From metric
            } ]
    };
    return trend;
}

in this way the script will execute, and visualization will be displayed.

Let me know if it was helpful.

Thanks

ezhilzrec · ‎04-27-2023

I dont understand this especially Data part, is this applicable only if datasource is datbaseview/Table and not for a scrpited Transform datasource?

To give more context my source of truth/datasource is not a table or a database view. I can get it only from script as JSON and hence i'm opting for Transform Datasource.

As guided by your earlier answer i was able to get it working by having the "Data Manually" toggle on and binding to the transform datasource output.

But with that im not have the flexibility to configure the groupBy, trendBy, Frequency.

Im trying to ask is there any way i can bind to my same transform datasource( manual data toggle off) with additional json data to provide groupBy, trendBy, Frequency fields so that they can dynamically be selected while creating/modifying the widget/report.