Subreport query fails to run after first non-null row returned for Maximo 7.5

I have a report with a main table bound to the main data set, and a sub report in a table in the detail row of the main table. The sub report is bound to a separate data set that references the main data set in the manner prescribed in the document "V75 BIRT 371 Report Development Guide - Rev3.pdf" by IBM / Tivoli. The report data source object is scripted per Maximo 7.x requirements. The main data set query looks like this: [color=#0000ff;]select wonum, description, reportdate, status...[/color][color=#0000ff;]from workorder[/color][color=#0000ff;]where parent is null and istask = 0 and ... {other criteria}[/color] [color=#0000ff;]The sub report data set query includes linking fields (foreign keys) as follows so that it will execute once for every row returned by the main data set query. Note that more than one row may be returned for each row returned by the main query. Here's the sub query:[/color][color=#0000ff;] [/color][color=#0000ff;]planLabSQL = "select wonum, laborcode as planlabcraft, (laborhrs * nvl(quantity,1)) as planLabHrs"[/color][color=#0000ff;]+ " "[/color][color=#0000ff;]+ " from wplabor "[/color][color=#0000ff;]+ " "[/color][color=#0000ff;]+ " where wonum in "[/color][color=#0000ff;]+ " ("[/color][color=#0000ff;]+ " select wonum "[/color][color=#0000ff;]+ " from workorder "[/color][color=#0000ff;]+ " start with wonum = '" + rows[0]["wonum"] + "'"[/color][color=#0000ff;]+ " connect by parent = prior wonum"[/color][color=#0000ff;]+ " )"[/color][color=#0000ff;]+ " order by to_number(wonum)"[/color][color=#0000ff;];[/color]The report executes with no errors in BIRT or in the DEBUG log file generated at run time. The main data set query executes and returns a row, then the sub query executes. I've attached the BIRT 3.7.1 report design file, debug log and sample output file. No sub query rows are returned for some main query rows. [color=#ff0000;]My problem is that for some reason, the sub query is never executed again after the very first main query row returned with a matching sub-query row. I then restricted the query to return (3) specific records from the main query. The first record from the main query returns no matching sub query rows. The second main query record DOES return a row and the correct sub-query result is displayed. However, the third and final main query row matches no sub query rows, yet the result for the second row is displayed. I found that all subsequent rows after the first successful row return from the sub query show the same data as the first row returned. The debug log file shows that an "open" action is called, but noSQL statement is generated based on the main query foreign key value. Oddly, if I force a syntax error in the sub query Fetch code section and run the report, the debug file shows all subqueries are correctly generated. However, no rows will be fetched, obviously, because of the syntax error.[/color] [color=#ff0000;]Anyway, any help would be greatly appreciated. I am stumped![/color]

Find more posts tagged with

Comments

wwilliams

It looks to me that you only have the "maintable" in your report. Take a look at woprint.rptdesign and you'll see the subreports have their own tables within the maintable.-W

rdpeterson

No, report has second table bound to a different data set. Second table is titled 'planLaborTable'. See attached hierarchical listing.

paulk

BIRT 3.7.1 (or at least IBM's implementation of it) has a bug with scripted datasets where queries only return the first result when a nested table is encountered. A workaround is to apply a grouping on the parent table on your unique column. If you have a sub-sub-table, the table and sub-table would both need a grouping.

rdpeterson

Thank you very much for the quick and helpful response! I encountered this problem with other reports as well. It's good to hear it's a confirmed bug. I will try your workaround. In the meantime, I found that the same unmodified report executable works correctly when published in Maximo. The problem only seems to appear in BIRT designer when the report is run. I discovered this with a previous report.

smellbop

Thank you +1