[Having a problem with variables and the IF function]

I have a query that is returning data,  I need to do a sum of a field QTY based on a value of partnumber.   This is the expression I am using for the Variable:

IF($F{partnumber}="90000013",$F{qty},0.00)

Jasper is telling me that this is not valid.  I have tried it with quotes and apostophies around the 90000013 and get the same thing.  For the life of me I can not find the syntax error.   Can anyone help?

[What is the best tool for designing complex querys in Jasper]

I am writing a report that goes after 7 linked tables in a postgresql database.  Most of the joins are one to many, some are 'give me a record is the left side has data, even if the right does not' joins.  I am trying to use the query builder but it keeps screwing up my links.

Can any one recogmend a good tool to help with designing these querys.   I really hate to say this, but MS Access query builder works like a charm for building these.  Anything out there for convery MS Access querys, or create queries like Access does?

Greg

[Unable to save simple Jasper Studio Report of Jasper Server]

I have a simple report, one table, one query, one parameter field.  I can do a SAVE-AS but I can not seem to save it to my Jasper Server.  I have saved other reports to my J Server, but this is the first one with a run time parameter.  Is this causing a problem somewhere?

[Run time queries in Jasper Studio reports]

the database in question is Postgresql.    Also I am never prompted for an input.

[Run time queries in Jasper Studio reports]

The exact SQL code is:

select * from parts where not(parts.obsolete) && (parts.bin like ($P{Location} || '%')

Here are the errors I am getting:

net.sf.jasperreports.engine.JRException: Error executing SQL statement for: data.
    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:342)
    at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1129)
    at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:696)
    at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:437)
    at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:523)
    at net.sf.jasperreports.engine.fill.JRFiller.fill(JRFiller.java:164)
    at net.sf.jasperreports.engine.JasperFillManager.fill(JasperFillManager.java:474)
    at com.jaspersoft.studio.data.reader.DatasetReader.start(DatasetReader.java:197)
    at com.jaspersoft.studio.property.dataset.dialog.DataPreviewTable$4.run(DataPreviewTable.java:241)
    at org.eclipse.core.internal.jobs.Worker.run(Worker.java:54)
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at end of input
  Position: 77
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273)
    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:332)
    ... 9 more
 

[Run time queries in Jasper Studio reports]

I have a report, in it have a parameter $P{Location Desired}    In the dataset and query dialog I am trying to get Jasper Server to ask the use for the location they want on the report.   Everything I have tried gives me an error.   Here is the SQL code I last tried.

select * from parts where bin like $P{Location Desired}

bin is the table field with the location information in it.

[Groups with subtotals, is there an esier way?]

I have a report is Jasper Studio.  The report is sorted on part_number, and has a group (Group1) based on part_number.  I have a variable Sum_OO which is:

Name:  Sum_OO

Value Class Name:  java.lang.Double

Calculation:  Sum

Expression:  $F{qty}-$F{ship}

Initial Value Expression:

Increment type: [Group] Group1

Incrementer Factory Class Name:

Reset Type: [Group] Group1

What happens is on the Group1 footer I get $V{Sum_OO} printed, but it only contains the last calculation.  Not the total for the group (or the total for the report).

 

What am I doing wrong, I agree this can not be this hard.

[One query, three tables two many to one joins]

I have three tables  INVOICE, IDETAIL, and INVENTORY

The link between INVOICE and IDETAIL is INVOICE.ID  --> IDETAIL.INV_NUMBER

The link between INVENTORY and IDETAIL is  INVENTORY --> IDETAIL.PART_NUMBER

'-->'  is a many to one join.  ie, each Invoice or Inventory item can have multiple IDETAIL records.   When I try and create this join in the query builder I keep getting the join lines all moved about.

Can anyone help here?

[How to not print-show zero values ?]

I found the simplest way in Jasper Studio was to use the "Print When Expression" control:

$V{NotDue}!= 0

This will cause the field to be printed only when the value of the variable NotDue is not equal to 0 (Zero).   It is good to note that zero and null are NOT that same thing.

[formatting numbers of a report]

WOW do I ever feel like a greenhorn.  I have a report, it has three numbers on it, 2 come from a Postgresql database, the third is calculated at runtime.    Here is the problem I am having, and it is simple.  The calculated field is coming out like "25.231254785"    Where what I need is "25.23"  I would have thought this would be easy, but for the life of me I can not find the place you put the format of a field.

Any help would be awsome, thanks in advance.

Greg

[IF statements in a Variable on a report.]

I have a varaible basically I want it to say OK if the markup is over 40% for most items, but for items under $25 I want the mark up to be OK if it is over 80%.    This is what I am trying to get to work, but I am getting an error at the AND operator:

IF($F{lastcost} >= ($F{sellprice} /1.40),"Low Sell",IF(($F{lastcost} >= ($F{sellprice} / 1.80)) AND ($F{lastcost} < 25),"Low Sell","OK"))

 

I expect my problem is the use of AND.   Pretty new to Jasper, so could be just about anything.

[Data is the 'Dataset & Query' not showing up in the report.]

OK, that is odd.  Inside the project I created a new report and the screens you talked about showed up.   When I creeated the report the first time, then the ddataset inside the report never could find the screen that allowed me to select the fields I needed on the report.

[Data is the 'Dataset & Query' not showing up in the report.]

I can't believe I am asking this, I am obviously missing somethiing really important but....

I have a dataset in my report defined, I am going after a Postgresql database.  I have the query working and when I go to Data Preview I can see the data I need.   So I press OK and go back to my report.  Problem is try as I might I can not find the fields to add to my report.  What am I doing wrong?

Novice to JasperSoft, but used Crystal, and other report writers for decades.   Feel kind of stupid to even be asking this.

 

Greg