按位置在iReport/Fishbowl中进行SQL筛选

Question

我有一个报告，我试图通过使用参数添加一个筛选器，该过滤器允许用户选择Part Number，然后显示项目在哪里，由Location过滤。

问题是，无论出于什么原因--即使我将LocationGroupID设置为LocationGroupID，它也无法工作或更新。

这里有一些截图。

我正在使用iReport和Fishbowl

​

​

<parameter name="path" class="java.lang.String" isForPrompting="false">
    <defaultValueExpression><![CDATA["C:/Program     Files/Fishbowl/Server/reports/WorkOrder/"]]></defaultValueExpression>
</parameter>
<parameter name="module" class="java.lang.Object" isForPrompting="false">
    <defaultValueExpression><![CDATA[null]]></defaultValueExpression>
</parameter>
<parameter name="REPORTDESCRIPTION" class="java.lang.String"     isForPrompting="false">
    <defaultValueExpression><![CDATA["Generates a summary of the quantity     and value of all parts currently on hand. This is a LIFO/FIFO based report."]]>    </defaultValueExpression>
    </parameter>
    <parameter name="partNum" class="java.lang.String" isForPrompting="false">
        <defaultValueExpression><![CDATA[]]></defaultValueExpression>
    </parameter>
    <parameter name="locationGroupID" class="java.lang.String" isForPrompting="false">
    <defaultValueExpression><![CDATA[]]></defaultValueExpression>
    </parameter>
    <parameter name="AssetAccount" class="java.lang.String" isForPrompting="false">
    <defaultValueExpression><![CDATA["%"]]></defaultValueExpression>
    </parameter>
    <parameter name="dateRange1" class="java.util.Date" isForPrompting="false">
    <parameterDescription><![CDATA[This Month]]></parameterDescription>
    <defaultValueExpression><![CDATA[new Date()]]></defaultValueExpression>
    </parameter>
    <parameter name="dateRange2" class="java.util.Date" isForPrompting="false">
    <defaultValueExpression><![CDATA[new Date()]]></defaultValueExpression>
    </parameter>
    <parameter name="ckShowActiveCostingLayers" class="java.lang.String" isForPrompting="false">
    <parameterDescription><![CDATA[10,100]]></parameterDescription>
    <defaultValueExpression><![CDATA["10"]]></defaultValueExpression>
</parameter>
<parameter name="ckShowFulfilledCostingLayers" class="java.lang.String" isForPrompting="false">
    <parameterDescription><![CDATA[20,100]]></parameterDescription>
    <defaultValueExpression><![CDATA["100"]]></defaultValueExpression>
</parameter>
<parameter name="ckShowVoidedCostingLayers" class="java.lang.String" isForPrompting="false">
    <parameterDescription><![CDATA[30,100]]></parameterDescription>
    <defaultValueExpression><![CDATA["100"]]></defaultValueExpression>
</parameter>
<parameter name="ShowHistoricalData" class="java.lang.Boolean" isForPrompting="false">
    <defaultValueExpression><![CDATA[new Boolean(false)]]>        </defaultValueExpression>
    </parameter>
    <queryString>
</queryString>
<field name="QTY" class="java.lang.Double"/>
<field name="ORGQTY" class="java.lang.Double"/>
<field name="ORGTOTALCOST" class="java.lang.Double"/>
<field name="TOTALCOST" class="java.lang.Double"/>
<field name="DATECREATED" class="java.sql.Timestamp"/>
<field name="PARTNUMBER" class="java.lang.String"/>
<field name="PARTDESCRIPTION" class="java.lang.String"/>
<field name="InventoryAccount" class="java.lang.String"/>
<field name="LOCATIONGROUP_ID" class="java.lang.Integer"/>
<field name="COMPANY" class="java.lang.String"/>
<variable name="AssetValue" class="java.lang.Double">
    <variableExpression><![CDATA[$P{ShowHistoricalData}.booleanValue() == true ?
$F{ORGTOTALCOST} :
$F{TOTALCOST}]]></variableExpression>
</variable>
<variable name="TotalAsset" class="java.lang.Double" resetType="Group" resetGroup="Part" calculation="Sum">
    <variableExpression><![CDATA[$V{AssetValue}]]></variableExpression>
</variable>
<variable name="ReportTotal" class="java.lang.Double" calculation="Sum">
    <variableExpression><![CDATA[$V{AssetValue}]]></variableExpression>
</variable>
<variable name="locationTotal" class="java.lang.Double" calculation="Sum">
    <variableExpression><![CDATA[$V{AssetValue}]]></variableExpression>
</variable>
<variable name="TotalCost" class="java.lang.Double" resetType="Group" resetGroup="Part" calculation="Sum">
    <variableExpression><![CDATA[$V{UnitCost}]]></variableExpression>
</variable>
<variable name="GrandTotalAsset" class="java.lang.Double" calculation="Sum">
    <variableExpression><![CDATA[$V{AssetValue}]]></variableExpression>
</variable>
<variable name="GrandTotalCost" class="java.lang.Double" calculation="Sum">
    <variableExpression><![CDATA[$V{TotalCost}]]></variableExpression>
</variable>
<variable name="UnitCost" class="java.lang.Double">
    <variableExpression><![CDATA[$P{ShowHistoricalData}.booleanValue() == true ?
new Double($F{ORGTOTALCOST}.doubleValue() / $F{ORGQTY}.doubleValue()) :
new Double($F{TOTALCOST}.doubleValue() / $F{QTY}.doubleValue())]]>    </variableExpression>
</variable>
<variable name="DateFormat" class="java.lang.String" resetType="None">
    <variableExpression><!    [CDATA[(System.getProperty("REPORT_DATE_FORMAT"))]]></variableExpression>
</variable>

Answer 1

问题是在成本层和位置之间没有直接的联系。如果您所要做的只是在给定的位置组中只对部分进行筛选，那么您需要通过标签(手头的库存表)返回到位置。

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

这将过滤并只显示给定位置组中的部分。通过将参数默认值设置为"%“并取消选择use选项，如果未选中，它将对所有参数进行筛选。请记住，如果您想要查看位置组，您的总计可能会与多个位置组的库存一起抛出，如果您对所有这些信息进行筛选。