<p>Hi All,</p>
<p> </p>
<p>I have an issue in the new 4.3.1 report designer and even in Maximo. When I use sub-query in the report the first binding is ok but then for all records the sub-data is displayed in the report.</p>
<p> </p>
<p>E.g. I have a MASTERPM report where I show all related JP sequence. When I run the report for more master PM the first record shows the proper jobplan sequences which jobplan sequences is displayed for all PM after that.</p>
<p> </p>
<p>I tried 2 solution here:</p>
<p> </p>
<p>1.</p>
<div><strong><span style="font-size:12px;">jpDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());</span></strong></div>
<div><strong><span style="font-size:12px;">jpDataSet.open();</span></strong></div>
<div> </div>
<div><strong><span style="font-size:12px;">var sqlText = new String();</span></strong></div>
<div> </div>
<div><strong><span style="font-size:12px;">// Add query to sqlText variable.</span></strong></div>
<div><strong><span style="font-size:12px;">sqlText2 = "select masterpmseq.jpnum, jobplan.description as jpdesc, masterpmseq.interval as intval "</span></strong></div>
<div><strong><span style="font-size:12px;">+ "from masterpmseq "</span></strong></div>
<div><strong><span style="font-size:12px;">+ "inner join jobplan on jobplan.jpnum = masterpmseq.jpnum "</span></strong></div>
<div><strong><span style="font-size:12px;">+ "where "</span></strong></div>
<div><strong><span style="font-size:12px;">+ "masterpmseq.masterpmnum = ?"</span></strong></div>
<div><strong><span style="font-size:12px;">;</span></strong></div>
<div> </div>
<div><strong><span style="font-size:12px;">jpDataSet.setQuery(sqlText);</span></strong></div>
<div><strong><span style="font-size:12px;">jpDataSet.setQueryParameterValue(1, rows[0]["mpmnum"]);</span></strong></div>
<div> </div>
<div> </div>
<div> </div>
<div>2.</div>
<div>
<div><strong><span style="font-size:12px;">jpDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());</span></strong></div>
<div><strong><span style="font-size:12px;">jpDataSet.open();</span></strong></div>
<div> </div>
<div><strong><span style="font-size:12px;">var sqlText = new String();</span></strong></div>
<div> </div>
<div><strong><span style="font-size:12px;">// Add query to sqlText variable.</span></strong></div>
<div><strong><span style="font-size:12px;">sqlText2 = "select masterpmseq.jpnum, jobplan.description as jpdesc, masterpmseq.interval as intval "</span></strong></div>
<div><strong><span style="font-size:12px;">+ "from masterpmseq "</span></strong></div>
<div><strong><span style="font-size:12px;">+ "inner join jobplan on jobplan.jpnum = masterpmseq.jpnum "</span></strong></div>
<div><strong><span style="font-size:12px;">+ "where "</span></strong></div>
<div><strong><span style="font-size:12px;">+ "masterpmseq.masterpmnum = '" + rows[0]["mpmnum"] + "'"</span></strong></div>
<div><strong><span style="font-size:12px;">;</span></strong></div>
<div> </div>
<div><strong><span style="font-size:12px;">jpDataSet.setQuery(sqlText);</span></strong></div>
</div>
<p> </p>
<p> </p>
<p>In the previous versions this worked but right now in the new version in both case in designer and Maximo as well the first sub-query result is bounded to the main record.</p>
<p> </p>
<p>Does anyone idea why?</p>
<p> </p>
<p>Kind regards</p>
<p>Peter</p>
