在Birt报表中获取左侧联接的重复行 [英] Getting duplicate rows on left join in Birt reports

问题描述

我要在Birt中加入两个数据集.它是左外部联接.下面是数据集的屏幕截图.

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

我需要左表的所有行的原因是我正在对左表的所有行进行时间戳计算.如果终端ID与左表匹配，我需要在右表中计算优先级(发生的次数).

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.

这两个表之间的关系是多对多的.我将举例说明我面临的问题以及我想要实现的目标. 例如.这是DeviceEventObject数据集的事件的数据:

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 :

记录1:

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

记录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.

现在另一个数据集是DeviceStatusErrorCodePrioirtyLevel: 例如.这是此数据集中的记录:

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"
    }

现在，我想计算对terminalId"testterminal"的优先级"test"发生的次数.以上数据集计数为1. 我正在根据terminalId加入这两个数据集.

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.

现在，使用上述数据集，我得到了重复的记录，这使我正在计算的时间加倍，并且还获得了优先级2的计数. 例如，这就是我得到的:

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"

我想要的是:

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

birt项目的其他信息:

Additional Info of the birt project :

从两个数据集中采样数据:

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"
    }
}

以下是我的报告的链接: https://drive.google.com/file/d/1dHOEneG2-fbeP9Mz86LUhuk0tSxnLZxi/view?usp = sharing

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

推荐答案

为DeviceEventObject添加新数据集

Add a new data set for DeviceEventObject

在命令表达式生成器中添加以下聚合函数.

Add the following aggregate function in the command expression builder.

下面的函数$lookup根据基于terminalId的状态错误代码优先级来获取数据，后跟$unwind来整理数据.

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

$group terminalId上的展平数据以累积终端ID的不同优先级.

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

$project计算不同的优先级

[{$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.

预览结果.

这篇关于在Birt报表中获取左侧联接的重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！