Tuesday, August 18, 2015

NextCharts: Bar Charts with Negative Values

NextCharts has now support for bar charts with negative values. A new chart type called nbar was introduced. From NextReports 8.2 version this feature will be part of designer and server.

A simple bar chart with negative and positive values will show all values over X axis if chart type is bar:


If we just change the type from bar to nbar,  X axis will be considered at 0, so positive values will be shown over it and negative values will be shown under it:


In case all values for nbar are positive, then it will be no difference between bar with startingFromZero=true and a nbar chart.

In case all values for nbar are negative, then the chart will have all its values under X axis:


nbar chart can support as styles glass (as above) or normal:

Wednesday, August 05, 2015

NextCharts: Highlight selection

From version 1.5, in addition to tooltip message,  NextCharts allows to highlight selected element through a changed color. This version of library will be used by NextReports Server 8.2

There are different situations which were taken into account. For simple 2D charts like line, area, bar, pie, bubble, selected element (like bar, arc, circle) will have another color, lighter or darker depending on the brightness of the current color. For 3D charts like dome, cylinder, parallelepiped, selected element will be the 'visible' part of it and it needs some more drawing adjustments to show (after fill) all the lines and arcs that are supposed to be on top.

Let's see some examples of highlighting.

Bar highlighting:



Stackedbar highlighting:



Line highlighting:



Pie highlighting:




Cylinder highlighting with different area selection between top and other elements:




Tuesday, July 14, 2015

NextReports: Grid Style for Charts

NextReports charts can have a grid shown for x, y values. Till version 8.2, this grid can only be a full-line of a specific color. From version 8.2 this grid can also have style like line, dashed line or dotted line. This style is specified for both X and Y grid lines:


By default, simple line style is used:


The other two styles are dashed line:


and dotted line:


This feature is available on all modern browsers and it is supported from Firefox 27+, IE 11+.

Monday, June 22, 2015

Adjust your SQL queries to see data series

Many users have data in a specific table format with a 'type' column and want to show inside a chart series for every value of 'type'.

Lets take a simple example of a DAY table (where NAME column is our 'type') :
NAME               VALUE               CREATION_DATE
----------------------------------------------------------------- 
MORNING            90                  2015-06-12
NOON               210                 2015-06-12
EVENING            60                  2015-06-12
MORNING            85                  2015-06-13
NOON               240                 2015-06-13
EVENING            50                  2015-06-13
MORNING            92                  2015-06-14
NOON               235                 2015-06-14
EVENING            55                  2015-06-14

We want to show DATE on x axis and series for MORNING, NOON, EVENING on y axis. To make this happen NextReports must obtain a query result like:
CREATION_DATE     MORNING_V    NOON_V   EVENING_V
-------------------------------------------------------------------------------------------------
2015-06-12        90           210      60 
2015-06-13        85           240      50   
2015-06-14        92           235      55 

Then it is very easy to add DATE on x axis and MORNING_V, NOON_V, EVENING_V on y axis as column series.

This transformation can be easily obtained using sub-queries. For example, on our table we will use following sql:
SELECT DISTINCT
   a1.CREATIONDATE,
   (select VALUE from DAY a2 where a2.name='MORNING' and         
              a2.CREATIONDATE=a1.CREATIONDATE) as MORNING_V,
   (select VALUE from DAY a2 where a2.name='NOON' and
              a2.CREATIONDATE=a1.CREATIONDATE) as NOON_V,
   (select VALUE from DAY a2 where a2.name='EVENING' and
              a2.CREATIONDATE=a1.CREATIONDATE) as EVENING_V
FROM
DAY a1

Thursday, June 04, 2015

NextReports Server: JNDI Data Source

Starting with version 8.1, NextReports Server (war) can use JNDI protocol to connect to such data sources defined inside some web server.

A new type (JNDI) was added and users will just need to specify the JNDI name:




Tuesday, May 26, 2015

NextReports: Batch Mode

From version 8.1 users can run/schedule a report in batch mode. What does this mean?
If a report has a single-selection parameter with a source and if this parameter is selected as a "batch parameter" then running it in batch mode will generate a report for every value of that batch parameter.

To make this right there are two conditions that must be met:
  1. Batch parameter must not be set as dynamic
  2. All chained parameters that are children of batch parameter must be set as dynamic.
These two conditions are also written inside batch definition panel and are automatically set on save if user does not do it.

As an example, we use a report that shows the Timesheet for an employee on all projects he is assigned to. There are two parameters : Employee and Work code (work, meeting, administration, travel) so we can see the Timesheet for that employee for a specific list of work codes.


Here we see that the parameter we want to use in batch (Employee) is not set as dynamic, but the other parameter (Code) which is a child of Employee parameter is set as dynamic.

Batch Mode is practically a new step in run/schedule wizard process:


"Employee" parameter was set as a batch parameter. This simple selection means the report will be generated for all "Employee" values.

Second field, called Batch Data Query, is a simple sql select that gives an email address for every Employee. First column in query must be the same as that used in source for Employee parameter, which is defined as:

select distinct employeeid, firstname || ' ' || name as fullName from employee

Second column in query is the email column.

Batch Data Query is needed only if we need to send every generated report to it's actual employee.

Mail Destination Step in this case is allowed to not define a To property, because it is dynamically set at run time:


But we can also select a To property if we also want to send all generated reports to a static email address. In mail step, we can see that besides existing template values until version 8.1 (like ${date}, ${time}, ${name}) we can use parameter values as $P{Employee} or $P{Code}. This allows to have a mail message customized for every employee.

Thursday, May 21, 2015

NextReports Server: Connection Logging

Starting from NextReports 8.1 connection creation is written to log for debug purposes. By default all connections are written to connections.log found inside <server_install_folder>/logs folder. Default log level type is INFO

Inside <server_install_folder>/webapps/nextreports-server/WEB-INF/classes/log4j.properties user can change log level from INFO to DEBUG, to see information from the pool when a connection is created/used:

log4j.logger.ro.nextreports.server.util.ConnectionUtil=INFO, CONNECTION_FILE
log4j.additivity.ro.nextreports.server.util.ConnectionUtil=false

Users will be able to see on DEBUG level how many connections are in pool (min and max values), how many connection are active, idle or busy.

In case you experience some connections problems (like timeouts) and you are sure your database is up and running, you should put this log's level on DEBUG and start accumulating data to reproduce the problem again.

Tuesday, May 19, 2015

NextReports: Analysis versus Table Widgets

Even if a table widget inside a dashboard seems to be similar to an analysis table, there are many differences between them.

"Analysis" is a special object with following major differences than a widget table:
  • Analysis shows raw data (no patterns are used) and that makes the possibility to easily have sortable columns
  • Analysis can have a list of sortable columns (sort by column1, then column 2, ….)
  • Analysis persists sortable columns
  • Analysis has a lot of other features  like selection, grouping, filtering, pagination, freeze.
  • Analysis uses local data brought here by an ETL process
"Table Widget" has the following characteristics :
  • Table Widget has behind a report with a layout
  • Table widget may contain formatted data (if patterns are used like for dates, numbers,..)
  • The report behind a table widget may contain different type of values on same column (because report's layout can have more than one detail band). 
  • Table Widget may have a style (foreground and background colors)
From version 8.1 of NextReports Server :
  • Table widgets will have a header with sorting columns. 
  • Sorting is added only to those table widgets that contain only one detail band (the actual database columns). On all others table widgets sorting has no sense.
  • Sorting on table widgets is not persisted, so if you will leave dashboard and return or if you do a refresh the sorting will disappear. But if you sort, you are able (before refresh) to Save to Excel with the used sorting.

Thursday, May 14, 2015

NextReports: yAxis with 'starting from zero' property

From NextReport 8.1, Next Charts library will add a new property on yAxis called 'starting from zero'.

The minimum value on yAxis is computed so that all y values be some 'nice' numbers. This minimum value can be sometimes negative, sometimes 0 and sometimes positive. In case of positive numbers users may desire to start yAxis from zero  if computed minimum  value is bigger than zero.

Let's take following chart, for example, with values 44, 32, 34, 31. Computed min value is 30.


If users want to see the chart starting from zero, this is easily done by selecting 'starting from zero' property:


In this case computed min value is considered zero and the chart will look accordingly:


In case of negative values, this property has no effect.

Tuesday, May 05, 2015

NextReports Server: Internal Reports

Starting with NextReports 8.1 version a new section called Audit will be available.


Here, any administrator can obtain data from NextReports Server repository.

Just some examples of questions, users can get an easy answer:

What are the reports with write permission for group 'Test'?
What were the failed run reports yesterday?
What is the full list of Schedulers?

Users can sort the result and save it as an excel file.

Tuesday, April 28, 2015

NextReports Server: Audits at your hand

By default NextReports Server outputs some events to log files with following action names:
  • Sign in
  • Sign in failed
  • Sign out
  • Add entity
  • Modify entity
  • Delete entity
  • Copy entity
  • Move entity
  • Rename entity
  • Restore entity
  • Grant user / group
  • Revoke user / group
  • Run report
These events have some common properties like id, date, user name, action,session, ip, level (0=info, 1=error), error message. For some events there are also other properties. For example for "Run report" there are report path and duration; for "Grant user" there are entity path, user name to grant for, permissions and recursive flag.

NextReports Server has an extensible auditor feature. A database auditor can be defined to save all these events inside a database. This is very important because NextReports can generate reports over that database to get valuable information.

Inside securityContext.xml there is a bean called auditor:
 <bean class="ro.nextreports.server.audit.CompoundAuditor" id="auditor">
        <property name="auditors">
            <list>
                <ref bean="logAuditor"/>
            </list>
        </property>
</bean>
          

In this list, besides the log auditor we can add a database auditor:

<bean class="ro.nextreports.server.audit.CompoundAuditor" id="auditor">        
        <property name="auditors">             
           <list>
                 <ref bean="logAuditor"/>
                 <ref bean="dbAuditor"/> 
            </list>
        </property>
</bean>       
  
<bean class="ro.nextreports.server.audit.MySqlAuditor" id="dbAuditor">
         <property name="dataSource" ref="auditDataSource"/>
</bean>
   
<bean class="org.springframework.jdbc.datasource.DriverManagerDataSource" id="auditDataSource">
      <property name="driverClassName" value="com.mysql.jdbc.Driver"/>      
      <property name="url" value=""/>
      <property name="username" value=""/>
      <property name="password" value=""/>
</bean>

The database behind must contain two tables and a sequence:
  • NS_AUDIT (EVENT_ID, EVENT_DATE, EVENT_USERNAME, EVENT_ACTION, EVENT_SESSION, EVENT_IP, EVENT_LEVEL, EVENT_ERROR_MESSAGE)
  • NS_AUDIT_CONTEXT (EVENT_ID, EVENT_NAME, EVENT_VALUE)
  • NS_AUDIT_SEQ
As stated before, common properties will be saved inside NS_AUDIT, while specific event properties will be saved inside NS_AUDIT_CONTEXT.

For example, for a MySql database we need to call following sqls to initialize:

CREATE TABLE NS_AUDIT (
  EVENT_ID INT UNSIGNED NOT NULL, 
  EVENT_DATE TIMESTAMP, 
  EVENT_USERNAME VARCHAR(50), 
  EVENT_ACTION VARCHAR(30),
  EVENT_SESSION VARCHAR(100), 
  EVENT_IP VARCHAR(20), 
  EVENT_LEVEL  INT(2) , 
  EVENT_ERROR_MESSAGE VARCHAR(150)
) 

CREATE TABLE NS_AUDIT_CONTEXT (
  EVENT_ID INT UNSIGNED, 
  EVENT_NAME VARCHAR(50), 
  EVENT_VALUE VARCHAR(200)
) 
 
CREATE TABLE NS_AUDIT_SEQ( 
  EVENT_ID INT UNSIGNED  NOT NULL,
  CONSTRAINT event_pk PRIMARY KEY (EVENT_ID)
)

INSERT INTO NS_AUDIT_SEQ (EVENT_ID) VALUES(0) 

You should also create an index on EVENT_ID column from NS_AUDIT.

Tuesday, April 14, 2015

NextReports Server: What are the references?

NextReports Server contains (among others) a number of entities like :
  • data sources
  • reports & charts
  • schedulers
  • widgets
  • dashboards
Some of them are referenced by others. For example  any report or chart has a data source. That means a data source is referenced by that report / chart. What happens if we want to delete this data source? User will be informed the action cannot be done because there are other entities that use it.

The message is not very useful to users because if they do not know what are the entities, they must to search for them and delete those first (if the deletion is needed).

From NextReports Server 8.1, users will be informed about these entities with their full path. For example if we want to delete a report which was scheduled by a number of schedulers, the following message will be shown:

In case there are too many referenced entities, only the first 10 will be shown. In next image we want to delete a data source and we will see the list of reports and charts that use it:
In case a report or chart is used (referenced) inside a dashboard as a widget, it can be deleted and the user will see inside dashboard a message that the entity behind it was removed.

Thursday, April 02, 2015

NextReports Server: Dashboard Embedded Code

NextReports Server is able to offer dashboards page as an url like

http://localhost:8081/nextreports-server/app/dashboards

This will bring to users the following page, without header, footer and tabs menu:


From version 8.1 a new Dashboard Embedded Code action is available in dashboard popup actions:


This will give users the url for a specific dashboard:

http://localhost:8081/nextreports-server/app/dashboard?dashboardId=4f6cb7bd-c8df-4ffd-91a9-38c2c2c50b16


This url will show users the dashboard without navigator and buttons and will become the default way of how to show a full dashboard on a monitor screen for visualization.


Thursday, March 19, 2015

NextReports: Iframe new parameters

Starting from NextReports 8.1 iframes will be able to contain in their url some new parameters alongside width, height and report parameters. See this old post about iframe parameters.

Two of them are meaningful for table widgets: tableFontSize and tableCellPadding in points. These can be handy if you want to have an iframe on a big monitor screen and users need to look at it from distance. To show the difference you can compare following two screens:


The second one has tableFontSize=18 and tableCellPadding=10.

Another parameter, adjustableTextFontSize, has a boolean value and by setting it to true the chart from iframe will have texts from title, legends and axis values adjusted in concordance with iframe width. This parameter is automatically set to true on chart Detach action from NextReports Server.

A simple comparison can be seen here:


This example is shown for an iframe with width=800 and height=500 pixels.

Thursday, March 12, 2015

How to access NextReports Server web services in your application

NextReports Server has a number of web services. These can be found in ro.nextreports.server.api. NextReports uses Jersey web services so in any web service class users can see @Path and @QueryParam annotations to know how to create the url. All urls start with:
http://<server_ip>:<server_port>/nextreports-server/api

If users need to access these services using java code, then a client for these services already exists and can be found in ro.nextreports.server.api.client. Inside designer installation folder in lib there is nextreports-server-client-8.0.jar library which can be used. To see how it is used in designer  you can look in ro.nextreports.designer.wizpublish . Here is the code used to publish/download report/charts to/from server. (it involves also how to authenticate, to get list of entities from server, create a folder in server structure and more)

For example to authenticate you have to do the following with the root url previously shown:

WebServiceClient client = new WebServiceClient();   
client.setServer(url);
client.setUsername(userTextField.getText());
client.setPassword(new String(passField.getPassword()));
client.setPasswordEncoder(new Md5PasswordEncoder());

boolean authorized = false;
try {
      authorized = client.isAuthorized();            
} catch (Exception e) {
     // connection error
     e.printStackTrace();
     return false;
}

To get entities from server:

List<EntityMetaData> entities = client.getEntities(folderPath);

This method sets the server url path by appending to base api url "storage/getEntities/" and then the folder path. There are some root constants used to create folderPath like /reports, /charts, /dataSources.

If you want to call Next web services  from Javascript/JQuery and get reports from a path, you have also to be authenticated. Next example uses two javascript libraries:

Base64 encoder (jquery.base64.js) : https://github.com/carlo/jquery-base64/archive/master.zip
MD5 generation (core-min.js and md5-min.js) : https://code.google.com/p/crypto-js/#MD5

This example does an ajax get to obtain the list of reports from root /reports path. Request header contains an Authorization object. On success the list of entities (path and type) is shown in a html list.

<html>
    <head>
       <title>NextReports Server JQuery Test</title>
       <script src="jquery-1.10.2.min.js" type="text/javascript"></script>
       <script src="jquery.base64.js" type="text/javascript"></script>
       <script src="core-min.js" type="text/javascript"></script>
       <script src="md5-min.js" type="text/javascript"></script>
       <script>       
             var serverPath = "/reports";    
             var username="admin";
             var password="1";       
             var passwordHash = CryptoJS.MD5("1");
             var base64 = $.base64.encode( username + ":" + passwordHash );                
             $.ajax( {               
                    type: "GET",
                    url : 'http://localhost:8081/nextreports-server/api/storage/getEntities?path=' + serverPath,
                    dataType : "xml",               
                    beforeSend : function(xhr) {            
                            console.log(base64);
                            xhr.setRequestHeader('Authorization', 'Basic ' + base64);
                    },        
                   error : function(xhr, ajaxOptions, thrownError) {
                            console.log("error");            
                            alert(thrownError);
                   },
                   success : function(data) {            
                            console.log("success");            
                            console.log(data);        
                            $('#title').append(serverPath);     
                            var paths = data.getElementsByTagName("path");
                            var types = data.getElementsByTagName("type");
                            var root = $("#list");
                            for (var i=0; i<paths.length; i++) {
                                  var type = "";
                                  if (types[i].textContent === "1") {
                                      type = "folder";
                                  } else if (types[i].textContent === "3") {
                                      type = "next-report";
                                  } else if (types[i].textContent === "4") {
                                      type = "jasper-report";
                                  }                  
                                  root.append('<li>'+paths[i].textContent+ ' (' + type + ')</li>');
                           }
                   }
         });
    </script>
    </head>    
    <body>
        <div>
            <span id="title">List of reports from path: </span>
            <ul id="list"></ul>
        </div>
    </body>

To be able to run this example you must be in the same domain as server, otherwise a cross domain exception will be seen in console. From NextReports Server 8.1 inside web.xml for jersey.springServlet you can uncomment the following to allow for cross domain testing calls (please be sure to comment it back after you make your tests):

       <init-param>
            <param-name>com.sun.jersey.spi.container.ContainerResponseFilters</param-name>
            <param-value>ro.nextreports.server.web.core.ResponseCorsFilter</param-value>
        </init-param>       

Tuesday, March 03, 2015

NextReports Server: Analysis

NextReports Server 8.0 has a new module (section) called Analysis. To be able to create an analysis, users must run/schedule an ETL (extract transform load) process which gets data from a data source and brings it locally to A NoSQL database. This process is just a run type defined in export property:


Anytime an ETL process is run, all data for it is first cleared and then new data is brought on.

Inside analysis section, users can select from existing "analysis sources" to create new analysis. At the beginning all the columns are selected and users will see the entire set of data.

From this step, users can start to play with data. New columns can be created like string concatenations, mathematical expressions, aggregate functions:


Users can select and order the columns they are interested in:


 Users can sort, group and filter data:


Pagination is mandatory but can be modified to suit users needs:


After obtaining the results, users can save the analysis for future view. This is handy, because if there is a scheduled ETL  process that brings data locally, users are able to see updated data just by entering the analysis section.

Results can be exported as excel and can be frozen. A "freeze" action means the data set is saved as a copy inside NoSql database and no other ETL process can modify it.


Analysis can be shared to other users. If there is no need for it, analysis can be deleted. This process will also delete the data set associated with it if there is no any other analysis linked to that data set.

To find more, you should read the server section manual.

Thursday, February 19, 2015

NextReports Server: Capture dashboard as PDF

NextReports 8.0 will allow users to capture a dashboard as PDF. PDF file will contain a single landscape page with the visible part of the dashboard. This means all content which is not visible, because a scroll is involved won't be captured.

Users has a small number of tricks to make the dashboard full-visible:

- NextReports Server has a button to hide dashboards explorer
- Browser F11 button makes it to be visible in full screen
- CTRL+mouse wheel will allow to zoom-in/zoom-out the browser content

Using the tricks above we make a dashboard like the following to be fully visible:


PDF capture  will generate a file which contains the dashboard name and a footer with generation date:



Tuesday, February 10, 2015

NextReports: Copy Destination

NextReports 8.0 will allow to define a 'Copy' destination type when a report is run / scheduled. This means a new report with user defined name will be created as a copy of the generated one. Because generated files have an unique name made of report name followed by a random UUID, it may be necessary sometimes to have a known name in order to access the file from an external application.

All existing destinations will have a new 'Changed file name' property which is optional. If user specifies another name for the report, then a file copy will be generated as well, that copy will be distributed to the destination and then the copy file will be removed. For 'Mail' destination this property has meaning only if the report is sent as attachment.


Friday, February 06, 2015

NextReports Server: ETL for Analysis

NextReports 8.0 version will bring something new in the way server can interact with data.

ETL (Extract Transform Load) process can be defined when any report is run or scheduled. ETL is practically selected in run formats list. After run, all data is brought locally to NextReports Server machine inside a OrientDB NOSQL database.

User will be notified when the process starts and when the process ends through NextReports Server messages.

Saved data can be used by a new Analysis feature, which allows any user with necessary rights to play with data.

Wednesday, January 14, 2015

NextReports Server: IFrame Parameters

IFrames can be used to allow for embedding widgets in HTML pages. You can read about this in an old post.

The new version of NextReports will also allow to enter the parameters string, if any, making embedding code generation easier.


The usefulness of this is that if an encryption key is entered inside iframe server settings, then the embedded code will have the special P encrypted parameter automatically created.