Chapter 16. ReportServer Scripting

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$

Tip: With the -w flag (e.g. exec -w hello1.groovy) you can redirect the script output (or the error output) to a separate window.

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

Tip: If you browse for a specific object in JavaDoc API (datasource, 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 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

Remark. If a script is in a folder that is marked as web accessible (see Chapter 6.) 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.