gshivsha

Problem : By default, the scheduler is configured to use TLS 1.0 Resolution : We need to add property "mail.smtp.ssl.protocols" in applicationContext-report-scheduling.xml under "javaMailProperties" to enforce TLS 1.2 to be used. The XML code snippet is given below: <bean id="reportSchedulerMailSender" class="org.springframework.mail.javamail.JavaMailSenderImpl" lazy-init="true"> <property name="host" value="${report.scheduler.mail.sender.host}"/> <property name="username" value="${report.scheduler.mail.sender.username}"/> <property name="password" value="${report.scheduler.mail.sender.password}"/> <property name="protocol" value="${report.scheduler.mail.sender.protocol}"/> <property name="port" value="${report.scheduler.mail.sender.port}"/> <property name="javaMailProperties"> <props> <prop key="mail.smtp.sendpartial">true</prop> <prop key="mail.smtp.auth">false</prop> <prop key="mail.smtp.ssl.protocols">TLSv1.2</prop> </props> </property> </bean> Save the applicationContext-report-scheduling.xml file and restart the server.

Problem : Using MySQL DATE_ADD function when creating calculated fields in Adhoc Explanation :By default, the DATE_ADD function in MySQL function is not available under the Adhoc View calculated fields lists of function Resolution : In order to make use of the DATE_ADD function, we need to add the following in applicationContext-semanticLayer.xml file under property "functionTemplates" inside bean with id "defaultSQLGenerator" <entry key="addDay"> <value>"DATE_ADD(" + sqlArgs[0] + ", INTERVAL " + sqlArgs[1] + " DAY)"</value></entry><entry key="addMonth"> <value>"DATE_ADD(" + sqlArgs[0] + ", INTERVAL " + sqlArgs[1] + " MONTH)"</value></entry>[/code] For displaying the newly created custom function in the Adhoc Calculated Field Function List add the following two function definitions under util:list id="operatorDefinitionList" in file WEB-INFapplicationContext-el-operators.xml - For adding the addDay custom Function: <bean parent="functionDef"> <property name="name" value="addDay"/> <property name="javaType" value="java.sql.Timestamp"/> <property name="argumentTypes"> <list> <value>#{ objectTypeMapper.checkType('Timestamp') }</value> <value>#{ objectTypeMapper.checkType('String') }</value> </list> </property> <property name="properties"> <map> <!-- show in calc field dialog --> <entry key="inAvailableFunctions" value="true"/> <!-- always run in SQL --> <entry key="alwaysInSQL" value="true"/> </map> </property> <property name="inMemory" value="false"/></bean>[/code]For adding the addMonth custom Function: <bean parent="functionDef"> <property name="name" value="addMonth"/> <property name="javaType" value="java.sql.Timestamp"/> <property name="argumentTypes"> <list> <value>#{ objectTypeMapper.checkType('Timestamp') }</value> <value>#{ objectTypeMapper.checkType('String') }</value> </list> </property> <property name="properties"> <map> <!-- show in calc field dialog --> <entry key="inAvailableFunctions" value="true"/> <!-- always run in SQL --> <entry key="alwaysInSQL" value="true"/> </map> </property> <property name="inMemory" value="false"/></bean>[/code] Save the files applicationContext-semanticLayer.xml and applicationContext-el-operators.xml and restart the server. Now in Adhoc when creating calculated field use expression like below: addMonth("date_field", '4') Note 1: date_field is the name of a field in the database table Note 2: For each different unit of time we need to add new functions. For example, to use DATE_ADD with WEEK interval we also need to add addWeek function: <entry key="addWeek"> <value>"DATE_ADD(" + sqlArgs[0] + ", INTERVAL " + sqlArgs[1] + " WEEK)"</value> </entry>[/code]Similary, we have to add the function definition "addWeek" under util:list id="operatorDefinitionList" in file WEB-INFapplicationContext-el-operators.xml file as stated above.

Problem : The locale selected is en-English even if the Firefox browser is set to use the French language. Explanation :By default, JasperReports Server appears in the locale selected in the end user’s browser. So if users have their browser set to use French language then Jaspeserver UI is displayed in the same language. So it depends on how the users have configured their browsers language settings. When trying to use the french variant fr_FR, the reason why the locale parameter selected is fr_FR in chrome is because in the browser language settings there is an option to select upto 3 variants like (fr-CA, fr-Zh,fr-FR) and you might have set default language there to French (France). Now when Jasperserver looks up for the list of locales available it is able to find fr_FR as it's configured there in applicationContext-security.xml (<value type="java.util.Locale">fr_FR</value>) On the other hand, firefox only provides one language (French) and not variants. So Jasperserver looks for "fr" whereas we have fr_FR(<value type="java.util.Locale">fr_FR</value>) so Jasperserver just selects the first value from the locales parameter from bean "userLocalesList" property locales as it is not able to find the fr_FR.(which happens to be "English"(anglias)) Resolution : So a simple workaround is to just move the "fr_FR" at the top of the list inside the bean "userLocalesList" in WEB-INF/applicationContext-security.xml file so that it looks like below: <property name="locales"> <list> <value type="java.util.Locale">fr_FR</value> <value type="java.util.Locale">en</value> <value type="java.util.Locale">de</value> <value type="java.util.Locale">es</value> <value type="java.util.Locale">it</value> <value type="java.util.Locale">ja</value> <value type="java.util.Locale">pt_BR</value> <value type="java.util.Locale">zh_CN</value> <value type="java.util.Locale">ar</value> </list></property>[/code]Save the applicationContext-security.xml file and restart the server. With this configuration, it will work in Firefox and we will have the fr_FR as our locale value.

Problem : How to redirect to the custom page with custom authentication once the session is timed out? Default Behaviour :By default, Jasperserver redirects users to the home/login page once the session ends. Resolution : We can redirect users to a custom page. For this we need to add the bean "proxyExceptionTranslationFilter" in the applicationContext-externalAuth-<authType>.xml file under the WEB-INF folder: There we have to pass the url as a constructor parameter for bean "com.jaspersoft.jasperserver.api.security.externalAuth.wrappers.spring.JSLoginUrlAuthenticationEntryPoint". A sample for the same is given below: <bean id="proxyExceptionTranslationFilter" class="com.jaspersoft.jasperserver.api.security.externalAuth.wrappers.spring.JSExceptionTranslationFilter"> <constructor-arg index="0"> <bean class="com.jaspersoft.jasperserver.api.security.externalAuth.wrappers.spring.JSLoginUrlAuthenticationEntryPoint"> <constructor-arg index="0"> <value>/externallogin.html</value> </constructor-arg> <property name="forceHttps"> <value>false</value> </property> </bean> </constructor-arg> <property name="accessDeniedHandler"> <bean class="com.jaspersoft.jasperserver.war.JRSAccessDeniedHandlerImpl"> <property name="errorPage" value="/WEB-INF/jsp/modules/system/AccessDeniedPage.jsp"/> <property name="themeResolver" ref="themeResolver" /> <property name="themeSource" ref="themeSource" /> <property name="localeResolver" ref="filterLocaleResolver"/> </bean> </property> </bean> Now save the applicationContext-externalAuth-<authType>.xml file and restart the server.

Problem : By default, when a report having multiple pages is embedded in the dashboard the pagination control button is available at the top left corner. Cause :The default themes provided in the jasperserver application position it to the top-left corner. Resolution : To position the pagination control at the different position we can use a custom theme as described in the below doc: https://community.jaspersoft.com/documentation/tibco-jasperreports-server-ultimate-guide/v780/changing-ui-themes To position, the pagination controller to the bottom left the following CSS can be used: .dashboardCanvas .dashlet .paginationControlWrapper { position: absolute; height: 36px; padding-bottom: 8px; padding-right: 8px; padding-left: 8px; bottom: 15px; right: 15px; } I have also attached a custom zip file which can be uploaded in the THEMES directory on the server for quick testing (Right Click On "Themes" Folder -> SELECT Option "Upload a Theme") dashboardpagination.zip

Problem :When connecting to Google BigQuery using Tibco BigQuery JDBC Driver below error is thrown : [toc]java.sql.SQLException: [TibcoSoftware][GoogleBigQuery JDBC Driver][GoogleBigQuery]general errorat tibcosoftware.jdbc.base6_00_000569.BaseExceptions.b(Unknown Source)at tibcosoftware.jdbc.base6_00_000569.BaseExceptions.a(Unknown Source)at tibcosoftware.jdbc.base6_00_000569.BaseExceptions.a(Unknown Source) at tibcosoftware.jdbc.base6_00_000569.BaseExceptions.a(Unknown Source) at tibcosoftware.phoenix6_00_1096.jdbc.q.b(Unknown Source) at tibcosoftware.phoenix6_00_1096.jdbc.q.a(Unknown Source) at tibcosoftware.phoenix6_00_1096.jdbc.q.a(Unknown Source) at tibcosoftware.phoenix6_00_1096.api.b.open(Unknown Source) at tibcosoftware.jdbc.base6_00_000569.BaseConnection.b(Unknown Source) at tibcosoftware.jdbc.base6_00_000569.BaseConnection.connect(Unknown Source) at tibcosoftware.jdbc.base6_00_000569.BaseConnection.d(Unknown Source) at tibcosoftware.jdbc.base6_00_000569.BaseConnection.a(Unknown Source) at tibcosoftware.jdbc.base6_00_000569.BaseDriver.connect(Unknown Source) at java.sql.DriverManager.getConnection(DriverManager.java:664) at java.sql.DriverManager.getConnection(DriverManager.java:247) at org.apache.commons.dbcp.DriverManagerConnectionFactory.createConnection(DriverManagerConnectionFactory.java:75) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.JdbcDriverManagerConnectionFactory.createConnection(JdbcDriverManagerConnectionFactory.java:36) at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582) at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:1148) at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:106)at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.JdbcDataSourceService.testConnection(JdbcDataSourceService.java:85) ...Caused by: tibcosoftware.phoenix6_00_1096.sql.ae: general errorat tibcosoftware.phoenix6_00_1096.sql.error.a.a(Unknown Source)at tibcosoftware.phoenix6_00_1096.api.metadata.m.a(Unknown Source)at tibcosoftware.phoenix6_00_1096.sql.j.u(Unknown Source)at tibcosoftware.phoenix6_00_1096.sql.j.open(Unknown Source)at tibcosoftware.phoenix6_00_1096.sql.k.a(Unknown Source) at tibcosoftware.phoenix6_00_1096.sql.k.a(Unknown Source)at tibcosoftware.phoenix6_00_1096.sql.bv.a(Unknown Source)at tibcosoftware.phoenix6_00_1096.api.core.al$c.connect(Unknown Source) ... 194 moreCaused by: java.lang.NullPointerExceptionat tibcosoftware.jdbc.googlebigquery.adapter.GoogleBigQueryJsonWebToken.generatePrivateKeyFromJson(Unknown Source)at tibcosoftware.jdbc.googlebigquery.adapter.GoogleBigQueryServiceAccount.generateAccessToken(Unknown Source)at tibcosoftware.jdbc.googlebigquery.adapter.GoogleBigQueryServiceAccount.a(Unknown Source) at tibcosoftware.phoenix6_00_1096.api.y.br(Unknown Source) ... 201 more[/code]Cause :Currently, when connecting to Google BigQuery service using Tibco JDBC Google Big Query Driver we cannot use the .p12 private key file to connect to the BigQuery datasource in JasperReports server Resolution : Instead of using the .p12 private key file format we can download and use the JSON private key for our project in google cloud platform and add it to the jasperserver repository. Now while creating the Google Big Query JDBC data source under the private key path specify the location of the JSON security file. The connection test for Google Big Query with the .json private key is now successful. Also, Jaspersoft uses progress JDBC drivers. For more info on how to establish the connection with your Google BigQuery Dataware House please refer to the below article: https://www.progress.com/tutorials/jdbc/connect-and-query-google-bigquery-using-jdbc-connector NOTE: 1) The stack trace given in the problem section is not the complete stack trace and just a part of the original one. So it might happen that the problem you have will be different.[/code]2a) The example jdbc url given in the article (https://www.progress.com/tutorials/jdbc/connect-and-query-google-bigquery-using-jdbc-connector) is like below: jdbc:datadirect:googlebigquery:AuthenticationMethod=serviceaccount;Project=<yourprojectname-12345>;Dataset=<your dataset name>;ServiceAccountEmail=<email@yourprojectname-12345.iam.gserviceaccount.com;ServiceAccountPrivateKey=/path-to/json-or-p12-file [/code]2b) In case of using Tibco BigQuery Driver the jdbc url will look something like below: jdbc:tibcosoftware:googlebigquery:AuthenticationMethod=serviceaccount;Project=<yourprojectname-12345>;Dataset=<your dataset name>;ServiceAccountEmail=<email@yourprojectname-12345.iam.gserviceaccount.com;ServiceAccountPrivateKey=/path-to/json-file/in/jasperserver-repository[/code]

ProblemHow to pass parameters in the JDBC URL when using TIBCO Autorest JDBC Driver to connect to the rest service which will be used as a Data Source. ResolutionWe can pass parameters in two ways. 1) HTTP Header Authentication -Set the AuthenticationMethod property to HttpHeader. -Set the AuthHeader property to specify the name of the HTTP header used for authentication. The default is Authorization. -Set the SecurityToken to specify the security token required to make a connection to your endpoint. For example, XaBARTsLZReM. (please give the JWT token here) e.g jdbc:tibcosoftware:autorest:AuthenticationMethod=HttpHeader;Config=C:/path/to/myrest.rest;SecurityToken=XaBARTsLZReM;TransactionMode=ignore Please refer below for more info- https://documentation.progress.com/output/DataDirect/jdbcautoresthelp/index.html#page/jdbcautorest%2Fhttp-header-authentication.html%23 2) The URL Parameter method -Set the AuthenticationMethod property to UrlParameter. -Set the AuthParam property to specify the name of the URL parameter used to pass the security token. For example, apikey. -Set the SecurityToken to specify the security token required to make a connection to your endpoint. For example, XaBARTsLZReM. (please give the JWT token here) -If required by your service, set the User property to specify your logon ID. -Optionally, specify values for any additional properties you want to configure. e.g. jdbc:tibcosoftware:autorest:AuthenticationMethod=UrlParameter;AuthParam=apikey;Config=C:/path/to/myrest.rest;SecurityToken=XaBARTsLZReM;User=jsmith;TransactionMode=ignore Please refer below for more info- https://documentation.progress.com/output/DataDirect/jdbcautoresthelp/index.html#page/jdbcautorest%2Furl-parameter-authentication.html%23 See alsoSee this for applicable versions and further info: https://community.jaspersoft.com/project/tibco-jaspersoft-connectors/resources

Problem : The default functionality of multi-select parameter, i.e. if no values are passed through the input control, it considers the scenario that all the values are selected Cause :From Jaspersoft doc- If the parameter is null or contains an empty list, meaning no value has been set for the parameter, the entire $X{} clause is evaluated as the always true statement “0 = 0” . Refer "IN and NOTIN Clauses" section here: https://community.jaspersoft.com/documentation/tibco-jaspersoft-studio-user-guide/v780/using-parameters-queries Resolution : To avoid the situation of all values selected for a particular multi-select input control we can create a new parameter of boolean type with "isForPrompting=false" and the "defaultValueExpression=$P{<MULTI_SELECT_PARAMETER_NAME>}.size()>0" . An example jrxml(report) code snippet given below with the query required: <parameter name="P1" class="java.util.Collection" nestedType="java.lang.String"/> <parameter name="P2" class="java.util.Collection" nestedType="java.lang.String"/> <parameter name="P3" class="java.util.Collection" nestedType="java.lang.String"/> <parameter name="isSelected1" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[$P{P1}.size()>0]]></defaultValueExpression> </parameter> <parameter name="isSelected2" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[$P{P2}.size()>0]]></defaultValueExpression> </parameter> <parameter name="isSelected3" class="java.lang.Boolean" isForPrompting="false"> <defaultValueExpression><![CDATA[$P{P3}.size()>0]]></defaultValueExpression> </parameter> <queryString language="SQL"> <![CDATA[select * from test.t1 where ($X{IN,t1.FIELD_NAME,P1} AND $X{EQUAL,true,isSelected1}) OR ($X{IN,t1.FIELD_NAME,P2} AND $X{EQUAL,true,isSelected2} )OR ($X{IN,t1.FIELD_NAME,P3} AND $X{EQUAL,true,isSelected3}) ]]> </queryString> Also, attached the jrxml report for testing the use case. Note : You need to change the test.t1 table with the db table you want to refer. Also, "name" in the report refers to the field name in table t1. jaspersoftmultiselectinputcontrol.jrxml jaspersoftmultiselectinputcontrol_0.jrxml

Problem : Hide the stack trace thrown by Tomcat (Application Server) when something goes wrong or there is an error in some configuration file while deploying JasperReports Server. Cause :When trying to change some configuration files or when adding some customization like (configuring JasperReports Server to use external authentication) we may make some mistake and the JasperReports Server login page may not be accessible and instead, we could see a Tomcat Page with a Stack trace which may be considered as a security issue by some. The stack trace is thrown by tomcat and not by Jasperserver which is why the Jasperserver settings do not hide even after configuring ../WEB-INF/applicationContext-security.xml file to hide the stack trace. Resolution : The default Tomcat configuration includes an AccessLogValve. These are normally configured per host but may also be configured per engine or per context as required. The Error Report Valve is a simple error handler for HTTP status codes in Tomcat that will generate and return HTML error pages. The default ErrorReportValve includes the Tomcat version number in the response and can display stack traces and/or JSP source code to clients when an error occurs. To avoid this, custom error handling can be configured within each web application. Hiding the tomcat stack trace could be achieved by adding the following lines to the host section of your server.xml file under <apche-tomcat>conf where you should already have the AccessLogValve: <Valve className="org.apache.catalina.valves.ErrorReportValve" showReport="false" showServerInfo="false"/> [/code]Disabling both showServerInfo and showReport will now only return the HTTP status code. More information about this could be found in tomcat docs: https://tomcat.apache.org/tomcat-7.0-doc/config/valve.html#Error_Report_Valve

Problem : Opening the Adhoc View in "Display Mode" in the JasperReports Server Cause :When opening any "Adhoc View" by default it opens in design mode and we can toggle the modes by using the "Toggle between design and display modes" option in the title bar from the JasperReports Server UI. Resolution : To always open an Adhoc View in "Display Mode" by default, first, stop the Application Server and then we need to modify the file redirToMainView.jsp under ../WEB-INF/jsp/modules/adhoc by changing the code like below: FROM <script> window.onload = function() { this.location.href = '${pageContext.request.contextPath}/flow.html' + '?_flowId=adhocFlow' + '&_eventId=initForExistingReport' + '&_flowExecutionKey=${flowExecutionKey}' + <c:if test="${param.decorate == 'no'}">'&decorate=no' + </c:if> '&viewReport=${viewReport}'; } </script>[/code]TO <script> window.onload = function() { this.location.href = '${pageContext.request.contextPath}/flow.html' + '?_flowId=adhocFlow' + '&_eventId=initForExistingReport' + '&_flowExecutionKey=${flowExecutionKey}' + <c:if test="${param.decorate == 'no'}">'&decorate=no' + </c:if> '&viewReport=${viewReport}'; <c:choose> <c:when test="${empty param.viewReport}">'&viewReport=true'</c:when> <c:otherwise>'&viewReport=${viewReport}'</c:otherwise> </c:choose>; } </script>[/code]----------------------------------------------------------------------------- Save the redirToMainView.jsp after making this change and restart your application server. Now every Adhoc view will be opened in "Display Mode" by default.

Problem:How to edit the properties like label font size or title font size when multiple axes are present? Cause:When dealing with multiple axes chart, for example, Column Spline - HTML5 Charts in Jaspersoft the labels or title font size for the other axes may be too large or small depending on the design and data and it may not look appealing which kills the whole purpose of a chart. Resolution:In the case of multiple axes, the xAxis or yAxis node is an array of configuration objects. For more info refer here: https://api.highcharts.com/highcharts/xAxis So, in order to refer and modify other axes property, we can use array indexing to specify axes. For example, in column spline in the attached jrxml report to access the other axes we’d use something like below: yAxis[1].title.text=”Customer Order Count” yAxis[1].title.style.fontSize=20px yAxis[1].labels.style.fontSize=20px I have attached a sample jrxml report for reference which consists of a column spline chart and the above-mentioned properties in play of the second axes of type spline (Axes label font size, Axes title font size, and Axes title text).

Problem:When using Details and Totals for Summary Calculation ‘SUM’ on a calculated field or measure with the formula “A / B” is calculated as “SUM(A) / SUM(B)” instead of “SUM(A / B)”. Cause:Ad Hoc has a built-in behavior in which the Summary Calculation SUM of a calculated field in the form "A / B" is done as "Sum(A) / Sum(B)", which also happens to be the average The Summary Calculation SUM works as expected when the calculation involves multiplication/subtraction/addition. To summarize, When a column A * B is totaled, the calculation used is SUM(A * B). When a column A + B is totaled, the calculation used is SUM(A + B). When a column A - B is totaled, the calculation used is SUM(A - B). Resolution/Workaround :There is already a bug reported with Jaspersoft Engineering for this unusual behavior. But, the good news is there is a workaround to resolve the issue. Slightly changing the divide operation, i.e. using A * (1 / B) instead of A / B will give proper results for Summary Calculation SUM

Problem:Removing an external library or external module from TIBCO Jaspersoft ETL Studio in case the external library or external module needs to be changed or replaced Cause:When placing a component for DB connection in TIBCO Jaspersoft ETL Studio an installation request for an external library or external module(for example, the JDBC driver) occurs only once at the beginning. So once done, we cannot directly replace the external module and first we need to remove or delete that external library or module in order to use a different library or module. Resolution:The workaround to fix the problem are given below. Please refer the following steps to replace or change the external library or external module - 1. Stop the TIBCO Jaspersoft ETL Studio. 2. Go to the < TIBCO Jaspersoft ETL Studio _installation>configuration.m2repositoryorgtalendlibraries. 3. Select the external library/module you want to remove (external library folder) -> delete it from this location. 4. Start TIBCO Jaspersoft ETL Studio and open the job 5. Go and check in the Windows -> show view -> Modules -> check if the external module is present in installed state there or uninstalled state. 6. The external module or library will now be returned to the non-installed state. 7. Install it again.

Problem:TIBCO Jaspersoft® Studio Professional 7.5.0, trying to open the Git Staging View leads to "Could not create the view: org. eclipse.egit.ui.StagingView" Cause:Jaspersoft® Studio which is built on Eclipse IDE provides support for the Git version control system. As you can easily perform the necessary Git commands like staging, commit, merge, rebase, pull and push via the Eclipse IDE so can be done with Jaspersoft Studio as well. But in the latest release of Jaspersoft Studio 7.5.0 for some reason, the packaging of the Eclipse GIT feature(s) with all plug-ins did not complete correctly. On the other hand, the E-Git stuff gets installed correctly when using into an Eclipse instance i.e. installed from the Marketplace. Resolution:Luckily there is a workaround you can adopt to fix the problem until the next release. Please refer the following steps - Open Preferences dialog under Window tab and in Install/Update > Available Software Sites add the following two sites: http://download.eclipse.org/releases/2019-06http://download.eclipse.org/eclipse/updates/4.12 Then in Help > Install New Software use the update site related to the specific version we were supposed to bundle. https://download.eclipse.org/egit/updates-5.4. Select the following two categories and install Git integration for EclipseJava implementation of Git Now after doing Accept Selected everything should complete correctly, and the functionalities related to GIT should be restored.