Friday, December 13, 2013

NextReports: Creating the report programmatically

NextReports can be used to create a report programmatically. In our company, there was a need to have a report automatically created starting from a sql query and then run it using the engine api. Just think you have a set of data and a simple template which informs about layout properties (fonts, colors, padding and so on) and you want to bypass the process of creating a report using the designer.

To achieve this you have to create a Report object.
  Report report = new Report();
  report.setName("Report File Name"); 
  report.setSql("select ..."); 
  report.setLayout(createLayout(columnNames, "Title"));
The only thing you have to do is to create the ReportLayout:
 public static ReportLayout createLayout(List<String> columnNames, String title) {

        ReportLayout reportLayout = new ReportLayout();
        int size = columnNames.size();

        // create a row in header band with a title cell spanned on the entire row
        Band headerBand = reportLayout.getHeaderBand();
        List<BandElement> titleRow = new ArrayList<BandElement>();
        BandElement titleElement = new BandElement(title);
        titleElement.setPadding(new Padding(1,1,1,1));
        for (int i = 0; i < size - 1; i++) {
        List<List<BandElement>> headerElements = new ArrayList<List<BandElement>>();

        // create two rows in detail band: 
        // first with column names and second with ColumnBandElement cells
        Band detailBand = reportLayout.getDetailBand();
        List<BandElement> headerNamesRow = new ArrayList<BandElement>(size);
        List<bandelement> headerFieldsRow = new ArrayList<BandElement>(size);
        for (String column : columnNames) {
         BandElement he = new BandElement(column);
         he.setPadding(new Padding(1,1,1,1));
            BandElement ce = new ColumnBandElement(column);
            ce.setPadding(new Padding(1,1,1,1));
        List<List<BandElement>> detailElements = new ArrayList<List<BandElement=>>();
        // create an empty footer row
        Band footerBand = reportLayout.getFooterBand();
        List<List<BandElement>> footerElements = new ArrayList<List<BandElement>>();
        List<BandElement> footerRow = new ArrayList<BandElement>();
        for (int i = 0; i < size; i++) {
         footerRow.add(new BandElement(""));


        return reportLayout;
This is the method used by "New Report" action from designer. You can choose any template properties to add on your BandElement cells.

After creating the report you can run it using the engine api or you can save it using  any ro.nextreports.engine.util.ReportUtil save methods.

Tuesday, November 19, 2013

NextReports Designer: View columns that have indexes

Even if NextReports Designer is a reporting tool and not a database tool, users asked to see what columns have indexes so they know if their queries are optimized or not.

From version 6.3 NextReports Designer will show indexed columns as an info icon

similar with those of primary keys and foreign keys. You can see this icon in "View Columns Info" action on any table:

You can also see this icon if you drag and drop a table in designer query perspective:

Monday, November 18, 2013

NextReports: Use reports to create portions of your web application

NextReports Server allows users to integrate charts & reports as iframes inside other applications using an embedded code.

If users do not want to use NextReports Server, there is also the possibility to use the generated reports inside iframes.

NextReports Server is implemented using Wicket framework. In Wicket , a simple document inline frame can be implemented to be used with an iframe tag inside html markup. This makes it very easy to use any Next report to show data from your database inside an application. After you have a NextReportPanel class like the one following, you do not need to write code to get particular data, you just have to create a report! and reuse this panel.

Lets say we want to see some report inside our web application.

The html file will look like:
        <iframe height="400px" wicket:id="report"></iframe>
Our Wicket component will be just a Wicket Panel. A ByteArrayResource just retrieves the report bytes which can also be cached if we want to. In this example we generate a HTML file:
public class NextReportPanel extends Panel { 
    private String reportName;
    // our object model (used to pass parameters to Next report)
    private Model model;
    // cache for report data per model
    private static Map<String, byte[]> dataMap = new HashMap<String, byte[]>(); 

    public NextReportPanel(String id, String reportName, IModel<String> model) {
        this.reportName = reportName; 
        this.model = model;
        add(new DocumentInlineFrame("report", new ReportResource()));
    private String getModelId() {
        String id = "";
        if (model.getObject() != null) {
            id = model.getObject().getId();            
        return id;

    class ReportResource extends ByteArrayResource {
        private static final long serialVersionUID = -6307719094949487807L;
        private byte[] data;

        public ReportResource() {
        protected byte[] getData(final Attributes attributes) {            
            String id = getModelId();
            data = dataMap.get(id);            
            if (data == null) {                    
                data = generateReport();                
                dataMap.put(id, data);                
            return data;
        protected void configureResponse(ResourceResponse response, Attributes attributes) {
        private byte[] generateReport() {            
            HashMap<String, Object> pValues = new HashMap<String, Object>();
            pValues.put("Id", getModelId());
            ByteArrayOutputStream output;
            Connection connection = null;
            Report report = getReport();
            try {
                output = new ByteArrayOutputStream();
                connection = ConnectionUtil.createConnection();
                return output.toByteArray();
            } catch (Exception e) {
                LOG.error(e.getMessage(), e);
                return new byte[0];
            } finally {

        private Report getReport() {
            InputStream is = getClass().getResourceAsStream("/" + reportName);
            try {
                return ReportUtil.loadReport(is);
            } catch (LoadReportException e) {
                return null;
This panel can be added in any page with different reports. To modify data (if users want to see more / less data) you will just have to modify the report! No source code modification!

Monday, October 21, 2013

NextReports: Search on google

In an older post we described what an External Drill is. What is not told there is that in  "Integration Settings" there is a property called "Drill Url". This is the base url used by all external links.

So we can set it for google web site:

Then, when we define an external  drill, we can put the relative url, in this case a search query for google site:

If no "Drill Url"is set inside "Integration Settings", the full url must be provided.

This means that inside our widget, when we click on second column (Employee):

 we will be redirected to a page with the results from google search:

Friday, October 11, 2013

NextReports: Some new things in 6.2 version

NextReports brings a few improvements in 6.2 version.

Cell formatting conditions allow to format that cell using an arbitrary expression value. Till now, only the value from that cell could be used.

You can see from the image above that user can choose between "Current value" of the cell and "Other value" which allows to enter an expression using any other report layout entities.

Report Layout can contain functions inside headers. Till now, functions could be added only in footers. So a rewritten Timesheet report for our demo will have the following layout :

and PDF result:

Server Alert definition can contain inside mail body the actual value for alarm or indicator. This must be specified through ${val} template string:

Wednesday, October 09, 2013

NextReports in an open source project!

This is a very important moment for NextReports. NextReports became an open source project. Engine and Designer were already free products and now are open source. But now, also, NextReports Server is free and open source.

You can find NextReports engine, designer and server on github. From there you can download sources, build the project and run designer and server.

NextReports Engine is found at You can read the README to find how to build the engine.

Programmers can use maven now to get NextReports engine in their projects:
A change was done in refactoring sources from com.asf.nextreports.* to ro.nextreports.*

NextReports Designer is found at You can read the README to find how to build and run the designer.

NextReports Server is found at You can read the README to find how to build and run the server.

Server url was changed from http://<host>:<port>/nextserver to http://<host>:<port>/nextreports-server.

Also, when upgrading you must know the following. If you need some older version history for your reports, please keep a copy of your server data before upgrading, to be able to download that version from repository using older NextReports Server. Because refactoring was needed for open source, in the new version you cannot see the older version history of your reports. As a best practice install the new version in other folder and set your server to connect to a copy folder of your repository.

We kept on NextReports site , as always, native installers for Windows and Linux and demo samples for integration.

Friday, October 04, 2013

NextReports Designer: Hidden Gems

Today someone needed to add a line break inside an expression, and to see the result inside different formats like HTML, PDF, RTF, EXCEL.  This means the line break must be considered even if the column width is big enough to fill the entire text on a single row, so "wrap text" property is not useful in such situation.

For HTML , adding a simple <br> tag does the trick. An expression like

$C_First_Name + "< br >" +  $C_Last_Name

will show the expression from a cell with the needed line break:

But for other formats like PDF, RTF and EXCEL the solution was not so obvious. Trying with "\n" had no effect. The needed character was the unicode representation for line feed: "\u000A":

$C_First_Name + "\u000A" + $C_Last_Name 

This works for all three formats:

Monday, September 30, 2013

NextReports Designer: Check for updates

Until version 6.2 , NextReports Designer could be checked for new updates using proprietary Install4J process. This allowed the users to see if a new version was available, to download it and to run the installer immediately.

From version 6.2 we want to make the designer to not depend on such proprietary process. Changing this means the designer has to read the last version from an URI and if this version is new compared to the current version, it gives the user a link to the NextReports download page.

Even this is more minimalistic than the existing wizard in previous versions, the benefits will be seen on a long-term NextReports evolution.

Wednesday, August 21, 2013

NextReports Engine: Integration Demo Hints

After you download nextreports-integration-demo from NextReports Site, you can run some sample code to see how to use engine api.

To make it easily for every java developer, from version 6.2, you can define a DemoDefinition class for your database. You can see such class for a Firebird connection, where you define database name, report /chart name, map of parameters and  database connection:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

public class FirebirdDemoDefinition implements DemoDefinition {

 public String getDatabaseName() {  
  return "Firebird Test";

 public String getReportName() {
  return "";

 public Connection createDemoConnection() throws ClassNotFoundException, SQLException {
        String url = "jdbc:firebirdsql:";
        System.out.println("Connect to '" + url + "'");
        return DriverManager.getConnection(url, "SYSDBA", "sysdba");
 public Map<String, Object> createDemoParameterValues() {
        Map<String, Object> parameterValues = new HashMap<String, Object>();
        return parameterValues;
 public String getChartName() {
  return null;

You just have to add your definition to DemoDefinitionFactory class and inside DemoUtil class change your NextReports home and your definition:
public static final String NEXTREPORTS_HOME = "D:\\Programs\\NextReports 6.2";
public static DemoDefinition def = DemoDefinitionFactory.get(DemoDefinitionFactory.FIREBIRD_DB);
This will help to test faster your JDBC driver compliance with NextReports.

Monday, August 05, 2013

NextReports: add your JDBC driver

Starting from version 6.2 NextReports will allow users to add their own JDBC drivers. We are using Vertica JDBC driver here as example. In version 6.2 Vertica driver will be added by default.

Such process can be resumed by following steps :

1. JDBC jar driver is added inside lib folder (designer and server). From 7.0, designer has a jdbc-drivers folder.

2. Driver must be added in driver_templates.xml which can be found :
  • inside designer in installation folder in \lib\nextreports-designer-6.1.jar 
  • inside server in installation folder in \webapps\nextserver\WEB-INF\classes 

3. You need to create a Dialect class for that type of driver. This dialect does a mapping between database types and java sql types and has some utilities methods.
import java.sql.Types;
import ro.nextreports.engine.util.ProcUtil;

public class VerticaDialect extends AbstractDialect {

    public VerticaDialect() {
     registerColumnType("binary", Types.BLOB);
     registerColumnType("varbinary", Types.BLOB);
     registerColumnType("bytea", Types.BLOB);
     registerColumnType("raw", Types.BLOB);
     registerColumnType("boolean", Types.BOOLEAN);
     registerColumnType("char", Types.CHAR);
        registerColumnType("varchar", Types.VARCHAR);
        registerColumnType("date", Types.DATE);
        registerColumnType("timestamp", Types.TIMESTAMP);
        registerColumnType("timestamp with timezone", Types.TIMESTAMP);
        registerColumnType("datetime", Types.TIMESTAMP);
        registerColumnType("smalldatetime", Types.TIMESTAMP);
        registerColumnType("double precision", Types.DOUBLE);
        registerColumnType("float", Types.FLOAT);
        registerColumnType("float8", Types.FLOAT);
        registerColumnType("real", Types.DOUBLE);        
        registerColumnType("bigint", Types.BIGINT);
        registerColumnType("smallint", Types.SMALLINT);
        registerColumnType("integer", Types.INTEGER);
        registerColumnType("int", Types.INTEGER);
        registerColumnType("tinyint", Types.INTEGER);
        registerColumnType("int8", Types.INTEGER);
        registerColumnType("decimal", Types.INTEGER);                
        registerColumnType("numeric", Types.NUMERIC);
        registerColumnType("number", Types.NUMERIC);
        registerColumnType("money", Types.NUMERIC);
        registerColumnType("time", Types.TIME);
        registerColumnType("time with timezone", Types.TIME);   
        registerColumnType("interval", Types.TIME);

    public String getCurrentDate() throws DialectException {
        return "current_date";
    public String getCurrentTimestamp() throws DialectException {
     return "current_timestamp";
    public String getCurrentTime() throws DialectException {
     return "current_time";

    public String getCurrentDateSelect() {
        return "select current_date";

    public String getRecycleBinTablePrefix() {
        return null;

    public String getCursorSqlTypeName() {
        return ProcUtil.REF_CURSOR;

    public int getCursorSqlType() {
        return Types.OTHER;

    public String getSqlChecker() {
        return "select 1";
4. You must register the dialect in NextReports. To make this happen in designer and server you have to add some java VM parameters. (nextreports.vmoptions  file from designer and start-nextserver.vmoptions from server)
-Dnext.dialect.database_1="Vertica Database" 

First parameter must be the name taken from DataBaseMetaData.getDatabaseProductName().

If you need to register more dialects, you use different suffix indexes.

Wednesday, July 17, 2013

NextReports Sight: an Android client for NextReports Server (Part Three)

NextReports Sight can show your widgets one by one by choosing them from a list after a dashboard was previously selected. If "Expand info" setting is selected user will see inside any action some information:
  1. logged user inside dashboards list panel
  2. selected dashboard inside widgets list panel
  3. widget title inside widget view panel
That info can be expanded / collapsed by clicking on it.

For flash charts and drill widgets, users can click to see the value or to go to the next chart or table in the drill chain.

Widgets will auto-size if phone orientation is changed between PORTRAIT and LANDSCAPE.

Previous Parts
Part One
Part Two

Monday, July 15, 2013

NextReports Sight: an Android client for NextReports Server (Part Two)

After NextReports Sight server settings are configured, user can connect to it using his server credentials. The following must be ok:
  1. phone must be able to connect to internet
  2. NextReports Server must be up and running
  3. credentials must be correct
If connection succeeds,  user will be presented with a list of all Dashboards that he has rights to see (personal and shared).

By clicking a dashboard, user will see the list of all widgets inside dashboard. This list does not contain collapsed widgets. Type of widget can be identified by the left icon between table, alarm, chart, indicator, drill and pivot.

Previous Parts
Part One

Wednesday, July 10, 2013

NextReports Sight: an Android client for NextReports Server (Part One)

NextReports Server has a lot of functionality, but most important are dashboards, reporting, monitor and scheduling. Taking the first feature, dashboards, it can be very useful to look at your personal data using a mobile phone. To make it possible, without zooming on the web page inside your browser, a new client application for Android systems was created. This is called NextReports Sight and is a new member inside NextReports Suite applications.

NextReports Sight first version is able to communicate with a NextReports Server in order to bring your widgets to an Android gadget in a more viewable and enjoyable form. When you start the application a login is shown.

User must define inside application settings how to connect to NextReports Server through some server properties like protocol, domain and port. From Menu, users can select Settings and then a new panel is shown.

By default the demo version of NextReports Server is configured. Also inside settings there are two other properties: first will enable sounds inside the application, second will show by default some information expanded or not.

Because we need to communicate  with a server, the api used by the Android client must have a version equal or less than the server version. Required server version can be seen using About action.

Tuesday, June 25, 2013

NextReports: WebService Authentication

NextReports Server offers a web service api to be used by different client applications. To be able to use any web service, users have to login with their credentials, otherwise no calls can be made to the api.

NextReports Server uses Jersey for REST web service implementation and Spring Security for authentication and authorization. To make authentication possible,  Jersey integrates with Spring through a special servlet defined inside web.xml:


All web service calls are mapped to a special url pattern /api/*  so to apply security a filter-mapping is added in web.xml:

Web Service client has a method to authenticate the user:
public boolean isAuthorized() throws WebServiceException
By default, Jersey has a big timeout value after a requests returns if no connection to the server is possible. All client applications need a smaller timeout, so a new method was added for this:
public boolean isAuthorized(int timeout) throws WebServiceException
where timeout is a value in milliseconds.

Monday, June 17, 2013

NextReports: Multiple Report

In general users want to insert a report inside another report to simulate a master-detail relation. To NextReports this means to insert a report inside the detail band of the master report, communication being done through a simple convention: the sub-report must have a parameter with the same name as a column from the master query.

This natural way (vertically data scaling) translates to generate a sub-report for every result from our query.You can read more about it here.

There are some situations when users want to scale their data horizontally and not vertically as in a master-detail form. Your query can become from too complicated to nearly impossible in achieving such thing. To make it easily possible, NextReports 6.1 introduced a new type of element inside report layout: Multiple Report.

Multiple Report element (FOR) contains a report with a special property: "multiple report sql" (FOR-SQL). The report must have a parameter with the same name as the column from FOR-SQL.

At run time,  this FOR element is replaced with a number of report elements. This number is the number of values returned by the FOR-SQL. A value from FOR-SQL will be passed to corresponding report's parameter.

To make it more clear, lets look at a simple example. We have a simple database with scores from EURO 2012 tournament. These scores are grouped on stages like Group First Game, Group Second Game, Group Third Game and so on until the Final. Inside database there are also kept the scores entered by some users. Any user guessed the result exactly, guessed just the prognostic 1/X/2 or did not guess anything and was rewarded with 3 points, 1 point, 0 points respectively. We want a report to show all the results as they happened and all the results entered by users with the rewarded points. We also want for every user to compute the total number of rewarded points per stage and per entire   tournament along with some other statistics.

To make an idea of what we want to accomplish I will start with showing you the results. You can see in the first image every stage with the games, scores, users scores and users rewarded points:

In the second image (the end of  report) you can see the statistics for the entire tournament:

This kind of report is a typical candidate for a "Multiple Report" usage. What we can observe by looking at the previous images is that report can be divided between to elements:
  • a stage element which shows the games from a stage and the scores

  •  a repetitive user element which shows the scores entered by user and the rewarded points

 So we need to create two reports. First one has just a grouping by stage (to show games and scores) and the labels for statistics section:

Second report has the same number of visible rows with the same fonts and padding for every corresponding row from the first report (We used some detail rows D1-D4 just to compute some expressions for statistics section, these expressions are hidden, and the detail rows D1-D4 are also hidden):

Second report also has a user parameter allowing us to run it for every user.

After that we can create a new report with the following layout (just two cells where we insert the previous reports):

We can see that the second report was added as a "Multiple Report" (FOR element). When we click the FOR element we can see it's properties with the new "multiple report sql". This query will return us the names of the users who entered scores for the tournament:

When you notice that you need a "Multiple Report" for your design, it is always easier (especially if you have a lot of rows) to create at first a single report which will show everything just for a single entity (user in our case).  Then you should cut it in two reports (left and right parts) and create the new report as showing before. In this way you are sure that all rows from the split parts have the same fonts and padding, so when you run the master report there will be no vertical shifts.

Tuesday, June 11, 2013

NextReports Designer: Favorites Menu

In NextReports 6.1 a new Favorites Menu can be found. Favorites are for NextReports what are Bookmarks for a browser. Practically, when a favorite is clicked, NextReports automatically connects to entity (report or chart) data source and the entity is loaded inside designer.

Any report or chart has a new action called "Add to favorites":

This action does just an addition to a list of entities which is stored in user home location. All favorites will be seen from Favorites Menu:

At the end of these links a "Remove favorites" action allows to manage favorites list.

Tuesday, May 14, 2013

NextReports Server: Data Migration

NextReports Server 6.1 will bring a new feature: data migration. Practically, a new section, visible only by administrators, is added.

From this section, administrator can export or import data. For data export we must select some entities like data sources, reports, charts and dashboards. The result will be a migration.xml file. There is no need to know which entities are used (referenced) by selected entities. All referenced entities will be automatically exported. Take a look at the following image:

We see that dashboard contains widgets. Widget contains a report or a chart. Report / Chart contains a data source and optionally links through drill-down to other reports or charts. So, by exporting a single dashboard, all entities that are involved will be exported too.

Any migration file can be used by an import process. When import is launched, a backup zip file of the entire repository is done. Any imported entity which has an id found inside repository will be updated. If id is not found, a new entity is created, taking care of name collision.

Migration process must not interfere with repository modification. Because of that, NextReports Server will show a maintenance page when export or import is running:

Thursday, May 09, 2013

Use NextReports for Information

When people think about reports they see almost every time analysis documents which aggregate their needed data in various formats. But reports can be anything else like simple information sheets. You can use data just to find answers to any domain you are interested in.

For example using a simple csv file with countries data we can find out needed information from a specific country like country code, phone code, capital, internet country code, currency and so on.

Query is done very easy with drag-and-drop designer's features. A Country parameter will allow to select information just for it:

A simple report will show us what information we are interested in:

Run it for desired countries and you are done.

This is just an example, but you can think at any csv data you can obtain live from the web (forecast, financial data). A simple (scheduled) process to get your csv file from web to a local storage is needed if you want up-to-date data. By comparing to saving to a regular database, such process does not need any programming!

Monday, April 22, 2013

NextReports Server: Settings per User

NextReports Server dashboards can contain any number and type of widgets like charts, tables, alarms, indicators, pivots. Any widget has a number of settings:
  • internal settings like refresh time, timeout, type (for charts)
  • parameters values
These settings can be modified and the state of widgets is changed for all users that have the right to see them.

Starting with 6.1 version a change will be done in the way these settings are kept.

If a user has write permission to a dashboard (dashboard was created by user or dashboard was shared to user with write permission) then these settings are stored globally and by changing them the global values are affected.

If a user has just read permission to a dashboard, user can still change the settings (in previous versions was not possible). In this case, the values for settings are kept just for the specific user. In this way it is possible for every user to have different settings for the same widget without altering the global values.

Monday, April 01, 2013

NextReports Server: Create your color theme

Starting from 6.1 version users can add their color theme to NextReports Server.

Color theme file must be named like theme-<color>.properties and must be added where the existing themes reside: webapps/nextserver/WEB-INF/classes/com/asf/nextserver/web/themes

All i18n files must contain a new property called:   Settings.personalize.theme.theme-<color>=<name of theme>

These color themes just change some foregrounds and backgrounds to make NextReports Server having the same chromatic as you desire.

There are also two images that are taken into account.

First is the actions image which is used in popups to decorate the actions menu and which you can define in theme files:


Second image is the Ok  image used inside table columns when everything is as should be. This is not defined inside theme files. You just make sure that an image with name tick_<name of theme>.png exists inside images folder, otherwise the default is used.

Tuesday, March 26, 2013

NextReports: Get data from text files

NextReports 6.1 will allow to create a data source for CSV files. Using a CSV JDBC driver , users will be able to create reports and charts simply by providing data inside a csv file. Data source url contains the directory path where csv files are found:

There are some special properties which can be edited using driver properties:

You are not restricted to csv files, you can use any text file in which data is separated through a special separator. Csv file may contain the columns names on the first row, but generally those names should be specified in driver properties. Here user can also define the types for columns choosing between Int, String, Double, Boolean and Date.

Lets say, as a simple example, we have a simple csv file like the following with balance accounts for some persons:


When we connect to the CSV data source we will see the csv files from that folder as tables and we can use them to create a report just like with any database data. The only restriction is that a query must be done on a single csv file. We cannot make joins between such tables.

We could make a simple report to compute the sum of balances:

We can also see that empty fields are correctly interpreted depending on the defined types: