Tirupathi Mangalarapu

IntroductionInternationalization is quickly becoming a growing requirement for many companies as they expand their offices to various locations around the world. Many companies are searching for a robust BI platform with multi-language support. This tutorial will identify various areas within the Jaspersoft Business Intelligence suite that utilize internationalization. ObjectivesThis guide will enable you to Internationalize Jaspersoft reports. Please download the attached project files and load them up in Jaspersoft Studio for follow along demonstration: Create a basic studio based report as shown in below screenshot As you can see, 5 text elements are added and each element points to corresponding element from the locale properties files (Also all other bands are removed from the report): When you preview the report, you see: For us to apply the locale settings to the report We need add the locale properties file to the project andSpecify the paths to the locale properties file(s) on the report.Finally test it.Add the locale files to our Jaspersoft ProjectA recommended approach would be to create a New folder on the Jaspersoft Project folder In the Project Explorer window, right click on the Project name and select "New" -> "Folder" and name the folder the base name of the resource bundle. Make sure all of the locale .properties files are moved into this folder. Right click on that resource bundle folder and go to "Build Path" and select "Use as Source Folder." To keep it simple, I choose two properties files one with “en_US” and another “en_CA” as listed: Filename: LocaleTestingReport_en_US.properties: Hello = Hello USA Payee = Payee PaymentType = Payment Type Payor = Payor Type = LocaleTestingReport_en_US Filename: LocaleTestingReport_en_CA.properties: Hello = Hello Canada Payee = Paid To PaymentType = Payment Type Payor = Source of Payment Type = LocaleTestingReport_en_CA As shown in the below screenshot: Note: Please ensure all your resource files follow the same naming format like “LocaleTestingReport_en_US” and “LocaleTestingReport_en_CA”. Specify the paths to the locale properties file(s) on the reportNow, that we have added the locale to our project. We need to reference this locale files so that we can test the report output. In the Outline window click on the report name. You will see properties window for the report. Under Dataset Section > Resource Bundle specify “LocaleTestingReport” and save the report. TestNow, you can click on Preview to preview the report. As you can see by default, it picks up the en_US and if you would like to test other file simply specify the resource bundle path to “LocaleTestingReport_en_CA” and preview the output. As you can see en_CA locale properties labels are applied to our report elements: Written By This wiki article was written by Tiru, CSE, October 2018 Referenceshttps://community.jaspersoft.com/wiki/missing-resource-bundle-jaspersoft-studio-550 https://community.jaspersoft.com/wiki/tibco-jasperreports-server-and-ireport-designer-internationalization localization_testing.zip

Symptom :When you run a report built based on Studio Jaspersoft domain that uses Filter expression “is one of” on parameterized field adds extra parenthesis and that breaks the report when ran on Jaspersoft Server with the following error: Error filling report Exception: [TibcoSoftwareInc][Oracle JDBC Driver][Oracle]ORA-00907: missing right parenthesis error Error Trace com.jaspersoft.jasperserver.api.JSException: Error filling report Arguments: at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$FillResultListener.reportFillError(EngineServiceImpl.java:1320) at net.sf.jasperreports.engine.fill.BaseFillHandle.notifyError(BaseFillHandle.java:216) at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:138) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousExecutor.execute(EngineServiceImpl.java:945) at net.sf.jasperreports.engine.fill.BaseFillHandle.startFill(BaseFillHandle.java:169) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$AsynchronousReportFiller.fillReport(EngineServiceImpl.java:896) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1812) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runWithDataSource(EngineServiceImpl.java:1143) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:1072) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:967) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source) Caused by: net.sf.jasperreports.engine.JRException: java.lang.RuntimeException: exception getting dataset from cache at com.jaspersoft.ji.adhoc.strategy.StrategyAwareDomainQueryExecuter.createDatasource(StrategyAwareDomainQueryExecuter.java:245) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1245) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:723) at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:440) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:554) at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:123) ... 10 more Caused by: java.lang.RuntimeException: exception getting dataset from cache at com.jaspersoft.commons.semantic.dsimpl.memory.MemoryDataSet.getWorkingDataSet(MemoryDataSet.java:140) at com.jaspersoft.commons.semantic.dsimpl.memory.MemoryDataSet.getWorkingDataSet(MemoryDataSet.java:76) at com.jaspersoft.ji.adhoc.strategy.CommonDomainDataStrategy.getWorkingDataSet(CommonDomainDataStrategy.java:1358) at com.jaspersoft.ji.adhoc.strategy.CommonDomainDataStrategy.getRuntimeDataSet(CommonDomainDataStrategy.java:1770) at com.jaspersoft.ji.adhoc.strategy.DomainDataStrategy.getRuntimeDataSet(DomainDataStrategy.java:323) at com.jaspersoft.ji.adhoc.strategy.CompiledDomainQuery.getRuntimeDataSet(CompiledDomainQuery.java:103) at com.jaspersoft.ji.adhoc.strategy.StrategyAwareDomainQueryExecuter.createDatasource(StrategyAwareDomainQueryExecuter.java:231) ... 15 more Caused by: com.jaspersoft.commons.dataset.DataSetException: exception fetching from cache at com.jaspersoft.commons.datarator.BaseDataSetCacheImpl.getWorkingDataSet(BaseDataSetCacheImpl.java:287) at com.jaspersoft.commons.semantic.dsimpl.memory.MemoryDataSet.getWorkingDataSet(MemoryDataSet.java:103) ... 21 more Caused by: com.jaspersoft.commons.dataset.DataSetException: exception fetching data at com.jaspersoft.commons.datarator.CachedData.fetchData(CachedData.java:252) at com.jaspersoft.commons.datarator.BaseDataSetCacheImpl.getWorkingDataSet(BaseDataSetCacheImpl.java:283) ... 22 more Caused by: com.jaspersoft.commons.dataset.DataSetException: Exception calling JRDataSource.next() for query select * from ( select "SALES_PERSON_ID" from "SPOTFIRE_DEMODATA"."PHARMA" where (("STATE" in ('Alabama', 'Ohio', 'Virginia', 'Wyoming')) and (("REGION" in (('NE', 'WE'))) or "REGION" = 'NULL')) ) where ROWNUM <= 200001 at com.jaspersoft.commons.semantic.dsimpl.JRQueryDataSet$JRDataSetIterator.next(JRQueryDataSet.java:433) at com.jaspersoft.commons.datarator.CachedData.fetchData(CachedData.java:215) ... 23 more Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: null. at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:347) at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:169) at com.jaspersoft.commons.util.JSControlledJdbcQueryExecuter.run(JSControlledJdbcQueryExecuter.java:156) ... 1 more Caused by: java.sql.SQLException: [TibcoSoftwareInc][Oracle JDBC Driver][Oracle]ORA-00907: missing right parenthesis at tibcosoftwareinc.jdbc.oraclebase.ddcr.b(Unknown Source) at tibcosoftwareinc.jdbc.oraclebase.ddcr.a(Unknown Source) at tibcosoftwareinc.jdbc.oraclebase.ddcq.b(Unknown Source) at tibcosoftwareinc.jdbc.oraclebase.ddcq.a(Unknown Source) at tibcosoftwareinc.jdbc.oracle.ddal.a(Unknown Source) at tibcosoftwareinc.jdbc.oracle.ddal.a(Unknown Source) at tibcosoftwareinc.jdbc.oracle.ddam.t(Unknown Source) at tibcosoftwareinc.jdbc.oraclebase.dde3.x(Unknown Source) at tibcosoftwareinc.jdbc.oraclebase.dde3.w(Unknown Source) at tibcosoftwareinc.jdbc.oraclebase.dddz.executeQuery(Unknown Source) at tibcosoftware.jdbc.common.oracle.PreparedStatementWrapper.executeQuery(PreparedStatementWrapper.java:45) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:310) ... 3 more[/code]Cause :Example use case: You create a domain-based report in Jaspersoft Studio using Oracle as reporting database. The structure of the report(jrxml) is as follows: To keep it simple, I have selected one of the columns from a table and in the Filters section: I choose "STATE" where is one of and provided some pre-defined values as shown below And added “REGION” column which uses “is one of” and passed in a parameter. Where $P{region} is the parameter that was created in the Studio. Here is how the query from the text field tab looks like: <query> <queryFields> <queryField id="SPOTFIRE_DEMODATA_PHARMA.SALES_PERSON_ID"/> </queryFields> <queryFilterString> SPOTFIRE_DEMODATA_PHARMA.STATE in ('Alabama','Ohio','Virginia','Wyoming') and (SPOTFIRE_DEMODATA_PHARMA.REGION in (Region) or SPOTFIRE_DEMODATA_PHARMA.REGION == 'NULL')</queryFilterString></query>[/code] As you have noticed that parameter adds () around it, a preview of the report in the Studio works and brings back the result data set. When you publish the report to Jaspersoft server and try to view in JRS you see an error. As you can see, Jaspersoft Studio can properly parse the query and can execute it. However, Jaspersoft server cannot parse the query correctly and adds extra parenthesis around the parameter and thus result in the runtime error. Solution :A workaround would be to get rid of the parenthesis around the (Region) in the text field tab i.e. remove the parenthesis around the is one of Parameter. In our example: From: <query> <queryFields> <queryField id="SPOTFIRE_DEMODATA_PHARMA.SALES_PERSON_ID"/> </queryFields> <queryFilterString> SPOTFIRE_DEMODATA_PHARMA.STATE in ('Alabama','Ohio','Virginia','Wyoming') and (SPOTFIRE_DEMODATA_PHARMA.REGION in (Region) or SPOTFIRE_DEMODATA_PHARMA.REGION == 'NULL')</queryFilterString></query>[/code]To: <query> <queryFields> <queryField id="SPOTFIRE_DEMODATA_PHARMA.SALES_PERSON_ID"/> </queryFields> <queryFilterString> SPOTFIRE_DEMODATA_PHARMA.STATE in ('Alabama','Ohio','Virginia','Wyoming') and (SPOTFIRE_DEMODATA_PHARMA.REGION in Region or SPOTFIRE_DEMODATA_PHARMA.REGION == 'NULL')</queryFilterString></query>[/code] And publish the report JRXML to the Server. Now, you should be able to run the report on the JRS. Note: A defect has been reported on this issue. Our engineering team is investigating to find the root cause and fix this issue. When you make any changes to the Studio report please ensure that you removed the parenthesis around the parameter field before publishing.

In this example, we will be using a Java Bean Data source. Unlike, traditional data sources, a Java bean data source is a Java class with getter and setter functions of an entity. Let us say a Person Bean has attributes like Name and Age. Prerequisites to create a Java Bean Data source:Eclipse or NetBeans IDE Java Bean Factory Class that returns the collection/array of the Beans. Jar File creation (.Jar file) Creating the Data SourceFor this example, we use Eclipse IDE Step 1: Create a Java ProjectCall it CustomDS and click Finish. Step 2: Add Package and add a new PersonBean classAdd Package named as “bean” under src folder and add a new PersonBean class package bean;public class PersonBean { private String name=""; private int age = 0; public PersonBean(String name, int age) { this.name = name; this.age=age; } public int getage() { return age; } public String getName() { return name; }}[/code]Step 3: Add javabeanset package and add TestFactory classAdd javabeanset package and add TestFactory class by importing bean.PersonBean (import bean.PersonBean;) from step 2 package javabeanset;import bean.PersonBean;public class TestFactory { //CreateArrayList public static PersonBean[] generateBeanArray() { PersonBean[] list = new PersonBean[2]; list[0] = new PersonBean("John",22); list[1] = new PersonBean("Tom",23); return list; } //createCollection public static java.util.Collection genereateCollection() { java.util.Vector collection = new java.util.Vector(); collection.add(new PersonBean("ted",25)); collection.add(new PersonBean("tom",26)); collection.add(new PersonBean("john",27)); return collection; }}[/code]Step 4: Right-click on the Project in Eclipse and Select Export: To create a Jar file: Just follow the instructions on the IDE to generate a JAR file for this project. Let us call this jar file as “CustomDSjarfile.jar”. Step 5: Create a Java Bean Data Adapter in Jaspersoft StudioNow, Open Jaspersoft Studio > Repository Explorer > Create Data Adapter Wizard Name it to say “JavaBean-Array” and add the Jar file path to point to “CustomDSjarfile.jar” and then set Factory class to “Javabeanset.TestFactory” and then static method in the Factory to “generateBeanArray” and click the Test button. As you can see generateBeanArray() is a static method created in TestFactory in Step 3. Once information is valid, click finish to create JavaBean Data Adapter in JasperSoft Studio. Step 6: Create a JRXML report to use this Java Bean data source.In Jaspersoft Studio, Create a blank Jasper report; choose “JavaBean-Array” as a data source as created in the previous step. On Dataset and Query Dialog, Select Java Bean, and search for the class name bean.PersonBean to find all the available fields for the Bean and add them to the report. Note: Please add “CustomDSjarfile.jar” to your Project Build path via Add External JARS to search for field names. Add name and age fields to the report. When running a report, select “JavaBean-Array” data adapter to fill the report. Report execution output: Note: You can also test the Data Adapter with Collection class defined in TestFactory “genereateCollection” Report output: Please use this KBA as a reference only. References:https://community.jaspersoft.com/documentation/tibco-jaspersoft-studio-user-guide/v640/working-collection-javabeans-data-adapter https://help.eclipse.org/mars/index.jsp?topic=%2Forg.eclipse.jdt.doc.user%2Ftasks%2Ftasks-33.htm http://www.oxfordmathcenter.com/drupal7/node/44 https://community.jaspersoft.com/wiki/building-custom-code-eclipse customdsjarfile.jar javabean-array.xml javabean-collection.xml customds.zip javabeandatasource_example.jrxml

Use caseWhen working with chart dashlets in the Dashboard Designer, you can specify a link that opens when a user clicks the dashlet. You can optionally include a parameter, which references an existing filter in another dashlet/report, and uses that filter to display a specific web page relevant to the filtered information. Suggested approachThe following example takes you through adding a parameterized hyperlink to the Heatmap chart dashlet. Step 1:Create a source (Main report): Using sample Super Mart Domain provided with any JRS installation, I choose to use Sales data and create an Ad Hoc view for a HeatMap with following configurations: Columns: Brand (field), Units per Case (Measure) and Rows: Country (field) Step 2:Create a drill down report: Using the same Super Mart domain, we create another Ad Hoc view with the following configuration: Columns: Store Sales 2013(measure) and Rows: Product Name (field) Note: Create filters for “Brand” and “Country” since we will be passing “Brand” and “Country” values as parameters to this drill down report from the Heatmap(from Step1). Save this Ad Hoc view as a report (Let’s call it: HeatMap_DrillDownSample). We need to know the exact values (filter values) from this drill down report so that we can pass them as hyperlink parameters to the drill down report. There are couple different ways that you find the correct name for a filter in a target resource: Method 1:Open the Dashboard Designer in a new window, and add the resource to the canvas. Then expand the resource's folder in the Filters panel and mouse hover over the filter name you want to see as shown below: Method 2:Export the saved Ad Hoc view and open Ad Hoc view XML file (Brand_Store_SalesADHocView.xml file) and look for <name></name> tag under input control to find correct filter name value(s). You can get the filter name values from the Ad Hoc view XML document: Once we find correct values of the filters from the drill down report, we can configure the Heatmap drill down to child report via hyperlink parameters. Step 3:Create a Dashboard. Select HeatMap Ad Hoc view from Step 1. Right-click on the chart and select properties and hyperlink. Enable Hyperlinks > Open new page in the Web Address/Repository URI: Select the drill down report using the Browse button And at the end add ?{filter name for Brand}=${P} as per below syntax: repo:/{location}/HeatMap_DrillDownSample?sales__product__brand_name_1=$P{Brand}&sales__store__region__sales_country_1=$P{Country} Save the Dashboard. Here “HeatMap_DrillDownSample” is a drill down report that has been created from Ad Hoc view in Step 2. Now, when you open the Dashboard and click on any brand a new report opens up(Hyperlink) with filtered values for the selected Brand and country (parameters) applied on the drill down report. Example: Country: "USA", Brand: "American" will drill down to the following report below: References:https://community.jaspersoft.com/documentation/tibco-jasperreports-server-user-guide/v640/adding-hyperlink-chart-dashlet https://community.jaspersoft.com/documentation/tibco-jasperreports-server-user-guide/v640/working-jaspersoft-dashboards

Prerequisites:Download and Install Net Beans IDE: https://netbeans.org/ Download Jasper report Library: https://sourceforge.net/projects/jasperreports/ Download Org-apache-commons-loggin.jar, commons-digester.jar, commons-collections.jar and commons-beanutils.jar files and add them to the libraries. ProcedureStep 1: Create a Java ApplicationCreate a Java with Ant Application, Provide a Project Name and make sure to uncheck Create Main Class. Step: 2: Add a ButtonI added a button and renamed the text to “Run Report” Step 3: Add JAR file referencesAdd JAR file references to the application. Under Libraries > Add JAR/Folder and add all the JAR files from dist folder as shown below: Step 3: Download all the jar filesOrg-apache-commons-loggin.jar, commons-digester.jar, commons-collections.jar and commons-beanutils.jar files and add them to the libraries and add them to the libraries path Please see above screenshot as a reference to the libraries that I have added to my project. In addition, you need to add database JAR file to the project. Since I was using Postgres Data source, I downloaded the postgresql-42.6.0.jar file and added that to library. Step 4: Create the Report in TIBCO Jaspersoft® StudioIn Jaspersoft Studio, I created a simple report that selects fields from a table from MySQL Datasource. Select * from a table and I have added the fields to the report Saved it and exported to file. Say this file name is “Sample_report.jrxml” file. I saved this file to C:TempSample_report.jrxml on my desktop. Step 5: Create a Click EventI double clicked on the Button to create a click event Added the following code: try { Class.forName("org.postgresql.Driver"); try (Connection con = DriverManager.getConnection("jdbc:postgresql://localhost:5432/foodmart790", "postgres", "postgres")) { String reportPath = "C:TempSample_report.jrxml"; JasperReport jr = JasperCompileManager.compileReport(reportPath); JasperPrint jp = JasperFillManager.fillReport(jr, null, con); JasperViewer.viewReport(jp); } } catch (Exception ex) { ex.printStackTrace(); }[/code]Importing :import java.sql.Connection;import java.sql.DriverManager;import net.sf.jasperreports.engine.JasperFillManager;import net.sf.jasperreports.engine.JasperPrint;import net.sf.jasperreports.engine.JasperReport;import net.sf.jasperreports.engine.JasperCompileManager;import net.sf.jasperreports.view.JasperViewer;[/code]Step 6: Run reportFinally, when I click the play button, a window pops up that says Run report. When I click on the button. Jasper Report is compiled filled and printed in to the Jasper viewer. As shown in below screenshot: Note:You may run into a warning message while executing the report: Video example References:https://sourceforge.net/projects/jasperreports/ https://netbeans.org/ http://www.java2s.com/Code/Jar/c/Downloadcommonscollections321100jar.htm http://www.java2s.com/Code/Jar/c/Downloadcommonsdigester17jar.htm https://www.google.com/search?q=commons-collections-2.1.1.jar