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

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. We recommend using the Jasper Studio version which matches the version of the JasperSoft report engine shipped with PaperCut: PaperCut MF/NG versionJasper Studio version19.1 to 22.x6.1.0: https://sourceforge.net/projects/jasperstudio/files/JaspersoftStudio-6.1.0/ 23.0.0 and later6.20.5: https://sourceforge.net/projects/jasperstudio/files/JaspersoftStudio-6.20.5/  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: <server path>\PaperCut MF\server\examples\reports</server> Or <server path>\PaperCut NG\server\examples\reports</server> 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. 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)