mperrone

Yeah it was a lot of code. Duh... I think I mixed up Transact-SQL with MySQL when I tried writing out the DATE_ADD statement... I tried it with and without the space, but I had the arguments listed out as (YEAR, -1, '2009-01-01'). Don't know how I missed that. Thanks Matt, much appreciated. - Mike

Thank you Christina!! That does work! - Mike

Hey Matt - Oops - yeah, we do use MySQL. And I had tried using DATE_ADD with the underscore; that syntax didn't work either. But it is quite helpful to know that when the SQL syntax doesn't work that it doesn't have anything to do with iReport... I had always wondered if our database and iReport were connected in such a way that iReport could somehow effect the way a SQL statement executes. (Our database adminstrator is a consultant for whom we're trying to keep hours down, and I'm an accountant who's trying to figure out how to generate some reports somewhat inexpensively... thus the gaping holes in my knowledge foundation.) Ideally I would prefer to handle all of this date manipulation through SQL than in the report itself, but I'll take whatever works. I just imported the Java Commons Lang package that you referenced and experimented with the expression for my date object, but got a "DateUtils cannot be resolved" error message. At some point I will look through the jrxml file for your sample report to see if I can figure out what the problem is... for now the java expression that Christina posted below does work for me, so I'm going to run with that in the interest of getting this done ASAP. Thank you for your help! - Mike

Hello - I am trying to design a report with multiple run dates (i.e. a series of subreports that use date parameters that offset from the prompted date parameters by an integer value of 1 - 4 years). I first tried to use the DATEADD function in my SQL statement but iReport tells me the function doesn't exist. So when that didn't work I tried to set up each offset date as a variable. I figured out that using groovy I could simply create a variable with the expression: $P{rundate} - 365 but that won't work thanks to leap years. Any idea how I can offset a date using x number of years? Thanks, - Mike Perrone

Is there anyone out there who can help me? I'm still stuck on this. Thanks, - Mike

Hi Sherman - Thanks for your reply. I'm still stuck though. ROLE_ADMINISTRATOR appears to have default access to every folder in the repository. So no matter how I restrictively I set the permissions for ROLE_DEVELOPER, when I assign a user both roles, the admin role trumps the dev role and that user can see all folders. (Incidentally, I also experimented with setting up ROLE_LIMITED_ADMIN and giving this role "Administer" permissions on everything in the repository, just so I could then try to scale it back. But it looks like only ROLE_ADMINISTRATOR can edit resources. Which leads me to wonder then why the "Administer" permission level exists if it can't do anything more than "Write + Delete + Read"...) Thanks, -Mike

Hi... I hope someone can help me here... I want to create a JS role called "developer" that will allow users with that role to do everything that jasperadmin can do, except access two specific folders in the repository. So I've set this role up with Permission Level = Administer on the root folder and all subfolders, except for the two subfolders I don't want this role to access. These subfolders have Permission Level = No Access. But when I log on as a user with this role, I can't edit any of my resources - even those in the subfolders with Permission Level = Administer. Is there something I'm missing? Thanks, - Mike Perrone

Thanks Matt - I ended up finding someone who figured it out - I hadn't assigned ROLE_USER read-only permissions on my datatypes. Duh. Thanks for taking the time too look at this though! - Mike

Can someone help me please? I'm quite stuck... Thanks, - Mike

Hello - I hope someone can help me with this... I'm trying to set up a report in JS for which ROLE_USER has read-only permission. I have done this many times before without a problem, although this particular report is the first report I have developed that uses Input Controls that are other than single value inputs. I have a single-select query and two multi-select queries that I am using as Input Controls. All three queries are saved in my repository, and ROLE_USER has read-only permissions for all of them (as well as the folder where they reside). When any user with ROLE_USER logs on, they can run any report they're supposed to have access to, but when they try to run this report, they get a message saying: org.acegisecurity.AccessDeniedException: Access is denied. The full text of the error message is below. Thank you, - Mike Perrone Code:org.acegisecurity.AccessDeniedException: Access is denied at org.acegisecurity.vote.AffirmativeBased.decide(AffirmativeBased.java:68) at org.acegisecurity.intercept.AbstractSecurityInterceptor.beforeInvocation(AbstractSecurityInterceptor.java:275) at org.acegisecurity.intercept.method.aopalliance.MethodSecurityInterceptor.invoke(MethodSecurityInterceptor.java:63) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:107) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:107) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:210) at $Proxy1.getResource(Unknown Source) at com.jaspersoft.jasperserver.war.action.ReportParametersAction.createWrappers(ReportParametersAction.java:447) at com.jaspersoft.jasperserver.war.action.ReportParametersAction.createWrappers(ReportParametersAction.java:414) at com.jaspersoft.jasperserver.war.action.ReportParametersAction.createWrappers(ReportParametersAction.java:123) at com.jaspersoft.jasperserver.war.action.ViewReportAction.checkForParams(ViewReportAction.java:148) at sun.reflect.GeneratedMethodAccessor414.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:585) at org.springframework.webflow.util.DispatchMethodInvoker.invoke(DispatchMethodInvoker.java:103) at org.springframework.webflow.action.MultiAction.doExecute(MultiAction.java:136) at org.springframework.webflow.action.AbstractAction.execute(AbstractAction.java:203) at org.springframework.webflow.engine.AnnotatedAction.execute(AnnotatedAction.java:142) at org.springframework.webflow.engine.ActionExecutor.execute(ActionExecutor.java:61) at org.springframework.webflow.engine.ActionState.doEnter(ActionState.java:180) at org.springframework.webflow.engine.State.enter(State.java:200) at org.springframework.webflow.engine.Flow.start(Flow.java:557) at org.springframework.webflow.engine.impl.RequestControlContextImpl.start(RequestControlContextImpl.java:195) at org.springframework.webflow.engine.impl.FlowExecutionImpl.start(FlowExecutionImpl.java:189) at org.springframework.webflow.executor.FlowExecutorImpl.launch(FlowExecutorImpl.java:206) at sun.reflect.GeneratedMethodAccessor237.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:585) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:281) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:187) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:154) at org.acegisecurity.intercept.method.aopalliance.MethodSecurityInterceptor.invoke(MethodSecurityInterceptor.java:66) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:210) at $Proxy13.launch(Unknown Source) at org.springframework.webflow.executor.support.FlowRequestHandler.handleFlowRequest(FlowRequestHandler.java:131) at org.springframework.webflow.executor.mvc.FlowController.handleRequestInternal(FlowController.java:170) at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153) at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:45) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:820) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:755) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:396) at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:350) at javax.servlet.http.HttpServlet.service(HttpServlet.java:689) at javax.servlet.http.HttpServlet.service(HttpServlet.java:802) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173) at com.jaspersoft.jasperserver.war.common.UploadMultipartFilter.doFilter(UploadMultipartFilter.java:86) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:264) at org.acegisecurity.ui.switchuser.SwitchUserProcessingFilter.doFilter(SwitchUserProcessingFilter.java:335) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274) at org.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:107) at org.acegisecurity.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:72) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274) at org.acegisecurity.ui.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:110) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274) at org.acegisecurity.providers.anonymous.AnonymousProcessingFilter.doFilter(AnonymousProcessingFilter.java:125) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274) at com.jaspersoft.jasperserver.api.metadata.user.service.impl.MetadataAuthenticationProcessingFilter.doFilter(MetadataAuthenticationProcessingFilter.java:141) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274) at com.jaspersoft.jasperserver.war.util.RequestParameterAuthenticationFilter.doFilter(RequestParameterAuthenticationFilter.java:97) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274) at org.acegisecurity.ui.basicauth.BasicProcessingFilter.doFilter(BasicProcessingFilter.java:181) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274) at com.jaspersoft.jasperserver.war.UserPreferencesFilter.doFilter(UserPreferencesFilter.java:165) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274) at org.acegisecurity.ui.AbstractProcessingFilter.doFilter(AbstractProcessingFilter.java:217) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274) at com.jaspersoft.jasperserver.war.UserPreferencesFilter.doFilter(UserPreferencesFilter.java:165) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274) at org.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilter(HttpSessionContextIntegrationFilter.java:191) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274) at org.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.java:148) at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:90) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173) at com.jaspersoft.jasperserver.war.util.CharacterEncodingFilter.doFilter(CharacterEncodingFilter.java:70) at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:138) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869) at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664) at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527) at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80) at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684) at java.lang.Thread.run(Thread.java:595)

Figured out a workaround... I put the subreport in the page footer and offset it vertically so that it essentially appears in the page header. Thanks for looking at this though... - Mike

Hello... I want to place a subreport in the page header of my master report, but when I do, the subreport returns null (when I move the subreport to the detail or summary bands, the subreport returns its data properly). Any idea why this is happening? Thanks, - Mike Perrone

Woops... can't belive I didn't realize I was using the + in a SQL statement. Thank you!!!

Tried that... doesn't seem to work. For one, iReport doesn't boldface CONCAT in the report query box like it does the other SQL statements it recognizes, and I know we're using MySQL 5.0. Nonetheless, I can run CONCAT statements even though the word doesn't show up boldfaced. But when I run a simple statement CONCAT("A" + "B") AS String, I get zeros in that field when I run the report. I think I need to get into MySQL somehow and try running some queries directly in there to try to narrow down where the problem lies. Thanks... - Mike

One last question... hopefully this will be the last stumbling block on this particular report. This pertains to my SQL statement; we use a JDBC connection to MySQL. I want to convert a numeric field into a string, and concatenate an "A" on the end of the field. The conversion to a string is not a problem; I used: convert (invoicedetails.`dinvoicenumber`, char(6)) AS StringInvoiceNumber But when I go to concatenate, I have a problem. iReport (or MySQL; not sure how exactly the running of the query is handled) doesn't recognize the CONCAT statement. This expression appeared to concatenate properly: convert (invoicedetails.`dinvoicenumber`, char(6)) + "A" AS StringInvoiceNumber , but it changesStringInvoiceNumber back into a double. So then I tried this: convert (convert (invoicedetails.`dinvoicenumber`, char(6)) + "A", char(7)) AS StringInvoiceNumber This expression evaluated properly, and StringInvoiceNumber remained a string. But when I run the report, the "A" isn't there, so it looks like the concatenation never actually took place... Thanks, - Mike Perrone