Chapter 6. Script Datasources

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/n4ex-cets/rows.xml?accessType=DOWNLOAD

It lists the top 5000 from INC.com in the following XML format:

<response>
	<row>
		<row _id="1" _uuid="896E0607-A4B7-44C3-B496-212EA911A118" _position="1" _address="http://opendata.socrata.com/resource/n4ex-cets/1">
			<rank>1.0</rank>
			<company url="http://www.inc.com/inc5000/2009/company-profile.html?id=200900010" description="Northern Capital Insurance"/>
			<city>Miami</city>
			<state>FL</state>
			<industry>Insurance</industry>
			<revenue>95000000.0</revenue>
			<growth>19812.2</growth>
		</row>
		<row _id="2" _uuid="E16EBF5E-3F16-4C5A-9C71-E958A7420122" _position="2" _address="http://opendata.socrata.com/resource/n4ex-cets/2">
			<rank>2.0</rank>
			<company url="http://www.inc.com/inc5000/2009/company-profile.html?id=200900020" description="National Retirement Partners"/>
			<city>San Juan Capistrano</city>
			<state>CA</state>
			<industry>Financial Services</industry>
			<revenue>47400000.0</revenue>
			<growth>13416.4</growth>
		</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.rs
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 data source (in the data source management tree of the administrator's module) of type script data source 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 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 7 attributes of various types:

def td = new TableDefinition(['rank', 'company', 'city', 'state', 'industry', 'revenue', 'growth'], [Integer.class, String.class, String.class, String.class, String.class, BigDecimal.class, BigDecimal.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/n4ex-cets/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.rs
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. Note above, that the rank is given in decimal notation, although it encodes an integer. We will, thus, before translating it into an integer remove the last two characters (the ".0").

feed.row.row.each {
   table.addDataRow(
     Integer.parseInt(it.rank.text()[0..-3]), 
     it.company.@url.text(), 
     it.city.text(), 
     it.state.text(), 
     it.industry.text(), 
     it.revenue.toBigDecimal(), 
     it.growth.toBigDecimal()
   ) 
}

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

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

def address = "https://opendata.socrata.com/api/views/n4ex-cets/rows.xml?accessType=DOWNLOAD"

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

def td = new TableDefinition(['rank', 'company', 'city', 'state', 'industry', 'revenue', 'growth'], [Integer.class, String.class, String.class, String.class, String.class, BigDecimal.class, BigDecimal.class])
def table = new RSTableModel(td)

feed.row.row.each {
   table.addDataRow(
     Integer.parseInt(it.rank.text()[0..-3]), 
     it.company.@url.text(), 
     it.city.text(), 
     it.state.text(), 
     it.industry.text(), 
     it.revenue.toBigDecimal(), 
     it.growth.toBigDecimal()
   ) 
}

return table