Discussions
Categories
Groups
Community Home
Categories
INTERNAL ENABLEMENT
POPULAR
THRUST SERVICES & TOOLS
CLOUD EDITIONS
Quick Links
MY LINKS
HELPFUL TIPS
Back to website
Home
Intelligence (Analytics)
User defined parameters used with default WHERE parameter
rdpeterson
<div>I created a report in the ASSET module that uses parameter info passed in via pre-selected asset records and manually entered by user on request page. Users need to be able to run this report for all asset at a cost center (essentially a site-level location) in our system, or for individually selected assets at that cost center. I have the report set up so that if the user goes to the Assets module, selects individual asset records for a cost center, then runs the report with required parameter to enter the cost center for that cost center, the report will run only for the selected equipment. The report ran correctly in BIRT and Maximo v7.5.0.3 until I made the cost center parameter select from an attribute-based lookup instead of a manually typed in value. After that, the report only runs without an error if asset records are pre-selected before running the report. Otherwise, an error is returned and the report query never runs. Here's a portion of the error message for one of the data objects I display in the report header to show the user entered parameters. I did not drag-and-drop the parameter from the Data Explorer in BIRT to the report as warned about in the BIRT/Maximo report development guide. I instead instantiated an empty data object by drag-and-drop from Pallette, then set it's value to the parameter with expression in the form params["{param name}"].value. If I remove the display of all these parameter-display data objects from the report entirely, the errors are eliminated, but the report published in Maximo still doesn't run the query. Again, the query works when run in BIRT. </div><div> </div><div>I'd appreciate any comments or help on this. Thanks.</div><div> </div><div>Data (id = 1178):</div><div>- Invalid expression to retrieve value in presentation. (Element ID:1178)</div><div>data.engine.InValidExpr ( 1 time(s) )</div><div>detail : org.eclipse.birt.report.engine.api.EngineException: Invalid expression to retrieve value in presentation. (Element ID:1178)</div><div>at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1214)</div><div>at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1193)</div><div>at org.eclipse.birt.report.engine.internal.document.v4.DataItemExecutor.doExecute(DataItemExecutor.java:97)</div><div>at org.eclipse.birt.report.engine.internal.document.v4.ReportItemExecutor.execute(ReportItemExecutor.java:294)</div><div>at org.eclipse.birt.report.engine.internal.document.v4.ContainerExecutor.prepareChildExecutor(ContainerExecutor.java:226)</div><div>at org.eclipse.birt.report.engine.internal.document.v4.ContainerExecutor.hasNextChild(ContainerExecutor.java:110)</div><div>at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportItemExecutor.hasNextChild(WrappedReportItemExecutor.java:86)</div><div>at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportItemExecutor.hasNextChild(WrappedReportItemExecutor.java:86)</div><div>at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.execute(HTMLAbstractLM.java:463)</div><div>at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.execute(HTMLAbstractLM.java:476)</div><div>at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.execute(HTMLAbstractLM.java:476)</div><div>at org.eclipse.birt.report.engine.layout.html.HTMLListingBandLM.intializeHeaderContent(HTMLListingBandLM.java:98)</div><div>at org.eclipse.birt.report.engine.layout.html.HTMLListingBandLM.initialize(HTMLListingBandLM.java:50)</div><div>at org.eclipse.birt.report.engine.layout.html.HTMLTableBandLM.initialize(HTMLTableBandLM.java:44)</div><div>at org.eclipse.birt.report.engine.layout.html.HTMLLayoutManagerFactory.createLayoutManager(HTMLLayoutManagerFactory.java:40)</div><div>at org.eclipse.birt.report.engine.layout.html.HTMLReportLayoutEngine.createLayoutManager(HTMLReportLayoutEngine.java:138)</div><div>at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:48)</div><div>at org.eclipse.birt.report.engine.layout.html.HTMLStackingLM.layoutChildren(HTMLStackingLM.java:26)</div><div>at org.eclipse.birt.report.engine.layout.html.HTMLTableLM.layoutChildren(HTMLTableLM.java:132)</div><div>at org.eclipse.birt.report.engine.layout.html.HTMLAbstractLM.layout(HTMLAbstractLM.java:140)</div><div>at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:70)</div><div>at org.eclipse.birt.report.engine.layout.html.HTMLPageLM.layout(HTMLPageLM.java:92)</div><div>at org.eclipse.birt.report.engine.layout.html.HTMLReportLayoutEngine.layout(HTMLReportLayoutEngine.java:100)</div><div>at org.eclipse.birt.report.engine.api.impl.RenderTask$PageRangeRender.render(RenderTask.java:681)</div><div>at org.eclipse.birt.report.engine.api.impl.RenderTask.render(RenderTask.java:294)</div><div>at org.eclipse.birt.report.service.ReportEngineService.renderReport(ReportEngineService.java:1585)</div><div>at org.eclipse.birt.report.service.BirtViewerReportService.getPage(BirtViewerReportService.java:204)</div><div>at org.eclipse.birt.report.service.actionhandler.AbstractGetPageActionHandler.doExecution(AbstractGetPageActionHandler.java:238)</div><div>at org.eclipse.birt.report.service.actionhandler.AbstractGetPageActionHandler.__execute(AbstractGetPageActionHandler.java:105)</div><div>at org.eclipse.birt.report.service.actionhandler.AbstractBaseActionHandler.execute(AbstractBaseActionHandler.java:90)</div><div>at org.eclipse.birt.report.soapengine.processor.AbstractBaseDocumentProcessor.__executeAction(AbstractBaseDocumentProcessor.java:47)</div><div>at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.executeAction(AbstractBaseComponentProcessor.java:143)</div><div>at org.eclipse.birt.report.soapengine.processor.BirtDocumentProcessor.handleGetPage(BirtDocumentProcessor.java:87)</div><div>at sun.reflect.GeneratedMethodAccessor271.invoke(Unknown Source)</div><div>at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)</div><div>at java.lang.reflect.Method.invoke(Method.java:611)</div><div>at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.process(AbstractBaseComponentProcessor.java:112)</div><div>at org.eclipse.birt.report.soapengine.endpoint.BirtSoapBindingImpl.getUpdatedObjects(BirtSoapBindingImpl.java:66)</div><div>at sun.reflect.GeneratedMethodAccessor270.invoke(Unknown Source)</div><div>at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)</div><div>at java.lang.reflect.Method.invoke(Method.java:611)</div><div>at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:397)</div><div>at org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.java:186)</div><div>at org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:323)</div><div>at org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:32)</div><div>at org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:118)</div><div>at org.apache.axis.SimpleChain.invoke(SimpleChain.java:83)</div><div>at org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:453)</div><div>at org.apache.axis.server.AxisServer.invoke(AxisServer.java:281)</div><div>at org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:699)</div><div>at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.doPost(BirtSoapMessageDispatcherServlet.java:265)</div><div>at javax.servlet.http.HttpServlet.service(HttpServlet.java:738)</div><div>at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327)</div><div>at javax.servlet.http.HttpServlet.service(HttpServlet.java:831)</div><div>at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.service(BirtSoapMessageDispatcherServlet.java:120)</div><div>at org.eclipse.equinox.http.servlet.internal.ServletRegistration.service(ServletRegistration.java:61)</div><div>at org.eclipse.equinox.http.servlet.internal.ProxyServlet.processAlias(ProxyServlet.java:126)</div><div>at org.eclipse.equinox.http.servlet.internal.ProxyServlet.service(ProxyServlet.java:60)</div><div>at javax.servlet.http.HttpServlet.service(HttpServlet.java:831)</div><div>at com.ibm.tivoli.maximo.report.birt.bridge.launcher.BridgeServlet.service(BridgeServlet.java:106)</div><div>at javax.servlet.http.HttpServlet.service(HttpServlet.java:831)</div><div>at com.ibm.ws.cache.servlet.ServletWrapper.serviceProxied(ServletWrapper.java:307)</div><div>at com.ibm.ws.cache.servlet.CacheHook.handleFragment(CacheHook.java:579)</div><div>at com.ibm.ws.cache.servlet.CacheHook.handleServlet(CacheHook.java:250)</div><div>at com.ibm.ws.cache.servlet.ServletWrapper.service(ServletWrapper.java:259)</div><div>at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1657)</div><div>at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1597)</div><div>at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:104)</div><div>at com.ibm.ws.webcontainer.filter.WebAppFilterChain._doFilter(WebAppFilterChain.java:77)</div><div>at com.ibm.ws.webcontainer.filter.WebAppFilterManager.doFilter(WebAppFilterManager.java:908)</div><div>at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:934)</div><div>at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:502)</div><div>at com.ibm.ws.webcontainer.servlet.ServletWrapperImpl.handleRequest(ServletWrapperImpl.java:179)</div><div>at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:353)</div><div>at com.ibm.tivoli.maximo.report.birt.servlet.ReportRequestProcessServlet.processReportRequest(ReportRequestProcessServlet.java:682)</div><div>at com.ibm.tivoli.maximo.report.birt.servlet.ReportRequestProcessServlet.doPost(ReportRequestProcessServlet.java:126)</div><div>at javax.servlet.http.HttpServlet.service(HttpServlet.java:738)</div><div>at javax.servlet.http.HttpServlet.service(HttpServlet.java:831)</div><div>at com.ibm.ws.cache.servlet.ServletWrapper.serviceProxied(ServletWrapper.java:307)</div><div>at com.ibm.ws.cache.servlet.CacheHook.handleFragment(CacheHook.java:579)</div><div>at com.ibm.ws.cache.servlet.CacheHook.handleServlet(CacheHook.java:250)</div><div>at com.ibm.ws.cache.servlet.ServletWrapper.service(ServletWrapper.java:259)</div><div>at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1657)</div><div>at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1597)</div><div>at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:131)</div><div>at psdi.webclient.system.filter.HttpCrossSiteScriptingSecurity.doFilter(HttpCrossSiteScriptingSecurity.java:66)</div><div>at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:188)</div><div>at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:116)</div><div>at com.ibm.ws.webcontainer.filter.WebAppFilterChain._doFilter(WebAppFilterChain.java:77)</div><div>at com.ibm.ws.webcontainer.filter.WebAppFilterManager.doFilter(WebAppFilterManager.java:908)</div><div>at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:934)</div><div>at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:502)</div><div>at com.ibm.ws.webcontainer.servlet.ServletWrapperImpl.handleRequest(ServletWrapperImpl.java:179)</div><div>at com.ibm.ws.webcontainer.servlet.CacheServletWrapper.handleRequest(CacheServletWrapper.java:91)</div><div>at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:864)</div><div>at com.ibm.ws.webcontainer.WSWebContainer.handleRequest(WSWebContainer.java:1583)</div><div>at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:186)</div><div>at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:445)</div><div>at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewRequest(HttpInboundLink.java:504)</div><div>at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.processRequest(HttpInboundLink.java:301)</div><div>at com.ibm.ws.http.channel.inbound.impl.HttpICLReadCallback.complete(HttpICLReadCallback.java:83)</div><div>at com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted(AioReadCompletionListener.java:165)</div><div>at com.ibm.io.async.AbstractAsyncFuture.invokeCallback(AbstractAsyncFuture.java:217)</div><div>at com.ibm.io.async.AsyncChannelFuture.fireCompletionActions(AsyncChannelFuture.java:161)</div><div>at com.ibm.io.async.AsyncFuture.completed(AsyncFuture.java:138)</div><div>at com.ibm.io.async.ResultHandler.complete(ResultHandler.java:204)</div><div>at com.ibm.io.async.ResultHandler.runEventProcessingLoop(ResultHandler.java:775)</div><div>at com.ibm.io.async.ResultHandler$2.run(ResultHandler.java:905)</div><div>at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1604)</div><div>Caused by: org.eclipse.birt.data.engine.core.DataException: Invalid expression to retrieve value in presentation.</div><div>at org.eclipse.birt.data.engine.impl.document.util.ExprResultSet.getValue(ExprResultSet.java:195)</div><div>at org.eclipse.birt.data.engine.impl.document.ResultIterator.getValue(ResultIterator.java:167)</div><div>at org.eclipse.birt.report.engine.data.dte.QueryResultSet.getValue(QueryResultSet.java:403)</div><div>at org.eclipse.birt.report.engine.internal.document.v4.DataItemExecutor.doExecute(DataItemExecutor.java:86)</div><div>... 106 more</div>
Find more posts tagged with
Comments
wwilliams
<p>Can you post the report?</p>
rdpeterson
<p>See attached zip file containing the design and config files.</p>
wwilliams
<p>"The report ran correctly in BIRT and Maximo v7.5.0.3 until I made the cost center parameter select from an attribute-based lookup instead of a manually typed in value"</p><p> </p><p>I think the issue is you have aliased the locations table and Maximo using the attribute based look-up is using the full table name "locations".</p><p> </p><p>What does the log tell you?</p>
rdpeterson
<p>Thanks. I made the following changes:</p><ul class='bbcol decimal'><li>Removed aliasing of LOCATIONS table in report query as you recommended. I've seen problems with lookups with aliasing before. I then re-published the report and got the same error. </li><li>I then changed the attribute of the cost center parameter in the report in Maximo Report Administration to LOCATION.COSTCENTER that references the LOCATIONS table in Maximo instead of the ASSET table. The report is published under ASSET. </li><li>I re-published report again and now it seems to work correctly, i.e., no error when only the required cost center parameter is entered with no pre-selected asset records passed into the built-in WHERE parameter.</li></ul><p>I spent a lot of time trying to figure this out. Thanks for your help!</p><p> </p><p>[color=#ff0000;]*** UPDATE: I spoke too soon. The cost center dynamic value list selector now shows invalid binding error when invoked in Maximo. Perhaps the problem is that the actual Maximo table name LOCATIONS, not LOCATION as reflected in the list of attributes for related tables in the ASSET module.[/color]</p>
wwilliams
<p>The log and or printing the value of the sqltext should give you the information you need.</p>
rdpeterson
<p>I tried displaying the generated SQL using a global variable with value set at the end of the query Open statement block. In the case of the aforementioned error, however, the code execution stops before it's able to generate the SQL statement so it's never displayed.</p><p> </p><p>I've referred to the instructions on how to set up logging when report is run in Maximo in the BIRT/Maximo report development guide, but I'm still kind of vague on how to do it, or where I can retrieve the log entries. Also, would this facility render anything more detailed than the detailed error message I provided at the beginning of this subject thread? I can't decipher it.</p>
wwilliams
<p>What app is this registered under?</p>
rdpeterson
<p>I already replied in this thread, I thought. Here's my response:</p><p> </p><p>I tried the first part of your recommended solution, but the report still errors out before the SQL text is generated, even when generated in the initialize statement. I also added the Maximo debugger statements but I'm waiting for our systems folks to give me access to the log file generated.</p><p> </p><p>The report is registered in Maximo 7.5.03. Here's the system information provided by Maximo:</p><p> </p><div style="margin:0px;">App Server IBM WebSphere Application Server 7.0.0.15 Version IBM Maximo Asset Management 7.5.0.3 Build 20120713-1120 DB Build V7503-01
Tivoli's process automation engine 7.5.0.3 Build 20120713-1120 DB Build V7503-157 Server OS Linux 2.6.32-220.17.1.el6.x86_64 Server DB Oracle 11.2 (Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options) </div><div style="margin:0px;"><div style="margin:0px;">© Copyright IBM Corp. 2012</div><div style="margin:0px;"> </div><div style="margin:0px;">I think I should submit this to IBM/Tivoli as well.</div></div>
wwilliams
<p>I just noticed your header with the v_sqlText is hidden, at least in the file I downloaded, so that may be the reason you don't see anything.</p>
rdpeterson
<p>Yes, it was hidden but I fixed that on my latest run test, and it still didn't display. The error must be occurring before the initialization code fires.</p>
andrej
<p>Try this.</p>
rdpeterson
<p>Yes, this seems to work! I performed test runs in Maximo 7.5 with and without pre-selected data for the WHERE clause in conjunction with the Cost Center parameter. I had no idea global variables could be defined that way. I thought they had to be created as separate "objects" under "Variables" in the report structure. Do all variables defined in the report initialize script block become global simply by virtue of their placement there?</p><p> </p><p>I see you also added this to the initialize script block. I had previously tried to remove all table aliasing from the query itself, but that action, at least by itself, didn't solve the problem. It seems the solution was a combination of the statements below and the passing of the other user parameters directly to those global variables you created there.</p><p> </p><p>// shorten table names in parameters</p><p>params["where"] = params["where"].replace(/measurepoint./g, "m.");</p><p> </p><p>Brilliant! Thanks for the hard work!</p><p>params["where"] = params["where"].replace(/locations./g, "l.");</p>
