create a report jasper

How to Generate Reports in a Spring Boot App Leveraging Jaspersoft

January 5, 2022.

We use reports in our day-to-day activities. We may use them when we are purchasing products, services, or managing transactions. This includes summary reports, financial reports, inventory reports, and budget reports.

These reports help in generating insights into an organization and making the right decision based on the knowledge generated from them. For example, an inventory report helps to decide on the appropriate reorder level.

In this tutorial, the reader will learn how to design an inventory report and how to integrate it into a Spring Boot application. The reader will use custom products to generate a standard report for use by an organization.

Table of contents

Prerequisites, create a spring boot application, add database configuration properties.

• Create a products model

Create a products repository to find all products by date

Create a report service to filter products by type.

• Design a report using jaspersoft template

Add the design to our spring boot application

• Add the jaspersoft dependency to our spring boot app
• Load and compile the Jaspersoft design

Create a folder to store the report

• Create a resource handler to display the generated pdf

Generate a report with the filtered products

Create a report controller, create a products page with date and file type fields, add a post mapping, test the application.

To follow along the reader will need:

• Knowledge in Spring Boot .
• Knowledge in Thymeleaf .
• Intellij IDEA installed on your computer.
• Jaspersoft Studio installed on your computer.
• A MySQL connector jar file.

On your browser, go to the spring initializr and create a new application. Add the dependencies: Spring Web , MySQL Driver , Thymeleaf , Lombok , and Spring Data JPA .

Extract the zip file to the desired folder and import the application in Intellij. Maven will download all our dependencies from the internet.

Add the following properties in the application.properties file. The main properties include the database URL, username, and password:

The spring.jpa.hibernate.ddl-auto property influences how the database tables are created and managed. The value create-drop tells Spring to create the tables on startup but drop them when the app terminates. The spring.jpa.show-sql property prints the queries generated by Hibernate so we know when errors occur.

The spring.jpa.properties.hibernate.format_sql property formats the SQL statements for readability. The spring.jpa.properties.hibernate.dialect property tells the application which SQL dialect to use.

Create a Products model

Create a class named Product with fields name , description , productType , price , and createdAt . The price is a BigDecimal , productType is an enum, and createdAt is a LocalDate .

The @Data annotation generates the getter and setter methods. This ensures we can display the list of products on our Thymeleaf page. The @NoArgsConstructor generates an empty constructor.

Create an interface named ProductRepository that extends JpaRepository . Then, create a method that returns a collection of products queried by date in the interface:

Create an interface named ReportService and add two methods. The first method returns the report link, given the date and the file format. The other method returns a collection of products from the database.

The JRException comes from the net.sf.jasperreports library which is added later in the application. This class will be thrown when there is an error when compiling or filling the report with our data from the database.

Design a report using Jaspersoft template

Let’s open JasperSoft studio. Before we create a design of the report, we will add the MySQL connector to the classpath. This will ensure we can connect to our database and generate the desired fields from our table.

In Jaspersoft studio, click on help , install new software , manage . Once you click manage a new panel named preferences opens. Use the search field provided by the panel to search for java . The search will return different results but in our case click on build path .

Under build path , click on classpath variables . The classpath variables provide us with the capability to add, edit, and remove variables using the new , edit , and remove buttons. Click on new and add the jar file on the window that opens with the desired name as shown below:

Add the MySQL connector and run the spring boot app. The spring boot app will generate the tables required by Jaspersoft studio. Create a design for our report from existing templates. On the Jaspersoft toolbar click on file , new , jasper report .

A window opens with a list of templates to choose from. For this tutorial, select the template named simple blue and click next. Add a new .jrxml file using the window that opens up. This is an extension for Jaspersoft report files. For our case name the file as products.jrxml and click next.

There are situations where Jaspersoft studio does not connect to the database by only adding the MySQL connector using the above steps. A ClassNotFoundException is caused by the software not being able to locate the MySQL driver class. To fix this error, we should add the MySQL connector in the driver classpath menu provided on the next window.

The window that opens next requires us to provide our data source. The data source is our database URL, username, and password to connect to our database. Click on new and on the data adapters window select database JDBC connection and click next. A window then opens where we need to fill the database connection properties as shown below:

Connect and write a query on the next window to select all the fields on the products table and click next:

The new window that appears displays all the fields from our database table. Add all the fields we want to appear in the report. Add from the data fields on the left side to the fields section on the right side. You can do this using the button with the greater-than symbol:

Click the Finish button to generate the final report. The report has our fields from the products entity as shown below. Edit the title and the description on the design header where necessary:

The design we have created generates XML describing the report structure. Get the code for it by clicking the source button on the bottom of the Jaspersoft design window:

Remove the properties in the field tags in the source file. Ensure the class and name properties of the field tag are the same as that of our product model:

The productType is an enum and it will throw a ClassCastException when we try to cast it to a string. To avoid this add a getter method in the products class that returns the string value of the enum.

Also, ensure that the text field expression in the details section is of the same name as that of our product fields.

Copy the entire source code in our Spring Boot application. Name the file products.jrxml in the resources package.

Add the Jaspersoft dependency to our spring boot app

The Jaspersoft dependency will help us in adding the functionalities. These functionalities include loading, compiling, filling, and generating the document. Add this dependency in the pom.xml file. Maven will download it for us and add it to the classpath where we can invoke its methods:

This dependency can be found from the maven central repository

Load and compile the Jaspersoft design source file in the report service

Create a class named ReportServiceImpl that implements the ReportService interface. Implement the generateReport() and findAllProducts() methods. The getJasperPrint() method handles the functionality of compiling the jasper report file.

This method accepts the collection of data that we use to fill the report and a string. The string indicates the location of the Jasper report source file. When run, the function returns an instance of JasperPrint :

In the ReportServiceImpl class create a method named getUploadPath() . Add the file format, a JasperPrint returned from the above method, and a file name as parameters. This method creates the specified directory if it does not exist. It also creates the generated PDF file in the folder with the file name passed to it.

We will also add a method named getPdfFileLink() that returns the link to our generated report. This method will take the file path returned by the getUploadPath() method as a string argument. We will use the two methods getJasperPrint() and getUploadPath() in the generateReport() method which currently returns null to generate our report.

Create a resource handler to display the generated PDF

Create a class named AppWebConfig that implements MvcConfigurer and override the addResourceHandlers() method. Create a method named uploadPath() that returns the absolute path. The application should allow requests to our PDF links using the string /generated-reports/** . Pass the absolute path returned by the method to the addResourceLocations() method:

We implement this functionality using the generateReport() method in the ReportServiceImpl class. This method accepts two parameters of type LocalDate and String . To implement it, first, inject the ProductsRepository bean. We will use it to search for products created on that specific day. Create a string containing the resource location of our source file, i.e, products.jrxml .

Then call the getJasperPrint() method and pass the collection of products. Create a string containing the name of the PDF report to generate i.e, products.pdf . Next, call the getUploadPath() method with the fileFormat parameter provided by the generateReport() method.

Now, return the value from the getFilePdfLink() method, passing the value returned by the call to getUploadPath() as a string. This method returns a link containing the path of our generated PDF file to our controller.

Our products page will display the list of products in the database. Ensure that the findAllProducts() method calls the findAll() method instead of returning null .

Allow any request to the root path and iterate through the list of products using a products model. Set the value of this model to be the return value of the findAllProducts() method of the ReportService :

The products page contains two sections. One has a form where we enter the date and select the document type to generate (which in our case is) a PDF document. These two values are request parameters. Leverage the th:name property as shown on the input and option tags of the form below:

When we press the generate report button of the above form a post request goes to the /report path. This URL corresponds to the generateReport() method of the controller. The @RequestParam annotation retrieves the date and the file format from the request.

It passes the values to the generateReport() method of our ReportService . Return a string containing the path to our generated PDF as we discussed earlier. We then issue a new request to the path by redirecting to it in the controller. The redirect allows us to display the report in our browser. Note that the resource handler must be available for this to work.

We need to populate our products table with dummy data for testing purposes. Add a CommandLineRunner with a list of products to create when our application starts up:

Run the Spring Boot application and visit the page at localhost 8080. Our page displays a form and a list of products:

Enter the current date, select PDF as the file type, and press the generate button. This redirects us to the generated PDF on a new browser tab as shown below.

Note that the dates will be different depending on the time you are reading this article.

We can play with the results using the current and previous dates. Some of the products created have LocalDate.now() to get the current date. Some of the products have LocalDate.now().minusDays(1) to get the date of the previous day.

In this tutorial, we have learned how to generate a PDF report using Jaspersoft studio. We have used an existing template and customized it to meet our needs. We have also learned how to load, compile, fill and generate the different types of documents.

Happy coding!

Peer Review Contributions by: John Amiscaray

Similar Articles

Languages, API

Creating a Nested Scroll Music Player App in Jetpack Compose

Integrating Razorpay in a React-Django Application

Implementing a Spring Query Language Spring Boot Search

Choose your language

Choose your login

• CustomReporting
• How-to-articles

Troubleshooting

• Known Issues
• Sales and Licensing
• End-user articles
• PaperCut NG and MF
• PaperCut Pocket and Hive
• Mobility Print
• Job Ticketing
• Print Deploy
• PaperCut MF release notes
• PaperCut NG release notes
• PaperCut NG
• PaperCut MF
• Knowledge Base

A How-To Guide to Custom Report writing with JasperSoft Studio and PaperCut NG/MF

Available to customers on:.

NOTE: For information on writing and running reports externally with other report engines, see Custom Reports With Other Report Engines ).

Introduction

PaperCut ships with a 3rd party reports engine called Jasper. Since PaperCut 19.1, it is possible to add custom created reports that are compatible with this engine to an instance of PaperCut

PaperCut inbuilt reports have used the Jasper engine for some time now.

PaperCut 19.1 adds the capability to run custom-written reports that are compatible with the Jasper engine.

This guide provides a brief overview of the process of writing a custom report.

We assume an understanding of SQL, and familiarity with the Jasper Studio report writing tool. It is also necessary to know which external database your organization uses, as SQL syntax varies slightly between the different RDBMS vendors.

You need to have hooked up your PaperCut deployment to an external database .

Jasper Studio

Jasper Studio Community Edition is a freeware product available from the JasperSoft website. As of PaperCut 19.1, we recommend using Jasper Studio 6.1.0, as this version matches the version of the JasperSoft report engine that is shipped with PaperCut.

Jasper Studio 6.1.0 can be downloaded here:

https://sourceforge.net/projects/jasperstudio/files/JaspersoftStudio-6.1.0/

Please ensure that you download the correct version for the platform you intend to use to write your reports.

Load example report into Jasper Studio

Let’s have a quick look at a report that was created by our tech services team.

Run Jasper Studio. You should see a work screen similar to the one shown above.

You can click on the File tab and select “Open File” to bring up a file selection dialog box and load one of the example custom reports.

The example reports are located here:

\PaperCut MF\server\examples\reports

\PaperCut NG\server\examples\reports

We’re going to look at the custom_shared_account_pcsf_breakdown _report .

There are three versions of each example report, each created to match a specific external database server.

Choose the version of that report that matches your database server.

Once you have loaded the report, you should see a work screen similar to the one shown above.

This gives an idea of what a completed report will look like.

If you click on the Source tab, you can browse the source code of the report.

Create a new report

Ok, now it’s time to create your own report. We’re going to go through the process of recreating the example report we looked at above.

This report was based on an actual customer request. In essence it takes the built-in Shared account print/copy/scan/fax - breakdown report and adds the current balance of each account as well.

We recommend keeping the example report open (in Jasper Studio you can have more than one report design open at once and easily switch between them) as a reference in case you have problems with any of the report creation steps below.

Adding a Data Adaptor, Dataset and Query

After completing this section you’ll have added Data Adaptor linking your report to the PaperCut database, and a SQL Query in the form of a “Dataset and Query”. This will insert “Fields” into your project that match the fields in your PaperCut database, an important step in sourcing the data you want to show in the report.

A “Field” in Jaspersoft Studio stores a type specific value and references a column outputted by the SQL query. After they’re added to your report you can position, format and process the fields in a number of ways. An example of a field would be “account_name” or “total_pages” or “usage_cost”.

The first step is to add what’s called a Data Adaptor to your report. 

TIP: JasperSoft’s Wiki on Data Sources in Jasper Reports is available here: https://community.jaspersoft.com/documentation/tibco-jaspersoft-studio-user-guide/v60/data-sources

Adding the Data Adapter to your Project

A “Data Adapter” is a connection Jaspersoft Studio makes to your PaperCut external database (MSSQL, MySQL, PostgreSQL or Oracle) from which the data is used to fill the report. This allows Jasper to know which fields are available for you to add to your custom report.

NOTE: The configuration for a Data Adapter is different for each database type. This guide will step through each type.

Start out by creating a Data Adapter using the Data Adapter Wizard in Jaspersoft Studio. 

TIP: Jaspersoft’s help article on creating Data Adapters is also a great resource for learning how to create data adapters. This could also come in handy for any troubleshooting. https://community.jaspersoft.com/documentation/tibco-jaspersoft-studio-user-guide/v640/creating-and-editing-data-adapters

Right click on “MyReports” in the Project Explorer pane, select “New”, and then select “Data Adapter”. A dialog box will appear asking you to name the data adapter file. Enter a name to describe the data source such as “PaperCut Database”, then press Next.

You will now see a list of Data Adapter types. Select the “Database JDBC Connection”. 

The “Database JDBC Connection” type can be used for all of PaperCut’s supported external databases. 

Next you’ll need to select a specific JDBC Driver that corresponds with your PaperCut configuration.

The specific JDBC driver to select will depend on the external RDBMS.

Microsoft SQL Server

Let’s start out with MS SQL Server. In this example we’ll select the “MS SQLServer (net.sourceforge.jtds.jdbc.Driver)” from the JDBC Driver dropdown. 

NOTE: Optionally you can also select the official MS SQL 2005–2012 JDBC Driver (com.microsoft.sqlserver.jdbc.SQLServerDriver). JasperSoft’s Instructions for using this driver can be found here: https://community.jaspersoft.com/wiki/getting-started-jaspersoft-studio-and-microsoft-mssql

This is the same driver that PaperCut NG and MF uses to connect to MS SQL server. Using this driver involves downloading the Microsoft JDBC Driver for SQL Server. Please refer to the instructions from Jaspersoft linked above.

Next, enter the JDBC Url. This is the path to the PaperCut SQL Database. The default entry is “jdbc:jtds:sqlserver://localhost/database” which has the layout of:

jdbc:jtds:sqlserver://[Server Address]/[Database-Name]

Enter the database username and password. 

TIP: If possible, use the same credentials the PaperCut Application Server uses to connect to the external PaperCut database.

To connect to your PaperCut PostgreSQL database, select the “PostgreSQL (org.postgresql.Driver)” option from the JDBC Driver dropdown. 

TIP: This is the same driver the PaperCut Application Server uses to connect to a PostgreSQL database.  

If your PaperCut Application Server is connected to a PostgreSQL database, the connection configuration for the PostgreSQL database is in the server.properties file under [app-path]\server\server.properties. You will be able to enter the same information in the Data Adapter Wizard to connect to the same PostgreSQL PaperCut database.

JDBC Driver: org.postgresql.Driver JDBC Url: jdbc:postgresql://[Server Address]:5432/[Database Name] Username: Database access username Password:  Database access password

To connect to your PaperCut MySQLdatabase, select the “MySQL (com.mysql.jdbc.Driver)” option from the JDBC Driver dropdown. 

TIP: This is the same driver the PaperCut Application Server uses to connect to a MySQL database.

If your PaperCut Application Server is connected to a MySQL database, the connection configuration for the MySQL database is in the server.properties file under [app-path]\server\server.properties. You will be able to enter the same information in the Data Adapter Wizard to connect to the same MySQL PaperCut database.

JDBC Driver: MySQL (com.mysql.jdbc.Driver) JDBC Url: jdbc:mysql://[Server Address]/[Database Name] Username: Database access username Password:  Database access password

Download and reference the MySQL JDBC Driver .jar.

To download the required version of the driver:

• Visit the MySQL web site download page for the MySQL Connector/J product here: http://dev.mysql.com/downloads/connector/j/ .
• Select the appropriate driver version (the latest version is best).
• Download the driver package and unzip the contents to a temporary directory.
• Find the driver JAR file, which is typically named mysql-connector-java-X.Y.Z-bin.jar.

You can reference this for Jasper via the Driver Classpath tab in the Data Adapter wizard as per the below screenshot.

To connect to your PaperCut Oracle database, select the “Oracle (oracle.jdbc.driver.OracleDriver)” option from the JDBC Driver dropdown. This driver requires a .jar driver to be downloaded from Oracle.

TIP: This is the same driver the PaperCut Application Server uses to connect to an Oracle database.

If your PaperCut Application Server is connected to an Oracle database, the connection configuration for the Oracle database is in the server.properties file under [app-path]\server\server.properties. You will be able to enter the same information in the Data Adapter Wizard to connect to the same Oracle PaperCut database.

JDBC Driver: Oracle (oracle.jdbc.driver.OracleDriver) JDBC Url: jdbc:oracle:thin:@[Server Address]:1521:[Plugged Database Name] Username: Database access username Password:  Database access password

Next download and reference the Oracle JDBC Driver .jar for the Data Adapter.

• Visit the Oracle web site here: http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html
• Select the appropriate Oracle version. The required file to download differs depending on the Oracle version.
• Download the relevant ojdbc.jar for your Oracle Database version.

NOTE: If you want your report to have a selectable date range, check out Providing a Date Range for Custom Reports

Please see JasperSoft’s help article on Data Adapters for help with common problems: https://community.jaspersoft.com/documentation/tibco-jaspersoft-studio-user-guide/v60/creating-and-using-database-jdbc-connections

Adding a Dataset and Query to your Custom Report

Now that we’ve created a Data Adapter for the external PaperCut database, a SQL query can be used as a data source to fill the new Jasper Report. 

We recommend testing and verifying the SQL query against your PaperCut database using a Database Management System before inserting it into Jaspersoft Studio.

The first step is to create the SQL query that your report will use. To help get started, we have made the basic SQL queries for all of the PaperCut inbuilt reports available for download from a GIT repository .

Depending on the data that you wish to include in your report, one of these queries should provide a good starting point.

In this case we want to create a custom version of the inbuilt Shared Account – Copy, Print, Fax, Scan report.

Here’s the basic query, for an MS SQL Server database. We have provided the query for all three supported RDBMS types in the appendices of this document. Select the version that matches your RDBMS.

CASE WHEN ta.sub_name != ‘ ’ THEN

END as account_name,

ta.sub_pin,

ta.balance,

tpul.job_type,

sum(tpul.total_pages) as total_pages,

sum(tpul.usage_cost) as usage_cost,

sum(tpul.total_color_pages) as total_color_pages,

sum(tpul.duplex_pages) as total_duplex_pages,

count(*) as total_jobs

JOIN tbl_account ta on ta.account_id = tpul.assoc_with_account_id

AND tpul.usage_date > @start_date

AND tpul.refunded = ‘N’

AND ((tpul.job_type = ‘PRINT’ and tpul.printed=‘Y’) OR tpul.job_type != ‘PRINT’)

ta.account_name,

ta.sub_name,

tpul.job_type

We will be able to see our Dataset and Query in action using this SQL query by following the steps below.

1. Right click on the Report Name in the Outline pane, and then click on “Dataset and Query”.

The following Dialog will appear:

2. Select your Data Adapter of choice from the Data Adapter drop down. The database metadata should appear in the left hand pane as per the below screenshot.

3. Paste the SQL query into the Texts pane. 

Once you’ve verified the SQL query, click on the “Read Fields” button. This will add the fields from the SELECT clause of the SQL query to the Fields section of the report.

4. Now click OK. The Fields will now be available for use in the Jasper Report if the SQL has run successfully against the Data Adapter and therefore the PaperCut database. The SQL will be ready to run as the reports data source.

NOTE: Please see Jaspersoft’s guide on “Registration of Fields from a SQL Query” for more guidance on the above steps.

https://community.jaspersoft.com/documentation/tibco-jaspersoft-studio-user-guide/v60/registration-fields-sql-query!jss-user-fields _3661870058_1024491

Back at the report design interface, to use the fields that have just been created, expand the “Fields” item in the Outline pane.

These “Fields” as shown in the above screenshot can now be referred to in your custom report. You will now be able to position, format and process these fields in Jaspersoft Studio to meet the requirements of your custom report.

Verifying a Report

To verify a report before adding it to the PaperCut server, you can run a report “Preview” in Jaspersoft Studio which will fill the Jasper Report with data from the SQL query. It is essential to test and verify the outcome of the Jasper Report customization before adding it to the PaperCut server’s list of custom reports. 

Previewing the report will compile the report in Jaspersoft Studio and populate it with data retrieved by the SQL query through your “Dataset and Query” with the “Data Adapter” connecting to your PaperCut database.

JasperSoft’s information on report previews https://community.jaspersoft.com/documentation/tibco-jaspersoft-studio-user-guide/v60/previewing-report!jss-user-basicreport _293982575_1024893

Creating the report

Now right click on “MyReports” in the Project Explorer pane, select “New”, and then select “Jasper Report”. A dialog box will appear with a number of templates to select from.

Select the “Blank A4 Landscape” template.

You should now have a screen that looks something like this…

You can see that a report is made up of a number of sections – Title, Page Header, Column Header, Detail 1 (essentially the body of the report), Column footer, Page footer, and Summary.

We’re not going to use the “Title” section of the report, so click on the lower border and drag it up to minimize the size of the “Title” section.

It should look like this…

You should be able to see a “Palette” panel at the top right of the screen. One of the components available in the palette is “Text Field”.

Click on the Text Field component to highlight it, and then move your mouse over to the “Main Report” panel.

By left clicking the mouse button, you should now be able to draw a text field wherever you wish on the report. Draw one inside the Page Header section, towards the top right.

Double click on the new text field, and type “Shared account printing - summary” in the dialog box that pops up.

We probably want the text to be a little larger, so click on the “Text Field” button in the “Properties” panel on the right, and change the font size to 20.

Add another text field below the first one with the text “Custom report with Shared Account balances”, to give the end user some more info about the report. You’ll notice the Page Header needs to be extended a little to fit our second Text Field, so go ahead and do that too by dragging the lower border of the Page Header down.

The final thing we’re going to put in the page header is some info about the specific instance of the report that they’re reading. This will be the first piece of dynamic information, generated when the report is run, that we’ve added to the report.

Add another text field to the page header below the second text field, and add this text to it:

“Report ran for the last 365 days from “ + NOW()

All the text between the quotation marks is static text that the Jasper engine will display verbatim, just like the other two text fields. The ‘+’ character tells Jasper to append whatever follows to the displayed text. ‘NOW()’ is a Jasper function that returns the current date and time at the moment that the report is run.

So, what we have done with the third text field is tell the user when the report was actually run, and what period of time it covers, which are both important pieces of context when reading a report.

The report should look something like this now:

Next we’re going to add some column headers. Scroll through the list of elements in the “Elements” panel at the top right of the screen until you find the “Rectangle” element.

Click on “Rectangle”, and then use the mouse to draw a rectangle in the “Column Headers” section of the report.

Make sure the new rectangle is selected, and then go to the “Appearance” section of the “Properties” panel.

Change the Forecolor and Backcolor to the RGB values 78, 91, 99. Secondly, go to the “Rectangle” tab and choose a Border Radius of 3. This will round out the edge of the rectangle.

Now we’ll add the text for the column headers.

Select the “Static Text” element from the “Elements” panel, and add a “Static Text” box inside of the Rectangle.

Let’s make the text white. Go to the “Appearance” tab in the “Properties” panel and change the forecolor to white (RGB 255, 255, 255). The “Static Text” box should have the “Transparent” check box ticked by default, so the color of the rectangle will show through and there is no need to set the backcolor.

Now click on the “Static Text” tab of the “Properties” panel and change the text to “Account name”. Then set the text to bold under the Font subheading.

The report design should now look something like this…

We need a few more column headers, so go ahead and make copies of your “Account name” Static Text box in the header band for the following column names:

Job type, Color pages, Greyscale pages, Duplex pages, Total pages, and Total cost.

The report design should now look like this…

Now we need to add a Group to the report. The Group will contain the records returned from the database by the report. Adding a Group will add a Group Header and a Group Footer to the report.

Make sure that no report elements are currently selected (you can do this by left-clicking outside the report area).

Right click anywhere in the Outline pane, and select “Create a group”.

Keep all the default settings and click on the “Finish” button.

You should now see “Group 1 Header 1” and “Group 1 Footer 1” sections in the report.

The fields that we’re going to be grouping are the Account name and the Account balance. When the report is run, each Group instance will represent one Account, and therefore we’ll want the Account name and Balance to appear only once per Group instance.

To achieve that, we add a Text Field for the Account Name, and another for the Balance, to the Group Header section.

In the Account name field, add the following text:

“Account: “ + $F{account_name} ’

Add another Text Field positioned next to the above field, and enter the following: :

“Balance: “ + $F{balance}

The “$F” and parentheses tell Jasper that the contents refer to a database field that will return a value at run time. In this case, the name of the account, and the balance of the account, respectively. These fields were defined in the Dataset and Query that we set up earlier.

The next step is to tell Jasper that we’ll be grouping and separating our results by Account. To do this, left click in the Group1 Group Header 1 section on the report, and then either:

type $F{account_name} in the Properties panel in the Expression field

Or, click on the icon next to the entry field for Expression. A dialog will appear where you can select “account_name” from the central pane.

Once you’ve done that, press Finish.

Now we’re up to the key section of the report. The Detail band is the section that will be repeated once for each job type that has been billed to the Account. This is where we’ll be presenting the bulk of our data to the reader.

We want each line to list the Job Type, and then the Color pages, Greyscale pages, Duplex pages, total pages, and total cost, for the job.

In order to achieve that, add six Text Fields in the Detail 1 band, spacing them to line up with the column headings you added in the Column Header band.

The contents for each Text Field are as follows:

$F{job_type}

$F{total_color_pages}

$F{total_pages} - $F{total_color_pages}

Note how we derive the number of greyscale pages by subtracting the color pages from the total pages.

$F{total_pages}

$F{usage_cost}

If you run the report as it now is, you should see the headers and a list of accounts, with a summary of the job types under each.

If it’s not working, you can compare your report to the finished example included with the product, and/or go back through all the steps (particularly setting up the Data Connector) to see what’s wrong.

Once everything is working, we can finish off the report by adding a page footer and an end summary.

In the page footer, we just want to show the current page number and the total number of pages in the report.

To do this, we’re going to use a neat feature of Jasper Studio.

Locate the “Page X of Y” tool In the Tools pane.

And here’s what it looks like once it’s been rendered.

Now we’re up to the final section of the report, the Summary band, where we shall put the totals for all the columns in the report.

First of all, we’ll add a text label. Add a Text Field in the Summary band, and set the text to “Report totals:”

Next we’ll look at adding some aggregated information to the group footer. The group footer will appear at the end of each Account section, showing us a list of totals for each of the fields we added to the Detail section.

For Color pages, Greyscale pages, Duplex pages, Total pages and total cost we’ll add a sum calculation to the Group Footer section.

To do this, in the Outline pane, expand the “Fields” sub-heading. Click and drag the first field we want to add the sum total for (total_color_pages) onto the Group Footer.

Once it’s dragged and dropped onto the Group Footer, a pop-up will appear asking about the calculation to perform. Choose “sum” to create a sum total for the total_color_pages printed on behalf of this Account.

Do this for each of the fields mentioned above and lay them out so that your report design looks something like this.

If everything has gone according to plan, the report should now be complete and working. Try it out by clicking on the Preview tab in Jasper Studio.

If there are any problems running the report, check that the connection to the database is still valid, and then go back through all the steps above to figure out what might be wrong. You could also compare the report directly to the example report file.

You can also add a section that contains a summary of all totals in the report to the end of the report. This is done in the same way as adding totals to the Group Footer, but we add them to the Summary section of the report instead. This will appear at the end of the report.

Now it’s time to install the report on your PaperCut server.

Installing the report is simple – save the report from within Jasper Studio, and then copy the .jrxml file to the following path beneath your PaperCut server folder:

For PaperCut MF:

PaperCut MF\server\reports\custom\jasper

For PaperCut NG:

PaperCut NG\server\reports\custom\jasper

After that it’s just a matter of navigating to the Custom tab of the Reports section in your PaperCut admin UI and you should be able to run the report from within PaperCut as a PDF or HTML.

• Tim Bentley (Product Owner, PaperCut Software)
• James Vinar (Technical Services Consultant, PaperCut Software)

Contributors:

• Damien White (Global Technical Services Manager, PaperCut Software)

PaperCut HQ www.papercut.com

Support [suppor[email protected]]( [email protected] ) www.papercut.com/support

Categories: How-to Articles , Reporting

Keywords: Custom , Report , Jasper , Create

Last updated July 5, 2023

• API Reference
• Ask the Mambu Community
• Contact Support
• Mambu User Guide
• Getting Started
• Managing the Mambu UI
• Managing your Organization
• Managing your Data
• Users and Access Control
• Clients and Groups
• Credit Arrangements
• Transactions and Interest
• Notifications
• Release Cycle and Compatibility
• Mambu Support
• 22 May 2023
• 5 Minutes To Read

Jasper Reporting Overview

• Updated On 22 May 2023

Jasper reports allow you to create custom reports that contain data from different Mambu database tables.

Following our migration from MySQL 5.7 to MySQL 8.0, you must update your existing Jasper reports. For more information, see Updating Jasper reports .

To keep track of future database changes, please follow our Release Notes in Community.

To create Jasper reports, you can use Jaspersoft Studio. For more information, see Creating Jasper reports .

Once your reports have been created in Jaspersoft Studio and you have the appropriate JRXML template, you may import this file to Mambu to display the Jasper report using data from various entities in Mambu. You can only import Jasper reports using the Mambu UI, you cannot import it using the API. For more information, see Importing Jasper report templates in Mambu .

• Jasper report templates and permissions

There are five permissions related to viewing, creating, editing, and deleting reports in Mambu:

• View Historical Data ( VIEW_INTELLIGENCE )
• View Reports ( VIEW_REPORTS )
• Create Reports ( CREATE_REPORTS )
• Edit Reports ( EDIT_REPORTS )
• Delete Reports ( DELETE_REPORTS )

With regards to reports, there is a Reports tab under the Administration menu item and there is a Reporting menu item on the main menu.

To view the Reports tab under Administration , you need at least one of the mentioned permissions assigned to your user - either directly or via a role.

To view the Reporting menu item on the main menu you need the View Historical Data and the View Reports permissions assigned to your user, either directly or via a role.

These permissions do not affect whether your user is able to view a particular Jasper report when they have been embedded in the relevant entities. This is managed by the Visibility section when you manage a specific Jasper report template. For more information, see Controlling access to reports below.

• Creating Jasper reports

To create your Jasper report templates, you will need to:

• Install Jaspersoft Studio and MySQL server. For more information on this setup, see Software Requirements .
• Link the MySQL database to Jaspersoft Studio so that all the data is available in Jaspersoft Studio. For more information on how to create an adapter between Jaspersoft Studio and MySQL Server, see Data Adapter .
• Download your current Mambu database and then import it into mySQL. For more information, see Database clone and Import Database clone .
• Importing Jasper report templates in Mambu

To import a Jasper report template in Mambu, you must first export the JRXML file from Jaspersoft Studio. Once you have this file:

• In the main menu, go to Administration > Reports .
• Using the Reports Type dropdown, select the entity for which you would like to create a report.
• Select Add Report .
• In the Importing New Report dialog, enter all the necessary information. For more information on the fields, see Fields for reports .
• Fields for reports
• Controlling access to reports

Access to a report is controlled by the Visibility settings you select when creating or editing the report.

If you select the All Users checkbox, then all roles will be selected and any user that has the appropriate permissions assigned to them either directly, or through a role will have access to the report.

If you do not select the All User checkbox then users will need to be assigned a role with appropriate permissions to allow them access to the Jasper report template. This role needs to also be selected in the list of roles. Users will not have access to the report if they have the permissions directly assigned to them.

• Editing, deleting, and rearranging reports

To edit, delete, or rearrange report templates:

• Using the Reports Type dropdown, select the entity for which you have created the report you want to edit, delete, or rearrange.
• To edit or delete a report, find the report in the list and select Actions and then either Edit or Delete . To rearrange the reports, select Rearrange Reports , and then drag and drop the reports into the order you prefer and select Save Changes .
• Downloading and previewing the report template

Jasper report templates are compiled before they are stored in the database, but the Jasper SDK is able to decompile reports into JRXML files. A Jasper report template will be decompiled before it is downloaded so that you can open it in Jasperstudio directly.

To download a report template:

• Using the Reports Type dropdown, select the entity for which you have created the report you want to download.
• Select the name of the report, which is a link, in order to go to the report detail page.
• Select either Download Report Template or Preview Report .
• Viewing reports

In order to view a report you have to go to the entity under which you created the report.

For example, if you create a report for the Clients entity, then to view the report:

• Go to the client detail page of any client.
• In the top right-hand corner select Reports , then select the name of the report you would like to view.

Viewing reports with report type Other

There is an additional Other report type option. To view reports with the Other report type:

• In the main menu, select Reporting > Other Reports
• In the list of reports, select the report you want to view.

What's Next

• MySQL 8.0 Update

On this page

• News & Events
• Mambu Process Orchestrator
• Mambu Community
• Marketplace
• [email protected]
• Privacy Policy

We value your privacy. We use cookies to provide the best site experience possible. By using our site, you acknowledge that you accept our Privacy Policy .