Histogram with custom SQL

Modified on Wed, 13 Apr 2022 at 09:47 AM

This article is outdated, you can access the most current version here: https://doc.onedata.de/apps/apps-docs/odml-documentation/AppBuilder/elements/HighchartsElement.html#type-highcharts

This is a sample configuration for a Histogram with custom SQL in Apps.

{

      "id": "histogram",

      "type": "highcharts",

      "source": "histogram_data",

      "sourceOptions": {

        "$sql": {

          "value": "select bucket_floor, CONCAT(bucket_floor, ' to ', bucket_ceiling)

                    as bucket_name, count(*) as count from

                    (select floor( (i.Col1*10) /5.00)*5/10 as bucket_floor,

                    floor( (i.Col1*10) /5.00)*5/10 + 1 as bucket_ceiling from inputTable i)

                    a group by 1, 2 order by 1"

}

},

      "config": {

        "title": {

          "text": "Histogram"

},

        "subtitle": {

          "text": ""

},

        "exporting": {},

        "chart": {

          "type": "column",

          "polar": false

},

        "plotOptions": {

          "series": {

            "pointPadding": 0,

            "groupPadding": 0,

            "borderWidth": 0.5,

            "shadow": false,

            "animation": false

}

},

        "series": [

{

            "columnName": "count",

            "name": "Data"

}

],

        "xAxis": {

          "title": {}

},

        "yAxis": {

          "title": {

            "name": "#"

}

}

}

}

Note that the custom SQL string in the upper example configuration was split into multiple lines for readability purposes. Within Apps it needs to be in one line.

This is the datasource used:

{

      "id": "histogram_data",

      "origin": "frt",

      "config": {

        "schema": "table",

        "frtId": "d87f859e-9621-4ae7-b3d1-49a8100f1c7e",

        "filterOptions": {

          "distinctValues": true,

          "enabled": true

}

}

}

The source links to a Filterable Result Table contained in a sample workflow, which is also appended at the bottom of the article. It has to be imported into ONE DATA, then saved and run once to retrieve the frtId, which must then be inserted in the datasource configuration above.

Attachments (1)

txt

Workflow_His....txt
5.38 KB