Custom Reporting

KB Home   |   Custom Reporting

Main.CustomReporting History

Hide minor edits - Show changes to output

August 23, 2019, at 10:16 PM by 207.173.252.92 -
Changed lines 22-23 from:
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.
to:
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.
Changed lines 82-83 from:
TIP: JasperSoft’s Wiki on Data Sources in Jasper Reports is available here:
to:
TIP: [=JasperSoft’s=] Wiki on Data Sources in Jasper Reports is available here:
Changed lines 88-89 from:
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.
to:
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.
Changed lines 96-97 from:
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.
to:
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.
Changed lines 110-111 from:
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. 
to:
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. 
Changed lines 114-115 from:
""""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:
to:
""""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:
Changed lines 132-139 from:
!!!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.
to:
!!![=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.
Changed lines 152-159 from:
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)
to:
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)
Changed lines 169-170 from:
Download and reference the MySQL JDBC Driver .jar.
to:
Download and reference the [=MySQL=] JDBC Driver .jar.
Changed line 173 from:
1. Visit the MySQL web site download page for the _MySQL Connector/J_ product here: [http://dev.mysql.com/downloads/connector/j/](http://dev.mysql.com/downloads/connector/j/).
to:
1. Visit the [=MySQL=] web site download page for the _MySQL Connector/J_ product here: [http://dev.mysql.com/downloads/connector/j/](http://dev.mysql.com/downloads/connector/j/).
Changed lines 184-185 from:
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.
to:
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.
Changed lines 190-191 from:
JDBC Driver: Oracle (oracle.jdbc.driver.OracleDriver)
to:
JDBC Driver: Oracle (oracle.jdbc.driver.[=OracleDriver=])
Changed lines 213-214 from:
Please see JasperSoft’s help article on Data Adapters for help with common problems:
to:
Please see [=JasperSoft’s=] help article on Data Adapters for help with common problems:
Changed lines 353-354 from:
JasperSoft's information on report previews https://community.jaspersoft.com/documentation/tibco-jaspersoft-studio-user-guide/v60/previewing-report!jss-user-basicreport_293982575_1024893
to:
[=JasperSoft's=] information on report previews https://community.jaspersoft.com/documentation/tibco-jaspersoft-studio-user-guide/v60/previewing-report!jss-user-basicreport_293982575_1024893
Changed line 359 from:
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.
to:
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.
August 22, 2019, at 06:24 AM by Tim Bentley - Minor formatting changes
Changed lines 86-87 from:
!!! **Adding the Data Adapter to your Project**
to:
!!! Adding the Data Adapter to your Project
Changed lines 98-101 from:
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. 
to:
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. 
Changed lines 108-109 from:
!!! **Microsoft SQL Server**
to:
!!! Microsoft SQL Server
Changed lines 114-115 from:
**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:
to:
""""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:
Changed lines 132-133 from:
!!!**PostgreSQL**
to:
!!!PostgreSQL
Changed lines 150-151 from:
!!! **MySQL**
to:
!!!MySQL
Changed lines 182-183 from:
!!! **Oracle**
to:
!!!Oracle
Changed lines 211-212 from:
!!! **Troubleshooting**
to:
!!!Troubleshooting
Changed lines 217-218 from:
!! **Adding a Dataset and Query to your Custom Report**
to:
!!Adding a Dataset and Query to your Custom Report
Changed lines 347-348 from:
!! **Verifying a Report**
to:
!!Verifying a Report
Changed lines 361-362 from:
Select the “Blank A4 **Landscape**” template.
to:
Select the “Blank A4 Landscape” template.
Changed lines 523-530 from:


<p id="gdcalert19" ><span style="color
: red; font-weight: bold">>>>>>  gd2md-html alert: inline image link here (to images/Custom-Reports18.png). Store image on your image server and adjust path/filename if necessary. </span><br>(<a href="!">Back to top</a>)(<a href="!gdcalert20">Next alert</a>)<br><span style="color: red; font-weight: bold">>>>>> </span></p>


![alt_text](images/Custom-Reports18.png "image_tooltip")

to:
Attach:Custom-Reports18.png
Changed lines 551-554 from:
Thank you


to:


Changed lines 560-564 from:
Tim Bentley (Product Owner, PaperCut Software)
James Vinar (Technical Services Consultant, PaperCut Software)


to:
*Tim Bentley (Product Owner, PaperCut Software)
*James Vinar (Technical Services Consultant, PaperCut Software)


Changed line 567 from:
Damien White (Global Technical Services Manager, PaperCut Software)
to:
*Damien White (Global Technical Services Manager, PaperCut Software)
August 22, 2019, at 06:19 AM by Tim Bentley - Minor edits
Changed lines 1-5 from:
A How-To Guide to Custom Report writing with JasperSoft Studio

(Note: For information on writing and running reports externally with other report engines, see [[CustomReportingWithOtherEngines]]

to:
A How-To Guide to Custom Report writing with [=JasperSoft Studio=]

(Note: For information on writing and running reports externally with other report engines, see [[CustomReportingWithOtherEngines|Custom Reports With Other Report Engines]]).

Deleted line 25:
https://sourceforge.net/projects/jasperstudio/files/JaspersoftStudio-6.1.0/
August 22, 2019, at 06:16 AM by Tim Bentley - Replaced with new guide for JasperSoft custom reports
Changed lines 1-35 from:
(:title Producing custom reports with PaperCut:)

PaperCut includes many built-in reports (over 60) for viewing printing history, data and trends.  These reports can be scheduled for automatic emailing, and can be combined with the many filter parameters and sorting options available to produce something more specific.

!!How to set up Custom Reporting

Learn how to integrate PaperCut MF and PaperCut NG's Custom Reporting with other reporting tools:

(:youtube jUt0vEG44kA:)

!!More functionality

If you require more specific reporting functionality there are several options available:

# [[https://www.papercut.com/contact/|Email support]] and let us know!  The report you're looking for may already be available, in which case our developers can provide advice about how to run it.  Ad-hoc report filters make it possible to produce many different report styles, and report data can be exported directly from most tabular data in PaperCut.  We regularly add new reports based on popular suggestions from our customers.

# Many reports contain extra fields/columns when run in CSV/Excel format (due to screen real estate not being an issue in spreadsheets).  After producing a report in CSV format additional manipulation can be performed from a spreadsheet application.  This method works best by running a report with a lot of data (such as a ''print logs'' report - one row per print job) then summarizing or grouping as required.

#Reports can also be run from the @@Logs -> Job Log@@ page by clicking on "Reports based on above data" at the bottom of the page. You can use the filters available on this page to get the data you want.

# If you're just after some cosmetic changes, check on the [[https://www.papercut.com/products/ng/manual/ch-customization-report-headers.html|report header customization option]].

# By deploying PaperCut on an external database the data can be accessed directly via SQL (see [[https://www.papercut.com/products/ng/manual/ch-ext-db.html | here]] for how to run PaperCut on an external database).  This can be used in combination with reporting tools such as [[http://en.wikipedia.org/wiki/Crystal_Reports|Crystal Reports]] or [[http://en.wikipedia.org/wiki/JasperReports|JasperReports]]  to produce custom reports.  There is an open and documented [[DatabaseSchema|database schema diagram]] to assist with this.  When PaperCut is deployed on Microsoft SQL Server some SQL views are set up and may be used to help build queries.  Report design can be complex and you will need some prior experience with these tools to be effective.

--->[-Note: You can find a detailed run-through of [[https://www.papercut.com/kb/Main/CustomReportingByExample|building custom reports with Crystal Reports]] as an example. Additionally you can refer to the [[https://github.com/PaperCutSoftware/PaperCutExamples/tree/master/Reports/Standard_PaperCut_Queries|repository of the SQL queries]] used in the PaperCut reports as starting points for your customized queries.-]

# If you have a suggestion for a new report which may be of benefit to our other customers (i.e. the report is general in nature) then we may be able to add it under our [[AdoptAFeature|adopt a feature]] program.  [[https://www.papercut.com/contact/|Contact us]] for details.

!!!See also
* [[ReportsFAQS|+]]
* [[https://www.papercut.com/products/ng/manual/ch-ext-db.html | Upsizing to external database]]
* [[DatabaseSchema|+]]

----
''Categories:'' [[Category.Reporting|+]]
to:
A How-To Guide to Custom Report writing with JasperSoft Studio

(Note: For information on writing and running reports externally with other report engines, see [[CustomReportingWithOtherEngines]]


!! 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.

At the current time attempting to use custom reports with the inbuilt Derby database that ships with the PaperCut product is not recommended – you will need to set up an external PaperCut database using one of the supported RDBM servers if you have not done so already.

!!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/
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



Attach:Custom-Reports0.png

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:

_<server path>\PaperCut MF\server\examples\reports_

Or

_<server path>\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.

Attach:Custom-Reports1.png

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.

Attach:Custom-Reports2.png

!!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](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. 

NOTE: Jaspersoft’s help article on creating Data Adapters. [https://community.jaspersoft.com/documentation/tibco-jaspersoft-studio-user-guide/v640/creating-and-editing-data-adapters](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. 

Attach:Custom-Reports3.png

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.

Attach:Custom-Reports4.png

!!!**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

Attach:Custom-Reports5.png

!!! **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


Attach:Custom-Reports6.png

Download and reference the MySQL JDBC Driver .jar.

To download the required version of the driver:

1. Visit the MySQL web site download page for the _MySQL Connector/J_ product here: [http://dev.mysql.com/downloads/connector/j/](http://dev.mysql.com/downloads/connector/j/).
2. Select the appropriate driver version (the latest version is best).
3. Download the driver package and unzip the contents to a temporary directory.
4. 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.

Attach:Custom-Reports7.png
 
!!! **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

Attach:Custom-Reports8.png

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

1. Visit the Oracle web site here: [http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html](http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html)
2. Select the appropriate Oracle version. \
The required file to download differs depending on the Oracle version.
3. 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.

Attach:Custom-Reports9.png

!!! **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.

https://github.com/PaperCutSoftware/PaperCutExamples/tree/master/Reports/Standard_PaperCut_Queries

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.

--Create a start date variable and use SQL math to determine a start date one year ago from today

declare @start_date datetime

set @start_date = DATEADD(YEAR,-1,cast(getdate() as date))

--Now the select statement...

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”.

Attach:Custom-Reports10.png

The following Dialog will appear:

Attach:Custom-Reports11.png

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.

Attach:Custom-Reports12.png

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.

Attach:Custom-Reports13.png

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.

Attach:Custom-Reports14.png

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

 Attach:Custom-Reports15.png

!!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…

Attach:Custom-Reports16.png

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…

Attach:Custom-Reports17.png

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.

<pic>

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()_

<pic>

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:

<pic>

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.

<pic>

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.

<pic>

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…

<pic>

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…

<pic>

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.

<pic>

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.

<pic>

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.

<pic>

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 Number” tool In the Tools pane. Drag it on to your report, into the Page Footer band. Next to it drag the “Total Pages” tool, also from the Tools pane. Arrange them so that they look like this:

<pic>

This looks a bit odd because it appears as though the same variable is being used for two purposes, but trust us!

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.



<p id="gdcalert19" ><span style="color: red; font-weight: bold">>>>>>  gd2md-html alert: inline image link here (to images/Custom-Reports18.png). Store image on your image server and adjust path/filename if necessary. </span><br>(<a href="!">Back to top</a>)(<a href="!gdcalert20">Next alert</a>)<br><span style="color: red; font-weight: bold">>>>>> </span></p>


![alt_text](images/Custom-Reports18.png "image_tooltip")


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

<pic>

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.

<pic>

Thank you



---



!!!! Authors:

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 \
[support@papercut.com](mailto:support@papercut.com) \
www.papercut.com/support \

June 29, 2016, at 02:42 AM by 139.130.165.134 -
Changed line 5 from:
!!How to set up Custom Rerporting
to:
!!How to set up Custom Reporting
June 29, 2016, at 02:42 AM by 139.130.165.134 -
Added lines 4-11:

!!How to set up Custom Rerporting

Learn how to integrate PaperCut MF and PaperCut NG's Custom Reporting with other reporting tools:

(:youtube jUt0vEG44kA:)

!!More functionality
June 16, 2016, at 04:06 PM by timg - Added links to custom reporting example KB
Added lines 16-17:

--->[-Note: You can find a detailed run-through of [[https://www.papercut.com/kb/Main/CustomReportingByExample|building custom reports with Crystal Reports]] as an example. Additionally you can refer to the [[https://github.com/PaperCutSoftware/PaperCutExamples/tree/master/Reports/Standard_PaperCut_Queries|repository of the SQL queries]] used in the PaperCut reports as starting points for your customized queries.-]
Changed line 11 from:
#Reports can also be run from the Logs -> Job Log page by clicking on "Reports based on above data" at the bottom of the page. You can use the filters available on this page to get the data you want.
to:
#Reports can also be run from the @@Logs -> Job Log@@ page by clicking on "Reports based on above data" at the bottom of the page. You can use the filters available on this page to get the data you want.
Added lines 10-11:

#Reports can also be run from the Logs -> Job Log page by clicking on "Reports based on above data" at the bottom of the page. You can use the filters available on this page to get the data you want.
Deleted lines 16-17:
# We can develop custom reports and provide them as plug-ins for your PaperCut installation.  This is the best option for reports that are specific in nature, or require a custom design.  The cost for most straight-forward custom reports is US$1000.  [[https://www.papercut.com/contact/|Contact us]] for details.
Changed lines 13-14 from:
# By deploying PaperCut on an external database the data can be accessed directly via SQL.  This can be used in combination with reporting tools such as [[http://en.wikipedia.org/wiki/Crystal_Reports|Crystal Reports]] or [[http://en.wikipedia.org/wiki/JasperReports|JasperReports]]  to produce custom reports.  There is an open and documented [[DatabaseSchema|database schema diagram]] to assist with this.  When PaperCut is deployed on Microsoft SQL Server some SQL views are set up and may be used to help build queries.  Report design can be complex and you will need some prior experience with these tools to be effective.
to:
# By deploying PaperCut on an external database the data can be accessed directly via SQL (see [[https://www.papercut.com/products/ng/manual/ch-ext-db.html | here]] for how to run PaperCut on an external database).  This can be used in combination with reporting tools such as [[http://en.wikipedia.org/wiki/Crystal_Reports|Crystal Reports]] or [[http://en.wikipedia.org/wiki/JasperReports|JasperReports]]  to produce custom reports.  There is an open and documented [[DatabaseSchema|database schema diagram]] to assist with this.  When PaperCut is deployed on Microsoft SQL Server some SQL views are set up and may be used to help build queries.  Report design can be complex and you will need some prior experience with these tools to be effective.
Changed lines 17-18 from:
# We can develop custom reports and provide them as plug-ins for your PaperCut installation.  This is the best option for reports that are specific in nature, or require a custom design.  The cost for most straightforward custom reports is US$1000.  [[https://www.papercut.com/contact/|Contact us]] for details.
to:
# We can develop custom reports and provide them as plug-ins for your PaperCut installation.  This is the best option for reports that are specific in nature, or require a custom design.  The cost for most straight-forward custom reports is US$1000.  [[https://www.papercut.com/contact/|Contact us]] for details.
Added line 21:
* [[https://www.papercut.com/products/ng/manual/ch-ext-db.html | Upsizing to external database]]
Changed lines 17-18 from:
# We can develop custom reports and provide them as plug-ins for your PaperCut installation.  This is the best option for reports that are specific in nature, or require a custom design.  The cost for most straightforward custom reports is US$650.  [[https://www.papercut.com/contact/|Contact us]] for details.
to:
# We can develop custom reports and provide them as plug-ins for your PaperCut installation.  This is the best option for reports that are specific in nature, or require a custom design.  The cost for most straightforward custom reports is US$1000.  [[https://www.papercut.com/contact/|Contact us]] for details.
Changed lines 15-16 from:
# If you have a suggestion for a new report which may be of benefit to our other customers (i.e. the report is general in nature) then we may be able to add it under our [[AdoptAFeature|adopt a feature]] program.  Most "list style" standard reports can be developed for as little as US$325.  [[https://www.papercut.com/contact/|Contact us]] for details.
to:
# If you have a suggestion for a new report which may be of benefit to our other customers (i.e. the report is general in nature) then we may be able to add it under our [[AdoptAFeature|adopt a feature]] program.  [[https://www.papercut.com/contact/|Contact us]] for details.
March 31, 2010, at 10:19 PM by 67.247.9.10 -
Added lines 11-12:
# If you're just after some cosmetic changes, check on the [[https://www.papercut.com/products/ng/manual/ch-customization-report-headers.html|report header customization option]].
March 31, 2010, at 10:17 PM by 67.247.9.10 -
Changed lines 11-12 from:
# By deploying PaperCut on an external database the data can be accessed directly via SQL.  This can be used in combination with reporting tools such as [[http://en.wikipedia.org/wiki/Crystal_Reports|Crystal Reports]] or [[http://en.wikipedia.org/wiki/JasperReports|JasperReports]]  to produce custom reports.  There is a [[DatabaseSchema|database schema diagram]] to assist with this.  When PaperCut is deployed on Microsoft SQL Server some SQL views are set up and may be used to help build queries.  Report design can be complex and you will need some prior experience with these tools to be effective.
to:
# By deploying PaperCut on an external database the data can be accessed directly via SQL.  This can be used in combination with reporting tools such as [[http://en.wikipedia.org/wiki/Crystal_Reports|Crystal Reports]] or [[http://en.wikipedia.org/wiki/JasperReports|JasperReports]]  to produce custom reports.  There is an open and documented [[DatabaseSchema|database schema diagram]] to assist with this.  When PaperCut is deployed on Microsoft SQL Server some SQL views are set up and may be used to help build queries.  Report design can be complex and you will need some prior experience with these tools to be effective.
March 31, 2010, at 10:16 PM by 67.247.9.10 -
Changed lines 3-4 from:
PaperCut includes many built-in reports for viewing printing history, data and trends.  These reports can be scheduled for automatic emailing, and can be combined with the many filter parameters and sorting options available to produce something more specific.
to:
PaperCut includes many built-in reports (over 60) for viewing printing history, data and trends.  These reports can be scheduled for automatic emailing, and can be combined with the many filter parameters and sorting options available to produce something more specific.
Changed lines 13-14 from:
# If you have a suggestion for a new report which may be of benefit to our other customers (i.e. the report is general in nature) then we may be able to add it under our [[AdoptAFeature|adopt a feature]] program.  Most standard reports can be developed for US$325.  [[https://www.papercut.com/contact/|Contact us]] for details.
to:
# If you have a suggestion for a new report which may be of benefit to our other customers (i.e. the report is general in nature) then we may be able to add it under our [[AdoptAFeature|adopt a feature]] program.  Most "list style" standard reports can be developed for as little as US$325.  [[https://www.papercut.com/contact/|Contact us]] for details.
Changed lines 11-12 from:
# By deploying PaperCut on an external database the data can be accessed directly via SQL.  This can be used in combination with reporting tools such as [[http://en.wikipedia.org/wiki/Crystal_Reports|Crystal Reports]] or [[http://en.wikipedia.org/wiki/JasperReports|JasperReports]]  to produce custom reports.  There is a [[DatabaseSchema|database schema diagram]] to assist with this.  When PaperCut is deployed on Microsoft SQL Server some SQL views are set up and may be used to help build queries.
to:
# By deploying PaperCut on an external database the data can be accessed directly via SQL.  This can be used in combination with reporting tools such as [[http://en.wikipedia.org/wiki/Crystal_Reports|Crystal Reports]] or [[http://en.wikipedia.org/wiki/JasperReports|JasperReports]]  to produce custom reports.  There is a [[DatabaseSchema|database schema diagram]] to assist with this.  When PaperCut is deployed on Microsoft SQL Server some SQL views are set up and may be used to help build queries.  Report design can be complex and you will need some prior experience with these tools to be effective.
Changed lines 11-12 from:
# By deploying PaperCut on an external database the data can be accessed directly via SQL.  This can be used in combination with reporting tools such as [[http://en.wikipedia.org/wiki/Crystal_Reports|Crystal Reports]] or [[http://en.wikipedia.org/wiki/JasperReports|JasperReports]]  to produce custom reports.  There is a [[DatabaseSchema|database schema diagram]] to assist with this.  When PaperCut is deployed on Microsoft SQL Server, some SQL Views are set up and may be used to help build queries.
to:
# By deploying PaperCut on an external database the data can be accessed directly via SQL.  This can be used in combination with reporting tools such as [[http://en.wikipedia.org/wiki/Crystal_Reports|Crystal Reports]] or [[http://en.wikipedia.org/wiki/JasperReports|JasperReports]]  to produce custom reports.  There is a [[DatabaseSchema|database schema diagram]] to assist with this.  When PaperCut is deployed on Microsoft SQL Server some SQL views are set up and may be used to help build queries.
Changed lines 15-16 from:
# We can develop custom reports and provide them as plug-ins for your PaperCut installation.  This is the best option for reports that are specific in nature, or require a custom design.  The cost for most straight forward custom reports is US$650.  [[https://www.papercut.com/contact/|Contact us]] for details.
to:
# We can develop custom reports and provide them as plug-ins for your PaperCut installation.  This is the best option for reports that are specific in nature, or require a custom design.  The cost for most straightforward custom reports is US$650.  [[https://www.papercut.com/contact/|Contact us]] for details.
Changed lines 11-12 from:
# By deploying PaperCut on an external database the data can be accessed directly via SQL.  This can be used in combination with reporting tools such as [[http://en.wikipedia.org/wiki/JasperReports|JasperReports]] or [[http://en.wikipedia.org/wiki/Crystal_Reports|Crystal Reports]] to produce custom reports.  There is a [[DatabaseSchema|database schema diagram]] to assist with this.
to:
# By deploying PaperCut on an external database the data can be accessed directly via SQL.  This can be used in combination with reporting tools such as [[http://en.wikipedia.org/wiki/Crystal_Reports|Crystal Reports]] or [[http://en.wikipedia.org/wiki/JasperReports|JasperReports]]  to produce custom reports.  There is a [[DatabaseSchema|database schema diagram]] to assist with this.  When PaperCut is deployed on Microsoft SQL Server, some SQL Views are set up and may be used to help build queries.
Changed lines 15-16 from:
# We can develop custom reports and provide them as plug-ins for your PaperCut installation.  This is the best option for reports that are specific in nature, or require a custom design.  The cost for most custom reports is US$650.  [[https://www.papercut.com/contact/|Contact us]] for details.
to:
# We can develop custom reports and provide them as plug-ins for your PaperCut installation.  This is the best option for reports that are specific in nature, or require a custom design.  The cost for most straight forward custom reports is US$650.  [[https://www.papercut.com/contact/|Contact us]] for details.

Comments

Share your findings and experience with other PaperCut users. Feel free to add comments and suggestions about this Knowledge Base article. Please don't use this for support requests.

Article last modified on August 24, 2019, at 02:53 AM
Printable View   |   Article History   |   Edit Article