viking79

Hello, What I am trying to do is create a text box property that for example: I have a column called customfieldid I want to have if customfieldid = 19 than it prints the value from the info field. Any ideas on how to do this would be greatly appreciated. Thanks, Viking

Hello, I am working on a report and I have a column I have summed. The summed value is currently in the column footer line. The problem is it puts the output near the bottom of the page even though I have moved everything up to where it is nearly touching the detail line. Is thier a setting or something to make it appear right under the column when the report is ran? Any help is greatly appreciated, Viking79

Hi, thanks for the response. The thing is I'm uploading the report into Fishbowl Inventory. I have the jasper Server plugin for iReports, Is there anyway to use that?

Is it possible to add drop down boxes for users instead of having them type anything in? Thanks

Thanks again for the help, that worked perfectly. Take care

Thanks for the response. I tried the first option because a total is all I am really looking for, but I get the error -104invalid expression in select list(not contained in either aggregae function or the GROUP BY clause Any ideas? Take care

Thanks for your help, it happened to be an interger data type so the second one worked out just fine. Thanks again :)

Hi, I want the report I'm working on to bring back info based on where an id equals either 1,2,or 11 When I put all three in the query, I get errors, Any suggestions? Thanks

Hi, thanks for the reply. I still don't get the group band part and when I put in my query Group By po.num, I get an error. I may have tried putting the Group By in the wrong part of the query. Here is my code without the group by, any suggestions? Thanks again Code:SELECT PO."NUM" AS PO_NUM, PO."VENDORCONTACT" AS PO_VENDORCONTACT, PO."DATECREATED" AS PO_DATECREATED, PO."QBCLASSID" AS PO_QBCLASSID, POITEM."TOTALCOST" AS POITEM_TOTALCOST, PAYMENTTERMS."ID" AS PAYMENTTERMS_ID, PAYMENTTERMS."NAME" AS PAYMENTTERMS_NAME, QBCLASS."NAME" AS QBCLASS_NAMEFROM "VENDOR" VENDOR INNER JOIN "PO" PO ON VENDOR."ID" = PO."VENDORID" INNER JOIN "POITEM" POITEM ON PO."ID" = POITEM."POID" INNER JOIN "PAYMENTTERMS" PAYMENTTERMS ON PO."PAYMENTTERMSID" = PAYMENTTERMS."ID" INNER JOIN "QBCLASS" QBCLASS ON PO."QBCLASSID" = QBCLASS."ID" AND QBCLASS."ID" = POITEM."QBCLASSID" AND PAYMENTTERMS."ID" = VENDOR."DEFAULTPAYMENTTERMSID"Where paymentterms.id = 11order by po.num ASC

Hello, I working on a summary and I want to sum total cost. For example if PO # 12234 has two items I want to sum those two items and if PO # 56788 had 3 items I want to sum those 3 together and have both POs on the same report. Right now when I run the report in the total cost column I get each individual item. Thanks

Hey thanks for all your help, still not getting the info we need, but I think is has to do with how we are trying to join the tables, rather than the text expression. I think once we figure that out, I can use some of the ideas you had to get the text boxes to fill out. Anyways, thanks again :)

Parttrackingid is int Trackingtextinfo is varchar Sorry about that, probably should have mentioned that/tools/fckeditor/editor/images/smiley/msn/regular_smile.gif Post Edited by viking79 at 01/27/2012 15:38

Hi, thanks for the response. What I would like to do is if ParttrackingID is equal to 5, I need it to print the value of TrackingTextInfo in the box. That's were I'm really hung up. I have several different sections of the report that need fulled, and ParttrackingID value is what dictates what goes in each one but the actual value I want shown is in the trackingtextinfo, if that makes sense. Any suggestions? Thanks and take care

I want to get the value of a field to show based on whether the values of two other fields match. I wrote a text expression I thought might work but no luck yet. Any ideas? Here is the expression as it stands riight now. It's not erroring out, it just doesn't bring back the desired value. Thanks Code:(($F{PARTTRACKINGID}== $F{TRACKINGTEXTTAGID})?"getValue($F{TRACKINGTEXTINFO})":"")

Hello, In my report I have the box for print repeated values unchecked, and even checked again in the XML view, but when I run the report it still wants to repeat values? Any suggestions? Thanks

Hello, I am customizing a Bill of Lading report to fit my companies needs. For Customer Order Information, I have four rows because sometime they have multiple orders leaving on the same truck. Anyways, even if I only have 1 order number, it fills in all four rows with that number. The same goes for the other columns as well. Is there any way to get it to print only in the first row only if that is all thier is? Any help is greatly appreciated, Thank you

Hello, I made a modified bill of lading report to fit the type of items we actually ship. We have a table called PARTTRACKING and one called TRACKINGTEXT that sort of go together. I'm trying to get those two tables into the bill of lading query, but the few things I've tried didn't work. I have pasted the code for the bill of lading that it starts with. I end up going back to this one everytime I screw something up. I really need those two tables to fill out the main detail of the BOL. Any help is greatly appreciated. Code:SELECT ship.ordertypeid AS ordertype, ship.orderid AS orderid, ship.billoflading AS bolnumber, ship.note AS shipnote, ordertype.name AS ordertypename, ship.cartoncount, ship.locationgroupid AS locationgroupid, carrier.name AS carriername, carrier.scac AS carrierscac, COALESCE(so.shiptoname, po.remittoname, xo.shiptoname) AS shipname, COALESCE(so.shiptoaddress, po.remitaddress, xo.shiptoaddress) AS shipaddress, COALESCE(so.shiptocity, po.remitcity, xo.shiptocity) AS shipcity, stateconst.code AS shipstate, countryconst.name AS shipcountry, COALESCE(so.shiptozip, po.remitzip, xo.shiptozip) AS shipzip, COALESCE(so.num, po.num, xo.num) AS ordernumber, COALESCE(so.note, po.note, xo.note) AS ordernote, shipterms.name AS shipterms, fobpoint.name AS fobpoint, shipcarton.id AS cartonid, shipitem.qtyshipped, uom.code AS uomcode, COALESCE(soitem.productnum, poitem.partnum, xoitem.partnum) AS partnum, COALESCE(soitem.description, poitem.description, xoitem.description) AS partdescription, COALESCE(weightTable.weight, 0) AS cartonWeight, COALESCE(weightUom.code, 'lbs') AS weightUomCodeFROM ship INNER JOIN shipitem ON ship.id = shipitem.shipid INNER JOIN carrier ON ship.carrierid = carrier.id INNER JOIN shipcarton ON (ship.id = shipcarton.shipid AND shipitem.shipcartonid = shipcarton.id) INNER JOIN (SELECT SUM(shipcarton.freightweight) AS weight, shipcarton.shipid FROM shipcarton GROUP BY shipid) AS weightTable ON weightTable.shipid = ship.id INNER JOIN ordertype ON ship.ordertypeid = ordertype.id INNER JOIN uom ON shipitem.uomid = uom.id LEFT OUTER JOIN po ON (po.id = ship.orderid AND ship.ordertypeid = 10) LEFT OUTER JOIN poitem ON (shipitem.orderitemid = poitem.id AND ship.ordertypeid = 10) LEFT OUTER JOIN so ON (so.id = ship.orderid AND ship.ordertypeid = 20) LEFT OUTER JOIN soitem ON (shipitem.orderitemid = soitem.id AND ship.ordertypeid = 20) LEFT OUTER JOIN xo ON (xo.id = ship.orderid AND ship.ordertypeid = 40) LEFT OUTER JOIN xoitem ON (shipitem.orderitemid = xoitem.id AND ship.ordertypeid = 40) LEFT OUTER JOIN stateconst ON stateconst.id = COALESCE(so.shiptostateid, po.remitstateid, xo.shiptostateid) LEFT OUTER JOIN countryconst ON countryconst.id = COALESCE(so.shiptocountryid, po.remitcountryid, xo.shiptocountryid) LEFT OUTER JOIN shipterms ON shipterms.id = COALESCE(so.shiptermsid, po.shiptermsid, 0) LEFT OUTER JOIN fobpoint ON fobpoint.id = COALESCE(so.fobpointid, po.fobpointid, 0) LEFT OUTER JOIN sysProperties ON sysProperties.sysKey = 'Default Weight UomId' LEFT OUTER JOIN uom weightUom ON sysProperties.sysValue = weightUom.id

Hello, Is there a way to have the page header only appear on the first page of a multiple page report? Thank you.

Nevermind..I got it

Hello, I have to move multiple text fields and I have been doing them one at a time. Is there anyway to select more than one item and move them? Thank you.

Never mind....just found the check box for "blank if null" in the properties...

Hello, I have a text field expression that says (($F{CustomfieldID)== 14)"APPROVED":""+$F{CustomfieldID}) It works fine with the exception of it brings back null when the value doesn't equal 14. Is there a way to have it bring back nothing at all, just leave the box blank? Thanks

Thanks for everyone's help, I got it using the Augarte's answer of making a variable in the textfield expression. Still I appreciate everyone's response in trying to help me out/tools/fckeditor/editor/images/smiley/msn/regular_smile.gif

Hello Another report I'm working on I'm using the CustomTimeStamp table, but I want to go back and use the CustomFieldView as well. I have added my code. I can get this to work with either one or the other, but when I go for both I get an error. Any help is greatly apprecriated. Thanks Code:SELECT po.vendorcontact, po.vendorso, po.vendorid,customtimestamp.info,customfieldview.cfsortorder, po.shiptoname, po.shiptoaddress, po.shiptocity, shipState.code AS shiptostate, po.shiptozip, shipcountry.name AS shiptoCountry, /*Bill To*/ po.remittoname, po.remitaddress, po.remitcity, billstate.code AS billtostate, po.remitzip, billcountry.name AS billtocountry, SUM(poitem.totalcost) AS totalcost, po.note, po.id AS recordid, po.totalTax AS totalTax,COALESCE(UPPER(sysProperties.sysValue), 'US') AS qbVersionFROM po INNER JOIN poitem ON po.id = poitem.poid LEFT OUTER JOIN stateconst AS shipState ON po.shiptostateid = shipState.id LEFT OUTER JOIN stateconst AS billState ON po.remitstateid = billState.id LEFT OUTER JOIN countryconst AS shipCountry ON po.shiptocountryid = shipCountry.id LEFT OUTER JOIN countryconst AS billCountry ON po.remitcountryid = billCountry.id LEFT OUTER JOIN sysProperties ON sysProperties.sysKey = 'QBVersion' AND UPPER(sysProperties.sysValue) = 'AU' LEFT OUTER JOIN customtimestamp ON po.id = customtimestamp.recordid LEFT OUTER JOIN customfieldview on po.id = customfieldview.recordid and customfieldview.cftableid = 397076832WHERE po.num = '10010'GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,18,19,20,21

Hello, Does this go in the query or where you edit the expression itself?