[Christian Has successfully completed the online course Basics (100)]

3 years ago #29651

Hello together,

I am trying to visualize data from a SQL database using charts. For this I use a SQL connector that accesses the SQL database. The retrieved data is stored in a variable and should be visualized in a chart afterwards. Unfortunately, this does not work, so I wonder if it is even possible to visualize the data from an SQL database using variables without a business object? Are they any tipps for this proceed?

[Jennifer Häfner Has successfully completed the online course Intermediate (200)Has successfully completed the online course Advanced (300)Has successfully completed the online course Basics (100)Has successfully completed the online course Advanced (310)Has successfully completed the online course Advanced (320)Has successfully completed the Intermediate CertificationHas successfully completed the Advanced Certification]

3 years ago #29699

Up

0

Down

::

Hi Christian,

To visualize data with the charts widget, it does not matter from which data source (SQL, SAP, REST, …) the data is loaded. However, the data needs to have a certain structure (depending on the charts type that you want to use, typically an array of ‘labels’ and an array of ‘datasets’ with ‘datapoints’) so that the charts widget can work with it.

In our knowledge base, we have an article that describes how to use the charts widget, and you can also see the necessary data structure there: https://community.simplifier.io/knowledge/using-the-charts-widget/

So in your case: you need to format the data after retrieving it from the SQL connector. You can call the SQL connector directly in a Server Side Business Object and then format the output so that it’s suitable for the charts widget.

Or you can format the data that you have stored in the global variable using a script or a Client Side Business Object.

Let us know if you need further help on this topic.

[Christian Has successfully completed the online course Basics (100)]

3 years ago #29755

Up

0

Down

::

Thank you for the explanation and further information.However, I have a problem with the datapoints as I don’t know how to format the connector output. At “Value” I call the connector which should output the specific number to me. However, I get back the entire structure of the connector call, which is why I can’t specify the Value correctly in the chart widget (in the example only static numbers are used). Can you help me here?

Attachments:

You must be logged in to view attached files.

[Daniel Has successfully completed the online course IntroductionHas successfully completed the online course Intermediate (200)Has successfully completed the online course Advanced (300)Has successfully completed the online course Basics (100)]

3 years ago #29760

Up

0

Down

::

Hi Christian,

if i got you correctly the connector call Status_Open will return an array / list of values. For each value you’ll need an entry in the datapoints array – is that correct?

So it should look something like this:
datapoints: [
   {
      value: 10
   },
   {
      value: 20
   },
   {
      value: 30
   }
]

Best regards,

Dan

[Christian Has successfully completed the online course Basics (100)]

3 years ago #29769

Up

0

Down

::

In my connector call (GetAll) I have the given datasets structure. However, when I call the connector in the business object(Dashboard / Chartkonnektor), I need to specify an array (“datasets”) for the connector call to work. However, this causes me to have “datasets” twice in the BO-output. How do I get the BO-output to show only the connector and its structure? I have marked the affected lines in the images.

Attachments:

You must be logged in to view attached files.

[Jennifer Häfner Has successfully completed the online course Intermediate (200)Has successfully completed the online course Advanced (300)Has successfully completed the online course Basics (100)Has successfully completed the online course Advanced (310)Has successfully completed the online course Advanced (320)Has successfully completed the Intermediate CertificationHas successfully completed the Advanced Certification]

3 years ago #29775

Up

0

Down

::

Hi Christian,

You can avoid the nested datasets object by adding the path (‘.datasets’) to the connector call in your business object.

So, in line 6 (image ‘BO_Ansicht.jpg’), write:

datasets: Simplifier.Connector.Dashboard.getAll().datasets

Now, the output should only contain the array ‘datasets’.

[Christian Has successfully completed the online course Basics (100)]

3 years ago #29778

Up

0

Down

::

Thank you! This works well.

My last question would be how do I add an additional array of “Datapoints” (values) in the created array “Datasets”? Currently the “Datapoints”(values) are not seperated in the Datasets,but would need to be mapped to a new array “Datapoints” for Visualization.

Attachments:

You must be logged in to view attached files.

[Jennifer Häfner Has successfully completed the online course Intermediate (200)Has successfully completed the online course Advanced (300)Has successfully completed the online course Basics (100)Has successfully completed the online course Advanced (310)Has successfully completed the online course Advanced (320)Has successfully completed the Intermediate CertificationHas successfully completed the Advanced Certification]

3 years ago #29795

Up

0

Down

::

Hi Christian,

You have to format the data that comes from the connector call. You can do this in your business object (that’s why you have to use the connector call inside a business object: to format the data coming from the connector for the chart widget). Here is an example that simply converts the values of a dataset into datapoint objects:

var oData = {
    "datasets": [{
        "color": "#00AF00",
        "label": "Tenant 1",
        "value": 1
    }, {
        "color": "#ff0000",
        "label": "Tenant 2",
        "value": 2
    }, {
        "color": "#cc33ff",
        "label": "Tenant 3",
        "value": 3
    }],
    "labels": [{
        "label": "Status_Open"
    }]
};

// write the value into a datapoints object for each dataset
oData.datasets.forEach(function(oDataset) {
    oDataset.datapoints = [{
        value: oDataset.value
    }];
});

output.datasets = oData.datasets;
output.labels = oData.labels;

When the datasets and labels are mapped to a bar chart, the resulting chart will look like the chart in the attachment.

Attachments:

You must be logged in to view attached files.

[Christian Has successfully completed the online course Basics (100)]

3 years ago #29886

Up

0

Down

::

Now it works! Thank you for your help!