Report queries and displays in one Maximo 7.5 environment but not another

<p>I'm experiencing some crazy behavior with a BIRT report that is being imported into two IBM Maximo 7.5 environments. It works in one w/o a problem but displays a blank screen in another. The environments are set up the same.</p>
<p> </p>
<p>It would seem that the blank screen would indicate that there is no data being returned in the environment that displays the blank screen but, in fact, when I load the SQL into TOAD there is data returned w/o issue.</p>
<p> </p>
<p>I'm using version: 3.7.1 Build id: M20110909-1335</p>
<p> </p>
<p>I've scoured the report looking for anything unusual but have been unsuccessful. I've checked and rechecked the SQL, the dataset and the settings in Maximo (amongst other things) and have found nothing. </p>
<p>Has anyone ever experienced anything similar? Just not sure why the report would function fine in one environment but not another?</p>
<p> </p>
<p>Thank you.</p>

Find more posts tagged with

Comments

<p>If you open the browsers debugging tools, do you see any errors occurring on the page?</p>

<p>Hi Jesse,</p>
<p>When you say 'open the browsers debugging tools' do you mean w/in IE or w/in Eclipse? Sorry, I'm new to BIRT and Eclipse (and Maximo).</p>
<p>If I use the debug option w/in Eclipse I get the attached results. There are some functions w/in the report that were custom written, I believe (I inherited this report so not 100% sure). But those functions are used in a number of other reports without issue so I think the errors in the attached file are irrelevant as I think they pertain to those functions.</p>
<p>Is there another way to debug?</p>
<p>Thanks.</p>

<p>>when I load the SQL into TOAD there is data returned w/o issue.</p>
<p> </p>
<p>So is this the sql created by the report or the sql you used in the report?</p>
<p> </p>
<p>Do the reports use parameters?</p>

<p>It is the SQL that is used in the report (I don't believe it was created by the report). And yes, the report has parameters. I've attached the report to make it easier.</p>

<p>Can you add a header and put your params["paramstring"] in a data element?</p>
<p> </p>
<p>Then see if you get the same sql when it is run within Maximo?</p>

<p>Well, that's interesting. The result is:</p>
<p> </p>
<p>select * from (select ASSET.LOCATION, ASSET.ASSETNUM, ASSET.DESCRIPTION, ASSET.INVENTORYCATEGORY, ASSET.CONDITIONINDEX, ASSET.REMAININGSERVICELIFE, TO_CHAR (CAST((FROM_TZ(CAST(asset.installdate AS TIMESTAMP),'US/Pacific') AT TIME ZONE 'America/Chicago') AS DATE), 'MM/DD/YY') as installdate, ASSET.QUANTITY, ASSET.UOM, ASSET.MASTERSYSTEM, ASSET.SYSTEM, ASSET.SUBSYSTEM, ASSET.PARENT, asset.assettype, ASSET.GROUPNAME, ASSET.SITEID, asset.REPLACECOST, ASSET.MANUFACTURER, asset.status, ASSET.SERIALNUM, ASSET.SPECIALREQ, ASSET.ADDLOCINFO, case when asset.assetnum in (select pm.assetnum from pm where status='ACTIVE' and pm.siteid=asset.siteid ) then 'Y' when asset.assetnum in (select assetnum from route_stop where route in (select route from PM where status='ACTIVE' and pm.siteid=asset.siteid)) then 'RY' else 'N' end as PMFlag from asset start with parent is null and ( ( ( asset.location like '%10401%' ) ) and ( asset.plustisconsist = 0 ) and ( asset.assettype in ( 'FACILITIES' ,'UTILITIES' ) )) and ASSET.LOCATION = UPPER('10401') connect by prior assetnum = parent ORDER SIBLINGS BY mastersystem, SYSTEM, subsystem )</p>
<p> </p>
<p>There is syntax in there that isn't included anywhere in the setup of the parameters but it's adding it. This whole piece is not part of the SQL w/in the report:</p>
<p> </p>
<p><strong>asset.location like '%10401%' ) ) and ( asset.plustisconsist = 0 ) and ( asset.assettype in ( 'FACILITIES' ,'UTILITIES' ) )) and </strong></p>
<p> </p>
<p>Any thoughts? Its almost as if it is hanging on to code that may have existed in the report previously but was subsequently removed. Can that happen?</p>

<p>Additional note, that extraneous code doesn't make a difference. It's bringing back rows when run in TOAD within the environment where the imported report returns a blank page.</p>
<p> </p>
<p>So, I'm back to square one. The report runs in one environment but when imported into another 7.5 environment it returns a blank page.</p>

<p>What browser are you using?</p>
<p>In most of the newer versions of browsers F12 will open the debugging tools which will have the console output for the browser. Do you see any errors in the browsers console output?</p>
<p> </p>
<p>From the Debug output you provided, I see the following errors occurring:</p>
<blockquote class="ipsBlockquote">
<p> </p>
<p>ReferenceError: "MXReportScriptContext" is not defined.</p>
<p>Caused by: org.mozilla.javascript.EcmaError: ReferenceError: "MXReportScriptContext" is not defined</p>
<p> </p>
<div>SEVERE: Engine exception</div>
<div>org.eclipse.birt.report.engine.api.EngineException: Failed to initialize emitter</div>
<div> </div>
<div>Caused by: java.io.FileNotFoundException: \navfac_ICAP_inv_validdate.rptdesign.html (Access is denied)</div>
</blockquote>
<p> </p>
<p>These may be the cause of the behavior you are describing.</p>

<p>Wow. You learn something new everyday! Thanks for the f12 hint. That's a pretty cool tool. Unfortunately, there are no errors, warnings or messages within the console output.</p>

<p>I think, the extra SQL is the 'where' parameter.</p>
<p> </p>
<p><span style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">Can you add another header and put your params["where"].value in a data element?</span></p>

<p>Hi Mica,</p>
<p> </p>
<p>So, here is the result of the params["where").value:</p>
<p> </p>
<div><strong>( ( ( asset.location like '%10401%' ) ) and ( asset.plustisconsist = 0 ) and ( asset.assettype in ( 'FACILITIES' ,'UTILITIES' ) )) and ASSET.LOCATION = UPPER('10401')</strong></div>
<div> </div>
<div>What's odd is that there is no reference to "asset.plustisconsist" w/in the report anywhere (unless I'm completely missing something). </div>
<div> </div>
<div>When I ran the report I only entered a LOCATION parameter of '10401' so only the last part of that Where clause is correct:</div>
<div> </div>
<div><strong>and ASSET.LOCATION = UPPER('10401')</strong></div>
<div> </div>
<div>The rest is being generated somehow and I'm not certain where it's coming from:</div>
<div> </div>
<div><strong>( ( ( asset.location like '%10401%' ) ) and ( asset.plustisconsist = 0 ) and ( asset.assettype in ( 'FACILITIES' ,'UTILITIES' ) )) </strong></div>
<div> </div>
<div>Any ideas?</div>
<div> </div>
<div>Thanks much.</div>

<p>I have no idea either. That is definitely the cause of your issue. I am hoping wwilliams has an idea.</p>

<p>Ok. Thanks, Mica. I'll sit tight and hopefully wwilliams will have an idea. Thanks again. Appreciate your time.</p>

<p>Can you provide a screenshot of the Maximo application BEFORE you ask to run the report? It might give us hints as to what is generating the where clause.</p>

<p>Screenshots attached. Thank you.</p>

<p>Is this all reports or just this one report.</p>

<p>Just this one and one other. All other reports are functioning fine in the environment where this report (and the one other) do not.</p>

<p>So I am going to ask the obvious. What is different with these 2? Can you post the design?</p>

<p>I posted the design earlier in this thread...but I'm attaching again as I've made some minor changes to it in an attempt to try to resolve the problem. I don't think there is anything different about these two (except, obviously, the SQL, the parameters and the layout). Same functions are being used in all reports pretty much across the board. The SQL in this report is fairly straight forward and simple so I'm just not sure. I mean, in reality, BIRT is a fairly simple product and the design is easy to read, so I'm not sure where the extraneous Where Clause syntax is coming from?? </p>

<p>Though I've looked at this report for so long I simply might be overlooking something incredibly simple that is right before my eyes.</p>

<p>What is this line? I do not think row[0]["timezone"] is defined. </p>
<p> </p>
<div>
<pre class="_prettyXprint">
+ " TO_CHAR (CAST((FROM_TZ(CAST(asset.installdate AS TIMESTAMP),'US/Pacific') AT TIME ZONE '" + rows[0]["timezone"] + "') AS DATE), 'MM/DD/YY') as installdate, "</pre>
</div>

<p>'timezone' is being selected on a subsequent query lower in the report. Is that not sufficient for definition? The query formats and returns the 'install date' info fine in the environment where the report runs to completion. </p>

<p>But quick (and maybe dumb) question...even if it weren't defined that wouldn't cause the extraneous Where Clause syntax, would it?</p>

<blockquote class="ipsBlockquote" data-author="onthegomiller" data-cid="133472" data-time="1423168013">
<div>
<p>But quick (and maybe dumb) question...even if it weren't defined that wouldn't cause the extraneous Where Clause syntax, would it?</p>
</div>
</blockquote>
<p>No, but it would cause the report to break.</p>

<p>Let me guess the 2 reports that do not work both have this <span style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">row[0]["timezone"]</span></p>

<p>BTW, where is the function getUserName defined?</p>
<pre class="_prettyXprint">
getUserName(this.getDataSource().getName());</pre>

<p>Just checked on that. Unfortunately, no, the other report that is also running to completion in one env and not the other does not use the row[0]["timezone"] syntax in the query. It doesn't use any "rows" syntax at all.</p>
<p> </p>
<p>In theory, though, if it were the row[0]["timezone"] syntax, shouldn't it fail in both environments?</p>

<blockquote class="ipsBlockquote" data-author="onthegomiller" data-cid="133477" data-time="1423169158">
<div>
<p><span style="font-size:12px;">In theory, though, if it were the row[0]["timezone"] syntax, shouldn't it fail in both environments?</span></p>
</div>
</blockquote>
<p> </p>
<p>True. My other thought is that the outer query (the one that retrieves the time zone) is failing. Hence my question on the function above.</p>

<p>The getUserName Function is the 3rd function from the top w/in the report.</p>
<p> </p>
<p>I apologize for taking up so much of your time! I was thinking this was going to be something simple that I was just overlooking. </p>

<p>I thought that the subsequent query (outer) might be failing as well, but I tested it several times thru TOAD in both environments and it retrieves data successfully so I concluded that I didn't think it was that after all.</p>