Report Question

 
Author
Message
SysAider
15
 
Been trying to fiqure this one out but have been unable so far. My boss wants a report that just show Opened SR's by administrator when the admin is the 'Submit user'. The 'Workload by Administrator' report is almost exactly what he wants but that report counts any modifications to a SR, He just wants to know which admins are opening the most SR's as the submit user.

Any help would be greatly appreciated. Thanks,

SysAid Wiz
1768
 
I have started to write a report for you that will do this, the basic query goes like this,

SELECT DISTINCT sysaid_user.user_name, sysaid_user.first_name, sysaid_user.last_name, COUNT(sysaid_user.user_name) AS Totals
FROM service_req_history INNER JOIN
sysaid_user ON service_req_history.submit_user = sysaid_user.user_name
WHERE (sysaid_user.administrator = 'y') AND (service_req_history.insert_time between $P{FromDate} and $P{ToDate} )
group by sysaid_user.user_name, sysaid_user.first_name, sysaid_user.last_name

If i get round to finishing the report, I will publish it here for you.
SysAider
15
 
Wow, Thanks for the help with this, I really appreciate it.
Not to sound like a dummy but where would I put this code? From within a new report?

Again, thanks for the help on this.
Elite SysAider
234
 
Thanks, always handy to see this kind of code. I learn a lot from this kind of examples.

Kind regards,
René
Take a look at my 3D creations and here is a little animation I made
SysAid Wiz
1768
 
ooops, messed that query up, here is the right one:-

SELECT DISTINCT sysaid_user.user_name, sysaid_user.first_name, sysaid_user.last_name,
COUNT(sysaid_user.user_name) AS Totals
FROM service_req INNER JOIN
sysaid_user ON service_req.submit_user = sysaid_user.user_name
WHERE (sysaid_user.administrator = 'y')AND (service_req.insert_time between $P{FromDate} and $P{ToDate} )
group by sysaid_user.user_name, sysaid_user.first_name, sysaid_user.last_name

SysAid Wiz
1768
 
I have created you a report that includes a pretty bar chart.

to use it download the jrxml file
and the 2 attached text files

go in to the manager portal

click on design reports

highlight sysaid at the top of the report structure and click on the new folder icon and give it a name like "my customised reports"
click on the group you just created (it seems to show the wrong icon at this point a piece of paper instead of a folder icon, ignore this).
now click the new report icon.
Select Empty Report
click save
Change the title to "Service Requests Opened by Administrator"
click ok.
click on the report Design tab
press the upload browse button
navigate to my jrxml file that you have just downloaded.
click on the prompt tab and paste in the contents of the prompt text file attached below
click on the script tab and paste in the contents of the script text file attached below
click apply and ok.

Let us know how you get on!
opened_SR_By_Administrator.jrxml
service requests opened by administrator - script tab.txt
service requests opened by administrator - prompt tab.txt
SysAider
15
 
Wow

I can't thank you enough for this. This is exactly what I was looking for. Out of curiousity, what did you use to create this report? iReport?

Let me know and thank again.
SysAid Wiz
1768
 
you are welcome,

Yes I used ireport to make the report.
SysAider
5
 
Hi!
I wanted to try your report, I created the report in manager Portal by uploading jrxml file and copying the script and promp content to appropriate tabs of new report.
After submit I received the message:

"Internal error while executing report (-5aaf79b1_11daa672713_-7fef)."

The report worked just fine in iReport 3.2.1.


SysAid Wiz
1768
 
what database are you running ?
also if you go on to the server, the proper error message is in the sysaid.log file can you paste the error line here.
Did you modify the report in ireports before uploading it as that may have broken it, only certain versions of ireports are supported.

This message was edited 1 time. Last update was at Nov. 19, 2008 03:19 PM

SysAider
5
 
We are running Oracle 9i
SysAider
5
 
Additional information:
1. as noted, we use Oracle 9i
2. yes, I changed the report using ireport 3.1.2. The error line is:
2008-11-19 17:35:43,160 [Thread-169] ERROR com.sysaid - Exception while trying to compile report code
net.sf.jasperreports.engine.JRException: Language "null" not supported by this report compiler.
Expecting "java" instead.
at net.sf.jasperreports.engine.design.JRJdtCompiler.checkLanguage(JRJdtCompiler.java:543)
at net.sf.jasperreports.engine.design.JRAbstractCompiler.compileReport(JRAbstractCompiler.java:137)
at net.sf.jasperreports.engine.design.JRDefaultCompiler.compileReport(JRDefaultCompiler.java:131)
at net.sf.jasperreports.engine.JasperCompileManager.compileReport(JasperCompileManager.java:211)
at net.sf.jasperreports.engine.JasperCompileManager.compileReport(JasperCompileManager.java:197)
at com.sysaid.report.e.a(Unknown Source)
at com.sysaid.report.GenericReport.a(Unknown Source)
at com.sysaid.report.GenericReport.doPost(Unknown Source)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
at jcifs.http.NtlmHttpFilter.doFilter(NtlmHttpFilter.java:11
at com.sysaid.server.LoginFilter.doFilter(Unknown Source)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:213)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2415)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:171)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:172)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:223)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:594)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:392)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:565)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:619)
at java.lang.Thread.run(Unknown Source)
2008-11-19 17:35:43,176 [Thread-169] ERROR com.sysaid - Error while executing report (-5aaf79b1_11daa672713_-7fef)
com.sysaid.report.d: Report compilation error or report not found.
at com.sysaid.report.GenericReport.a(Unknown Source)
at com.sysaid.report.GenericReport.doPost(Unknown Source)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
at jcifs.http.NtlmHttpFilter.doFilter(NtlmHttpFilter.java:11
at com.sysaid.server.LoginFilter.doFilter(Unknown Source)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:213)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2415)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:171)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:172)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:223)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:594)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:392)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:565)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:619)
at java.lang.Thread.run(Unknown Source)

3. If I use your original jrxml, the report works fine but there is no data ("no data found"). Which version of ireport is supported?
SysAid Wiz
1768
 
SysAider
5
 
Thank you very much.
SysAid Wiz
1768
 
Your welcome, if that is the issue and it is fixed, let us know what you have modified on the report, perhaps post it back to the forum if it may be useful to others.