oleksandr.voloschuk

RequirementWhen we publish a report from Jaspersoft® Studio on a node of a cluster, the updates are not taken in charge by the other nodes. In our specific case, we have a cluster of 2 nodes and we have to publish updates on both, in order to keep them aligned SolutionThere are some inaccuracies in TIBCO JasperReports® Server Ultimate Guide regarding to ehcache / partial session replication configuration. Guide will be fixed and updated in the next release of JasperReports® Server The app server usually manages the user session for a web application and is responsible for the policies that allow the session to be replicated in a cluster environment. However, you must also configure parts of JasperReports Server, including the Ehcache component. Usually, the app server manages the user session and its replication, but some configuration files in JasperReports Server must be updated, including the Ehcache component. This section describes two ways to configure replication for JasperReports Server: • Ehcache replication only.The repository cache handles permissions and the folder structure, and speeds up repository access in a given instance of JasperReports Server. Changes to permissions and folders are cached on the server where they occur, but they can take one to two minutes to be written to the repository database. To maintain performance and avoid collisions, you should configure Ehcache replication whenever you have multiple JasperReports Server instances that share a single repository. Ehchache replication can be configured independently of session replication. • Partial session replication for failover. Partial session replication shares information about the logged-in users and allows for failover without requiring re-authentication. If you configure this, you must first configure Ehcache replication.For configuring ehcache between nodes with JasperReports® Server instances there are necessary to do next steps: 1. On each node of the cluster, enable ehcache replication in your app server or web container. For example, to enable cache replication on Tomcat, edit the file <tomcat>/conf/server.xml as follows: <Cluster className="org.apache.catalina.ha.tcp.SimpleTcpCluster" channelSendOptions="8"> <Manager className="org.apache.catalina.ha.session.DeltaManager" expireSessionsOnShutdown="false" notifyListenersOnReplication="true"/> <Channel className="org.apache.catalina.tribes.group.GroupChannel"> <Membership className="org.apache.catalina.tribes.membership.McastService" address="228.0.0.4" port="45564" frequency="500" dropTime="3000"/> <Receiver className="org.apache.catalina.tribes.transport.nio.NioReceiver" address="172.17.16.93" port="4000" autoBind="100" selectorTimeout="5000" maxThreads="6"/> <Sender className="org.apache.catalina.tribes.transport.ReplicationTransmitter"> <Transport className="org.apache.catalina.tribes.transport.nio.PooledParallelSender"/> </Sender> <Interceptor className="org.apache.catalina.tribes.group.interceptors.TcpFailureDetector"/> <Interceptor className="org.apache.catalina.tribes.group.interceptors.MessageDispatchInterceptor"/> </Channel> <Valve className="org.apache.catalina.ha.tcp.ReplicationValve" filter=""/> <Valve className="org.apache.catalina.ha.session.JvmRouteBinderValve"/> <ClusterListener className="org.apache.catalina.ha.session.ClusterSessionListener"/> </Cluster>* where address="172.17.16.93 this is IP of your current node. 2. RMI – Remote Method Invocation is the simplest and fastest cache distribution mechanism. Use RMI distribution if your cluster runs on your own real or virtual computers, as long as their addresses will not change. You cannot use RMI distribution if your cluster is hosted in a cloud, such as with Amazon Web Services, because the IP addresses of the nodes may change. For RMI distribution, uncomment the RMI section on every node (edit both <web-app>/WEB-INF/ehcache_hibernate.xml and <web-app>/WEB-INF/classes/ehcache_hibernate.xml files), and make sure the properties are correct for your IP multicast. You must also add the hostname property with the value of the real IP address: <!-- ******************************************************************** RMI ********************************************************** --> <!-- START --> <cacheManagerPeerProviderFactory class="net.sf.ehcache.distribution.RMICacheManagerPeerProviderFactory" properties="peerDiscovery=automatic,multicastGroupAddress=228.0.0.1,multicastGroupPort=4446,timeToLive=1"/> <cacheManagerPeerListenerFactory class="net.sf.ehcache.distribution.RMICacheManagerPeerListenerFactory" properties="hostName=172.17.16.93,port=40011,remoteObjectPort=40012,socketTimeoutMillis=120000"/> <cache name="org.hibernate.cache.StandardQueryCache" maxEntriesLocalHeap="5000" maxElementsInMemory="5000" eternal="false" timeToLiveSeconds="120"> <cacheEventListenerFactory class="net.sf.ehcache.distribution.RMICacheReplicatorFactory" properties="replicateAsynchronously=true, replicatePuts=true, replicateUpdates=true,replicateUpdatesViaCopy=false, replicateRemovals=true "/> <bootstrapCacheLoaderFactory class="net.sf.ehcache.distribution.RMIBootstrapCacheLoaderFactory" properties="bootstrapAsynchronously=true, maximumChunkSizeBytes=5000000"/> </cache> <cache name="org.hibernate.cache.UpdateTimestampsCache" maxEntriesLocalHeap="5000" eternal="true"> <cacheEventListenerFactory class="net.sf.ehcache.distribution.RMICacheReplicatorFactory" properties="replicateAsynchronously=true, replicatePuts=true, replicateUpdates=true,replicateUpdatesViaCopy=true, replicateRemovals=true "/> <bootstrapCacheLoaderFactory class="net.sf.ehcache.distribution.RMIBootstrapCacheLoaderFactory" properties="bootstrapAsynchronously=true, maximumChunkSizeBytes=5000000"/> </cache> <cache name="defaultRepoCache" maxElementsInMemory="10000" eternal="false" overflowToDisk="false" timeToIdleSeconds="36000" timeToLiveSeconds="180000" diskPersistent="false" diskExpiryThreadIntervalSeconds="120" statistics="true"> <cacheEventListenerFactory class="net.sf.ehcache.distribution.RMICacheReplicatorFactory" properties="replicateAsynchronously=true, replicatePuts=false, replicateUpdates=true,replicateUpdatesViaCopy=false, replicateRemovals=true "/> <bootstrapCacheLoaderFactory class="net.sf.ehcache.distribution.RMIBootstrapCacheLoaderFactory" properties="bootstrapAsynchronously=true, maximumChunkSizeBytes=5000000"/> </cache> <cache name="aclCache" maxElementsInMemory="10000" eternal="false" overflowToDisk="false" timeToIdleSeconds="360000" timeToLiveSeconds="720000" diskPersistent="false"> <cacheEventListenerFactory class="net.sf.ehcache.distribution.RMICacheReplicatorFactory" properties="replicateAsynchronously=true, replicatePuts=false, replicateUpdates=true,replicateUpdatesViaCopy=false, replicateRemovals=true "/> <bootstrapCacheLoaderFactory class="net.sf.ehcache.distribution.RMIBootstrapCacheLoaderFactory" properties="bootstrapAsynchronously=true, maximumChunkSizeBytes=5000000"/> </cache>* where hostName=172.17.16.93 this is IP of your current node. 3. Restart JasperReports® Server on each nodes. VO - 20190409, case #01730769 server.xml ehcache_hibernate.xml

RequirementIs there a way to assign 'ROLE_SUPERUSER' to 'Admin' user without logging to TIBCO JasperReports® Server as 'superuser' and giving this role? SolutionBy default 'Admin' user has two roles: 'ROLE_ADMINISTRATOR' & 'ROLE_USER'. And only 'superuser' can add ROLE_SUPERUSER to it through user interface: One way how to achieve this could be adding necessary data to repository DB, in 'jiuserrole' table (however, you have to have proper DB user id/password for doing this). Please note, that we DO NOT recommend to do any manual actions on repository DB due to it may break you DB at all! Do a backup of your repository DB before doing next steps! Find the 'id' for 'Admin' user in table 'jiuser': Find the 'id' for ROLE_SUPERUSER in table 'jirole': Insert these values (it will add the ROLE_SUPERUSER to 'Admin' user) in table 'jiuserrole': Re-login to JasperReports® Server as 'Admin' user and check: ReferencesWhy A User With ROLE_SUPERUSER Assignment Does Not Seem To Have Superuser Privileges VO - 20190403, case #01735011

RequirementJaspersoft® Studio report includes dynamic parameter values and looks like this: [toc]http://<host>:<port>/jasperserver[-pro]/rest_v2/reports/reports/iReports/Test/Portfolio_Information_Details.xlsx?ignorePagination=true&P_PORT_POSITION="+$P{P_PORT_POSITION}+"&P_UNIT_ID="+$P{P_UNIT_ID}+"&P_REGIONS_ONLY="+$P{P_REGIONS_ONLY}+"&P_UNIT_ROLLUP="+$P{P_UNIT_ROLLUP} The issue is that the parameter "P_PORT_POSITION" is a collection type and can pass multiple values to the exporting report. When multiple values are passed, it looks like this: "&P_PORT_POSITION=[001,054]": http://<host>:<port>/jasperserver[-pro]/rest_v2/reports/reports/iReports/Test/Portfolio_Information_Details.xlsx?ignorePagination=true&P_PORT_POSITION=[001,%20054]&P_UNIT_ID=90001010&P_REGIONS_ONLY=false&P_UNIT_ROLLUP=true You can see "&P_PORT_POSITION=[001,%20054]" is passed, which isn't a valid value so the export report runs for all P_PORT_POSITION values instead of just 001 and 054. According to Jaspersoft® Server REST API guide , there is an example, which mentions "input controls that are multi-select may appear more than once" (but, those are hardcoded values.): GET http://<host>:<port>/jasperserver[-pro]/rest_v2/reports/reports/samples/Cascading_multi_select_report.html?Country_multi_select=USA&Cascading_state_multi_select=WA&Cascading_state_multi_select=CA Anyway, it doesn't show how to do it dynamically. Is there a way to split these collection values for passing them correctly, such as "&P_PORT_POSITION=001&P_PORT_POSITION=054"? SolutionAs workaround, you can do something like: http://<host>:<port>/jasperserver[-pro]/rest_v2/...&P_PORT_POSITION=" + String.join("&P_PORT_POSITION=", $P{P_PORT_POSITION}) + ... If you need to consider the case of P_PORT_POSITION is empty, you will have to do this more complex: http://<host>:<port>/jasperserver[-pro]/rest_v2/..." + (($P{P_PORT_POSITION} == null || $P{P_PORT_POSITION}.isEmpty()) ? "" : ("&P_PORT_POSITION=" + String.join("&P_PORT_POSITION=", $P{P_PORT_POSITION}))) + ... VO - 20190329, case #01690565

In the beginning, please do a backup of your current instance TIBCO JasperReports® Server (.../webapps/jasperserver-pro) in order to have a possibility to restore your working application, if something goes wrong. To apply the context root of JasperReports® Server application you want, please do next (for instance, in this case we will use context 'jasper'): Rename the directory' name of your current JasperReports® Server Pro from .../webapps/jasperserver-pro to .../webapps/jasper Change context <param-value> for 'webAppRootKey' in .../WEB-INF/web.xml file (this value must match with the name of directory from previous step!): <context-param> <param-name>webAppRootKey</param-name> <param-value>jasper.root</param-value> </context-param>Transform log4j.appenders by overriding fileout locations for JasperReports® Server Pro logs in .../WEB-INF/log4j.properties: log4j.appender.fileout.File=${jasper.root}/WEB-INF/logs/jasperserver.log log4j.appender.jasperanalysis.File=${jasper.root}/WEB-INF/logs/jasperanalysis.log For Scheduling - alter 'report.scheduler.web.deployment.uri' in .../WEB-INF/js.quartz.properties: report.scheduler.web.deployment.uri=http://hostname:port/jasper - for Diagnostic - modify diagnostic default remote access configuration in .../WEB-INF/js.diagnostic.properties: diagnostic.name = jasper Stop application server. Clear all application server caches (for example, for Tomcat application server these folders are /tomcat/temp/ & /tomcat/work/). Start application server and check the link http://hostname:port/jasper If you use TIBCO Jaspersoft® Studio, don't forget to change connection to the new context of JasperReports® Server Pro by overriding the URL. ReferencesHow to change the default jasperserver.log file location DailyRolling log4j Appender For JasperReports Server VO - 20190219

TIBCO JasperReports® Server is looking for the license from three locations (it takes the one where it finds first). The priority is next: If the JVM property (-Djs.license.directory=...) of application server IS SET and EXISTS, it takes precedence over other locations.If JVM property above IS NOT SET, JasperReports® Server looks for the license in /WEB-INF/ folder of the web application.If clauses 1 or 2 either are false (file was not found in alternate locations), JasperReports® Server looks for the license file in "user.home" directory (user who starts the application server).The second approach is useful for users who don't have necessary permissions to add JVM option to application server, or copy the license file to specific "user.home" folder. Please note, according to this step you have to give 'read + write' permissions (to copied license file in /WEB-INF/ folder) for the user on whose behalf application server works. Restarting application server (after any of the above cases) is required. ReferencesAudit feature in license file License expiration notices for an expiration date that is far into the future Periodic 'freezing' of Jasperreports Server webapp VO - 20190204

RequirementHow to change default logs parsing to daily one on JasperReports® Server? SolutionLogs configuration file is located in directory <JasperReports® Server install dir>webappsjasperserver-proWEB-INFlog4j.properties For achieving the expected result (in this case - the logs' structure will be jasperserver.log.yyyy-MM-dd), modify this property file with next configurations (this procedure includes changes as for JasperReports® Server Professional as for JasperReports® Server Community Edition): - add 'FILE' to log4j.rootLogger: #log4j.rootLogger=INFO, stdout, fileout log4j.rootLogger=INFO, FILE, stdout, fileout- change log4j.appender.stdout.layout.ConversionPattern: #log4j.appender.stdout.layout.conversionPattern=%d{ISO8601} %5p %c{1},%t:%L - %m%n log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %t %c{2}:%L - %m%n- disable default Rolling log file output: # Rolling log file output... # jasperserver.root is used only by JasperServer Community Edition. # JasperServer Professional users should look for jasperserver-pro.root lower in this file. #log4j.appender.fileout=org.apache.log4j.RollingFileAppender #log4j.appender.fileout.File=${jasperserver.root}/WEB-INF/logs/jasperserver.log #log4j.appender.fileout.MaxFileSize=1024KB #log4j.appender.fileout.MaxBackupIndex=1 #log4j.appender.fileout.layout=org.apache.log4j.PatternLayout #log4j.appender.fileout.encoding=UTF-8 # Valid date format specifiers for the conversionPattern include "ABSOLUTE", "DATE" and "ISO8601" #log4j.appender.fileout.layout.conversionPattern=%d{ISO8601} %5p %c{1},%t:%L - %m%n- add DailyRolling log4j appender: log4j.appender.FILE=org.apache.log4j.DailyRollingFileAppender log4j.appender.FILE.DatePattern='.'yyyy-MM-dd log4j.appender.FILE.File=${jasperserver.root}/WEB-INF/logs/jasperserver.log log4j.appender.FILE.layout=org.apache.log4j.PatternLayout log4j.appender.FILE.encoding=UTF-8 log4j.appender.FILE.layout.ConversionPattern=%d{ABSOLUTE} %5p %t %c{2}:%L - %m%n log4j.appender.FILE.ImmediateFlush=true- modify overriding fileout location: # jasperserver-pro.root is used only by JasperServer Professional. # JasperServer Community Edition users should look for jasperserver.root above in this file. #log4j.appender.fileout.File=${jasperserver-pro.root}/WEB-INF/logs/jasperserver.log log4j.appender.FILE.File=${jasperserver-pro.root}/WEB-INF/logs/jasperserver.log log4j.appender.jasperanalysis.File=${jasperserver-pro.root}/WEB-INF/logs/jasperanalysis.logRestarting application server is required. Example of log4j.properties for JasperReports® Server v.7.1.0 is attached for your reference. ReferencesConfiguring Apache Commons Logging and Log4J in Jaspersoft Studio How to change the default jasperserver.log file location VO - 20190201 log4j.properties

Scenario:While using parameter (created with property class as 'java.util.Collection') in $X{IN, , } clauses on PostgreSQL DB, getting the type casting error: [toc]Error Message net.sf.jasperreports.engine.JRException: Error executing SQL statement for : at com.jaspersoft.studio.editor.preview.view.control.ReportController.fillReport(ReportController.java:550) at com.jaspersoft.studio.editor.preview.view.control.ReportController.access$16(ReportController.java:525) at com.jaspersoft.studio.editor.preview.view.control.ReportController$1.run(ReportController.java:420) at org.eclipse.core.internal.jobs.Worker.run(Worker.java:55) ************************ Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = character varying Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Solution:PostgreSQL is strict and does not allow type casting, i.e. you cannot compare an integer with a varchar (meanwhile as, another DB type, for instance, SQLServer could allow this). So, to compare these two different types, you will have to cast one to the other using the casting syntax '::' As example, please take a look on attached jrxml report, where field 'employeeid' is Integer, and parameter 'employee' is Collection. Query is next: SELECT * FROM orders where $X{IN,(employeeid::varchar), employee} Next screencast demonstrates this procedure -> https://screencast-o-matic.com/watch/cqVU2m3yw3 VO - 20190131, case #01674394 example.jrxml

Description:The legend colors change depending on how many bars there are. Example: The legend has 6 bars ('states') with its own colors: CA - orangeWA - yellowBC - greenDF - redMexico - light greyOR - dark grey[toc] When remove 'CA' from filter - there are 5 bars which are for should be without 'orange' color. However, you see the 'orange' color on 'DF' field, the 'dark grey' one is removed (the last in the list). How to get the legend color to stick to the color assigned? Solution:Using property 'SeriesItemProperty' instead of 'SeriesProperty' would apply the colors to the entire series accordingly VO - 20181101, case #01575456 color.jrxml

Problem:User has a requirement of exporting crosstab output to Excel with existing formulas. Once he exported report to excel he wants to modify data (this case if he edit any column cell value then that particular column total should get changed automatically). He uses Excel export property in report by providing SUM(C3:C9). This is working for only 'C column', and for the rest columns it is also printing SUM of 'C column' values. [toc] He wants to use and to export dynamically 'SUM' function which can take column names(C, D, E, etc) and Row numbers(3, 4, 5, etc) instead of hard coding in the below code (for each column): <propertyExpression name="net.sf.jasperreports.export.xls.formula"><![CDATA["SUM(C3:C9)"]]></propertyExpression> Suggested Resolutions:Excel formula feature in TIBCO JasperReports® Server The JasperReports® Server engine provides a feature which allows using a formula as content of a text field. The formula will affect the text field value only when the document will be exported to XLS or XLSX format, and will be neglected when exporting the document to other formats. All other exporters will take into account the value given by the <textFieldExpression /> element. But first of all one have to ensure that cells are enabled to detect their own data type. By default all data are exported as text only. Enabling the cell type detection can be done setting the export hint property net.sf.jasperreports.export.xls.detect.cell.type to true: <property name="net.sf.jasperreports.export.xls.detect.cell.type" value="true"/> In JasperReports® Server a formula can be stored using the PROPERTY_CELL_FORMULA text field property. The property name is net.sf.jasperreports.export.xls.formula, and usually its value is a string containing the formula expression. The expression can be a very simple one, or a more and more complex, as needed. If the expression contains only constants or static data, then using the <property /> element is recommended. If the expression contains one or more dynamic data, then the <propertyExpression /> element should be used instead. Although in Excel any formula expression should start with the "=" sign, the JasperReports® Server engine is more permissive and allows a formula expression to start also without the "=" sign. Both formula expressions are considered as valid, and are further evaluated and prepared to be successfully exported to the XLS output format. You can use a dynamic formula in this case, based on the following dynamic Excel functions: ADDRESS() - Returns a reference as text to a single cell in a worksheetINDIRECT() - Returns a reference indicated by a text valueCOLUMN() - Returns the column number of a referenceROW() - Returns the row number of a referenceMore information about these dynamic functions you can find out there -> Lookup and reference functions If you need to export to XLS format, the formula should look like: <propertyExpression name="net.sf.jasperreports.export.xls.formula"> <![CDATA["=SUM(INDIRECT(ADDRESS(3,COLUMN())&":"&ADDRESS(ROW()-1,COLUMN())))"]]> </propertyExpression> The only problem here is that the prior XLS format is too old to support exactly the same formula as the XLSX format. So, if you need to export to XLSX, the formula should be: <propertyExpression name="net.sf.jasperreports.export.xls.formula"> <![CDATA["=SUM(INDIRECT(ADDRESS(3,COLUMN())&":"&ADDRESS(ROW()-1,COLUMN())))"]]> </propertyExpression> As example, please see attached .jrxml VO - 20181009, case #01640521 crosstab.jrxml