Chapter 16. ReportServer Scripting

ReportServer Enterprise Edition comes with scripting support. Scripts can be used in a variety of ways and can, for example, serve as a basis for reports and datasources, perform administrative tasks, or even contribute new functionalities. In this section we want to give you an introduction to the comprehensive script subject, and make you familiar with the various options provided by ReportServer scripts. Scripts and their fields of use will be treated in the ReportServer script manual in detail.

ReportServer scripts are written in Groovy (http://groovy-lang.org/) and run in the same VM where ReportServer is located. The decisive advantage here is that the scripts have access to the complete set of services provided by ReportServer. However, it also means that scripts represent a potential security and stability risk. Persons who are authorized to write or change scripts have full access to the system. The permissions to write scripts should thus be granted with care.

The following explanations address persons with basic knowledge in programming. Java and/or Groovy experience are not necessarily required but might be helpful. Under https://groovy-lang.org/ you will find many excellent tutorials on programming in Groovy.

Scripts are located in the internal file manager, by default beneath folder bin. Several configuration options can be configured in configuration file /etc/scripting/scripting.cf (also see the ReportServer configuration guide).

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
	<scripting>
		<enable>true</enable>
		<restrict>
			<location>bin</location>
		</restrict>
		<startup>
			<login>fileserver/bin/onlogin.groovy</login>
			<rs>fileserver/bin/onstartup.groovy</rs>
		</startup>
		<cache>
			<maxsize>10000</maxsize>
			<expiresafter>30</expiresafter> <!-- in minutes -->
      </cache>
	</scripting>
</configuration>

The first option allows the global deactivation of scripts. Please consider that if you apply it, you will not be able to use script reports anymore, and that the documentation report available in the demo data will not work any longer. By the second option (restrict.location) you will define a root folder in which scripts have to be filed. This allows to give access rights for the file system to individual users without enabling them to create or change scripts.

The next section (startup) allows to configure two special scripts. As soon as a user has signed on, the onlogin script will run with the rights of this user. Here, for instance, interface enhancements can be loaded. To do this, ensure to grant users the execute right for the script. The start-up script, on the other hand, will run when starting the system. Note that then the script is run without any logged in user. Instead of specifying scripts, you can also specify folders. In this case, all scripts within the folder are executed.

The cache section controls the internal script caching mechanism. This is used for performance reasons, e.g. for not recompiling the same script twice when used frequently. The maxsize setting specifies the maximum number of entries the cache may contain. The expiresafter specifies that each entry should be automatically removed from the cache once the given fixen duration has elapsed after the entry's creation, or the most recent replacement of its value.

In the following we want to present a simple sample script. Open the terminal (press CTRL+ALT+T) and switch to the directory fileserver/bin (if you failed to create this directory so far, create it by using mkdir).

reportserver$ cd fileserver/bin

Now, create a tmp directory and switch to it.

reportserver$ mkdir tmp
reportserver$ cd tmp/

We can now create your a first ReportServer script by issuing createTextFile hello1.groovy. The extension .groovy has no relevance here, although, it has established as a standard for scripts (besides .groovy, .rs is frequently used):

reportserver$ createTextFile hello1.groovy
file created

A pop-up window opens to edit the newly created file. For our first simple Hello World script we only want to induce the script to return Hello World. By default, scripts return the result of the last statement. So we can write the Hello World script simply by entering:

"Hello World"

Close the dialogue by clicking on the Submit button. With the "exec" command you can run the script.

reportserver$ exec hello1.groovy
Hello World

Before we continue, we will have a brief look at an error case. If the execution of a script fails, ReportServer will print an error message which tries to pin point the error. Let us consider the following simple script. Here we forgot to place the closing quotation mark:

return "Hello World

Here the error message would be as follows:

reportserver$ exec helloFail.groovy
Script execution failed.
error message: startup failed:
Script2.groovy: 1: unexpected char: 0xFFFF @ line 1, column 20.
return "Hello world
^

1 error
(java.util.concurrent.ExecutionException)
script arguments:
file: helloFail.groovy (id: 10074, line 1)
line number: 1
col. number: 20

Here the error message points to the problem: there is an unexpected character in line 1. In some cases, however, the error message might be insufficient to pinpoint the problem. In this case, you can tell ReportServer to print a detailed stack trace of the execution by running the script with the -t flag. In this case the output would be similar to the following

reportserver$ exec -t helloFail.groovy
net.datenwerke.rs.scripting.service.scripting.exceptions.ScriptEngineException: javax.script.ScriptException: org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed:
Script17.groovy: 1: unexpected char: 0xFFFF @ line 1, column 20.
return "Hello world
^

1 error

------- SCRIPT ERROR INFO -------
Script execution failed.
error message: startup failed:
Script17.groovy: 1: unexpected char: 0xFFFF @ line 1, column 20.
return "Hello world
^

1 error
(java.util.concurrent.ExecutionException)
script arguments:
file: helloFail.groovy (id: 10074, line 1)
line number: 1
col. number: 20

at net.datenwerke.rs.scripting.service.scripting.engines.GroovyEngine.eval(GroovyEngine.java:107)
at net.datenwerke.rs.scripting.service.scripting.ScriptingServiceImpl.executeScript(ScriptingServiceImpl.java:217)
at net.datenwerke.rs.scripting.service.scripting.ScriptingServiceImpl.executeScript(ScriptingServiceImpl.java:263)
at net.datenwerke.rsenterprise.license.service.EnterpriseCheckInterceptor.invoke(EnterpriseCheckInterceptor.java:35)
at net.datenwerke.rs.scripting.service.scripting.ScriptingServiceImpl.executeScript(ScriptingServiceImpl.java:317)
at net.datenwerke.rsenterprise.license.service.EnterpriseCheckInterceptor.invoke(EnterpriseCheckInterceptor.java:35)
at net.datenwerke.rs.scripting.service.scripting.ScriptingServiceImpl.executeScript(ScriptingServiceImpl.java:288)
at net.datenwerke.rsenterprise.license.service.EnterpriseCheckInterceptor.invoke(EnterpriseCheckInterceptor.java:35)
at net.datenwerke.rs.scripting.service.scripting.terminal.commands.ExecScriptCommand.doRollbackExecute(ExecScriptCommand.java:335)
at com.google.inject.persist.jpa.JpaLocalTxnInterceptor.invoke(JpaLocalTxnInterceptor.java:66)
at net.datenwerke.rs.scripting.service.scripting.terminal.commands.ExecScriptCommand$1$1.doFilter(ExecScriptCommand.java:272)
at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:66)
at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:168)
at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58)
at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:168)
at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58)
at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:168)
at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58)
at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:118)
at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:113)
at net.datenwerke.rs.scripting.service.scripting.terminal.commands.ExecScriptCommand$1.call(ExecScriptCommand.java:263)
at net.datenwerke.rs.scripting.service.scripting.terminal.commands.ExecScriptCommand$1.call(ExecScriptCommand.java:1)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.lang.Thread.run(Thread.java:745)
Caused by: javax.script.ScriptException: org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed:
Script17.groovy: 1: unexpected char: 0xFFFF @ line 1, column 20.
return "Hello world
^

1 error

at org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.compile(GroovyScriptEngineImpl.java:181)
at net.datenwerke.rs.scripting.service.scripting.engines.GroovyScriptCache$1.load(GroovyScriptCache.java:57)
at net.datenwerke.rs.scripting.service.scripting.engines.GroovyScriptCache$1.load(GroovyScriptCache.java:1)
at com.google.common.cache.LocalCache$LoadingValueReference.loadFuture(LocalCache.java:3522)
at com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2315)
at com.google.common.cache.LocalCache$Segment.lockedGetOrLoad(LocalCache.java:2278)
at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2193)
at com.google.common.cache.LocalCache.get(LocalCache.java:3932)
at com.google.common.cache.LocalCache.getOrLoad(LocalCache.java:3936)
at com.google.common.cache.LocalCache$LocalLoadingCache.get(LocalCache.java:4806)
at net.datenwerke.rs.scripting.service.scripting.engines.GroovyScriptCache.get(GroovyScriptCache.java:79)
at net.datenwerke.rs.scripting.service.scripting.engines.GroovyEngine.eval(GroovyEngine.java:73)
... 23 more
Caused by: org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed:
Script17.groovy: 1: unexpected char: 0xFFFF @ line 1, column 20.
return "Hello world
^

1 error

at org.codehaus.groovy.control.ErrorCollector.failIfErrors(ErrorCollector.java:309)
at org.codehaus.groovy.control.ErrorCollector.addFatalError(ErrorCollector.java:149)
at org.codehaus.groovy.control.ErrorCollector.addError(ErrorCollector.java:119)
at org.codehaus.groovy.control.ErrorCollector.addError(ErrorCollector.java:131)
at org.codehaus.groovy.control.SourceUnit.addError(SourceUnit.java:359)
at org.codehaus.groovy.antlr.AntlrParserPlugin.transformCSTIntoAST(AntlrParserPlugin.java:137)
at org.codehaus.groovy.antlr.AntlrParserPlugin.parseCST(AntlrParserPlugin.java:108)
at org.codehaus.groovy.control.SourceUnit.parse(SourceUnit.java:236)
at org.codehaus.groovy.control.CompilationUnit$1.call(CompilationUnit.java:164)
at org.codehaus.groovy.control.CompilationUnit.applyToSourceUnits(CompilationUnit.java:928)
at org.codehaus.groovy.control.CompilationUnit.doPhaseOperation(CompilationUnit.java:590)
at org.codehaus.groovy.control.CompilationUnit.processPhaseOperations(CompilationUnit.java:566)
at org.codehaus.groovy.control.CompilationUnit.compile(CompilationUnit.java:543)
at groovy.lang.GroovyClassLoader.doParseClass(GroovyClassLoader.java:297)
at groovy.lang.GroovyClassLoader.parseClass(GroovyClassLoader.java:267)
at groovy.lang.GroovyClassLoader.parseClass(GroovyClassLoader.java:253)
at groovy.lang.GroovyClassLoader.parseClass(GroovyClassLoader.java:211)
at org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.getScriptClass(GroovyScriptEngineImpl.java:366)
at org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.compile(GroovyScriptEngineImpl.java:173)
... 34 more
reportserver$

Now, we want to develop a more comprehensive script which returns all reports that access a defined table in the query. Here we will get acquainted with some sample services provided for scripts. You will find a detailed description of all services in the ReportServer script manual. In addition, the Java Doc API description by ReportServer provides you with initial information.

Now, we create our second script:

reportserver$ createTextFile searchReportByQuery.groovy
file created

The entry to be made shall include a scrap text and output all dynamic lists for which the data connection is defined as a relational database, and the named scrap text is to be found in their query. Scripts can access arguments via the variable (array) args. So the following script would simply output the single argument again:

if(args.size() == 0)
	return "No arguments"
args[0]

If we execute this script we get the following result:

reportserver$ exec searchReportByQuery.groovy
No argument stated
reportserver$ exec searchReportByQuery.groovy A B C
A

Please note that we leave the script in line 2 by entering a return if no argument has been stated. In the following we want to browse through all dynamic lists. Via the GLOBALS object (an object that ReportServer adds to the scope of every script) you have access to the various services and auxiliary methods. For instance, the method getEntitiesByType allows to simply access all objects of a defined type. Dynamic lists are internally managed as a TableReport type (in the packet net.datenwerke.rs.base.service.reportengines.table.entities).

In order to be able to use objects, you have to import them. Subsequently, we can pass the class related to dynamic lists (TableReport.class) to the method getEntitiesByType. By entering the statement .each we can then run a piece of code (in Groovy language a "Closure") for each object found.

/* imports */
import net.datenwerke.rs.base.service.reportengines.table.entities.TableReport

/* argument handling */
if(args.size() == 0)
	return "No arguments" 
def searchString = args[0]
GLOBALS.getEntitiesByType(TableReport.class).each {
	tout.println(it.getName())
}
""

By the object tout you can generate outputs on the console (the object is of type java.io.PrintWriter). Within the closure you have access to the loop object, here the current report, via the dynamically generated variable it. With this we output the name of all dynamic reports on the console. Please also have a look at the last line of the script "". It returns an empty string, as otherwise the return of GLOBALS.getEntitiesByType(TableReport.class). each will be output on the console.

When running the script (please ensure to pass an argument to the script) you will find out that not only basic reports have been processed but also the related variants. Reoprt variants inherit from their respective base classes and will therefore also be returned by

GLOBALS.getEntitiesByType(TableReport.class)

To exclude it we will test whether the currently processed object (within the closure) is of type ReportVariant (in the packet net.datenwerke.rs.core.service.reportmanager.interfaces). Now, the adapted script will only return the name of base reports.

/* imports */
import net.datenwerke.rs.base.service.reportengines.table.entities.TableReport 
import net.datenwerke.rs.core.service.reportmanager.interfaces.ReportVariant 

/* argument handling */
if(args.size() == 0)
	return "No arguments" 
def searchString = args[0]
GLOBALS.getEntitiesByType(TableReport.class).each { 
	if(it instanceof ReportVariant)
		return;
	tout.println(it.getName())
}
""

Now we have nearly reached our goal. The datasource can be addressed via the field datasourceContainer.datasource, and the relational datasources are of type Database- Datasource (in the package net.datenwerke.rs.base.service.datasources.definitions). The corresponding configuration is also to be found in the datasource container (datasourceContainer.datasourceConfig) and of type DatabaseDatasourceConfig.

/* argument handling */
if(args.size() == 0)
	return "No arguments" 
def searchString = args[0]
GLOBALS.getEntitiesByType(TableReport.class).each {
	if(it instanceof ReportVariant)
		return;
	
	if(it.datasourceContainer?.datasource instanceof DatabaseDatasource){ 
		def query = it.datasourceContainer?.datasourceConfig?.query 
		if( null != query && query =~ searchString)
			tout.println(it.getName() + ": " + query)
	}
} 
""

Running on system with installed demo reports, the following output could result:

reportserver$ exec searchReportByQuery.rs T_AGG_ORDER
T_AGG_ORDER - Basis: SELECT * FROM T_AGG_ORDER
T_AGG_ORDER - Parametrized: SELECT * FROM T_AGG_ORDER WHERE $X{IN, 
		OR_CUSTOMERNUMBER, P_CUSTNUM} AND OR_ORDERDATE > ${P_DATE_FROM} AND 
		OR_ORDERDATE < ${P_DATE_TO}

By using scripts, you can of course also change or create objects automatically. A slightly changed version of the above script resets the key of the reports found.

/* imports */
import net.datenwerke.rs.base.service.reportengines.table.entities.TableReport 
import net.datenwerke.rs.core.service.reportmanager.interfaces.ReportVariant

def key = 1; GLOBALS.getEntitiesByType(TableReport.class).each {
	if(it instanceof ReportVariant) 
		return;
	tout.println("set key for report " + it.getId())
	it.setKey("myKey" + key++)
}
"done"

If you execute this script you will find out that it runs smoothly, but the changes have not been adopted.

reportserver$ exec resetReportKeys.groovy
done
set key for report 12
set key for report 17
set key for report 22
set key for report 26
set key for report 33
set key for report 39

By default, ReportServer performs a rollback on the database once the script is executed. However, in order to commit the changes, use the -c flag.

reportserver$ exec -c resetReportKeys.groovy

Apart from the administrative tasks, scripts can be used to enhance ReportServer. Enhancements can be hooked up on the server side just as well as integrated on various points in the interface to, for example, display additional information, or to provide enhanced functionality. In the following we want to present an enhancement on the server side by giving a simple example.

Imagine, we run our business properly and want to ensure that our employees will only be able to retrieve reports during working time. Here, ReportServer provides the option to directly hook up in the report execution and, if required, to interrupt it. In the ReportServer jargon, enhancement interfaces are called hooks. They are provided at various locations. The easiest way to get an overview of the enhancement interfaces is by browsing through the JavaDoc API for interfaces which implement the interface Hook. For further information on hooks refer to the ReportServer script guide.

To delimit the working time we implement the hook ReportExecutionNotificationHook. It will be called up before and after report execution and allows to prevent it. In the following, a code is given which basically implements the interface and checks the current time in the method doVetoReportExecution, and if it lies outside the range of 9 a.m to 5 p.m it throws an exception. The callback will be "hooked in" added to the last line.

import net.datenwerke.rs.core.service.reportmanager.exceptions.* 
import net.datenwerke.rs.core.service.reportmanager.hooks.*

def HOOK_NAME = "PROHIBIT_EXECUTION" 
def callback = [
	notifyOfReportExecution : { report, parameterSet, user, outputFormat, configs ->  },
	notifyOfReportsSuccessfulExecution : { compiledReport, report, parameterSet, user, 
outputFormat, configs -> },
	notifyOfReportsUnsuccessfulExecution : { e, report, parameterSet, user, outputFormat,
configs -> },
	doVetoReportExecution: { report, parameterSet, user, outputFormat, configs -> 
		def cal = Calendar.instance
		def hour = cal.get(Calendar.HOUR_OF_DAY)
		if(hour > 17 || hour < 9)
			throw new ReportExecutorException("Please come back during office hours");
	}
] as ReportExecutionNotificationHook
		
GLOBALS.services.callbackRegistry.attachHook(HOOK_NAME, ReportExecutionNotificationHook.class, 
	callback)

Now, if you try to run a report after 6 p.m. you will be welcomed with the message "Please come back during office hours.".

Please ensure to give the hook a name. By doing so, you prevent to apply the hook repeatedly when running the script repeatedly. Use the following script to remove the hook:

def HOOK_NAME = "PROHIBIT_EXECUTION" 
GLOBALS.services.callbackRegistry.detachHook(HOOK_NAME)

Scripts can be planned by a timer controlled schedule. To do this, use the scheduleScript command. For further information refer to Chapter 19.

Similar to accessing files, you can also directly access scripts by URL:

http://SERVER/APPLICATIONFOLDER/reportserver/scriptAccess?id=XX

The following URL attributes can be used

If you want to pass more than one argument to the script, you can achieve this separating the arguments through whitespaces, here an example: http://SERVER/APPLICATIONFOLDER/reportserver/scriptAccess?id=XX&args=firstArg\%20secondArg

The return value of the script will be passed on to the browser as a text message. In addition, you have the option to directly impact the output with the substitutions httpRequest and httpResponse. This objects hide the Java objects HttpServletRequest (http://docs.oracle.com/javaee/6/api/javax/servlet/http/HttpServletRequest.html) and HttpServletResponse (http://docs.oracle.com/javaee/6/api/javax/servlet/http/HttpServletResponse.html). If the script has no value returned (zero), it is assumed that it files its output independently in the OutputStream. For further information refer to the Scripting guide.

httpResponse.getWriter().write("Hello world") 
return null

For further information on ReportServer scripts please refer to the ReportServer scripting guide.