Differences

This shows you the differences between two versions of the page.

--- gettingstartedwithjasperreports [2022/01/05 16:16] – [Global Report Query] wikiadmin
+++ gettingstartedwithjasperreports [2022/01/05 20:09] (current) – [Generate a Spreadsheet from SimpleReport] wikiadmin
@@ Line 14: / Line 14: @@
 You will need the SQLite driver jar included in the [[databasereferencecheatsheet#sqlite|SQLite]] distribution to run the examples.
-====Simple Report Example====
+====Simple Report Design and Test Example====
+This tutorial will show you how to design and test a simple table report in Jaspersoft Studio which uses a SQLite database for the table data.
 ===Jaspersoft Studio Startup===
@@ Line 22: / Line 24: @@
 ===Data Adapters===
-The workspace contains projects and data adapters. Data Adapters are the connections to databases (and sometimes other data sources) for the report. When you first start up Jaspersoft Studio you will see two items under Data Adapters: "Sample DB" and "One Empty Record". For the report examples we will be adding another Data Adapter for the SQLite database. To prepare for this copy the SimpleSQLiteTest.sqlite file and the SQLite driver jar to a convenient location on your system (with an easy directory path to remember and use, no spaces in the path elements). For these reports examples, these files are copied to C:/Projects/JasperReports.
+The workspace contains projects and data adapters. Data Adapters are the connections to databases (and sometimes other data sources) for the report. When you first start up Jaspersoft Studio you will see two items under Data Adapters: "Sample DB" and "One Empty Record". For the report examples we will be adding another Data Adapter for the SQLite database. To prepare for this copy the SimpleSQLiteTest.sqlite file and the SQLite driver jar to a convenient location on your system (with an easy directory path to remember and use, no spaces in the path elements). For these reports examples, these files are copied to C:/Projects/JasperReports (for the examples we are using a Windows system, but for a Linux system these paths are similar, such as using a project subdirectory in your home folder).
 To add the SQLite database as a Data Adapter, right click on Data Adapters and select "Create Data Adapter". You will see a popup with various Data Adapter choices. Select Database JDBC Connection.
@@ Line 116: / Line 118: @@
 <note important>
-Every time you make a change to the report design, the project should be saved. The project is automatically saved every five minutes by the Jaspersoft Studio application. Whenever you see a "*" in front of the project name in the workspace, the project can be saved at any time by pressing Ctrl-S.
+Every time you make a change to the report design, the project should be saved. The project is never automatically saved by the Jaspersoft Studio application. Whenever you see a "*" in front of the project name in the workspace, the project should be saved by pressing Ctrl-S, or using the main menu File->Save option.
@@ Line 122: / Line 124: @@
 </note>
+===Global Report Query===
+The Jasper Report "SimpleReport" will need a default SQL query which is executed when the report starts up. For this example project we do not need much information for the report, just a test statement. We will use the simple "SELECT * from data LIMIT 1" to just get a test result from the data table.
+Right click on the main report node "SimpleReport" in the Outline design view and select "Dataset and Query...".
+{{:pasted:20220105-145353.png?width=316}}
+The Dataset and Query panel will popup. Be sure that the exported connection "SQLiteTestDatabase.xml" is selected for the Data Adapter at the top. Enter "SELECT * from data LIMIT 1" into the query field to the right as shown in the image below.
+{{:pasted:20220105-145947.png?width=838}}
+Press OK to finish this setup and close the Dataset and Query Dialog panel.
 ===Adding a Table to the Simple Report===
@@ Line 219: / Line 235: @@
 It is recommended that you add your jasper report definitions to the same code repository you use for other projects so that you can recover the file and track changes as needed. The two files you should save for this project are the SimpleReport.jrxml and SQLiteTestDatabase.xml, and optionally the SQLite database.
 </WRAP>
+====Tutorial 2: Running the SimpleReport in MIStudio====
+This is an extension of the Tutorial 1: Simple Report Design and Test Example. If you want to skip Tutorial 1, you can get the set of files needed to run Tutorial 2 [[https://www.ergotech.com/files/examples/jasperreports/JasperReportsTutorial1FilesForTutorial2.zip|here]].
+The purpose of this Tutorial is to show you how to use the compiled SimpleReport JasperReport project built with Jaspersoft Studio in Tutorial 1 to generate a PDF report in MIStudio.
+===Setting Up===
+If you do not have MIStudio Plus Reporting installed please do this step first.
+You will need these files from the SimpleReport project in Tutorial 1: the compiled report SimpleReport.jasper, the database SimpleSQLiteTest.sqlite, the SQLite driver jar (i.e., sqlite-jdbc-3.30.1.jar or similar), and the exported data adapter definition SQLiteTestDatabase.xml. Put all of these files into the MIStudioSuite/MIStudio directory of your installation (so in the root of the MIStudio folder).
+You will need to edit the SQLiteTestDatabase.xml file in a text editor (Notepad++ or similar) to remove the hard-coded paths to the database file and the driver jar. It should look similar to this, with the correct name for your SQLite driver jar:
+<code xml>
+<?xml version="1.0" encoding="UTF-8" ?>
+<jdbcDataAdapter class="net.sf.jasperreports.data.jdbc.JdbcDataAdapterImpl"><name>SQLite Test Database</name><driver>org.sqlite.JDBC</driver><username></username><password></password><savePassword>true</savePassword><url>jdbc:sqlite:SimpleSQLiteTest.sqlite</url><database></database><serverAddress></serverAddress><classpath>sqlite-jdbc-3.30.1.jar</classpath></jdbcDataAdapter>
+</code>
+You will also need the DynamicJasper.jar in your MIStudio lib directory, which is installed with the Jasper Reports module for MIStudio.
+===MIStudio SimpleReport Project===
+Start MIStudio and create a new project, SimpleReport. Add a button to the design window and copy this to the diagram window. Label this button "Generate PDF". In the Diagram Window, add a JavaScript Bean. Connect the Button to the trigger of the JavaScript.
+Copy and paste this script to the JavaScript bean:
+<code javascript>
+//Script to generate a PDF report from SimpleReport.jasper
+var HashMap = Java.type("java.util.HashMap");
+var FileInputStream = Java.type("java.io.FileInputStream");
+var JasperPrint = Java.type("net.sf.jasperreports.engine.JasperPrint");
+var JasperFillManager = Java.type("net.sf.jasperreports.engine.JasperFillManager");
+var JasperExportManager = Java.type("net.sf.jasperreports.engine.JasperExportManager");
+compiledReport="SimpleReport.jasper";
+outputName = "SimpleReport.pdf";
+try {
+        reportStream = new FileInputStream(compiledReport);
+        parameters = new HashMap(); //empty hashmap  (no parameters)
+        jasperPrint = JasperFillManager.fillReport( reportStream , parameters);
+        JasperExportManager.exportReportToPdfFile(jasperPrint,outputName);
+} catch (e) {
+    print("Error in Run JasperReport script: \n"+e.stack);
+    print(e);
+}
+</code>
+Go to the Test Frame of MIStudio and press the "Generate PDF" button. You will see a pdf called "SimpleReport.pdf" generated in the same directory as the SimpleReport.jasper file (the root MIStudio directory).
+<note tip>If you see the error:
+net.sf.jasperreports.engine.JRRuntimeException: No secret found for "" key in "net.sf.jasperreports.data.adapter" category, you will need to add the JasperReports identity.secrets.jar https://www.ergotech.com/files/extra/identity.secrets.jar to the runtime classpath. This allows database connections to databases, such as the test database, which do not use passwords (clear passwords) </note>
+That's the end of this basic Tutorial. You can also generate other report types from this same SimpleReport.jasper file (see code examples below).
+===Generate a Spreadsheet from SimpleReport===
+Instead of generating a PDF you can make an xls or xlsx report (excel spreadsheets), or a csv file.
+To make an xls spreadsheet, use this code in the JavaScript bean:
+<code javascript>
+//Script to run SimpleReport.jasper as an XLS Excel Spreadsheet
+var HashMap = Java.type("java.util.HashMap");
+var FileInputStream = Java.type("java.io.FileInputStream");
+var JasperPrint = Java.type("net.sf.jasperreports.engine.JasperPrint");
+var JasperFillManager = Java.type("net.sf.jasperreports.engine.JasperFillManager");
+var JRXlsExporter = Java.type("net.sf.jasperreports.engine.export.JRXlsExporter");
+var SimpleOutputStreamExporterOutput = Java.type("net.sf.jasperreports.export.SimpleOutputStreamExporterOutput");
+var SimpleExporterInput = Java.type("net.sf.jasperreports.export.SimpleExporterInput");
+compiledReport="SimpleReport.jasper";
+outputName = "SimpleReport.xls";
+try {
+        reportStream = new FileInputStream(compiledReport);
+        parameters = new HashMap(); //empty hashmap  (no parameters)
+        jasperPrint = JasperFillManager.fillReport( reportStream , parameters);
+        xlsExporter = new JRXlsExporter();
+        xlsExporter.setExporterInput(new SimpleExporterInput(jasperPrint));
+        xlsExporter.setExporterOutput(new SimpleOutputStreamExporterOutput(outputName));
+        xlsExporter.exportReport();
+} catch (e) {
+    print("Error in Run JasperReport script: \n"+e.stack);
+    print(e);
+}
+</code>
+to make an xlsx spreadsheet, use this code in the JavaScript bean:
+<code javascript>
+//Script to run SimpleReport.jasper as an XLSX Excel Spreadsheet
+var HashMap = Java.type("java.util.HashMap");
+var FileInputStream = Java.type("java.io.FileInputStream");
+var JasperPrint = Java.type("net.sf.jasperreports.engine.JasperPrint");
+var JasperFillManager = Java.type("net.sf.jasperreports.engine.JasperFillManager");
+var JRXlsxExporter = Java.type("net.sf.jasperreports.engine.export.ooxml.JRXlsxExporter");
+var SimpleOutputStreamExporterOutput = Java.type("net.sf.jasperreports.export.SimpleOutputStreamExporterOutput");
+var SimpleExporterInput = Java.type("net.sf.jasperreports.export.SimpleExporterInput");
+compiledReport="SimpleReport.jasper";
+outputName = "SimpleReport.xlsx";
+try {
+        reportStream = new FileInputStream(compiledReport);
+        parameters = new HashMap(); //empty hashmap  (no parameters)
+        jasperPrint = JasperFillManager.fillReport( reportStream , parameters);
+        xlxsExporter = new JRXlsxExporter();
+        xlxsExporter.setExporterInput(new SimpleExporterInput(jasperPrint));
+        xlxsExporter.setExporterOutput(new SimpleOutputStreamExporterOutput(outputName));
+        xlxsExporter.exportReport();
+} catch (e) {
+    print("Error in Run JasperReport script: \n"+e.stack);
+    print(e);
+}
+</code>
+to make a csv file, use this script:
+<code javascript>
+//Script to run SimpleReport.jasper to generate a CSV file
+var HashMap = Java.type("java.util.HashMap");
+var FileInputStream = Java.type("java.io.FileInputStream");
+var JasperPrint = Java.type("net.sf.jasperreports.engine.JasperPrint");
+var JasperFillManager = Java.type("net.sf.jasperreports.engine.JasperFillManager");
+var JRCsvExporter = Java.type("net.sf.jasperreports.engine.export.JRCsvExporter");
+var SimpleCsvExporterConfiguration = Java.type("net.sf.jasperreports.export.SimpleCsvExporterConfiguration");
+var SimpleWriterExporterOutput = Java.type("net.sf.jasperreports.export.SimpleWriterExporterOutput");
+var SimpleExporterInput = Java.type("net.sf.jasperreports.export.SimpleExporterInput");
+compiledReport="SimpleReport.jasper";
+outputName = "SimpleReport.csv";
+try {
+        reportStream = new FileInputStream(compiledReport);
+        parameters = new HashMap(); //empty hashmap  (no parameters)
+        parameters.put("IS_IGNORE_PAGINATION", true);
+        jasperPrint = JasperFillManager.fillReport( reportStream , parameters);
+        csvExporter = new JRCsvExporter();
+        csvExporter.setExporterInput(new SimpleExporterInput(jasperPrint));
+        csvExporter.setExporterOutput(new SimpleWriterExporterOutput(outputName));
+        csvConfiguration = new SimpleCsvExporterConfiguration();
+        csvConfiguration.setWriteBOM(true);
+        csvConfiguration.setRecordDelimiter("\r\n");
+        csvExporter.setConfiguration(csvConfiguration);
+        csvExporter.exportReport();
+} catch (e) {
+    print("Error in Run JasperReport script: \n"+e.stack);
+    print(e);
+}
+</code>
+===Run the SimpleReport from TransSECS===
+You can trigger a script in TransSECS Devices with a CEID (Collection Event) or any other trigger to run the same reports as you can run in MIStudio and MIX. Copy the files listed in the setup section to your TransSECS project's resources directory. These files will be copied to the deployment when you build your TransSECS project.