Accessing data from different datasets

Posted by: rob.kuiper on 21 September 2021, 7:12 pm EST

  • Posted 21 September 2021, 7:12 pm EST

    I am a starting user of ActiveReports and I am struggling with the way to access data. Can anyone help me with this?

    I am using a JSON file as datasource (example data below).
    Based on the datasource I defined a number of datasets:
    - orderLine (id, lineReference, orderedQuantity, itemId, itemName)
    - deliveredGoods (orderLineId, itemId, itemName, lot, quantity)
    - itemAttributes (attributeTypeName, value)

    I have a table control with delivered goods. In the table the goods are grouped by itemId. In the group header I am showing the ordered quantity for which I use a lookup function.
    I also want to show whether this item has an item attribute of type "Fragile". Therefore I want to access the item attributes with itemId and attributeTypeName but I don't know how to do this. The dataset itemAttributes
    has no itemId to match on and moreover the lookup function matches only on 1 property.

    Example JSON:
    "orderLine": [
    {
    "id": 1234,
    "lineReference": "Line20210901-001-1",
    "orderedQuantity": 200,
    "orderedItem": {
    "id": 123456,
    "name": "Example item",
    "externalProperties": {
    "attributes": [
    {
    "attributeTypeName": "Temperature Classification",
    "value": "Chilled"
    }, {
    "attributeTypeName": "Fragile",
    "value": "Yes"
    }],
    },
    },
    "deliveredGoods": [
    {
    "id": 5678,
    "orderLineId": 1234,
    "externalGoodsProperties": {
    "id": 5678,
    "externalProperties": {
    "itemId": 123456,
    "itemName": "Example item",
    "lot": "Lot 123",
    "quantity": 120
    }
    }
    }, {
    "id": 5679,
    "orderLineId": 1234,
    "externalGoodsProperties": {
    "id": 5678,
    "externalProperties": {
    "itemId": 123456,
    "itemName": "Example item",
    "lot": "Lot 456",
    "quantity": 80
    }
    }
    }]
    }]

    Desired result:

    ItemId Item name Fragile Lot Quantity
    --------------------------------------------------------------------
    123456 Example item Yes Lot 123 120
    123456 Example item Yes Lot 456 80
  • Replied 21 September 2021, 7:32 pm EST

    Hello,
    I am not sure that I fully understand your question.
    You mentioned that a report has 3 datasets, but the data sample shows a single dataset.
    Which one is actually used?
  • Replied 22 September 2021, 5:38 pm EST

    The example data shows a part of a JSON file which contains all the data that is used in my report. So this JSON file is my only data source in this report. Within my report I defined multiple datasets from which I mentioned 3 in my example.
    The table control that I use to show the delivered goods is connected to dataset deliveredGoods. In this table I also want to show data from the other 2 datasets in the example.
    There is an array of order lines. Each order line contains an ordered item with an array of item attributes and each order line contains an array of delivered goods.
  • Replied 23 September 2021, 8:20 pm EST

    Unfortunately, I don't think that it would be feasible without re-shaping the data.
  • Marked as Answer

    Replied 23 September 2021, 9:04 pm EST

    Unfortunately this isn't the right forum for my posting. It should have been Active Reports V7+.
    However it seems that we now have found a solution ourselves for selecting the correct data from the JSON file using JSON Path.
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels