Home Ask Login Register

Developers Planet

Your answer is one click away!

Ashton February 2016

SQL filtering by location in iReport/Fishbowl

I have a report that I am trying to add a filter, by use of parameter, that allows the user to select the Part Number and then shows where the item is, filtered by, Location.

The issue is that for what ever reason- even though I have the LocationGroupID set = to LocationGroupID it wont work or update.

Here are some screenshots.

I am using iReport and Fishbowl

enter image description here enter image description here

<parameter name="path" class="java.lang.String" isForPrompting="false">
    <defaultValueExpression><![CDATA["C:/Program     Files/Fishbowl/Server/reports/WorkOrder/"]]></defaultValueExpression>
<parameter name="module" class="java.lang.Object" isForPrompting="false">
<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 name="partNum" class="java.lang.String" isForPrompting="false">
    <parameter name="locationGroupID" class="java.lang.String" isForPrompting="false">
    <parameter name="AssetAccount" class="java.lang.String" isForPrompting="false">


Mamof February 2016

The issue you're having is there is no direct link between the cost layers and the locations. If all you're attempting to do is filter by only parts within a given location group you'd need to go back through the tag (on hand inventory table) to the location.

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

This will filter and show only parts in a given location group. By setting the parameters default value to "%" and unselecting use as prompt option it will filter by all if not selected. Keep in mind if you want to see the location group your totals may be thrown off with inventory across multiple location groups if you filter by all.

Post Status

Asked in February 2016
Viewed 1,199 times
Voted 9
Answered 1 times


Leave an answer

Quote of the day: live life