nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Report generation based on data set results?

MrJB12345

Looking for some help

... Thanks in advance.

I have a periodic report that I need to do but it's based on thresholds. If the threshold in a certain time period has not been exceeded we do not want to generate a report.

The threshold is determined to be exceeded if a SQL query to a MySQL database returns a result (using counts and group by ... having count > threshold...). When I tell BIRT to generate a report is there a way for that I can design my report such that if the query returns no results that I can easily just jump out and not generate a report?

Thank-you!

Find more posts tagged with

Comments

mwilliams

Hi MrJB12345,

Do you know what the date threshold is prior to running the report? Is it like the current date or 30 days ago or something specific like that? So that if the user enters a date beyond the threshold, you could check it and stop the report.

MrJB12345

Hi Michael,

It's not the date that contains the threshold. The user enters in the threshold as a report parameter and the threshold is checked against a database. So I don't know until the result has been returned by the data set.

But, a more trivial question. How would I exit just by checking the report parameter? Would I do it using the "Initialize" script for the report? If so, which instruction is it? Is it "exit", "return", "throw new exception"?

I am not familiar with the constructs and instructions that BIRT supports. I know it has support for javascript as a scripting engine but there is no exit in javascript. I come from a programmer background and need to know how to code it or add code or find code references (such as php.net for PHP, MSDN for M$, etc...) that would lead me to an answer so I can jump out and exit without generating a report. Does that make sense?

Thank-you.

mwilliams

MrJB12345,

You would validate a report parameter in the "validate" script on the report parameter. If you return true, the report runs. If you return false, an error message pops up and the report is not ran. I don't know if there is any other way to stop a report's generation. You could probably open a JDBC connection in the script and query the database with a count statement with the parameters the user enters. If that returns 0, then you could return false and the report would fail.

mwilliams

Another thing you could do is use a cascaded parameter. Set the sub parameter to required. If the first parameter returns no data, they won't be able to select a value from the associated cascaded parameter and would effectively keep you/the user from running the report. I can attach an example of something like this if I'm not explaining good enough.

MrJB12345

<blockquote class='ipsBlockquote' data-author="mwilliams">Another thing you could do is use a cascaded parameter. Set the sub parameter to required. If the first parameter returns no data, they won't be able to select a value from the associated cascaded parameter and would effectively keep you/the user from running the report. I can attach an example of something like this if I'm not explaining good enough.</blockquote>
 
Hi Michael, 
 
Thanks for the rapid responses. The help you have presented me is very helpful and insightful. From examples I've seen on cascading parameters this may not work for me since I don't want the user to be prompted. I need the report to be automated. Basically, if a result is returned than run. The result of the report is not based on the selection from a list of options. If any result is returned by the query then I know my threshold has been exceeded. I will look into your other suggestion. 
 
Thank-you!!

mwilliams

MrJB12345,

Let me know if anything I said sounds like it might work. If not, can you explain more about how you create the query you pass to the database? Are there no user parameters that you're checking? Are the parameters all internal? Whatever the case may be, the connection to your database with a count query based off the user's or the internal parameters may be the way to go. You could do this from your own jsp page before you ever run the report. This way you would never even call the report if the expected value was not returned.

Hope this helps.

MrJB12345

Hi Michael,

I've evaluated the options given. I really have 2 options out of your suggestions.

1) In the "Validate" script for the report parameter check if the threshold level passed in has been exceeded by first checking the parameter against the database.

2) Do the check in the Java code that invokes the BIRT report engine before calling BIRT to generate the report.

I think I'm going to choose 1). The Java code I've written is a generic report generator that reads in a configuration file and uses that data to generate reports based on the intervals and thresholds in the configuration file (report parameters also come from here). However, some reports have no thresholds and are just periodic reports (daily, weekly, etc...) I want to keep the logic as generic as possible so that's why I'm choosing option 1). It's sort of like cron in UNIX but made specifically to invoke BIRT.

BTW, you have been most helpful. Thank-you!!

mwilliams

MrJB12345,

No problem. Let us know whenever you have any questions!

MrJB12345

Well, not as successful as I was hoping. I found the link which has some sample code which I can modify for my report parameter validation script here: 
 
<a class='bbc_url' href='https://developer.mozilla.org/En/Server-Side_JavaScript/Walkthrough'>https://developer.mozilla.org/En/Server-Side_JavaScript/Walkthrough</a> 
 
But the problem is I can't even get the simplest of scripts to run because I fail at the first "importPackage" line. I've tried reading everywhere about classpaths and going into BIRT report viewer directory and others to put my libraries in either the birt/WEB-INF/class directory or the birt/scriptLib directories. But I don't know if that's relevant. Isn't that for my own java libraries. I even get that error for classes part of the Java RTE. 
 
At the viewer it will throw an exception even when I do something simple like importPackage ( Package.java.io ). And that's part of the sample code that's in the help tutorial. There's probably a simple fix that I'm unaware off but if anyone knows can they please let me know. Many thanks. 
 
Here is the exception btw. 
org.eclipse.birt.report.service.api.ReportServiceException: Parameter "RP_Threshold" script source validation error. at org.eclipse.birt.report.service.ReportEngineService.runAndRenderReport(ReportEngineService.java:961) at org.eclipse.birt.report.service.BirtViewerReportService.runAndRenderReport(BirtViewerReportService.java:933) at org.eclipse.birt.report.service.actionhandler.BirtGetPageAllActionHandler.__execute(BirtGetPageAllActionHandler.java:132) at org.eclipse.birt.report.service.actionhandler.AbstractBaseActionHandler.execute(AbstractBaseActionHandler.java:90) at org.eclipse.birt.report.soapengine.processor.AbstractBaseDocumentProcessor.__executeAction(AbstractBaseDocumentProcessor.java:47) at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.executeAction(AbstractBaseComponentProcessor.java:143) at org.eclipse.birt.report.soapengine.processor.BirtDocumentProcessor.handleGetPageAll(BirtDocumentProcessor.java:183) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.process(AbstractBaseComponentProcessor.java:112) at org.eclipse.birt.report.soapengine.endpoint.BirtSoapBindingImpl.getUpdatedObjects(BirtSoapBindingImpl.java:66) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:397) at org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.java:186) at org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:323) at org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:32) at org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:118) at org.apache.axis.SimpleChain.invoke(SimpleChain.java:83) at org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:454) at org.apache.axis.server.AxisServer.invoke(AxisServer.java:281) at org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:699) at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.doPost(BirtSoapMessageDispatcherServlet.java:225) at javax.servlet.http.HttpServlet.service(HttpServlet.java:616) at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327) at javax.servlet.http.HttpServlet.service(HttpServlet.java:689) at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.service(BirtSoapMessageDispatcherServlet.java:112) at org.eclipse.equinox.http.registry.internal.ServletManager$ServletWrapper.service(ServletManager.java:180) at org.eclipse.equinox.http.servlet.internal.ServletRegistration.handleRequest(ServletRegistration.java:90) at org.eclipse.equinox.http.servlet.internal.ProxyServlet.processAlias(ProxyServlet.java:111) at org.eclipse.equinox.http.servlet.internal.ProxyServlet.service(ProxyServlet.java:59) at javax.servlet.http.HttpServlet.service(HttpServlet.java:689) at org.eclipse.equinox.http.jetty.internal.HttpServerManager$InternalHttpServiceServlet.service(HttpServerManager.java:269) at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:428) at org.mortbay.jetty.servlet.ServletHandler.dispatch(ServletHandler.java:677) at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:568) at org.mortbay.http.HttpContext.handle(HttpContext.java:1530) at org.mortbay.http.HttpContext.handle(HttpContext.java:1482) at org.mortbay.http.HttpServer.service(HttpServer.java:909) at org.mortbay.http.HttpConnection.service(HttpConnection.java:820) at org.mortbay.http.HttpConnection.handleNext(HttpConnection.java:986) at org.mortbay.http.HttpConnection.handle(HttpConnection.java:837) at org.mortbay.http.SocketListener.handleConnection(SocketListener.java:245) at org.mortbay.util.ThreadedServer.handle(ThreadedServer.java:357) at org.mortbay.util.ThreadPool$PoolThread.run(ThreadPool.java:534) Caused by: org.eclipse.birt.report.engine.api.impl.ParameterValidationException: Parameter "DSP_Threshold" script source validation error. at org.eclipse.birt.report.engine.api.impl.EngineTask.validateScalarParameter(EngineTask.java:687) at org.eclipse.birt.report.engine.api.impl.EngineTask.access$0(EngineTask.java:660) at org.eclipse.birt.report.engine.api.impl.EngineTask$ParameterValidationVisitor.visitScalarParameter(EngineTask.java:604) at org.eclipse.birt.report.engine.api.impl.EngineTask$ParameterVisitor.visit(EngineTask.java:1076) at org.eclipse.birt.report.engine.api.impl.EngineTask.doValidateParameters(EngineTask.java:588) at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.doRun(RunAndRenderTask.java:84) at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.run(RunAndRenderTask.java:72) at org.eclipse.birt.report.service.ReportEngineService.runAndRenderReport(ReportEngineService.java:954) ... 48 more

MrJB12345

The only issue is when it's run from the report viewer in Eclipse. I can properly set it up to run in any other context by pointing to the location of the mysql jar in the classpath.

Thanks for the help.

mwilliams

MrJB12345,

So, it's working the way you need it to now? Or is there still an issue?

MrJB12345

Hi Michael,

It works but just not in the Eclipse Report Viewer. When I run it in any other context where I set my own class path it works fine. My Eclipse is probably not setup correctly since I compile and run everything off-target. Since I can get it to run on-target I'm not overly concerned.

Thanks again!

MrJB

mwilliams

MrJB,

Glad to hear it at least works in the context that you need it.