SQL filtering by location in iReport/Fishbowl

Question

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

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

You should show the code for passing parameter's value to report — Alex K, Feb 08 '16 at 21:52
Try to reduce the jrxml your posting to relevant xml, remove text formatting , fields that not are of interest (we need to see you param def etc.) creating a [mcve] — Petter Friberg, Feb 08 '16 at 22:58
Also included the java code you use to call the report... so we see that you put data in the parameter map. — Petter Friberg, Feb 08 '16 at 22:59
Sorry, ashton, to much stuff (and I forgot : (, good you pinged me, I will try to check it out to night.... — Petter Friberg, Feb 11 '16 at 18:34
I have no experience of fishball, how do you query? I can't see any query in queryString tag, on "normal" database in this tag you would but "SELECT * FROM myTable where myField = $P{myParameter}, in json and xml you use a syntax similar to xpath... so we need to fill the queryString tag with correct fishball syntax... — Petter Friberg, Feb 11 '16 at 22:53
ahh sorry now I see a query in image? (hate images;), why is this not in jrxml example?. Anyway you are mixing string concat with prepared statement see this post http://stackoverflow.com/questions/11871042/jasperreports-passing-parameters-to-query , you can do this but its not good practice, try to use only prepared statement. — Petter Friberg, Feb 11 '16 at 23:00
Furthermore when you use between and in you can use the $X{ command see this http://jasperreports.sourceforge.net/sample.reference/query/ — Petter Friberg, Feb 11 '16 at 23:04
Note the parameter class need's to correspond to the column type, I would try to debug my query, making first very simple (just one parameter) query and then work my way from there.... — Petter Friberg, Feb 11 '16 at 23:06

score 2 · Accepted Answer · answered Feb 22 '16 at 06:10

2

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.

answered Feb 22 '16 at 06:10

Mamof

181
5
13

Can you paste the error you are getting? The query is based on the 16.4 default report. – Mamof Feb 23 '16 at 18:27
I apologize it does work! I am not sure what the error was but it was cleared up. Thank you Mamof! Also I read your description above as to why what i had did not work but i am not sure if i understand. Did i just not have the correct relations set up? I am new to Fishbowl and it may be my lack of knowledge with the tables but I assumed simply adding the locationgroup table should have only printed the part values ascociated with the location selected. Could you help me learn this? I appreciate this greatly. – Ashton Feb 23 '16 at 18:36
Also i am unsure as to why someone would down vote my question when it was a real concern :/ – Ashton Feb 23 '16 at 18:37
1

What you were needing is to specify the left join on the tables starting at the part. You were looking at the location group table but didn't have the intermediate tag and location tables to get to it. – Mamof Feb 23 '16 at 18:55
I see now. Thank you! This is another question but it seems that i am now gettind duplicate "repeating values" but even when i uncheck the "print repeated values" it only takes out some and then my total sum stays the same as if the values were not removed. Do you know a way to fix this? I will post a new question if you'd like. Once again- thank you for teaching! – Ashton Feb 23 '16 at 18:57
The show repeat values only takes into consideration items next to each other. If your duplicates are mixed along the report it will not catch those. – Mamof Feb 23 '16 at 19:03
They are right next to eachother. The issue is that it still takes off random parts but not all of the repeated and my GrandTotalAsset variable doesn't change – Ashton Feb 23 '16 at 19:05
1

Are all of the fields on the row have the print repeated values unchecked? And does the part group total cost change? – Mamof Feb 23 '16 at 19:09
Yes i made sure to uncheck the box for every field and i am unsure about the group total or how to check – Ashton Feb 23 '16 at 19:15
In looking at the report the part group total will be the Total value while the full reports total is the Grand Total – Mamof Feb 23 '16 at 19:16
So there is no way to make the value change when i stop the repeating values : /? – Ashton Feb 23 '16 at 19:20
If you do not mind i will post another question showing the issue i am having now. Thank you and i will comment a link to the new issue. Thank you! – Ashton Feb 23 '16 at 19:25
I was just asking because you said the grand total wasn't changing. I was wondering if the other values were changing? So with this being a report dealing directly with the cost layers you should not be getting actual duplicates. By that I mean the timestamps should be different. You can double check this by looking at the cost layers in the inventory module. – Mamof Feb 23 '16 at 19:29
here is the link to my new question - thank you so much for all of your help! http://stackoverflow.com/questions/35638896/unable-to-filter-out-the-repeating-values-in-ireport – Ashton Feb 25 '16 at 21:39

SQL filtering by location in iReport/Fishbowl

1 Answers1