Reports for NORD POS. Part 1
We take data, JasperReports and fill the template in iReport
This article is not so much devoted to how to do it beautifully from the point of view of design, but how to use the JasperReports tools to make reports clear to the user and convenient for further use by the integrator. Since JasperReports is not primarily an independent application, but an extension library for Java programs, we will use the database from NORD POS as a source of information for building reports (more about this in my separate project) At the same time, the material presented “under the cut”, I hope, will be interesting not only to users of my POS, but also to all those who want to learn more about the powerful reporting tool for their application, and there are not so many materials dedicated to JasperReports in Russian.
Download the framework
The basis for building any report using JasperReports is a template with the jrxml extension. This XML-format file can be created from scratch, both in a text editor, and assembled from elements (and in fact XML-tags) in the iReport visual shell . The iReport application is available as a plug-in for NetBeans or as a standalone program. For this article, I chose the second option, as more visual from my point of view.
The first thing to do after installing and starting iReport is to create a blank template sheet for subsequent filling with report elements ( File -> New ... command ). I chose an A4 format template located portrait.
In the future, you can change both the format of the sheet and the margins on it (command Format -> Page format ... ). And here you will encounter the first problem that will have to be taken into account in the course of all work with JasperReports, the inability to accurately set the values in millimeters familiar to us. You can precisely set only the value in pixels, with 72 pixels per inch, but in millimeters it will always be a fractional value of 25.4 mm. In a regular report from the columns, this is not very significant, but if you make a label template, then this approach to calculations will spoil your nerves, since you will only have a millimeter ruler at hand and you will need to experimentally select the number of pixels that will fit when printing on a blank of labels tapes. Here's how I got it:
The same empty XML report form
<band height="79" splitType="Stretch"/>
And these sizes will approximately correspond to the indentation from the top and bottom by 11 mm, 15 mm on the left, and 10 mm on the right.
Connect the source
If the report form template is the main visual component of JasperReports, then data sources are the internal basis of the processing and construction of the report. As a data source, can serve as a database or data warehouse, and for example, just table or text files. But in my case with NORD POS, the Apache Derby database connected via the JDBC driver will act as a data source. Since the Apache Derby client driver library is not added to the iReport environment by default, you must first specify the path to it in the parameters ( Tools -> Options command ) and restart iReport.
Initially, the selected source is empty by default.
But having entered the parameters, you can add it, making it a source of information to fill in the report fields.
By specifying the parameters of the JDBC driver and running NORD POS first, you can test the connection. And in order to make sure that the database fields are accessible by JasperReports, we will make a simple request.
This request, but in XML format
Our source is ready to use.
Writing a request
It was a very simple SQL query, as a result of its execution we get only a list of goods stored in the NORD POS database. But usually in reports you have to use more complex constructions. In addition to the names of the goods themselves, it would be nice to get the names of the categories of goods for the subsequent grouping of goods in our report. And, since the selling price of goods does not include tax, it is still necessary to get the tax rate for each item in the directory. As a result, for the further construction of the report, we will use an SQL query of this kind:
SELECT PRODUCTS.NAME AS PRODUCT_NAME , PRODUCTS.REFERENCE AS PRODUCT_REFERENCE , PRODUCTS.PRICESELL AS PRODUCT_PRICESELL , PRODUCTS.CATEGORY AS CATEGORY_ID , CATEGORIES.NAME AS CATEGORY_NAME , TAXES.RATE AS TAX_RATE FROM PRODUCTS LEFT OUTER JOIN CATEGORIES ON PRODUCTS.CATEGORY = CATEGORIES.ID LEFT OUTER JOIN TAXCATEGORIES ON PRODUCTS.TAXCAT = TAXCATEGORIES.ID LEFT OUTER JOIN TAXES ON TAXCATEGORIES.ID = TAXES.CATEGORY ORDER BY CATEGORIES.NAME, PRODUCTS.NAME
At the same time I want to warn you right away, when building SQL queries, try to avoid constructs in them to group data directly at the source level, but do not forget to sort the data first. It is better to entrust all operations related to the consolidation of information directly with JasperReports, so your report will be more universal for various DBMSs, and you will be able to change the sequence of processing algorithms for presenting data in different forms in the future.
As a result, fields from the data source will become available on the panel of the report structure.
And in the XML template a set of tags will appear
We post data
When the data is available, it's time to talk about how to place it in the report. To get started, simply place the received fields in the Detail section ; automatically, the labels for the fields will be placed in the header section of the Column Header .
In the XML template, columnHeader and detail sections will be generated.
Now, if we click on the preview, we will see our list of products from the database already in the form of a report.
The data have been received and the report has been generated, now you can start processing them and preparing the report for a more visual presentation of the information.
Let's start with the simplest, we’ll make the header and footer of our report. To do this, in the Title section, add a signature and a blue banner for the background, and in the Page Footer field with variable substitution for the number of pages.
Report title section title
<band height="36" splitType="Stretch"> <rectangle> <reportElement uuid="9fb6b6a2-ea0f-4f4d-b1a6-a31857059071" style="banner" x="0" y="0" width="525" height="36"/> <graphicElement> <pen lineWidth="0.0"/> </graphicElement> </rectangle> <staticText> <reportElement uuid="5b0c017a-ff0b-436d-a40c-80193711879f" style="title" x="2" y="2" width="365" height="32"/> <textElement> </textElement> <text><![CDATA[Товары на продажу]]></text> </staticText> </band>
PageFooter footer section
In order to make the reports uniform, I recommend using a set of styles, making one of them the default default. Styles are a group of fields at the beginning of our template, they set general parameters for designing report elements, and can be set not only for text elements, but also for shapes and lines included in the template.
Do the calculations
When creating templates in the variable section, several counters are automatically generated, their value changes depending on the position in the template. For example, we already used PAGE_NUMBER to count pages, using the same variable, but setting the parameter evaluationTime = “Report” for the second field, we got the total number of pages in the report.
To indicate the serial number of the item in the report, we use the COLUMN_COUNT variable , selecting it from the list and inserting the field value into the expression for calculating.
The value of the article PRODUCT_REFERENCE , since it contains only numbers and a minus sign as a separator between the group code and the product code, we place the generating bar code in a separate object.
jasperreports.sourceforge.net/jasperreports/components "xsi: schemaLocation =" jasperreports.sourceforge.net/jasperreports/components jasperreports.sourceforge.net/xsd/components.xsd "moduleWidth =" 1.5 "textPosition =" bottom "quietZone =" 10.0 »>
The field PRODUCT_NAME , PRODUCT_PRICESELL and TAX_RATE will be left as they were obtained from the database, only by specifying the patterns for PRODUCT_PRICESELL currency, and for TAX_RATE percent.
Fields PRODUCT_NAME, PRODUCT_PRICESELL and TAX_RATE
Last we have the calculation field for the amount of the price with tax.
Price calculation with tax
And finally, we will make two groups. First, we group our product range by category code, counting with the help of Categories_COUNT how many items are included in each.
Grouping an item by product category
The second is the final section of the Summary report , in it REPORT_COUNT will calculate how many total inventory items are included in our report.
The template is ready
That's it, you can click preview, our template is ready to generate a report.
Now you can begin to integrate it into NORD POS, but the second part of the article will be devoted to this . And in this part, I tried to maximally generalize and just talk about the basic steps for creating templates in iReport, so that described here is useful not only to NORD POS users, but to everyone who needs to create their own report for the first time for the JasperReports library.