Chapter 13. ReportServer Scripting

13. 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 data sources, 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 http:// groovy.codehaus.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.rs</login>
			<rs>fileserver/bin/onstartup.rs</rs>
		</startup>
	</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 last two options allow 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.

13.1. A first Hello World

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.rs. The extension .rs has no relevance here, although, it has established as a standard for scripts (beside .rs, .groovy is frequently used):

reportserver$ createTextFile hello1.rs
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.rs
Hello World
13.2. How to Handle Errors

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$
Tip: With the -w flag (e.g. exec -w hello1.rs) you can redirect the script output (or the error output) to a separate window.
13.3. Administrative Scripts

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.rs
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.rs
No argument stated
reportserver$ exec searchReportByQuery.rs 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).

Tip: If you browse for a specific object in JavaDoc API (data source, report, etc.) you will be able to find many important objects because they are marked with the annotation @Entity. This annotation is element of all objects that are physically represented in the database.

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 data source can be addressed via the field datasourceContainer.datasource, and the relational data sources 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 data source 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 &gt; ${P_DATE_FROM} AND 
		OR_ORDERDATE &lt; ${P_DATE_TO}
13.4. Changing the Data Model

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.rs
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.rs
13.5. Enhancing ReportServer with Scripts

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)
Tip: Use the onStartup or onLogin script to hook up enhancements automatically.
13.6. Scheduling of Scripts

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

13.7. Accessing Scripts by URL

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

id ID of a file.
path Path leading to a file, e.g. bin/script.rs
args Arguments passed on to the script.
exception true to receive an error message in case of a failure

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
Remark. If a script is in a folder that is marked as web accessible (see Chapter 5.) then the script can be accessed also by users that are not logged in. This can, for example be used to create a custom login page where the script is used to authenticate the user.

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