PaperCut ships with a 3rd party reports engine called Jasper. Since PaperCut NG/MF version 19.1, it is possible to add custom created reports. PaperCut built-in 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. Download Jaspersoft Studio Community Edition version 6.x from: https://community.jaspersoft.com/download-jaspersoft/community-edition/jaspersoft-studio_windows_6.x
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.
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:
<server path>\PaperCut MF\server\examples\reports</server>
Or
<server path>\PaperCut NG\server\examples\reports</server>
Â
Next, weâll need to set our compatibility option. The instructions below outline the process for changing the settings globally, but this can also be done on a per-project basis by switching the radio button to Use Project Settings.
Go to Project, Properties
On the left go to Jaspersoft Studio, Compatibility
Select the Use Workspace Settings radio button, and click Configure Workspace Settings. In the new setting window, look for Version and select 6.20.6 from the dropdown list. (this may not be the current version)
Â
Now, 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/knowledgebase/getting-started/getting-started-jaspersoft-studio-and-microsoft-sql-server/
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.
PostgreSQL
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
MySQL
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.
Oracle
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.
You can reference this for Jasper via the Driver Classpath tab in the Data Adapter wizard as per the below screenshot.
NOTE: If you want your report to have a selectable date range, check out Providing a Date Range for Custom Reports
Troubleshooting
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.
declare @start_date datetime
set @start_date = DATEADD(YEAR,â1,cast(getdate() as date))
SELECT
âUse SQL functions to determine if a sub account was used. If it was, append it to the parent seperated by a â\â character. Return the results as a column called account_name
CASE WHEN ta.sub_name != âââ THEN
ta.account_name + â\â + ta.sub_name
ELSE
ta.account_name
END as account_name,
ta.pin,
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
FROM
tbl_printer_usage_log tpul
JOIN tbl_account ta on ta.account_id = tpul.assoc_with_account_id
WHERE
ta.account_type = âSHAREDâ
AND tpul.usage_date > @start_date
AND tpul.refunded = âNâ
AND ((tpul.job_type = âPRINTâ and tpul.printed=âYâ) OR tpul.job_type != âPRINTâ)
GROUP BY
ta.account_type,
ta.account_name,
ta.sub_name,
ta.pin,
ta.sub_pin,
ta.balance,
tpul.job_type
ORDER BY
ta.account_type,
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.
Drag it on to your report, into the Page Footer band like the below screenshot.
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.
-â
Authors:
- Tim Bentley (Product Owner, PaperCut Software)
- James Vinar (Technical Services Consultant, PaperCut Software)
Contributors:
- Damien White (Global Technical Services Manager, PaperCut Software)
Keywords: Custom , Report , Jasper , Create
Comments