NextReports - Stored Procedure Call

From version 2.2 of NextReports, there is the possibility to create a report from a stored procedure. To accomplish this, there is a syntax which must be used inside the query editor :

call <schema_name>.<procedure_name><(${P1}, ... , ${Pn}, ?)

where ${P1}, … , ${Pn} are procedure IN parameters. These can also be parameters defined for the query and will appear at runtime. The ‘?’ parameter is the cursor output parameter. For Oracle databases this is used. For other databases like MySql and MSSQL , the call must not contain ‘?’ parameter.

Inside this syntax , also the package (for Oracle) or catalog for other databases must be specified (if there are more procedures with the same name in different packages).

Oracle

call <schema_name>.<package_name>.<procedure_name>(${P1}, ... , ${Pn}, ?)

MSSql

call <catalog_name>.<schema_name>.<procedure_name>(${P1}, ... , ${Pn})

Not every stored procedure is a candidate for reporting. It can have any number of IN parameters. It must have an OUT parameter of type REF CURSOR (and only one) for Oracle. Must not have any other OUT parameters.

If some procedure parameters are also query parameters, they must be checked as ‘stored procedure parameters’ and a preview value must be set for them. This preview value must be used inside the procedure business to exit as fast as possible, making the column finding process as fast as possible.

The user can drag and drop a procedure from the explorer panel to the query editor panel. If the procedure is not a candidate for reporting a message will notify the user. If it is a candidate, the general syntax shown previously will be automatically written to the editor. The user will have to define the parameters if any.

The user can see in the explorer tree all the stored procedures. There is an action on Procedures node which will validate which procedures are candidates for reporting. This process will mark the candidates with another icon.

NextReports Import

From version 2.1 the user can import its data sources, queries and reports from an older version (starting with 2.0). If a data source with the same name already exists, the imported data source will have a name finished with the creation time stamp. This action is accessible from the Tools menu and also from the 'Quick Start' panel.


The difference between Import and Restore is that for Restore the current workspace (data sources, queries, reports) are not kept, but for Import all the new files are added to the workspace. Restore does some more things : it also overwrites the visible schemas and the new database drivers added by user.

NextReports 2.1

A new version of NextReports is just around the corner.
Besides resolving bugs and adding some eye-candy , this version brings also some new features.
The most important are :

1. - Support for an 'or criteria' in designer
2. - Undo / Redo for report layout actions
3. - Export to RTF
4. - Apply a template on any loaded report (not just through wizard)
   
5. - An action to restore docking layout to default

Also at startup a welcome panel will inform a new user about some actions to take in order to see what can be done with NextReports:

How to integrate NextReports

In your applications, running reports generated from NextReports is a very simple process to implement. The following lines are self-explanatory :

FileOutputStream stream = new FileOutputStream("test.html");
FluentReportRunner.report(report)
.connectTo(connection)
.withQueryTimeout(60)
.withParameterValues(createParameterValues())
.formatAs(ReportRunner.HTML_FORMAT)
.run(stream);

Rtf Export in NextReports 2.1

NextReports - Screenshots

That's the way a generated report (the demo report provided) looks in pdf, excel, html.

NextReports : A Reports Tale

Overview
Every business nowadays needs a way to generate a lot of reports. There are various software applications that try to fill this gap. Some are very powerful tools, but not as easy to learn and use. Every tool has its benefits and its drawbacks. Trying to create an application for report generation which is also simple to learn as it is to use was the first thing taken into account when NextReports began its journey.

So what is NextReports?
It is an Ad Hoc Query and Reporting tool. Its current version 2.0 was redesigned to allow for a better report layout and functionalities. NextReports can connect to popular databases like oracle, mysql, microsoft sql server, derby. But it also can be extended to connect to any database for which there is a jdbc driver. Reports created with NextReports can be exported in html, excel, pdf, csv and tsv.

NextReports is written in java and it can be used on any platform which supports at least java 1.5.Also a zip archive is provided for other operating systems. There are installers with or without the jre, so you do not have to worry if you have java installed or not. Just use an installer with jre and you're ready to start. It has only a few jars dependencies and so the installer has a small size (an installer without jre has about 6MB).

NextReports has a very easy learning curve. For that , an embedded data base was added to the application, and a demo report can be used to understand the main functionalities. A manual and some basic tutorials will guide you through all the aspects.

With NextReports creating reports can be a simple process. Even if you have no knowledge of what sql means, you can do your reports using a designer, only with drag and drop and mouse clicks. The layout has a grid structure and in contrast with an xy Layout (at pixel level) for other tools, the user can work with it as easy as in excel.

How to use NextReports
Practically, a full process consists of three steps. The first step is to create a data source. For this you have to know the parameters needed for database connection. (Figure.1) But if someone already did that, he can export the sources to a file and send it to you. With a simple import you can now use all the data sources. After you connect to a data source, you will be able to see all the tables , views, queries and reports. You can also select what schemas you are interested in.

Figure.1

Using a designer, you can drag and drop what tables you want, select the columns, create expressions. (Figure 2.) In this way a user creates a query which can be saved and used in the process of report creation. You can define parameters and use them in some criterias. A parameter can have a type (string, data, integer ,...) and a selection type (single, multiple). Depending on these, at runtime the user will see for parameter selection a combobox, a list , a calendar , a text field. A parameter can take its values from a database column, from the result of an sql select execution, or can be simply entered by user at runtime. A parameter can depend on other parameters and that will bring benefit in loading fewer values at runtime.

Figure.2

Starting from a query, the user creates a report and can modify its layout. (Figure.3) The layout is a grid structure, and every cell can be editted through its properties like font, background color, foreground color, allignment, padding, border, data pattern . Report layout allows user to add rows,columns, to create groups, to insert database columns, parameters, variables, functions, text.With the wizard, the report can be automatically formatted using templates.

Figure.3

So, NextReports will allow you to create reports in an enjoyable manner and in short time. Feel free to try it and see for yourself.

www.nextreports.ro