Search code examples
eclipsemongodbreporting-servicesbirtbusiness-intelligence

Getting duplicate rows on left join in Birt reports

I am joining two data sets in Birt . Its a left outer join. Below is the screen shot of the data sets.

enter image description here

The reason why I need all the rows of left table is I am doing some calculations on timestamp for all the rows of left table. I need to count the priority levels (how many times it occurred) in the right table if terminal Id matches with the left table.

When I get the records it gets the duplicate records which causes my timestamp calculations to get doubled. I can't do inner join because I need to do the timestamp calculation from left table for must.

Relation of both the tables in many to many. I will explain with example what is the issue I am facing and what I want to achieve. E.g. This is the data for the events of DeviceEventObject data set :

record 1 : 

    "event" : "EXITED SUPERVISOR MODE",
        "timestamp" : ISODate("2017-12-17T06:06:23.181Z"),
  "terminal" : {
            "terminalId" : "testterminal",
            "branchId" : "test"
        }

record 2: 

 "event" : "ENTERED SUPERVISOR MODE",
        "timestamp" : ISODate("2017-12-17T06:06:23.181Z"),
  "terminal" : {
            "terminalId" : "testterminal",
            "branchId" : "test"
        }

From this the timestamps of each event I am calculating the time between entered and exited events.

Now the other data set is DeviceStatusErrorCodePrioirtyLevel: E.g. This is the records in this data set :

"status" : "Online",
    "errorCode" : "123",
    "priorityLevel" : "test",
    "emailTypeCode" : "123",
    "terminal" : {
        "terminalId" : "testterminal",
        "branchId" : "test"
    }

Now I want to calculate the number of times the priority level "test" occurred for the terminalId "testterminal" . with the above data set count will be 1. I am joining the both data sets on the basis of terminalId.

Now with the above data set I get duplicate records which doubles my time which I am calculating and also I get the count for the priority level 2. For example this is what I get :

"event" : "EXITED SUPERVISOR MODE",   "priorityLevel" : "test"
"event" : "ENTERED SUPERVISOR MODE", "priorityLevel" : "test"

What I want is : 

 "event" : "EXITED SUPERVISOR MODE",   "priorityLevel" : "test"
    "event" : "ENTERED SUPERVISOR MODE",

Additional Info of the birt project :

enter image description here

Sample data from both data sets : 

DeviceStatusErrorCodePrioirtyLevel:

{
    "_id" : ObjectId("5a36095f1854ad0b7096184b"),
    "className" : "com.omnia.pie.cm.models.snapshot.terminal.v2.DeviceStatusErrorCodePrioirtyLevel",
    "timestamp" : ISODate("2017-12-17T06:06:23.181Z"),
    "deviceName" : "CardReader",
    "status" : "Online",
    "errorCode" : "123",
    "priorityLevel" : "test",
    "emailTypeCode" : "123",
    "terminal" : {
        "terminalId" : "testterminal",
        "branchId" : "test"
    }
}

DeviceEventObject:

{
    "_id" : ObjectId("5a3608c61854ad0b70961846"),
    "className" : "com.omnia.pie.cm.models.snapshot.terminal.v2.DeviceEventObject",
    "event" : "EXITED SUPERVISOR MODE",
    "value" : "True",
    "timestamp" : ISODate("2017-12-17T06:03:50.901Z"),
    "transactionData" : {
        "transactionType" : "",
        "transactionNumber" : "",
        "sessionId" : ""
    },
    "terminal" : {
        "terminalId" : "testterminal",
        "branchId" : "test"
    }
}

Here is the link to my report in case : https://drive.google.com/file/d/1dHOEneG2-fbeP9Mz86LUhuk0tSxnLZxi/view?usp=sharing

Solution

  • Add a new data set for DeviceEventObject

    Add the following aggregate function in the command expression builder.

    The below function $lookup the data from status error code priority level based on terminalId followed by $unwind to flatten the data.

    $group the flatten data on terminalId to accumulate the distinct priority levels for a terminal id.

    $project to count the distinct priority levels

    [{$lookup:{
        from: "devicestatuserrorcodeprioirtylevel", // name of the collection
        localField: "terminal.terminalId",
        foreignField: "terminal.terminalId",
        as: "dsecpl"
}},
{$unwind:"$dsecpl"},
{$group:{
    "_id":"$terminal.terminalId", 
    "prioritylevels":{"$addToSet":"$dsecpl.priorityLevel"},
    "events":{"$push":"$event"}
}},
{"$project":{"prioritylevelcount":{"$size":"$prioritylevels"}, "events": 1} }
]

    Move all the available fields to the selected fields column.

    Preview results.