NextReports: Single Table / Pivot Widget

NextReports Server can show inside a dashboard as many widgets as you like. But sometimes when you need all the space available, a single widget is desired. This may be the case when you have a table or a pivot widget.

To use as much space as possible dashboard must have a single column. Also dashboards tree may be hidden:

 

The problem in versions 7.3 and previous is the height of the widget which has a fixed value. This is good in case two or more widgets are found inside a dashboard:

But if a single table /pivot widget is found, it would be nice to have the height 100%. So, newer versions of NextReports Server will take this into account, allowing for full space expansion:

NextReports & MS Acccess

NextReports will be able to create reports on MS Access Databases from version which will come after 7.3. (it may be 7.4 or 8.0, time will decide)

The newly born Java 8 has pushed Java driver implementations in a good direction. Java 8 has removed the JDBC-ODBC Bridge functionality. Now, database vendors without a pure Java JDBC driver are forced to create such drivers.

UCanAccess is a relatively new open source JDBC driver which allows to read/write Microsoft Access database. NextReports is now using it.

A simple connection definition has to specify the full path to access database file:

A new dialect was added and users can now create Next reports as with any other supported type of database:

NextReports Designer: Name Pattern for Tables,Views and Procedures

Forthcoming release of NextReports will allow, after connecting to a data source, to set a 'name pattern' for Tables, Views and Procedures. This is useful in case you know the names of those entities you want to use in your queries reducing the time of loading. There may be thousands of tables in your database, so to have a filter is a nice addition.

'Name Pattern' is just a right-click action on Tables,Views and Procedures nodes from structure tree.

Users just enter a pattern using % sign (in this example we want all tables that start with BILL):

The Tables node will contain only filtered tables:

NextReports: Docx Exporter

NextReports can supply many export formats for generated files like PDF, HTML, EXCEL, RTF, XML, CSV, TSV, TXT. Starting from version 7.3 DOCX can be also used.

Generated docx files must be seen with Word Microsoft Office 2007+. Other free applications like LibreOffice or OpenOffice may not be able to correctly render the file, especially if features like "new page", "vertical merge", "html text" or "sub-reports" are involved.

Docx exporter has also some restrictions:

1. "Page number" variable can be used inside header or footer band, but alone. If you use more cells, they will be ignored.
2. "Total Page number" variable has no support.
3. "Background image" has no support.

Designer action bar contains now the newly exporter:

Sample "Timesheet" report, that comes with demo database, has the following result as a docx file:

NextCharts: Dual Y Axis

NextCharts can present any number of series on an existing chart. Chart type can be one of line, area, bar and even combo bar-line to allow for comparison results.

But there are cases when there it is needed to compare series with data of different order of magnitude. In such situations some series may look flat. To prevent this, NextCharts offers now the possibility to add a second Y axis.

Json definition may now contain 3 new properties:

• dualYAxis: true means second Y axis will be shown
• y2Count: this is the number of (last) series that will be represented on second Y axis (by default it is one)
• y2Legend: legend for second Y axis

 In next image it can be seen that the "Profit" line series is represented on second Y axis.

These properties will also be found in NextReports Designer's chart layout from version 7.3

NextReports: Excel Sheet Name

When a report is generated as Excel, sheets are named as "Page1", "Page 2" and so on. This is hard coded till version 7.3.

But starting with 7.3 version a new 'excel sheet name' property is used to address this issue.

If this property is left empty, sheets will have names like 1, 2, 3, ....

If user wants to use it, he has three options:

1. Use a static list of strings: This is useful if you know from start how many sheets you have. For example A;B;C;D will name the first four sheets in this order as A, B, C, D.  If there are more sheets than elements in the list, next sheets will have their page number as name.
2. Use a template  marker for page number ${NO}. For example Page ${NO} will name the sheets as Page 1, Page 2, Page 3, ...
3. Use a template marker for group name ${G1}. This is useful if you have a group and you set "new page" after the group finishes, so you have group specific data on different sheets. In this case the sheets will be named with the group name. If there are more sheets than groups, next sheets will have their page number as name.

NextReports Evolution

Analyzing comparative data for previous and current year shows a strong positive evolution regarding the number of downloads (designer&server) and the number of updates (designer) made by users. Actual values are not displayed here, being sensitive data.

Number of updates has tripled in 2014 compared to 2013.

Number of downloads of all NextReports products has doubled this year compared to 2013.

This is very good news and I hope this trend will continue in following years.

NextReports: New lines in Excel

NextReports Engine can select text data which may contain 'new line' characters like \r , \n or \r\n. These are well interpreted if report is generated in PDF or RTF.  For Excel there was no posibillity until version 7.3.

If you have a character column with 'new line' characters, these will be replaced by Excel exporter. Exporter will also compute how many rows are present to know and to update the excel cell height accordingly.

Because this is a just a particular case, Excel exporter will not look to all texts for finding 'new line' characters. User has to select 'wrap text' cell property to true, to make searching active.

Table Widget: Rows per page

Table Widget is just a simple component used by NextReports Dashboards. As all other widgets, it has two common properties:

• Refresh Timeout - how many seconds must pass until an auto-refresh
• Timeout - maximum number of seconds must pass until the database query is executed (otherwise a timeout exception is raised)

Sometimes, a table widget may contain more data and pagination is used with a default of 100 rows per page. This not just helps with visualization, but it prevents OutOfMemory errors if someone is

ill-intentioned and adds a table with millions of records inside a dashboard.

In general tables used in dashboards should have a small number of rows, but in case you really need a table with many rows, you are able from version 7.3 to set a "Rows per page" property.

 

This value can be set between 5 and 100 allowing to have a different pagination if business requires.

NextReports: HTML Text inside PDF/RTF

Sometimes data inside database may contain simple HTML which has some formatting using tags like < b >,< i >,< font >, < br >.  When we render to a HTML file, we will see data formatted as needed.

But it would be nice to have it also in PDF / RTF files. Starting with NextReports 7.3 this will be possible out-of-the-box. The only requirement is that data starts with < html > and ends with < /html > and contains a correctly formed HTML string.

Let's say we have a column in which data is HTML text like:

<html><b><font size=\"4\" color=\"blue\">Winters</font></b></html>

Then our PDF/RTF exported reports will look like the following:

If your column does not contain < html > and < / html > tags , you can create an expression and add them by yourself.

Display revisited

NextCharts 7.2 introduced a new type of report / widget called display. You can read about it here.

One thing that needs to be polished is title alignment. In 7.2 release title is aligned with value. This is good in all cases when value is big enough to be at least length comparable with title. But in case the value is small and title is longer, display does not look very nice.

Lets take a simple display example when the value is smaller than the title:

In such cases it would be nicer to center the title text. That's why we added a new property to NextCharts display making json containing titleAlignment with two possibilities: center or alignToValue. By default, alignToValue is selected if property is undefined. Choosing center for this property will make the widget looks as we desired:

When we define the report in NextReports Designer 7.3, title band element can have horizontal alignment property selected as center or left (right is not considered, generating inside json alignToValue).

Scheduler Template Fields

NextReports Scheduler allows to schedule reports at some moments in time.

Special kind of reports like Alarm,Indicator and Display can be scheduled to raise an alert if the corresponding value is in some limits. To show the actual value in the email sent, a special template field ${val} is used.

NextReports 7.2 will add more template fields which can be used in email's subject or body. These fields are:

${date}    for current date
${time}   for current time
${name}  for report name

NextReports: Report & Chart Internationalization

Starting from 7.2 version, NextReports will allow to have internationalized reports and charts. Users can define a set of string keys, a set of languages and the values for keys in every language. This is a special action found in Tools menu:

Only one language can be set as default. Default language is the one used when the report is run from designer. On the server side, Locale language set in Settings > General is used. If server Locale language is not defined inside report or chart, then the default language from report/chart is used.

The following things can be internationalized in reports & charts:

• static text
• parameters run time names
• layout cell's pattern property
• database column values (through expressions)
• chart title, description, legends and tooltips

After the keys are defined, users can use them between @@ markups.

For example, a report layout with internationalized title and header column names looks like:

Our date column cell has an internationalized pattern:

where our datePattern key can be MM-dd-yyy or dd/MM/yyyy or any date pattern is needed.

$E{Activity} expression is defined to allow for database strings internationalization:

Knowing your values for Activity_Code column allows to define the needed keys: WRK, MTG, TRV, ADM (as you can see in first image). This is possible, because internationalization for expression is always done after it's evaluation.

Any parameter can have it's runtime name internationalized:

A chart can have title, description, X legend, Y legend, column legends and tooltip internationalized. For tooltip, user can enter even more than one key. This is useful for bubble charts where you can have more information inside tooltips. Such string can be like the following:

#label
@@lifeExpectancy@@: #x
@@fertilityRate@@: #val
@@region@@: #c
@@population@@: #z

Using internationalized reports and charts will make server interaction more language-dependent. See below same dashboard in romanian and english:

NextCharts: Alarm Widget

'Alarm' is the oldest widget used by NextReports Server. It is just a status button with a specific color and a text. NextCharts 1.2 will contain alarm widget alongside indicator and display.

Being drawn on a HTML5 canvas, we can also add a shadow property for text and a simple animation. Json is very simple:

{
   "text": "Project delay is imminent! (Only 42 hours worked)",
   "color":"red",
   "background":"white",
   "shadow":true"
}

Now, NextCharts widgets is a bigger family:

Display Report and Widget

From version 7.2 of NextReports a new type of special report is added. This is Display, and you can read about it in previous post.

As other special reports, there is a convention of representing this report. A "display" report layout looks like:

Header row band contains a title (in this case with a parameter value) and two boolean values for shouldRise and shadow properties described in previous post.

Detail row contains one column value ($C{value}) or two column values ($C{prev}). Second column value is the previous one (on a previous period of time). In case we have two values, inside band detail we will also have two expressions:

- up : to tell how "display" arrow is:

This is just a comparison between current value and previous value.

- percent : to compute the previous value as a percent:

In our example we have a table with quarter values, and we use a parameter Quarter:

Our query obtains the current and previous values as two database columns:

In our query "value" column contains the actual value and "quarter" column is the quarter string Q1, Q2, Q3 or Q4.

This is the theory behind a "display" report. This type can be selected from report root node inside layout.

To create a "display" report, users will use the Wizard tool. First step is to select a "display" type:

Following step

Then, after data source and columns/query selection steps, user has to choose one or two columns:

Following step will allow user to define the report:

In our case, we enter the parameter inside title, we choose some colors and that our value rising is a "good" thing. By clicking finish we will obtain the layout shown at the beginning of this post. Automatically the generated report will have "display" type selected.

Going to server, after a "display" report publish, users are allowed to add such widgets to dashboards:

Although, server will allow to see "display" widgets in statistics and to search for this kind of reports.

NextCharts: Display Widget

A new widget was added to NextCharts html5 library: Display.

"Display" shows a value (without min/max boundaries like those used for Indicator) which is associated with a period of time. You can think as example for: Number of site visitors per day, Interest Rate per month, Profit per year.Although, this widget has the possibility to show how this value performed regarding a previous value (on previous period of time).

Json for such widget is like:

{
   "value": "2.354",
   "valueColor":"#004CB3",
   "title":"Visitors",
   "titleColor":"blue",
   "previous":"1.17%",
   "previousColor":"#c0c0c0",  
   "up":true,
   "shouldRise":true,
   "background":"white",
   "shadow":true"
}

Besides title, value and previous values and colors, we can have a background color and some boolean expressions:
- shadow: to show or not a shadow
- up: true for up arrow, false for down arrow
- shouldRise: true to indicate that a rise in value is a good thing, false to indicate that a lower value is a good thing.  For example: a rising number of visitors is good, a lower value of bounce rate is good.

Depending on last two properties we can have a green or a read arrow if the value evolves in the "good" direction or not.

NextCharts: Indicator

Indicators are special widgets that were added in 6.0 version of NextReports. You can read about them here.

Because NextCharts HTML5 library appeared in 7.0 version, it was time to move our indicator to NextCharts. So, NextCharts 1.2 will contain a new nextWidget function and this will be used by NextReports Server from 7.2 version.

A sample Json object for indicator is like:

{
     "title": "Balance",  
     "description":"monthly",
     "unit":"$",
     "value":200,
     "min":0,
     "max":1000, 
     "showMinMax":true,                          
     "color":"blue"             
 }

with following result:

You can view an HTML test in NextCharts github repository.

NextReports: Header Expressions with Functions

NextReports designer layout allows to insert functions and expressions.

Function is created on a column or on one existing expression.
Expression can contain columns, variables, parameters and functions.

Till version 7.2, expressions could contain functions, only if expressions were found in footer or group footer. Also functions could be inserted only in footers till 6.2 and also in headers from 6.2+.

From version 7.2, expressions in headers can also contain functions. To see this in action we create a Timesheet report with functions and expressions in headers (instead of footers, like in demo report).

Report Layout is as in following picture.

We can see in header band all the functions and also expressions that compute percent values from total. For example "Administration Hours Percent" is a simple expression like:

You can see that "Functions" selection is possible here.

Similar, in group G1 header we compute the percent of worked hours per project:

HTML result will show us computed values for expressions with functions:

NextReports Server: Auto open generated reports

Everytime you run a report in NextReports Server, a new message will popup when the report is done, showing the link of the artifact. User clicks that link and the report is opened with the default viewer. This is the normal way, especially if your reports take some time to run.

But there are some cases when all your reports are fast (instant running) and users want an option to automatically open generated reports. From version 7.1, settings contain a new property called "auto open". If this is checked,  after a report is run, the message will popup like in normal case, but the report will also be automatically open.

So, feel free to use this option.

NextReports: Generate an excel file based on existing template

NextReports 7.1 will introduce a new feature which will allow to generate an excel file based on one existing template file.

What does it mean? You may have an excel file with 2 (or more) sheets. One sheet is the actual data sheet and the others are computations made on values from data sheet like pivots for example.

Let's say you have a data sheet like:

and a pivot created on this data on a separate sheet:

This excel file is our template. By modifying data inside 'Data' sheet we will have by default this pivot in our resulted file. To make the pivot automatically refresh when we open the file, in the PivotTable Options dialog box, on the Data tab, select the 'Refresh data when opening the file' check box.

Now lets see how is this template used inside NextReports.

You create a simple report that prints the data in the form shown in 'Data' sheet. We have a simple query like:

 with a report showing just header and detail (no formatting needed):

Running such report as EXCEL will show us just a sheet (see previously Data sheet).

To specify that we have a template, some new properties were added on the Report node from layout tree:

 Here we can select a template excel file and the sheet number (starting from 1) where NextReports engine will write the data. In our example is sheet number 2. And that is all!

When you use some actions like Publish, Download from designer, the template file will be automatically passed between designer and server. On the server, when we do an upload, a new file upload field will allow to add a template. Also, Download and Edit in designer server actions will pass the template file to the client machine.

NextReports Survey

From some time ago, on NextReports site there is a small survey about the product. It can be found here.

From 7.1, to make people help us and also the entire NextReports open source community, we will show a small information about the survey on designer and server. This is not an intrusive message, once you click on the link or close the window, it will never show up again for that user.

Survey message will show after 10 days from installation, or , for server, if you use old data it may appear right away depending from how long logged user was created.

Inside NextReports Designer a small window will ask for taking the survey:

Inside NextReports Server a small message will notify the user on the top right corner:

If you want to contribute, please tell us on the survey wizard what are your needs.