Chapter 6. Script Datasources

Script datasources are your swiss army knife when it comes to integrating data from various formats. Basically, what script datasources do is to produces any sort of data and output it in a table format that ReportServer understands. This data will then be loaded (and potentially cached) in ReportServer's internal database (see Administrator's guide) which then allows you to build any sort of report (for example a dynamic list) on top of it.

Say you have an XML dataset such as the following dataset taken from socrata (a platform for open data):

https://opendata.socrata.com/api/views/2dps-ayzy/rows.xml?accessType=DOWNLOAD

It lists the data in the following XML format:

<response>
	<row>
		<row _id="row-cwj5-dis8~w6z6"
			_uuid="00000000-0000-0000-2B3F-C9C31B6F54CD" _position="0"
			_address="https://opendata.socrata.com/resource/_2dps-ayzy/row-cwj5-dis8~w6z6">
			<employee_name>ABBOT, JUDITH L</employee_name>
			<office>SENATOR TOBY ANN STAVISKY</office>
			<city>FLUSHING</city>
			<employee_title>COMMUNITY LIAISON</employee_title>
			<biweekly_hourly_rate>1076.93</biweekly_hourly_rate>
			<payroll_type>SA</payroll_type>
			<pay_period>23</pay_period>
			<pay_period_begin_date>2019-02-07T00:00:00</pay_period_begin_date>
			<pay_period_end_date>2019-02-20T00:00:00</pay_period_end_date>
			<check_date>2019-03-06T00:00:00</check_date>
			<legislative_entity>SENATE EMPLOYEE</legislative_entity>
		</row>
		<row _id="row-6b26~66gt~f43k"
			_uuid="00000000-0000-0000-6047-FAD2CDBD8A36" _position="0"
			_address="https://opendata.socrata.com/resource/_2dps-ayzy/row-6b26~66gt~f43k">
			<employee_name>ABRAHAM, PRINCY A</employee_name>
			<office>MINORITY COUNSEL/PROGRAM</office>
			<city>ALBANY</city>
			<employee_title>ASSOCIATE COUNSEL</employee_title>
			<biweekly_hourly_rate>2376.93</biweekly_hourly_rate>
			<payroll_type>RA</payroll_type>
			<pay_period>23</pay_period>
			<pay_period_begin_date>2019-02-07T00:00:00</pay_period_begin_date>
			<pay_period_end_date>2019-02-20T00:00:00</pay_period_end_date>
			<check_date>2019-03-06T00:00:00</check_date>
			<legislative_entity>SENATE EMPLOYEE</legislative_entity>
		</row>
	</row>
</response>

In order to make this data available in ReportServer, we need to load this data using a simple script and transform it into an object of type net.datenwerke.rs.base.service.reportengines.table.output.object.RSTableModel which is a simple wrapper for a table. RSTableModel basically consists of a definition of a table (defining attributes) and a list of rows. So a simple table could be defined as

import net.datenwerke.rs.base.service.reportengines.table.output.object.*

def td = new TableDefinition(['colA', 'colB', 'colC'], [String.class, Integer.class, String.class])

def table = new RSTableModel(td)
table.addDataRow('A', 10, 'C')
table.addDataRow('foo', 42, 'bar')

return table

If executed, this script would produce the following output.

reportserver$ exec ds1.groovy
Table(definition:TableDefinition [columnNames=[colA, colB, colC], columnTypes=[class java.lang.String, class java.lang.Integer, class java.lang.String]], rows: 2)

A script, as in the above example, is all we need to specify a script datasource. All that would be left to do is to create a new datasource (in the datasource management tree of the administrator's module) of type script datasource and select the just created script. The only other configuration choice is to specify whether the data should be cached in the internal database (and if so, for how long) or whether the script should be executed whenever a request to the dataset is made. Henceforth, the datasource can be used similar to any other relational database.

In addition, you can pass arguments to the script, which can be referred in the script with the args variable. E.g., refer to the following example:

import net.datenwerke.rs.base.service.reportengines.table.output.object.*;

def definition = new TableDefinition(['a','b','c'],
					[String.class,String.class,String.class]);

def model = new RSTableModel(definition);
model.addDataRow(args[0],"2","3");
model.addDataRow("4","5","6");
model.addDataRow("7","8","9");

return model;

The args[0] prints the 0th argument passed to the script. You can either pass a text, e.g.''myValue'', or the value of a given report parameter, e.g. ${myParam} for a ''myParam'' parameter. Note that if the value contains blank spaces, quotation marks are needed.

In the following we will explain, step by step, how the above dataset can be transformed into a RSTableModel. The first step is to create the TableDefinition. The dataset consists of 11 attributes of various types:

def td = new TableDefinition(['employee_name', 'office', 'city', 'employee_title', 'biweekly_hourly_rate', 'payroll_type', 'pay_period', 'pay_period_begin_date', 'pay_period_end_date', 'check_date', 'legislative_entity'],[String.class, String.class, String.class, String.class, Float.class, String.class, Integer.class, Date.class, Date.class, Date.class, String.class])

In a next step we need to read in the XML. This task can be easily achieved using Groovy's XmlSlurper.

import groovy.xml.XmlSlurper

def address = "https://opendata.socrata.com/api/views/2dps-ayzy/rows.xml?accessType=DOWNLOAD"

def connection = address.toURL().openConnection()
def feed = new XmlSlurper().parseText(connection.content.text)

return feed.size()

Executing the above script might take a few seconds, but the you should see the following result

reportserver$ exec ds1.groovy
1

There is exactly one root node, so the size is one. Now, all left to do is to loop over the records and add them to the table.

feed.row.row.each { row -> 
  table.addDataRow(
    row.employee_name.text(),
    row.office.text(),
    row.city.text(),
    row.employee_title.text(),
    Float.parseFloat(row.biweekly_hourly_rate.text()),
    row.payroll_type.text(),
    Integer.parseInt(row.pay_period.text()), 
    formatter.parse(row.pay_period_begin_date.text()),
    formatter.parse(row.pay_period_end_date.text()),
    formatter.parse(row.check_date.text()),
    row.legislative_entity.text(),
  )
}

Putting it all together, we get the following simple script:

import net.datenwerke.rs.base.service.reportengines.table.output.object.*
import groovy.xml.XmlSlurper
import java.util.Date
import java.text.SimpleDateFormat

def address = "https://opendata.socrata.com/api/views/2dps-ayzy/rows.xml?accessType=DOWNLOAD"

def connection = address.toURL().openConnection()
def feed = new XmlSlurper().parseText(connection.content.text)

def td = new TableDefinition(['employee_name', 'office', 'city', 'employee_title', 'biweekly_hourly_rate', 'payroll_type', 'pay_period', 'pay_period_begin_date', 'pay_period_end_date', 'check_date', 'legislative_entity'],[String.class, String.class, String.class, String.class, Float.class, String.class, Integer.class, Date.class, Date.class, Date.class, String.class])
def table = new RSTableModel(td)
def formatter = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss")

feed.row.row.each { row -> 
  table.addDataRow(
    row.employee_name.text(),
    row.office.text(),
    row.city.text(),
    row.employee_title.text(),
    Float.parseFloat(row.biweekly_hourly_rate.text()),
    row.payroll_type.text(),
    Integer.parseInt(row.pay_period.text()), 
    formatter.parse(row.pay_period_begin_date.text()),
    formatter.parse(row.pay_period_end_date.text()),
    formatter.parse(row.check_date.text()),
    row.legislative_entity.text(),
  )
}
return table

Further, useful script datasource examples can be found in our GitHub samples project: https://github.com/infofabrik/reportserver-samples.

For using script datasources (csv datasources analogously) together with pixel-perfect reports, you need additional minor configuration in the given reports. The configuration is based on the following.

The data of script datasources is buffered to internal temporary tables. The query type to be used is

SELECT * FROM _RS_TMP_TABLENAME

where _RS_TMP_TABLENAME is a temporary table name assigned by ReportServer. The following replacements are available:

_RS_TMP_TABLENAME:	The name of the table
_RS_QUERY:	The basic query.

These replacements can be used in pixel-perfect reports as described by the following sections.

You can add one of the parameters above to your Jasper report and use it inside the query. E.g., you can add a text parameter _RS_TMP_TABLENAME to your report and use the following query:

select * from  $P!{_RS_TMP_TABLENAME}

You may have to create the fields you are going to use in your report manually. Also note that you need to use $!{} instead of ${} as replacements need to be directly written into the query.

You can add one of the parameters above to your BIRT report. The parameter can not be used directly in the query of your data set, though. Instead, you need a ''beforeOpen'' script in your BIRT report, which performs the replacement needed. E.g.:

this.queryText = "SELECT * FROM " + params["_RS_TMP_TABLENAME"].value;

In order to create your dataset fields and use them later in your report, you may create a dummy query similar to

select '' as myfield1, '' as myfield2, '' as myfield3

Thus, the fields ''myField1'', ''myField2'' and ''myField3'' can be now used in your report.

You can use one of the parameters above in your JXLS query tag directly. E.g. for JXLS2 reports:

jx:area(lastCell="B2")
jx:each(items="jdbc.query('SELECT EMP_FIRSTNAME as firstname, EMP_LASTNAME as lastname FROM $!{_RS_TMP_TABLENAME}')" var="employee" lastCell="B2")

First name: | ${employee.firstname}
Last name: | ${employee.lastname}