无法过滤掉iReport中的重复值 [英] Unable to Filter out the repeating values in iReport

问题描述

我的报告应该根据部分和数量 >位置已选中。它是Fishbowl中成本核算层评估的修改版本。

I have a report that is supposed to show the part and quantity based on the location selected. It is a modified version of the Costing Layer Valuation in Fishbowl.

我已经能够使位置过滤工作但现在值正在重复，我无法找出原因。

I have been able to get the location filtering to work but now the values are being duplicated and I am unable to figure out why.

这些是在取消选中并选中打印所有重复复选框时显示报告打印的屏幕截图。

These are screenshots to show the print out of the report when the Print All Repeating check box is unchecked and checked.

这是我的SQL查询

SELECT 
costlayer.qty AS Qty, costlayer.orgqty, costlayer.orgtotalcost,
costlayer.totalcost AS TotalCost, costlayer.datecreated AS DateCreated,
part.num AS PartNumber, part.description as PartDescription, asaccount.name as "InventoryAccount",
company.name AS company, currency.symbol

FROM CostLayer
LEFT JOIN Part ON part.id = costlayer.partid
LEFT JOIN Tag ON part.id = tag.partId
LEFT JOIN Location ON tag.locationId = location.id
LEFT JOIN LocationGroup ON location.locationGroupId = locationGroup.id
LEFT JOIN asaccount ON part.inventoryaccountid = asaccount.id
JOIN company ON company.id = 1
LEFT JOIN currency ON currency.homeCurrency = 1

WHERE 
costlayer.datecreated BETWEEN $P{dateRange1} AND $P{dateRange2}
AND costlayer.statusid IN ($P!{ckShowActiveCostingLayers},$P!{ckShowFulfilledCostingLayers},$P!{ckShowVoidedCostingLayers})
AND UPPER(part.num) LIKE UPPER($P{partNum})
AND (UPPER(COALESCE(asaccount.name,'')) LIKE UPPER('%' || $P{AssetAccount} || '%'))
AND LocationGroup.id LIKE $P{locationGroupID}

ORDER BY (CASE WHEN $P{AssetAccount} NOT LIKE CAST('%' AS varchar(256)) THEN asaccount.name ELSE part.num END), part.num ASC, costlayer.id, costlayer.datecreated

推荐答案

在查看屏幕截图时，根据每个位置的标签数量显示它是重复的。这将来自标签上的连接，以便能够过滤位置。通过向查询添加distinct，它将清除重复的数据库值。在这样做之后，您可能希望重新显示显示重复值，因为如果您为给定零件提供相同的购买数量和价值，则不会显示。

In looking at your screen shots it appears it's duplicating based on the number of tags in each location. This would be from the join on tags to be able to filter the locations. By adding a distinct to your query it will weed out the duplicate database values. After doing that you'll probably want to turn back on the show duplicate values as it will then not show if you make the same quantity and value of purchases for a given part.

SELECT DISTINCT costlayer.qty AS Qty, costlayer.orgqty, costlayer.orgtotalcost,
costlayer.totalcost AS TotalCost, costlayer.datecreated AS DateCreated,
part.num AS PartNumber, part.description as PartDescription, asaccount.name as "InventoryAccount",
company.name AS company, currency.symbol

FROM CostLayer
LEFT JOIN Part ON part.id = costlayer.partid
LEFT JOIN Tag ON part.id = tag.partId
LEFT JOIN Location ON tag.locationId = location.id
LEFT JOIN LocationGroup ON location.locationGroupId = locationGroup.id
LEFT JOIN asaccount ON part.inventoryaccountid = asaccount.id
JOIN company ON company.id = 1
LEFT JOIN currency ON currency.homeCurrency = 1

WHERE costlayer.datecreated BETWEEN $P{dateRange1} AND $P{dateRange2}
AND costlayer.statusid IN ($P!{ckShowActiveCostingLayers},$P!{ckShowFulfilledCostingLayers},$P!{ckShowVoidedCostingLayers})
AND UPPER(part.num) LIKE UPPER($P{partNum})
AND (UPPER(COALESCE(asaccount.name,'')) LIKE UPPER('%' || $P{AssetAccount} || '%'))
AND LocationGroup.id LIKE $P{locationGroupID}

ORDER BY (CASE WHEN $P{AssetAccount} NOT LIKE CAST('%' AS varchar(256)) THEN asaccount.name ELSE part.num END), part.num ASC, costlayer.id, costlayer.datecreated

这篇关于无法过滤掉iReport中的重复值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！