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.util.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.util.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.