Getting Started with Scripting

In this tutorial we introduce the scripting capabilities of ReportServer Enterprise Edition and look at four standard use cases for scripting: Script Reports, Script Datasources, Maintenance Scripts and Script Extensions. For a detailed introduction into scripting we refer to the ReportServer Scripting Guide. For this tutorial we assume a basic knowledge of programming, preferably in Java or Groovy.

Scripting 101

ReportServer comes with a scripting interface that allows you to run scripts written in the Groovy language in the context of ReportServer. ReportServer scripts are stored in the internal file server. In order to be executable they have to be placed somewhere beneath the bin folder. Usually, when working with scripts you will use the ReportServer terminal. You can open the terminal by pressing CTRL+ALT+T.

The ReportServer terminal mimicks a standard unix terminal. You can navigate to specific folders via the cd (change directory command) and show the contents of the current folder via the ls (list) command. Running the ls command on a freshly opened terminal should return the following output.

reportserver$ ls
datasources	tsreport
reportmanager	usermanager
dadgetlib	
fileserver

ReportServer knows various virtual file systems for the various components present in ReportServer. That is, for users, reports, datasources, TeamSpaces and dashboards there exists a virtual file system that you can access via the terminal. In addition, the fileserver entry provides the root to the internal file system which we are going to use to store and run scripts.

To move to the root of the internal file server issue the following command.

reportserver$ cd fileserver/

The terminal supports auto completition. To try to autocomplete a command, press the TAB key.

If you again run the ls command you should now see the contents of the root folder of the internal file system (compare the output to the view provided in the administration module; they should be identical). Scripts need to be placed somewhere beneath the bin folder. If the folder does not yet exist, you can create it via

reportserver$ mkdir bin

Note that, contrary to standard file systems ReportServer currently allows to have two (or more) identically named files (or folders) within the same folder. Hence mkdir bin will not throw an error message even if a folder with the name bin already exists. It is generally adviseable not to make use of this feature and we note that this might also change in future versions of ReportServer.

Let us create a directory called getstarted beneath the bin folder and move there.

reportserver$ mkdir /fileserver/bin/getstarted
reportserver$ cd /fileserver/bin/getstarted/

In order to work with text files in the terminal there are two important commands that you should know: createTextFile takes a single argument and will create a new text file and open it for editing. editTextFile opens an existing file for editing. Again note that createTextFile, similarly to mkdir, does not check whether the file already exists, and allows you to create multiple files with the same name. Consider the following sequences of commands

reportserver$ createTextFile test.txt
file created
reportserver$ createTextFile test.txt
file created
reportserver$ ls -l
10469	test.txt	FileServerFile
10472	test.txt	FileServerFile
reportserver$ 

By running the command createTextFile test.txt twice we have created two files with name test.txt. If you run the ls command with the -l flag, then you are given additional details, such as the ID of each element. In order to remove a file you can use the rm command. This command takes a single argument which points to the file you want to remove. This can be the name of the element in which case the first found element is removed. Alternatively you can remove an element via its id as

rm id:10469

Thus, the following sequence of commands clears our getstarted directory again:

reportserver$ rm id:10469
reportserver$ rm test.txt
reportserver$ ls
reportserver$

It is now time to create our very first script. Let us create the traditional hello world script.

reportserver$ createTextFile hello.groovy

Scripts by convention are given the suffix .groovy or .rs. Scripts can return a single object which if possible is printed to the console. Thus, to print the string Hello World onto the terminal we can issue a single return statement

return "Hello World"

Scripts can be executed directly from the terminal via the exec command. Sure enough, running exec hello.groovy writes Hello World onto the console.

reportserver$ exec hello.groovy
Hello World

Congratulations, you have just run your first ReportServer script. Before we dig deeper, let us discuss one of the most important aspects of scripting: debugging.

Error Handling

Let's be honest. Writing scripts that are flawless on the first attempt is almost impossible. Especially, once you are starting on a bit more complex scripts. Thus, being able to read error messages and find the bugs is one of the most important skills you will need to learn. A solid background in programming is, naturally, very helpful for this. However, don't be discouraged, even if you do not have a strong programming background. ReportServer tries to simplify error messages and point you towards the line that contains the error. If that fails, java stacktraces, though long and bulky at first, will become easier to read with time. And finally, the ReportServer community will help you, if you get stuck. So, to prepare you for this we start our scripting exploration with looking at how things go wrong. Before you try out the example, we suggest to read to the end of this section.

So how do error messages look like? Let us create a new script called error1.groovy (createTextFile error1.groovy) with the following contents (note that this script has a small bug):

							import net.datenwerke.rs.core.service.reportmanager.ReportService;
							
							def reportService = GLOBALS.getInstance(ReportService.class);
							
							reportService.getAllReports().each { report ->
								tout.println(report.getname());
							}
							
							return null;
							

Don't worry, if there is lots that you do not understand at this point. This script is, indeed, much more complex than our previous hello world script. In short, it uses the ReportService that is provided by ReportServer to access reports that are stored in the system. We use the service to get a list of all the reports in the system (getAllReports) and then for each of the reports we run the following Groovy closure:

tout.println(report.getname());

A Groovy closure is a small code snippet that can be stored in a variable or passed as an argument to a function. A closure is wrapped in curly brackets, and consists of one or two parts. There can be an optional first part to define a (comma separated) list of parameters. In the above example this is

report ->

where we specify that there exists a single parameter which we name report. Then there is the (mandatory) closure body, in our case

tout.println(report.getname());

Here we use the special object tout which is given to every ReportServer script and allows to write to the terminal via its println method.

So let's put this together. The script uses the ReportService to get all the reports in the system and then for each of the reports it executes the code provided by the closure which simply accesses the report's name and prints it to the console. Finally, since the script does not return anything we add a return null to the end of the script.

As mentioned earlier, the script contains a small bug, and if we execute it ReportServer will print the following error message.

reportserver$ exec error1.groovy
Script execution failed.
error message: No signature of method: net.datenwerke.rs.base.service.reportengines.table.entities.TableReport.getname() is applicable for argument types: () values: []
Possible solutions: getName(), getName(), setName(java.lang.String), getType(), getAcl(), getAt(java.lang.String) (groovy.lang.MissingMethodException)
script arguments:
file: error1.groovy (id: 10702, line 6)
line number: 6 (5)
line: tout.println(report.getname());

Let us go through this line by line. The first line tells us, that the script execution failed. Ok, so this is what we expected. The next line contains the error message, and this often already pinpoints the problem. In this case the message says that there is no signature of method

net.datenwerke.rs.base.service.reportengines.table.entities.TableReport.getname()

Here everything until the last full stop identifies a class, and the final part a method. In short, the error message says that the (...)TableReport class (which represents a Dynamic List) does not have the method getname().

The error message then continues to give some information about the script that was executed, printing the arguments with which the script was called, a line number in which the error is suspected (line 6), and the line in question.

To rectify the error, we need to change report.getname() into report.getName(). With this change, the code the runs smoothly.

Now, at times the information provided by ReportServer does not allow to pinpoint the error. In this case it can be helpful to run the script with the -t flag which tells ReportServer to output the entire stack trace in case an error occurs. If we run our original error1.groovy script with the -t flag, we will get the following response:

reportserver$ exec -t error1.groovy
net.datenwerke.rs.scripting.service.scripting.exceptions.ScriptEngineException: javax.script.ScriptException: groovy.lang.MissingMethodException: No signature of method: net.datenwerke.rs.scriptreport.service.scriptreport.entities.ScriptReport.getname() is applicable for argument types: () values: []
Possible solutions: getName(), getName(), setName(java.lang.String), getType(), getAcl(), getAt(java.lang.String)
------- SCRIPT ERROR INFO -------
Script execution failed.
error message: No signature of method: net.datenwerke.rs.scriptreport.service.scriptreport.entities.ScriptReport.getname() is applicable for argument types: () values: []
Possible solutions: getName(), getName(), setName(java.lang.String), getType(), getAcl(), getAt(java.lang.String) (groovy.lang.MissingMethodException)
script arguments:
file: error1.groovy (id: 17459, line 6)
line number: 6 (5)
line: tout.println(report.getname());

at net.datenwerke.rs.scripting.service.scripting.engines.GroovyEngine.eval(GroovyEngine.java:79)
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: groovy.lang.MissingMethodException: No signature of method: net.datenwerke.rs.scriptreport.service.scriptreport.entities.ScriptReport.getname() is applicable for argument types: () values: []
Possible solutions: getName(), getName(), setName(java.lang.String), getType(), getAcl(), getAt(java.lang.String)
at org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.eval(GroovyScriptEngineImpl.java:347)
at org.codehaus.groovy.jsr223.GroovyCompiledScript.eval(GroovyCompiledScript.java:41)
at net.datenwerke.rs.scripting.service.scripting.engines.GroovyEngine.eval(GroovyEngine.java:74)
... 23 more
Caused by: groovy.lang.MissingMethodException: No signature of method: net.datenwerke.rs.scriptreport.service.scriptreport.entities.ScriptReport.getname() is applicable for argument types: () values: []
Possible solutions: getName(), getName(), setName(java.lang.String), getType(), getAcl(), getAt(java.lang.String)
at org.codehaus.groovy.runtime.ScriptBytecodeAdapter.unwrap(ScriptBytecodeAdapter.java:56)
at org.codehaus.groovy.runtime.callsite.PojoMetaClassSite.call(PojoMetaClassSite.java:46)
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:45)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:110)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:114)
at Script6$_run_closure1.doCall(Script6.groovy:6)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:90)
at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:324)
at org.codehaus.groovy.runtime.metaclass.ClosureMetaClass.invokeMethod(ClosureMetaClass.java:292)
at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:1016)
at groovy.lang.Closure.call(Closure.java:423)
at groovy.lang.Closure.call(Closure.java:439)
at org.codehaus.groovy.runtime.DefaultGroovyMethods.each(DefaultGroovyMethods.java:2027)
at org.codehaus.groovy.runtime.DefaultGroovyMethods.each(DefaultGroovyMethods.java:2012)
at org.codehaus.groovy.runtime.DefaultGroovyMethods.each(DefaultGroovyMethods.java:2053)
at org.codehaus.groovy.runtime.dgm$162.invoke(Unknown Source)
at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite$PojoMetaMethodSiteNoUnwrapNoCoerce.invoke(PojoMetaMethodSite.java:271)
at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite.call(PojoMetaMethodSite.java:53)
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:45)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:110)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:122)
at Script6.run(Script6.groovy:5)
at org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.eval(GroovyScriptEngineImpl.java:344)
... 25 more

That is quite a bit longer, than the standard error message. The first part, however, is identical, it contains the summary which we already saw earlier. Then, starting from line 13, we have the stack trace. The first line of the stack trace gives you the outer most error location which in our case is the location in ReportServer that calls the erroneous script: the GroovyEngine. This doesn't provide much information and similarly the following lines which provide information on the call stack that led to executing the script won't hold much information. The interesting bit starts with line 37 and the Caused by clause. With each Caused by we are getting one step closer to the original error position, so usually the last one is the one you should be looking for. We find the last Caused by in line 43 and it tells us that the error message originated in the call

at org.codehaus.groovy.runtime.ScriptBytecodeAdapter.unwrap(ScriptBytecodeAdapter.java:56)

From there we can trace the error backwards by looking at the following lines. We get our hit in line 50.

at Script6$_run_closure1.doCall(Script6.groovy:6)

Anything that is prefixed Script refers to a dynamic groovy file, and since there is only a single file in our example, Script6 represents our script error1.groovy. The last number in each line represents the line number of the corresponding command, that is, line 50 of the stack trace tells us that the last line that was executed in our script was line 6.

This has been quite a lot of information, and don't worry if the stack trace still looks like a huge amount of nonsense. Usually, the summary message is sufficient to trace an error, and, with time, you will get used to reading stack traces.

Groovy with ReportServer 101

Now that we have an idea of how to deal with errors, let's have a look at the basic Groovy that you usually need when scripting. Being a fully matured programming language we can't hope to give you a complete picture. The good news is, there is tons of material on the net that tells you about how to get started with Groovy, and once you've mastered the basics, how to dig deep. A good start is the official Groovy website.

We have already seen the traditional Hello World program

return "Hello World"

Now, if you open the next best Groovy tutorial the hello world example will be slightly different. It will tell you that to print Hello World to the console you have to write

println "Hello World"

and they'd be right if you were using Groovy in a standard environment. Since we run Groovy within the context of ReportServer things are sometimes a bit different and this is one of these times. The println command tells groovy to print the following string to its standard output stream. Usually, that would be the console, for example, of your development environment. In ReportServer, the standard output stream of scripts is sent to the ReportServer log files and thus, if you execute the above script, although you will not see any message on the terminal window you will see the string Hello World if you expect the log files.

In order to write to the terminal in ReportServer you need to use the tout object which is short for (Terminal Output). Thus, if we translate the above to ReportServer we get

tout.println "Hello World"

If we now execute this script (assuming it is called hello.groovy), we get the expected result:

reportserver$ exec hello.groovy
Hello World

Additionally to writing directly to the console, by convention ReportServer will print the result of the script (whatever the script returns) onto the console which is why

return "Hello World"

produces an equivalent result. One thing to note is that the return key word is not necessary. In other words, the script will return the result of the last instruction. So the following would again have an identical effect:

"Hello World"

Now that we know how to print messages on to the terminal, let us introduce variables. Variables are defined via the def key word. The following code

							def myVariable = "I am a variable";
							tout.println myVariable;
							

prints I am a variable. Statements in Groovy can be terminated by a semicolon (as in the above example) but they don't have to. So equivalently we can write

							def myVariable = "I am a variable"
							tout.println myVariable
							

Naturally, variables can hold not just strings but also, for example, numbers and we can work with variables.

							def a = "I am a String"
							a = 19 // once we have defined a variable, no more def
							/* this is a multi-line 
							comment */
							// this is a single line comment
							def b = 23
							def c = a + b
							tout.println c
							

The above code first defines a variable a to be the string "I am a String". It then changes a to 19, defines b as 23 and c as the sum of a and b. Also note the two ways of writing comments in Groovy via a double slash and slash-star. Strings as we have seen are enclosed in double quotes. Additionally, Groovy knows the following syntax for strings.

							def a = "I am a String"
							def b = 'I am also a String'
							def c = """I am a 
							multi-line
							String"""
							tout.println c
							

When using double quoted strings, you can also refer to other variables like so

							def firstname = "John"
							def lastname = 'Doe'
							tout.println "Hello ${firstname} ${lastname}"
							

Groovy has quite a few tricks when it comes to composing strings so you should definitely have a look at the templating capacitites that groovy has to offer (a good start is to search for "Groovy Templates").

Groovy supports the standard C/Java-like conditional and loop expressions.

							def a = 5
							if(a < 5) {
							  tout.println "small number"
							} else {
							  tout.println "big number"
							}
							
							for(def i = 1; i < a; i++)
							  tout.print "."
							
							tout.println "\nThere we printed some dots in a single line"
							

You will probably often use lists and maps.

							def list = ["I", "am", "a", "list"]
							tout.println list.size() // outputs 4
							tout.println list[1] // outputs am
							
							def map = [ "I" : "You", "am" : "are"]
							tout.println map.size() // outputs 2
							tout.println map["am"] // outputs are
							

So be sure to check out the Groovy Collections as they have quite some nifty functionality. What we already saw in the error handling section was an example of how to iterate over a list of items via the each method of a list.

							def list = ["I", "am", "a", "list"]
							list.each { it -> 
								tout.println it;
							}
							return null;
							

This prints each item of the list onto a single line. That is, the each method takes as argument a Groovy Closure which is executed for each of the lists items. For further information on the each method and other helper methods to use with lists see the Groovy doc for lists.

When you want to work with ReportServer or advanced Groovy objects you need to import them

							import groovy.xml.MarkupBuilder
							
							def writer = new StringWriter();
							def hb = new MarkupBuilder(writer);
							hb.html {
								head {
									title "Some document title"
								}
								body {
									h1 "Some heading"
									p "And a corresponding paragraph"
								}
							}
							
							tout.println writer
							

Now that was some shorthand to write an HTML document the Groovy way with the MarkupBuilder object. The StringWriter object is part of the java.io package which is imported by default by Groovy and we could thus use it directly without additionally importing it. For more informatin on Groovy program structure and default imports look here.

So much for our little Groovy 101. As we mentioned at the beginning, this barely scratches the surface, but we hope that it helps to understand the remaining parts of this tutorial and that it encourages you to search the net for further information on Groovy. There is tons out there. And, if you get stuck, try our community forum or, if it is a programming question rather than a ReportServer question, have a look at stack overflow.

Installing Libraries

Before we look at what you can do with scripts in ReportServer let us briefly discuss how to install additional libraries for scripts that are not yet part of ReportServer. For example, if you want to access a REST webservice then you might want to use the Unirest library.

In order to install additional libraries they need to be placed on ReportServer's classpath. The easiest way to do so is to use the lib directory of the external configuration dir. In case you used the Bitnami installer this would be

INSTALL_DIR/apps/reportserver/reportserver-conf/lib

If you did a manual installation and followed one of our tutorials for Linux or Windows then this would be

/opt/reportserver/lib

or

C:\Program Files\reportserver\lib

Once you've placed the .jar library files into the right location, you will need to restart ReportServer to make it aware of the new library.

Script Reports

The first use case we want to disucss are script reports. Script reports are the swiss army knife of reporting. They can be anything from a static list of data to a complex HTML5 application. The idea is that you use a ReportServer script to generate the output and hence, anything that you can program can be a report. So let us start with a simple Hello World example once more.

Hello Script Report

For a script report we need two ingredients, a script and a script report. Let us begin by preparing the script. We create a new script called helloreport.groovy with the following contents.

							return """<html>
 <head>
  <title>A hello world</title>
 </head>
 <body>
  <h1>Hello Script Report</h1>
 </body>
</html>"""
							

What we have done is to simply return a static HTML page. While script reports can generate arbitrary outputs (e.g., PDFs, Excel or even custom byte formats) HTML is necessary when you want to present the result within ReportServer's report preview. Of course, just like other reports, a script report can also offer multiple output formats. We'll get to that in a moment.

Now that we have the script, the next step is to create the script report. For this we go to the Reports section in the administration and create a new Script report. What we need to configure for now is the name of the report (e.g., Hello Script Report) and we need to set the script to our previously created helloreport.groovy script. We can leave the other config options (datasource, arguments, export formats) blank for the moment.

Once we submit the report configuration and then open the report in ReportServer you should see a blank page with a single headline: Hello Script Report. Congratulations, you have just created your first script report.

Permissions. As with every other report in ReportServer script reports can only be executed by users that have the correct permissions. Note, however, that in case of a script report it is not sufficient to have the execute permission on the report object but that the user also needs the execute permission on the corresponding script.

Parameters

Naturally, script reports can make use of parameters. So let us add a a simple text parameter with key name to our script report. Now the question is, how can we access the parameter from within the script. To access parameters, ReportServer adds the special variable parameterMap to each script when it is executed via a script report. The parameterMap, as the name suggests is a map containing an entry for each of the parameters. Thus, to access our previously created parameter, we could use the following.

							def name = parameterMap['name']
							return """<html>
							<head>
							  <title>A hello world</title>
							 </head>
							 <body>
							  <h1>Hello ${name}</h1>
							 </body>
							</html>"""
							

Datasources

If you look at the properties of a script report, you see that besides the script you can also provide a datasource. While scripts could leverage ReportServer's services to access datasources directly, providing a datasource as part of the configuration allows you to access the data more easily as ReportServer takes care of opening and closing a connection. Additionally, it would allow to reuse the same script with multiple datasources.

If you provide a database datasource as configuration (note that you do not need to provide a query), ReportServer will place a special connection variable into the scope of the script, that you can then use to load data from the datasource. Assuming we have configured our script with the demo datasource that is shipped with ReportServer. In this case, we could use the connection to read data using Groovy's SQL object as follows:

							new SQL(connection).eachRow("SOME SQL SELECTION") { row -> 
								/* do something with the data */
							}
							

Following is a complete example (note that we need to import the SQL object). Additionally, we use Groovy's MarkupBuilder to get a somewhat more structured script.

							import groovy.sql.Sql;
							import groovy.xml.MarkupBuilder;
							
							/* create writer for catpuring html output */
							def writer = new StringWriter()
							
							/* create report */
							new MarkupBuilder(writer).html {
								head {
									title 'A script report with a data source'
								}
								body {
									h1 'Customer List'
									ul {
										/* get customer data from datasource */
										new Sql(connection).eachRow('SELECT CUS_CUSTOMERNAME FROM T_AGG_CUSTOMER ORDER BY 1') { row ->
											li row.CUS_CUSTOMERNAME
										}
									}
								}
							}
							
							return writer.toString();
							

Output Formats

So far we have only generated HTML reports. As mentioned before, script reports are of course not limited to producing HTML, although you would need to produce HTML if you want to show a preview within ReportServer. In order to add multiple output formats, you need to configure the Export formats property on the script report. Here you can provide a comma separated list of formats. For example, if you wanted to produce HTML and PDF output you could set the property to

HTML,PDF

If we adapt our previous script report with the above configuration and execute it, you will notice that now ReportServer offers you to export the report to HTML and PDF. However, whatever output format you choose, ReportServer will send you a text file with the HTML document. The reason is that we have not yet taken care of the different formats within our script.

In order to tell ReportServer what type of file it should provide as download, we need to return a special object of type net.datenwerke.rs.core.service.reportmanager.engine.CompiledReport (if you have downloaded ReportServer's API documentation search for CompiledReport). Further information on working with these CompiledReport objects can be found in the Scripting Guide. For the most common types (HTML and PDF) there is a shortcut which you can access via the special variable renderer.

Let us adapt our previous example, to produce PDF and HTML depending on the selected output format.

							import groovy.sql.Sql;
							import groovy.xml.MarkupBuilder;
							
							/* create writer for catpuring html output */
							def writer = new StringWriter()
							
							/* create report */
							new MarkupBuilder(writer).html {
								head {
									title 'A script report with a data source'
								}
								body {
									h1 'Customer List'
									ul {
										/* get customer data from datasource */
										new Sql(connection).eachRow('SELECT CUS_CUSTOMERNAME FROM T_AGG_CUSTOMER ORDER BY 1') { row ->
											li row.CUS_CUSTOMERNAME
										}
									}
								}
							}
							
							if(outputFormat  == 'pdf')
								return renderer.get("pdf").render(writer.toString())

							return renderer.get("html").render(writer.toString())
							

The only parts that changed are the last three lines. The special variable outputFormat stores the output format specified by the user. In case of the preview in ReportServer the variable will contain the String preview. Otherwise it contains the format (in lowercase). In line 23 we check whether the output format specifies that we should produce a PDF report. To generate PDF, ReportServer offers a shortcut that allows to transform HTML directly into PDF. To get the PDF renderer and render html you use

							renderer.get("pdf").render(StringContainingHTML)
							

The renderer transforms the HTML into a valid PDF document and creates the propert CompiledPDFReport object to let ReportServer know that the output is PDF.

Besides the PDF renderer there are also renderers for Microsoft Word (use renderer.get("docx")) and, of course, for HTML which we used in the very last line.

Script Datasources

While script reports provide you with a tool to implement almost any type of report requirement, script datasources allow you to incorporate almost any data source into the system to then use any of ReportServer's reporting engines (such as the Dynamic List) to report upon this data. In the following we are going to implement a datasource that accesses an XML dataset of the World Health Organization (WHO) to then use a Dynamic List to report upon it. The WHO offers various open datasets via http://www.who.int/gho/en/ and in the following we will show how to import a dataset on life expectancy per country which is available at

• WHO: Life expectancy by country

The dataset has the following format:

<Data xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://apps.who.int/gho/athena/data/GHO/WHOSIS_000001,WHOSIS_000002.xsd?format=xml&profile=simple-schema">
	<Fact>
		<COUNTRY>Angola</COUNTRY>
		<GHO>Life expectancy at birth (years)</GHO>
		<PUBLISHSTATE>Published</PUBLISHSTATE>
		<REGION>Africa</REGION>
		<SEX>Female</SEX>
		<YEAR>2015</YEAR>
		<Display>54.0</Display>
		<Numeric>54.02480</Numeric>
	</Fact>
	<Fact>
		<COUNTRY>United Arab Emirates</COUNTRY>
		<GHO>Life expectancy at birth (years)</GHO>
		<PUBLISHSTATE>Published</PUBLISHSTATE>
		<REGION>Eastern Mediterranean</REGION>
		<SEX>Both sexes</SEX>
		<YEAR>2015</YEAR>
		<Display>77.1</Display>
		<Numeric>77.06616</Numeric>
	</Fact>
	...
</Data>

In order to use this data set in ReportServer we need a script datasource that reads the above XML and transforms it into a format that ReportServer understands which in most cases will be an RSTableModel object. But, before we explain the details, let us show you the entire script.

							import net.datenwerke.rs.base.service.reportengines.table.output.object.*;

							/* the url from which to load the data */
							def url = "http://apps.who.int/gho/athena/data/GHO/WHOSIS_000001,WHOSIS_000002.xml?profile=simple&filter=COUNTRY:*;YEAR:2015"

							/* read in data */
							def dataText = new URL(url).getText();

							/* prepare table model */
							def tableDefinition = new TableDefinition(
							  ['Country', 'Region', 'Sex', 'Year', 'Life Expectancy'],
							  [String.class, String.class, String.class, String.class, BigDecimal.class]
							);   
							def model = new RSTableModel(tableDefinition);

							/* process data */
							def data = new XmlSlurper().parseText(dataText);
							data.Fact.each { it ->
							   model.addDataRow(it.COUNTRY.toString(),it.REGION.toString(),it.SEX.toString(),it.YEAR.toString(),it.Numeric.toBigDecimal())
							}

							return model;
							

22 lines, not bad. So let us go through this line by line. First line, we need to import the necessary objects to create the RSTableModel, which provides an internal object representation of a table for ReportServer. The whole point of the script, so to speak, is to then transform the XML structure into an RSTableModel.

In line 4, we simply store the URL with the data feed to then in line 7 read in the entire document to store it in variable dataText. Lines 10 to 14 prepare the RSTableModel object that we will use. To create an RSTableModel we need to tell ReportServer how the table will look like and we do this via a TableDefinition. The TableDefinition takes two lists, one to define the names of the table's columns and the second to define the data type of each column. In the example, we are going to transform the XML into the following table:

Country	Region	Sex	Year	Life Expectancy
Angola	Africa	Female	2015	54.02480
...	...	...	...	...

Here, the first four columns are of type String, the last column is a decimal number and hence of type BigDecimal.

Once we have a TableDefinition object, we can create an RSTableModel as done in line 14. What is left is to add the actual data to the model which we do in lines 17 to 20.

To process XML, Groovy offers a nice helper called the XMLSlurper. The first step is to read in our XML text into the XMLSlurper which happens in line 17. We can then use the resulting object to access various parts of the XML. For example,

data.Fact[0].COUNTRY

would access the COUNTRY field within the first Fact tag. Similarly, data.Fact contains a list of all the Fact tags and we use the each method to process each in turn (line 18). Line 19 now contains the transformation of a single Fact tag to a data row. We add a new data row to our RSTableModel via the addDataRow method and provide the data fields as input. Here we need to ensure that the fields are of the appropriate type and we use the toType helper methods provided by the XMLSlurper.

And, that is it. You can now use this script to power a script datasource which can then be used, for example, together with a Dynamic List. To create a script datasource go to the Datasources section in the Administration module and create a new Script Datasource which you configure with the script.

Behind the Scenes

When working with script datasources it is important to understand what happens behind the scenes. If your script returns an RSTableModel, ReportServer will load the resulting data into its Internal Database, that is, into a temporary database table. This allows all the reporting engines to access the data as they would access any other database tables and thus all the functionality of the Dynamic List or Jasper can be used when reporting on a scripted data set.

Loading data from an external source, processing it, and then loading it into a database takes some time which is why you might not want to perform these steps for every single request that is made to the data set. For this, ReportServer allows you to define whether or not a data set defined via a script datasource is cached and if you decide to enable a cache for how long the data is cached before it is recomputed. By default the Database cache option of a script datasource is set to -1 which means that the data is cached indefinitely. To turn off the cache, set this entry to 0. Any positive integer specifies a cache in minutes, for example, a setting of 10 would mean that the data is recomputed after 10 minutes (but only if it is accessed).

In case you want to clean the cache manually, you can simply hit the Apply button on the script datasource. That is, any change on the script datasource (and even if no property actually changed) will cause the cache to be flushed.

Maintenance Scripts

A very useful property of scripts is that they can access the entire ReportServer object model and thus can be used to automate and maintain the platform. For example, you can automatically generate users, or reports, synchronize user groups with TeamSpaces or automatically update user dashboards. Or consider the case that some fields changed in your data warehouse and you now want to understand the impact this is having on existing reports. You could, for example, search for all Dynamic Lists that contain a particular attribute, or even perform a test execution of all reports in the system.

In order to make use of these techniques you will need a basic understanding of the ReportServer object model and the helper services provided by ReportServer which goes far beyond this tutorial. A good place to start is the scripting guide as well as the various blog posts and tutorials. Also, you might want to have a look at the API documentation and ReportServer sources. For questions and pointers also try our community forum or if your company has a support contract, our development team will be happy to help.

In the introduction we have already seen an example of how to use ReportServer service objects to loop over all the reports in the system. For this we used the ReportService object provided by ReportServer. The most important services to manipulate objects are:

Service	Description	Full name
ReportService	Manage reports	net.datenwerke.rs.core.service.reportmanager.ReportService
UserManagerService	Manage users, groups and OUs	net.datenwerke.security.service.usermanager.UserManagerService
DatasourceService	Manage datasources	net.datenwerke.rs.core.service.datasourcemanager.DatasourceService
FileService	Manage the internal file system	net.datenwerke.rs.fileserver.service.fileserver.FileServerService
TeamSpaceService	Manage TeamSpaces	net.datenwerke.rs.teamspace.service.teamspace.TeamSpaceService
TsDiskService	Manage the file system of a TeamSpace	net.datenwerke.rs.tsreportarea.service.tsreportarea.TsDiskService

Each service manages a set of related entities, for example, the ReportService is used to fetch or create report entities. But there is not only one entity to represent a report, but one entity per report type. For example, Dynamic Lists are internally represented by net.datenwerke.rs.base.service.reportengines.table.entities.TableReport while a BIRT report would be represented by objects of type net.datenwerke.rs.birt.service.reportengine.entities.BirtReport. All together, ReportServer consists of more than 180 different entity objects.

To get a list of all provided services and entities in ReportServer, download the API documentation.

When starting to manipulate ReportServer's object model with scripts you can seriously damage your installation. You should thus develop scripts always on a seperate development server and only deploy such scripts that have been thoroughly tested.

To access ReportServer internals we have said that you will need to access ReportServer's services. For this, ReportServer provides yet another special variable to every script which is called GLOBALS and which offers various helper methods. In particular, it offers the method getInstance which allows you to obtain a ReportServer service. To load a ReportServer service you will need to import the service class and then use GLOBALS.getInstance to access the service. The following example loads the UserManagerService.

							import net.datenwerke.security.service.usermanager.UserManagerService;
							
							def userService = GLOBALS.getInstance(UserManagerService.class);
							

We could now, for example, use the service to list all users in the system.

							import net.datenwerke.security.service.usermanager.UserManagerService;
							
							def userService = GLOBALS.getInstance(UserManagerService.class);
							
							userService.getAllUsers().each{ it -> tout.println it.getFirstname() + " " + it.getLastname() }
							
							return null;
							

So what about creating a new user? The user entity is net.datenwerke.security.service.usermanager.entities.User and we can create a new user object by

							import net.datenwerke.security.service.usermanager.entities.User;
							
							def user = new User();
							user.setUsername("testuser");
							user.setFirstname("John");
							user.setLastname("Testuser");
							

This script alone, however, does not add a user to the system. For this, we would need to make ReportServer aware of the user and attach the user into an organizational unit (i.e., add it as a child of a user folder in the user tree). For the first part we need to call the persist method of the UserManagerService and pass the newly created user. To add the user to a folder, we need to load a folder that is already in the system. To obtain the root folder of the user tree we can use the method getRoots() of the UserManagementService. Note that the method is called getRoots and not getRoot and indeed it returns a list rather than a single object. This is due to the fact that the abstract implementation of a tree in ReportServer can have multiple roots. In case of the user tree, there will, however, always be only one root object so we can access the folder via getRoots().get(0). Once we have the folder, we can add the user by calling the addChild object of the folder.

Note that all tree-based entities, i.e., any entity that is represented in a tree in ReportServer such as users or reports have a method called setParent. It may be tempting to add a user to a folder by calling user.setParent(folder). Don't!

Thus, we have all that we need to put the example together. Following is the resulting code.

							import net.datenwerke.security.service.usermanager.UserManagerService;
							import net.datenwerke.security.service.usermanager.entities.User;
							
							/* load service */
							def userService = GLOBALS.getInstance(UserManagerService.class);
							
							/* create user */
							def user = new User();
							user.setUsername("testuser");
							user.setFirstname("John");
							user.setLastname("Testuser");
							
							/* load root folder and add user */
							def root = userService.getRoots().get(0);
							root.addChild(user);
							
							/* persist user */
							userService.persist(user);
							
							return "added new user";
							

If you run the above script on the terminal you will see that it returns with the message added new user, but if you go to the user management and look the user isn't there. The reason for this is that, by default, scripts are executed in non-commiting mode meaning that changes to ReportServer's object model will not be made persistent. This provides an extra level of security to not accidentally cripple your system by running the wrong script. In order to run a script in commiting mode you need to call exec with the -c flag. Thus, if your script is named addUser.groovy then you need to call:

exec -c addUser.groovy

Now, if you refresh the user tree you should see John Testuser beneath the root OU. We'll leave you to explore the possibilities at this point. And once more let us warn you: ReportServer scripts are a fantastic maintenance tool. However, remember that with great power comes great responsibility, so please make sure to develop and test your scripts on a dedicated development machine.

Script Enhancements

The final use case that we want to discuss in this tutorial is the enhancement of ReportServer using scripts. ReportServer is build upon a flexible Hooking mechanism which allows to register hooks that can add or influence various functionality. For example, you can register a hook that is called before a report is executed to either change or deny execution. A list of all available hooks is given in the API documentation. Besides registering hooks, you can also register for certain events, such as the event that an entity changed or was removed. You could then either perform an action or deny the change by throwing an exception.

As an example we are going to implement the net.datenwerke.rs.core.service.reportmanager.hooks.ReportExecutionNotificationHook which is called before a report is executed. Hooks that were designed to be implemented by scripts usually come with a default implementation called HOOKNAMEAdapter. In the case of ReportExecutionNotificationHook we should thus find the ReportExecutionNotificationHookAdapter. You should always implement the adapter rather than the hook directly. To implement a hook in Groovy we create a map that for each of the methods we want to implement has a closure. In the example, we want to implement the doVetoReportExecution method and deny execution after seven pm. We understand that reporting is fun, but at some point we should call it a day. To implement the hook we use the following structure

							import HOOK_NAME;
							import HOOK_NAME_ADAPTER;
														
							def callback = [
								method1 : { params ->
								},
								method2 : { params ->
								}
							] as HOOK_NAME_ADAPTER;
							

If you are familiar with Java, then what we are doing is that we provide an anonymous implementation of the hook. Using a map and the as class keyword is a Groovy shortcut for implementing interfaces.

Note that we import both the actual hook and the adapter. We will see in a moment why. So here is the implemataton of ReportExecutionNotificationHook.

							import net.datenwerke.rs.core.service.reportmanager.hooks.ReportExecutionNotificationHook;
							import net.datenwerke.rs.core.service.reportmanager.hooks.adapter.ReportExecutionNotificationHookAdapter;
														
							def callback = [
								doVetoReportExecution : { report, parameterSet, user, outputFormat, configs ->
									if(Calendar.getInstance().get(Calendar.HOUR_OF_DAY) > 19)
										throw new RuntimeException("Have some free time.");
								}
							] as ReportExecutionNotificationHookAdapter;
							

All that is missing now, is to make ReportServer aware of the hook. That is, to hook-in our implementation. For this, we once more use the GLOBALS object which provides a service called callbackRegisry which manages the hooking in for us. Here we finally need the actual hook class (and not the adapter) to tell the service where to hook this in. Following is the completed example.

							import net.datenwerke.rs.core.service.reportmanager.hooks.ReportExecutionNotificationHook;
							import net.datenwerke.rs.core.service.reportmanager.hooks.adapter.ReportExecutionNotificationHookAdapter;
							
							def callback = [
								doVetoReportExecution : { report, parameterSet, user, outputFormat, configs ->
									if(Calendar.getInstance().get(Calendar.HOUR_OF_DAY) > 19)
										throw new RuntimeException("Have some free time.");
								}
							] as ReportExecutionNotificationHookAdapter;
							
							GLOBALS.services.callbackRegistry.attachHook("NO_WORK_AFTER_SEVEN", ReportExecutionNotificationHook.class, callback)
							

Note that we've provided a name for our implementation: NO_WORK_AFTER_SEVEN. This is used, such that if we execute the script twice (for example, if we want to change something in the implemenation), the hook is replaced rather than that a second one is added. It also allows us to deregister the hook at a later time via the detachHook method of the callbackRegistry.

Once you've executed the above script, report executions after seven are not longer possible and this is also where we end this tutorial. We hope that this provided some first insights into scripting. Scripting is one of the most complex but also one of the most powerful tools in ReportServer. Furthermore, scripting can be fun, not the least because it allows you to get the job done, whatever it is.

If you have any feedback to this tutorial, we are always happy to hear about it. Until then.

Happy Scripting.