How to get library in iReport 3.5.2

Classic Servoy
1

Hi everybody,

I’m beginner in iReport/JasperReport. I’m currently learning using Servoy tutorials. But I have a problem with one of them because of different iReport version.
Here is the tutorial: http://www.servoy.com/downloads/tutoria … eport.html

At 14:51 of the video the user uses a field from the ‘fields’ section of the library. Apparently this section contains all fields of the two table ables involved in the query (maybe all fields of the database?).
But in my version of iReport (3.5.2) the layout is totally different and I can’t find the equivalent of this section. I found a ‘Fields’ section in the report inspector but it only shows the fields I needed in my setup query.

How can I get access to the fields of the second table (table contact in the tutorial)?

Cheers

2

Yes you picked the correct section: Report Inspector : Fields

If you do not see fields form the contact table then your SQL is not set up correctly to include that table.
Look carefully at the tutorial where the SQL is changed to include the “JOIN contacts …”

3

Here is my query:

SELECT
     tbl_purchase_orders_items."fld_description" AS Purchase_Description,
     tbl_purchase_orders_items."fld_part_no" AS Purchase_Part_No,
     tbl_purchase_orders_items."fld_manufacturer" AS Puchase_Manufacturer,
     tbl_purchase_orders_items."fld_quantity" AS Purchase_Quantity,
     tbl_purchase_orders_items."fld_unit_cost" AS Purchase_Unit_Cost,
     tbl_purchase_orders_items."fld_discount" AS Purchase_Discount,
     tbl_purchase_orders_items."cal_total_line_cost" AS Purchase_Total_Line_Cost,
     tbl_purchase_orders_items."fld_service" AS Purchase_Service
FROM
     "public"."tbl_purchase_orders_items" tbl_purchase_orders_items
LEFT JOIN
    "public"."tbl_stock_control" tbl_stock_control ON tbl_stock_control."cal_purchase_order_item_id" = tbl_purchase_orders_items."fld_id"
WHERE 
    tbl_purchase_orders_items."fld_purchase_order_id" = 6812
ORDER BY
   tbl_purchase_orders_items."fld_id"

Is it the ‘LEFT JOIN’ which could be the problem?

If I compare with the tutorial query I don’t see another difference:

SELECT * 
FROM companies 
JOIN contacts ON contacts.company_id = companies.company_id
WHERE companies.company_id = $P{company_id}
ORDER BY companies.company_id

**EDIT:**I have tried with ‘JOIN’ instead of ‘LEFT JOIN’ but nothing has changed, except the query result. I still just have only the selected fields in the section ‘Fields’

4

I’ve found why!

I need to do SELECT * :lol:

5

Now I have a new problem!
My query normally returns 6 rows but in the report preview I just get one result. Moreover the details (contacts name, etc… in the example) from the 6 rows are displayed under this single result instead of showing only the ones of this result.

Last question: if you have the same field names in both tables (company and contact) you get the field only once in the Fields section. How to get both?

6

To get fields named the same from tables you have to specify the SQL the way that iReport does it - using the “AS” e.g.

tbl_purchase_orders_items.fld_description AS Purchase_Description,

(Notice the back tick here).
To make it easier for you why not just use the query designer? There you tick the fields that you want from the tables and specify the join on the relationship. If you go into the query designer with your current query (that you typed in) it should show the current design.
Tip: erase all your query in the box and then go into the query designer and rebuild from scratch. Then when you exit back to the box you will see the sql that it creates for you (which is not necessarily always correct BTW).

7

Actually it’s a good workaround :lol:

So now it’s ok for the column names. But I still have the problem with only one company record display while the same query in the console gives 6 records. And all the contacts displayed for this companie.

Here is the query used:

SELECT
     tbl_purchase_orders_items."fld_description" AS tbl_purchase_orders_items_fld_description,
     tbl_purchase_orders_items."fld_part_no" AS tbl_purchase_orders_items_fld_part_no,
     tbl_purchase_orders_items."fld_manufacturer" AS tbl_purchase_orders_items_fld_manufacturer,
     tbl_purchase_orders_items."fld_unit_cost" AS tbl_purchase_orders_items_fld_unit_cost,
     tbl_purchase_orders_items."fld_discount" AS tbl_purchase_orders_items_fld_discount,
     tbl_purchase_orders_items."cal_total_line_cost" AS tbl_purchase_orders_items_cal_total_line_cost,
     tbl_purchase_orders_items."fld_service" AS tbl_purchase_orders_items_fld_service,
     tbl_purchase_orders_items."fld_quantity" AS tbl_purchase_orders_items_fld_quantity,
     tbl_stock_control."fld_action" AS tbl_stock_control_fld_action,
     tbl_stock_control."fld_action_date" AS tbl_stock_control_fld_action_date,
     tbl_stock_control."fld_notes" AS tbl_stock_control_fld_notes,
     tbl_stock_control."fld_quantity" AS tbl_stock_control_fld_quantity
FROM
     "public"."tbl_stock_control" tbl_stock_control RIGHT JOIN "public"."tbl_purchase_orders_items" tbl_purchase_orders_items ON tbl_stock_control."cal_purchase_order_item_id" = tbl_purchase_orders_items."fld_id"
WHERE 
    tbl_purchase_orders_items."fld_purchase_order_id" = 6812
ORDER BY
   tbl_purchase_orders_items."fld_id"

And the design of my report:

The 1st fields row is the company details and the 2nd row is the company’s contact details.

8

Check your SQL in iReport by copying it and paste it in your SQL utility tool and see if it returns the same result as this query.
Does your SQL DB support RIGHT joins?

I usually find that a LEFT join is what you want. So go back into the query design, right click on the link between the two tables and up pops Edit/Remove options. Select edit and change so that you select all the rows from the table to the LEFT.

9

I have already tested with my SQL Manager and it gives me 6 results:

The same query in my report gives that:

Only one company and all contacts displayed.

I do a right join to display the companies which don’t have any contact.

PS: I use PostgreSQL 8.3

10

I finally fixed it!

In fact I used the description as the group main field instead of the id.

Now I’m trying to integrate my report to my Servoy solution. I have added the parameter in the SQL query as explained in the tutorial, but when I run it does not include the companies which don’t have any contact.
I think I should do a RIGHT JOIN but I don’t know how to do with a parameter.

Here is my current query:

SELECT
     tbl_purchase_orders_items."fld_line_no" AS tbl_purchase_orders_items_fld_line_no,
     tbl_purchase_orders_items."fld_description" AS tbl_purchase_orders_items_fld_description,
     tbl_purchase_orders_items."fld_quantity" AS tbl_purchase_orders_items_fld_quantity,
     tbl_purchase_orders_items."fld_part_no" AS tbl_purchase_orders_items_fld_part_no,
     tbl_purchase_orders_items."fld_discount" AS tbl_purchase_orders_items_fld_discount,
     tbl_purchase_orders_items."fld_notes" AS tbl_purchase_orders_items_fld_notes,
     tbl_purchase_orders_items."cal_total_line_cost" AS tbl_purchase_orders_items_cal_total_line_cost,
     tbl_purchase_orders_items."fld_service" AS tbl_purchase_orders_items_fld_service,
     tbl_purchase_orders_items."fld_unit_cost" AS tbl_purchase_orders_items_fld_unit_cost,
     tbl_stock_control."fld_action" AS tbl_stock_control_fld_action,
     tbl_stock_control."fld_action_date" AS tbl_stock_control_fld_action_date,
     tbl_stock_control."fld_notes" AS tbl_stock_control_fld_notes,
     tbl_stock_control."fld_quantity" AS tbl_stock_control_fld_quantity,
     tbl_personnel."fld_name" AS tbl_personnel_fld_name,
     tbl_purchase_orders_items."fld_manufacturer" AS tbl_purchase_orders_items_fld_manufacturer,
     tbl_purchase_orders_items."fld_id" AS tbl_purchase_orders_items_fld_id
FROM
     "public"."tbl_purchase_orders_items" tbl_purchase_orders_items RIGHT OUTER JOIN "public"."tbl_stock_control" tbl_stock_control ON tbl_purchase_orders_items."fld_id" = tbl_stock_control."cal_purchase_order_item_id"
     INNER JOIN "public"."tbl_personnel" tbl_personnel ON tbl_stock_control."fld_action_by_id" = tbl_personnel."fld_id"
WHERE tbl_purchase_orders_items.fld_purchase_order_id = $P{po_id}
11

Two issues come to mind:

  1. the use of the INNER JOIN may the culprit - change to LEFT and check for nulls
  2. the where clause parameter should be $P!{po_id}

The po_id is a parameter that you define as Integer (because it is a PK)and that you pass in using the plugin.

Tip for all Jasper Report users: adding flexibility for the where clause:
Create a parameter called WHERE_CLAUSE (type String) and assemble the where clause as you would like to see it. This can either be done in code on the servoy side and the whole where clause passed in or you can assemble the where clause string from other parameters and fields. ENSURE that the WHERE_CLAUSE is the LAST parameter in the list since Jasper process the list from top to bottom and the WHERE_CLAUSE depends on all other preceding parameters and fields.

The same logic can also be applied to the ORDER BY clause as well to further provide flexible reporting strategies.

12

For absolute flexibility I already used $P!{query} to pass the whole query dynamically created in advance.

My (java) app was building the entire query, aliasing the fields as needed to match the fields that Jasper was waiting for.
It worked like a charm!

13

Thomas Parry:
Tip for all Jasper Report users: adding flexibility for the where clause:
Create a parameter called WHERE_CLAUSE (type String) and assemble the where clause as you would like to see it. This can either be done in code on the servoy side and the whole where clause passed in or you can assemble the where clause string from other parameters and fields. ENSURE that the WHERE_CLAUSE is the LAST parameter in the list since Jasper process the list from top to bottom and the WHERE_CLAUSE depends on all other preceding parameters and fields.

The same logic can also be applied to the ORDER BY clause as well to further provide flexible reporting strategies.

Good idea - but then it chokes when compiling the report. What I really want is to use an IN statement as part of my WHERE clause (the rest I already have parameters for) - but I also have an ORDER BY and GROUP BY in the query, so it seems I can’t use a dynamic WHERE.

Do you know what the right syntax is (in the QUERY) to allow an array to be passed in (e.g. when using an IN clause)?

I tried the syntax according to the sample code:

/* Jasper Reports supports queries with IN operators through the following notation: X${IN,columnName,parameterName} like 'select * from customers where X$(IN,customer_id,pcustomeridlist)

  • When using this notation, the pcustomeridlist parameter needs to contain one or more values in the following way:
    */

But it doesn’t like it in the query in the report builder…

BTW: I have a fairly complex query with multiple tables - can I use table aliasing - or do I have to fully qualify the table name? FOR EXAMPLE:

I have this: SELECT a.field1, b.field2 FROM table1 a LEFT JOIN table2 b ON (a.key1 = b.key1)

Should I use this: SELECT table1.field1, table2.field2 FROM table1 LEFT JOIN table2 ON (table1.key1 = table2.key1)

14

Hi Bob!

I would try passing the whole query as a string and put $P!{query} in the report builder.

And yes you can use table aliases, as long as your DB supports it.

15

Bob,
See this old post on Jasper Forge:
http://jasperforge.org/plugins/espforum/view.php?group_id=102&forumid=103&topicid=23795

Basically check that you have defined the report parameter type as a “Collection” or “array” (Java types).

The $X{} applies to the WHERE clause rather than the relationship clause on a JOIN.

Does this help?

16

ptalbot:
I would try passing the whole query as a string and put $P!{query} in the report builder.
And yes you can use table aliases, as long as your DB supports it.

Hi Patrick - thanks very much for your response! The problem with passing the entire query - is that the report won’t compile unless you give it a default value. Then, when I give it a default value - it complains…

17

Thomas Parry:
http://jasperforge.org/plugins/espforum/view.php?group_id=102&forumid=103&topicid=23795

Basically check that you have defined the report parameter type as a “Collection” or “array” (Java types).

The $X{} applies to the WHERE clause rather than the relationship clause on a JOIN.

Hi Tom,

Thanks very much for the link. Yeah, I get that it goes in the where clause - but that assumes that you have only one thing in the where.

I guess it would help to have the actual query - so here it is:

SELECT a.model_num, a.customer_internal_id,
isnull(b.customer_name,'UNKNOWN CUSTOMER') as customer_name,
a.rep_internal_id, ifnull(c.rep_name,'UNASSIGNED REP') as rep_name,
ifnull(e.area_name,'UNASSIGNED AREA') as area_name,
ifnull(f.region_name,'UNASSIGNED REGION') as region_name,
ifnull(g.territory_name,'UNASSIGNED TERRITORY') as territory_name,
ifnull(d.user_name,'UNASSIGNED USER')as user_name,
a.user_internal_id,
sum(a.fc_ship_qty) as fc_ship_qty, avg(a.fc_ship_price) as fc_ship_price,
sum(a.fc_ship_amt) as fc_ship_amt, sum(m.actual_ship_qty) as actual_ship_qty,
sum(m.actual_ship_amt) as actual_ship_amt, sum(a.fc_book_amt) as fc_book_amt,
sum(a.fc_book_qty) as fc_book_qty, sum(m.actual_backlog_qty) as actual_backlog_qty
FROM fc_data a
LEFT JOIN customer b ON (a.customer_internal_id = b.customer_internal_id)
LEFT JOIN rep c ON (a.rep_internal_id = c.rep_internal_id)
LEFT JOIN users d ON (a.user_internal_id = d.user_internal_id)
LEFT JOIN area e ON (d.area_id = e.area_id)
LEFT JOIN region f ON (d.region_id = f.region_id)
LEFT JOIN territory g ON (d.territory_id = g.territory_id)
LEFT JOIN actuals m ON (a.model_num = m.model_num AND a.customer_internal_id = m.customer_internal_id AND a.fc_date = m.fc_date)
WHERE a.tenant_id = $P{tenant_id} and (a.fc_date BETWEEN $P{dateStart} and $P{dateEnd})
and a.user_internal_id IN ($P{user_internal_id}) and a.is_aop_record <> 1
GROUP BY a.model_num, a.customer_internal_id, a.rep_internal_id, a.user_internal_id, b.customer_name, 
c.rep_name, d.user_name, e.area_name, f.region_name, g.territory_name
ORDER BY e.area_name, f.region_name, g.territory_name, b.customer_name, a.model_num

The part that I’m trying to replace is the “a.user_internal_id IN ($P{user_internal_id})” part. I want to put multiple values into that - rather than a single value. If I try to just change that parameter to a collection (or array) - it barks out an error saying that data type is not valid in a query…

:D I really appreciate any help you could provide…

18

bobcusick:
The problem with passing the entire query - is that the report won’t compile unless you give it a default value. Then, when I give it a default value - it complains…

Is that a new behaviour? I used to do it with jasper 3.0 and never got any problem compiling!
I will give it a try to see if there is a workaround… That’s bad news.

bobcusick:
The part that I’m trying to replace is the “a.user_internal_id IN ($P{user_internal_id})” part. I want to put multiple values into that - rather than a single value.

Have you tried “a.user_internal_id IN ($P{user_internal_id})” and pass a string obtained by array.join(‘,’).
The “!” exclamation mark is important here, it basically says to jasper: don’t try to format that stuff, the placehoder must be replaced by the parameter “as-is”.

Alternatively, you could use a subquery, but that might not be very much adapted to your code.

19

ptalbot:
Have you tried “a.user_internal_id IN ($P{user_internal_id})” and pass a string obtained by array.join(‘,’).
The “!” exclamation mark is important here, it basically says to jasper: don’t try to format that stuff, the placehoder must be replaced by the parameter “as-is”.

Ah! I think that’s the part I’m missing! Where does the exclamation point go - after the $P and before the bracket (like $P!) ?

20

Sorry messed up with copy/paste again ;-)

It goes after the P:

... 
a.user_internal_id IN ($P!{user_internal_id})
...