jsreport is an open source JavaScript based reporting server that lets developers design reports using HTML and JavaScript templating. It supports various output formats, including HTML, PDF, CSV and others. It also includes advanced reporting features like user management, a REST API, and scheduling capabilities.

Getting Started

jsreport can be downloaded and installed on premise and is also available as an online service. While it's open source, the on premise version is limited to 5 report templates without purchasing a license. jsreport online offers a free plan, with several paid upgrade options. Both the on-prem and online free versions provide more than enough resources to thoroughly test it out and get a feel for whether this might be the appropriate reporting tool for your use.

Local Installation

Installing jsreport locally requires Node.js (>= 8.9) and npm (>= 6.x). Once your machine has those prerequisites installed, run the commands below from the terminal.

npm install jsreport-cli -g
mkdir jsreportapp
cd jsreportapp
jsreport init
jsreport configure
jsreport start

Now you can reach jsreport on the default port 5488. See the configuration documentation for changing the port and other options. Updating an existing install can be done through npm, see the update steps in the FAQ. The jsreport on-prem page provides additional information for installing on other platforms, including docker and aws.

Exploring jsreport Studio

Fresh installs include some sample reports in the samples folder (you can also follow along with this playground sample). Expand the samples > Invoices folder to preview the resources for the sample Invoice report. You should see the following resources:

invoice-data sample report data
invoice-logo.png report logo image asset
invoice-main report template
invoice-styles.css report css asset

The report template is the most important file here. Templates defines what the resulting reports look like and are used together with the input data every time you render a new report. Templates are designed using standard HTML and CSS, combined with JavaScript templating engines for binding input data, using loops, conditions, and JavaScript helper functions.

Take a moment to review the invoice template and notice the use of handlebars expressions for binding the data. You can preview the sample JSON data by clicking on the invoice-data object:

{
  "number": "123",
  "seller": {
    "name": "Next Step Webs, Inc.",
    "road": "12345 Sunny Road",
    "country": "Sunnyville, TX 12345"
  },
  "buyer": {
    "name": "Acme Corp.",
    "road": "16 Johnson Road",
    "country": "Paris, France 8060"
  },
  "items": [{
    "name": "Website design",
    "price": 300
  }]
}

This data is used to populate the report based on the handlebars expressions in the HTML template. In this case, the output recipe is chrome-pdf. The recipe is used to convert the template output into the desired format. Different recipes have different settings for things like headers, footers, margins, etc.

Notice the item table row wrapped in an each loop (lines 62-71). This loops through the items array and appends a row (<tr>) to the table with the item name and price. The total function referenced on line 75 and defined on line 11 in the script section below the HTML sums up the prices for the Total at the bottom of the invoice. You can test this by adding another object to the items array in the sample data, saving it, and re-running the report.

Reports can be generated within jsreport studio from static JSON data, fetched from a remote source such as a database or web API using the global beforeRender function, or programmatically generated by POSTing data to the jsreport API.

Integration with Fulcrum

We can configure jsreport to render reports from Fulcrum data several different ways. Data exported to SQLite or PostGIS can be fetched directly from the database or we can make an HTTP request to the Fulcrum Query API to grab the data we want. These methods are great for archiving or packaging up data in a multipage PDF with embeded photos.

Enabling Node Modules

In order to allow jsreport to make HTTP requests, we need to update the jsreport.config.json configuration file and either explicitly enable the request Node module:

{
  "extensions": {
    "scripts": {
      "allowedModules": ["request"]
    }
  }
}

or enable all Node modules:

"allowLocalFilesAccess": true

Be sure to restart jsreport after making any configuration changes.

Fetching Data from the Query API

Now we can script a beforeRender function to fetch the data from the Query API and inject it into the report.

function beforeRender(req, res, done) {
  let query = "SELECT _latitude, _longitude, local_id, id, town, road, CASE WHEN rd_imp = '0' THEN 'Unspecified' WHEN rd_imp = '1' THEN 'Low' WHEN rd_imp = '2' THEN 'Medium' WHEN rd_imp = '3' THEN 'High' END AS rd_imp, twn_hwy_cd, location, to_char(inv_dt, 'YYYY-mm-dd') AS inv_dt, year_built, historic, owner, cul_type, cul_matl, hdr_matl, bankfull_w, height, width, length, cover_dpt, oa_cond, hdr_cond, erosion, drop_inlet, alignment, dir_output, perched, need_clean, comment1, comment2, comment3, comment4, comment_no, FCM_Photo(photos) AS photo_links FROM \"Culvert Inventory\" WHERE photos IS NOT NULL AND hdr_cond IS NOT NULL";
  if (req.data.id) {
    query += " AND _record_id = '" + req.data.id + "'";
  }
  query += " ORDER BY id LIMIT 25";
  require('request')({
    url: "https://api.fulcrumapp.com/api/v2/query",
    json: true,
    headers: {
      "User-Agent": "jsreport",
      "X-ApiToken": "your-fulcrum-token"
    },
    qs: {
      "format": "json",
      "q": query
    }
  }, function (err, response, body) {
    req.data = {
      rows: body.rows
    };
    done();
  });
}

In the snippet above, we are selecting specific columns from our "Culvert Inventory" app and leveraging the FCM_Photo helper function to return an array of accessible URLs for our photos. We can upload this as a new script by clicking on the + button in the upper left-hand corner under the jsreport logo New Entity > script.

Once uploaded, you can associate the script with the template:

Clicking the Run button will fetch the data from Fulcrum and generate a PDF report that can immediately be downloaded from within jsreport studio!

Note the getPhoto1 and getPhoto2 functions included at the bottom of the template script used to split the array of photo URLs for proper placement in the report. See the Displaying Photos section for more on this.

Fetching Data from a Database

If your data is in a SQLite or PostgreSQL database, you can modify your script with the beforeRender function to query the database instead of the Query API. Be sure you've got the required Node modules installed (npm install sqlite3 or npm install pg) and enabled in the jsreport config.

PostgreSQL

const {Client} = require('pg');
const db = new Client({
  user: 'bryan',
  host: 'localhost',
  database: 'fulcrumapp',
  //password: 'secretpassword',
  port: 5432,
});
db.connect();
function beforeRender(req, res, done) {
  let query = "SELECT _latitude, _longitude, local_id, id, town, road, CASE WHEN rd_imp = '0' THEN 'Unspecified' WHEN rd_imp = '1' THEN 'Low' WHEN rd_imp = '2' THEN 'Medium' WHEN rd_imp = '3' THEN 'High' END AS rd_imp, twn_hwy_cd, location, to_char(inv_dt, 'YYYY-mm-dd') AS inv_dt, year_built, historic, owner, cul_type, cul_matl, hdr_matl, bankfull_w, height, width, length, cover_dpt, oa_cond, hdr_cond, erosion, drop_inlet, alignment, dir_output, perched, need_clean, comment1, comment2, comment3, comment4, comment_no, photos[1] AS photo_1, photos[2] AS photo_2 FROM culvert_inventory WHERE photos IS NOT NULL AND hdr_cond IS NOT NULL";
  
  if (req.data.id) {
    query += " AND _record_id = '" + req.data.id + "'";
  }
  query += " ORDER BY id LIMIT 25";
  db.query(query, (err, result) => {
    req.data = {
      rows: result.rows
    };
    db.end();
    done();
  });
}

The example above is using a Fulcrum Desktop synced table so the column names match the Query API.

SQLite

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('/Users/bryan/Desktop/fulcrum-culverts-download/export.sqlite');  
function beforeRender(req, res, done) {
  let query = "SELECT latitude AS _latitude, longitude AS _longitude, local_id, id, town, road, CASE WHEN rd_imp = '0' THEN 'Unspecified' WHEN rd_imp = '1' THEN 'Low' WHEN rd_imp = '2' THEN 'Medium' WHEN rd_imp = '3' THEN 'High' END AS rd_imp, twn_hwy_cd, location, inv_dt, year_built, historic, owner, cul_type, cul_matl, hdr_matl, bankfull_w, height, width, length, cover_dpt, oa_cond, hdr_cond, erosion, drop_inlet, alignment, dir_output, perched, need_clean, comment1, comment2, comment3, comment4, comment_no, instr(photos,',') AS pos, CASE WHEN instr(photos,',') > 0 THEN substr(photos, 1, instr(photos,',') -1) ELSE photos END AS photo_1, CASE WHEN instr(photos,',') > 0 THEN substr(photos, instr(photos,',') +1) ELSE null END AS photo_2 FROM culvert_inventory WHERE photos IS NOT NULL AND hdr_cond IS NOT NULL";
  if (req.data.id) {
    query += " AND fulcrum_id = '" + req.data.id + "'";
  }
  query += " ORDER BY id LIMIT 25";
  db.all(query, function(err, rows) {
    req.data = {
      rows: rows
    };
    db.close();
    done();   
  }); 
}

The example above is using a SQLite file from a Fulcrum export, which uses a slightly different column naming convention. Notice how we are aliasing the latitude and longitude columns to match the Query API so our template can be used with different data sources. We also had to change the unique ID column from _record_id to fulcrum_id for exports.

Displaying Photos

We can display photos in our report using the <img> tag src attribute in our template HTML. In our culvert report example we are displaying 2 side-by-side images at the top of the page. We can use standard CSS to apply any desired styles and reference helper functions in our handlebars expression to define the source path.

Query API Photos

The Query API is able to generate an array of web URLs using the FCM_Photo function. In this scenario, our helper functions need to check to see if the photo exists and split out the URLs for photo 1 and photo 2.

function getPhoto1(photos) {
  if (photos) {
    return photos[0];
  } else {
    return 'https://via.placeholder.com/485x291/FFFFFF.jpg?text=no%20photo';
  }
}
function getPhoto2(photos) {
  if (photos && photos[1]) {
    return photos[1];
  } else {
    return 'https://via.placeholder.com/485x291/FFFFFF.jpg?text=no%20photo';
  }
}

If the photo exists, return the URL. If it doesn't exist, we are returning a placeholder image with the text "no photo". Here's what the template looks like to reference the above functions:

<div>
  {{<img src="{{getPhoto1 photo_links}}" class="photoleft">}}
</div>
<div>
  {{<img src="{{getPhoto2 photo_links}}" class="photoright">}}
</div>

Local Photos

If you're working with photos that you've downloaded locally, you just need to reference to full file path. Fulcrum photo fields are stored as text[] array types in Postgres so our SQL query can just pull out the photo IDs in 2 seperate fields using SELECT ... photos[1] AS photo_1, photos[2] AS photo_2 .... Then our helper functions need to concatenate the path, photo ID, and extension like so:

function getPhoto(photo) {
  if (photo) {
    return 'file:///Users/bryan/Desktop/fulcrum-culverts-download/' + photo + '.jpg' 
  } else {
    return "https://via.placeholder.com/485x291/FFFFFF.jpg?text=no%20photo";
  }
}

Programmatically Generating Reports

To programmatically generate and return the report, we can make a POST request to the /api/report endpoint of our jsreport server, passing the template ID and any other options we want to include. In this case we are setting the save option to true, which will save a copy of the report on the server. Note that if you are using the authentication extension, you will need to include your Basic auth info.

curl -X POST \
  http://localhost:5488/api/report \
  -H 'Accept: */*' \
  -H 'Content-Type: application/json' \
  -d '{
  "template": {
    "shortid": "HkZLJkCyE"
  },
  "options": { 
    "reports": {
      "save": true
    }
  }
}'

You may have noticed in our script that we set up our query variable with an if statement to check for a record ID.

if (req.data.id) {
  query += " AND _record_id = '" + req.data.id + "'";
}

We can use this to generate a report for a single record by passing the record's unique Fulcrum ID in the POST data payload. If we wanted to run the report for a record with the ID of 08bcf7c7-bd52-4283-b103-dced4f32684c, we would use the following

curl -X POST \
  http://localhost:5488/api/report \
  -H 'Accept: */*' \
  -H 'Content-Type: application/json' \
  -d '{
  "template": {
    "shortid": "HkZLJkCyE"
  },
  "data": {
    "id": "08bcf7c7-bd52-4283-b103-dced4f32684c"
  }
}'

The PDF is returned with the response and if report saving is enabled, the link to the saved report will be included in a Permanent-Link header.

Next Steps

Hopefully this jsreport integration guide has helped illustrate how this powerful reporting engine can be used to generate fully customizable reports from your field data. We just scratched the surface of what's possible when integrating Fulcrum with jsreport but once you've got the fundamentals sorted out, you can begin exploring advanced capabilities like scheduling email deliveries and creating live interactive dashboards.

You can download the example culvert report resources that include the scripts for fetching data from the Fulcrum Query API, PostgreSQL, and SQLite databases, helper functions, and basic HTML template. This zip file can be imported into jsreport and used as a foundation for building your own reports.

The video below dives deeper into advanced jsreport features including scripting, scheduling, user management and more.

If this all sounds great but you don't have the technical capabilities or development resources to get up and running on your own, our Professional Services team can help with all your reporting needs! To contact them, please reach out to your account executive.