Study Case : NextReports - A Real Time Exchange Rates Chart

From a developer's perspective, to have a real time chart, you need to have a database with real time data. We create a simple mysql database with only one table where we will insert currency values for every day (regarding our national currency RON).

create database currencies;

DROP TABLE IF EXISTS ron_values;
CREATE TABLE ron_values (
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),
    code varchar(3) NOT NULL,
    value decimal(10,4),
    exchange_date datetime    
);

First step to think of is where you get your data. We have this data offered in an xml file here: http://www.bnr.ro/nbrfxrates.xml. This file is changed every day except weekends and holidays.

The algorithm is pretty straightforward :

if (data_read_successfully) {
    get_list_of_currency_objects
    if (today_is_weekend_or_holiday) {
         // the currencies values will be the same like in previous day
         change_date_for_all_read_currencies_to_today
     }
     if (currencies_not_already_inserted_into_database) {
        insert_currencies_into_database
     }
}

We test if currencies were already inserted, because we do not want to depend on the date time when the xml file is updated, so our process can run lets say every hour or every minute.

After we have our real time data, we need to create our Next chart. First we define a currency parameter to see only exchange rates for a specific currency :

Parameter values are taken from a source sql which gives us all currency codes. We also have to put a default value for this parameter because our chart will be used inside a dashboard.

We want to see data for last 15 days and mysql query is :

Select DATE(RV.EXCHANGE_DATE) as EXCHANGE_DATE,
           RV.code,
           RV.value
From ron_values RV
Where RV.EXCHANGE_DATE Between DATE_ADD(CURDATE(), INTERVAL -14 DAY) And CURDATE()
and ${P_Currency} = RV.code
order by RV.EXCHANGE_DATE

We see how our defined parameter is used between curly brackets ${P_Currency}.
If we run the query we will be asked to select the value for currency parameter :

Result will show us all desired values :

After we have the query we will create a new chart. We will select EXCHANGE_DATE column for X_AXIS and VALUE column for Y_AXIS. We will select all properties we want like chart type, labels, colors, fonts, patterns, legends:

We can preview the chart inside designer to see how it looks like :

Then we can publish the chart to a server and we can add it to a dashboard. You can see this chart on our demo server http://demo.next-reports.com/nextserver on the NextSite dashboard.