Chapter 5. Script Reporting
Besides for administrative tasks, ReportServer scripts can be used for generating reports or for providing datasources that can then be used together with any other reporting engine within ReportServer. If you are familiar with scripting and fluent in HTML and CSS you will find script reports a simple to use reporting alternative that is highly flexible. Script reports usually render to HTML which allows to create highly dynamic reports using AJAX and advanced web technologies. Naturally, you are not limited to HTML reporting but are free to produce any output format you choose, for example, PDF or even a zip archive containing multiple files. ReportServer supports you in that it provides renderers to turn HTML formatted reports into PDF and Microsoft Word (docx) files. Furthermore, ReportServer provides a simple to use exporter to generate Microsoft Excel documents. An example of a script report using the PDF renderer is the report documentation report that is shipped with ReportServer (see Administrators guide).
As for datasources, script datasources give you the flexibility to easily integrate any sort of data with ReportServer. A script datasource can even accumulate data from various sources, that is, it can be used to implement a multi-platform join.
A script report consists of at least two objects: a script and a report. In addition a script report can be configured to use a datasource. In the following we are going to, step by step, build several example reports:
- The first report we are going to create is a simple static report that allows us to show the basic concepts of script reports. This includes exporting into various output formats, working with Groovy's (X)HTML builder and working with parameters and arguments. If you have read the chapter on script reports in the administrator's manual most of this should be familiar.
- The second script will explain how to work with datasources.
- The third and final example will explain how to create a dynamic report, that allows for user interaction and which uses a dynamic list as data backend.
By default script reports are supposed to generate HTML. Thus, all we really need is to return HTML code as in the following example (note that we are using Groovy's multiline string feature here).
return """\
<html>
<head>
<title>Hello World</title>
</head>
<body>
<h1>Hello World</h1>
</body>
</html>
"""Let us store this script as /bin/reports/report1.groovy. The next step is to define a script report. For this go to the report manager and create a new object of type script report. Provide a name and select the previously create script as script reference. After submitting the data you can execute the report as usual by simply double clicking on the item from the report manager. You should see that the report simply displays the content as defined by the HTML code. In contrast to other reports, there is no button for scheduling the report or exporting it. This is because we have not yet defined output formats for the report. For this go back to the script report in the report manager. Here you can define output Formats as a comma separated list. For example we can define
HTML, PlainSubmit the changes and reopen the report. You see that you now have two export options: HTML and Plain. However, both options produce the same, somewhat unexpected result. The browser displays the HTML code rather than the interpreted website. This is, because we have not specified the return type. For this, we need to return an object of type net.datenwerke.rs.core.service.reportmanager.engine.CompiledReport which besides the report's content contains information on, for example, the resulting mime type. Several predefined and simple to use objects are available:
- {net.datenwerke.rs.core.service.reportmanager.engine.basereports.CompiledTextReportImpl} For simple text documents.
- {net.datenwerke.rs.core.service.reportmanager.engine.basereports.CompiledHtmlReportImpl} For html documents.
- {net.datenwerke.rs.core.service.reportmanager.engine.basereports.CompiledDocxReport} For Microsoft Word documents.
- {net.datenwerke.rs.core.service.reportmanager.engine.basereports.CompiledXlsxReport} For Microsoft Excel documents.
- {net.datenwerke.rs.core.service.reportmanager.engine.basereports.CompiledJsonReport} For JSON documents.
- {net.datenwerke.rs.core.service.reportmanager.engine.basereports.CompiledCsvReport} For comma separated value documents.
- {net.datenwerke.rs.core.service.reportmanager.engine.basereports.CompiledPdfReport} For PDF documents.
Thus, to make the browser render the output we could adapt our above script to
import net.datenwerke.rs.core.service.reportmanager.engine.basereports.CompiledHtmlReportImpl
def report = """\
<html>
<head>
<title>Hello World</title>
</head>
<body>
<h1>Hello World</h1>
</body>
</html>
"""
return new CompiledHtmlReportImpl(report)While the browser now renders the exported report properly, it also does so when using the export option Plain. To differentiate between the two output formats scripts have access to a predefined variable outputFormat. This variable contains the selected output format. Consider the following adaption
import net.datenwerke.rs.core.service.reportmanager.engine.basereports.CompiledHtmlReportImpl
def report = """\
<html>
<head>
<title>Hello World</title>
</head>
<body>
<h1>Hello World</h1>
</body>
</html>
"""
if(outputFormat == 'plain')
return 'Hello World'
return new CompiledHtmlReportImpl(report)Note that, although the output format was defined as Plain, the script is given the output format in lower case and with leading and trailing whitespace removed. Now if you export the report to Plain you get the expected plain Hello World response, while an export to HTML will lead to a rendered Hello World response.
In the above example we wrote the HTML code as a plain text string. In the following example we use Groovy's Markup XML Builder.
import net.datenwerke.security.service.authenticator.AuthenticatorService
import net.datenwerke.rs.core.service.reportmanager.engine.basereports.CompiledHtmlReportImpl
import groovy.xml.*
def user = GLOBALS.getInstance(AuthenticatorService).currentUser
def writer = new StringWriter()
new MarkupBuilder(writer).html {
head {
title ( 'Hello World' )
}
body {
h1("Hello ${user.firstname}")
}
}
return new CompiledHtmlReportImpl(writer.toString())Besides using the Markup Builder we have personalized the greeting a bit. Let us add some extra styling:
import net.datenwerke.security.service.authenticator.AuthenticatorService
import net.datenwerke.rs.core.service.reportmanager.engine.basereports.CompiledHtmlReportImpl
import groovy.xml.*
def user = GLOBALS.getInstance(AuthenticatorService).currentUser
def writer = new StringWriter()
new MarkupBuilder(writer).html {
head {
title ( 'Hello World' )
}
body {
h1(style: 'color: #f00', "Hello ${user.firstname}")
p("Isn't that an easy way to create a report?")
}
}
return new CompiledHtmlReportImpl(writer.toString())Now that we have a simple styled report, let us export it not only to HTML but also to PDF and Microsoft Word. Basically, what we need to do is to output the byte code expected by a PDF reader (resp. a valid Word document). For this we can either use one of many java libraries such as Apache POI (http://poi.apache.org/). ReportServer, however, makes it easy for you to go from HTML directly to PDF and Word. For this you have access to two renderers via the predefined object renderer. First go back to the report manager and define the following output Formats
HTML, PDF, WordNow, we need to adapt the report
import net.datenwerke.security.service.authenticator.AuthenticatorService
import groovy.xml.*
def user = GLOBALS.getInstance(AuthenticatorService).currentUser
def writer = new StringWriter()
new MarkupBuilder(writer).html {
head {
title ( 'Hello World' )
}
body {
h1(style: 'color: #f00', "Hello ${user.firstname}")
p("Isn't that an easy way to create a report?")
}
}
if(outputFormat == 'word')
return renderer.get("docx").render(writer.toString())
if(outputFormat == 'pdf')
return renderer.get("pdf").render(writer.toString())
return renderer.get("html").render(writer.toString())You can now export the report to the various formats. Also note, that the HTML renderer that we used in the very last line is equivalent to using CompiledHtmlReportImpl.
DOT graphs https://graphviz.org/doc/info/lang.html allow to define Graphviz nodes, edges, graphs, subgraphs, and clusters in a text-based manner. The resulting DOT file can be then converted into a SVG file, which can be displayed directly by most browsers.
Here: https://github.com/infofabrik/reportserver-samples/tree/main/src/net/datenwerke/rs/samples/admin/svg/scriptreport we added examples on how to render a given DOT file to SVG and show it directly in the browser as a script report.
Notice that you can insert the $mySvgString directly into your HTML without further changes.
As of ReportServer 4.7.0 you can use the ''dot-svg'', ''dot-png'' and ''dot-html'' renderers directly. Here you can see two examples of this: https://github.com/infofabrik/reportserver-samples/blob/main/src/net/datenwerke/rs/samples/admin/svg/scriptreport/DOT_SVG_renderer.groovy and https://github.com/infofabrik/reportserver-samples/blob/main/src/net/datenwerke/rs/samples/admin/svg/scriptreport/DOT_SVG_renderer_file.groovy.
The ''dot-svg'' renderer renders a given DOT file as a SVG graph, the ''dot-png'' renders a given DOT file as a PNG image, and the ''dot-html'' renders the DOT file as a SVG graph and embeds it into a HTML page.
You can use the renderers analogously as the renderers shown in Section 5.3. Generating PDF and Word output:
def dot = '''
digraph D {
A -> {B, C, D} -> {F}
}
'''
return renderer.get("dot-svg").render(dot)Note that the above examples are very powerful yet simple to use, as you can create the DOT file dynamically.
Markdown is an easy-to-use markup language that is used to add formattig elements(headings, bulleted lists, URLs) to plain text(https://www.markdownguide.org/). As of ReportServer 4.7.0 markdown files in the virtual filesystem can be previewed in the corresponding tab and the ''markdown-html'' renderer allows you to produce a similar preview contained within a CompiledHtmlReport.
Here: https://github.com/infofabrik/reportserver-samples/blob/RS-7743/src/net/datenwerke/rs/samples/admin/markdown/scriptreport/MARKDOWN_HTML_renderer.groovy we added an example on how to render a given markdown file to HTML and show it directly in the browser as a script report.
Although most commands are supported, there are exceptions which cannot be parsed to HTML.
You can use the renderers analogously as the renderers shown in Section 5.3. Generating PDF and Word output:
def mdContent = '''
# h1 Heading
## Emphasis
**This is bold text**
__This is bold text__
*This is italic text*
_This is italic text_
'''
renderer.get("markdown-html").render(mdContent)For testing it might be helpful to run the script directly from the terminal. However, if you try to do this, with our above script you get the following exception:
reportserver$ exec report1.rs
net.datenwerke.rs.scripting.service.scripting.exceptions.ScriptEngineException: javax.script.ScriptException: javax.script.ScriptException: groovy.lang.MissingPropertyException: No such property: outputFormat for class: Script13
...The problem is that we used the variable outputFormat which is supplied by the script reporting engine but if we run the script as is, this variable does not exist.
import net.datenwerke.security.service.authenticator.AuthenticatorService
import groovy.xml.*
// define outputFormat if not already in binding
if(! binding.hasVariable('outputFormat'))
outputFormat = null
def user = GLOBALS.getInstance(AuthenticatorService).currentUser
def writer = new StringWriter()
new MarkupBuilder(writer).html {
head {
title ( 'Hello World' )
}
body {
h1(style: 'color: #f00', "Hello ${user.firstname}")
p("Isn't that an easy way to create a report?")
}
}
if(outputFormat == 'word')
return renderer.get("docx").render(writer.toString())
if(outputFormat == 'pdf')
return renderer.get("pdf").render(writer.toString())
if(outputFormat == 'html')
return renderer.get("html").render(writer.toString())
return writer.toString()The variable binding is a special groovy variable that allows you to access the available variable bindings for the current script. It is an object of type groovy.lang.Binding. By not giving the outputFormat a type, that is by defining it as
outputFormat = nullinstead of as
def outputFormat = nullor
String outputFormat = nullwe add the variable to the binding instead of the current scope (which would be limited by the if clause. Thus, we can safely later on assume that the variable is defined. Note also that we slightly changed the final return value as also the renderer is a special object provided by the reporting engine.
Simpler than testing via the terminal is to open the report in its own browser window by directly accessing it via the URL. For this use
http://SERVER:PORT/rsbasedir/reportserver/reportexport?id=REPORT_ID&format=HTML
More information on calling reports directly via the URL can be found in the administrator's guide.
To conclude our first simple report example we want to show you how to access parameters and work with script arguments. The latter is easily established. If you return to the report management and select the script report you see a field for arguments. These arguments are passed to the script in the same way as command line arguments are passed to scripts. Thus, the following would simply output the arguments
new MarkupBuilder(writer).html {
head {
title ( 'Hello World' )
}
body {
h1(style: 'color: #f00', "Hello ${user.firstname}")
p("Isn't that an easy way to create a report?")
p('Arguments: ' + args.join(','))
}
}Parameters are handled similarly. Parameters can be accessed via the variables parameterSet and parameterMap. The first variable points to the ReportServer ParameterSet object. Other than the parameterMap this does not only store parameter keys and values but contains additional information on the parameter. See net.datenwerke.rs.core.service.reportmanager.parameters.ParameterSet for more details. Usually only the parameterMap variable is needed which stores the parameters in a java.util.Map<String, Object>. Let us add a simple text parameter to our report and we give it the name param.
new MarkupBuilder(writer).html {
head {
title ( 'Hello World' )
}
body {
h1(style: 'color: #f00', "Hello ${user.firstname}")
p("Isn't that an easy way to create a report?")
p('Arguments: ' + args.join(','))
p('The single parameter is: ' + parameterMap['param'])
}
}That concludes the basic example. In the next section we see how to work with datasources.
In the following section we are going to create a second script report that directly accesses a datasource. Let us create a new script called report2.groovy which we also put into /bin/reports. We will use Groovy's Sql functionality to directly access the database. Create a new script report in the report manager. As for any other report type you can select a datasource for the report. This datasource will then be given to your script via the predefined variable connection. In case the datasource is a relational database the connection variable would hold an actual database connection. Note that there is no need to close the connection as ReportServer will do that for you. The following script accesses the table T_AGG_CUSTOMER from the demo database.
import groovy.sql.Sql
import groovy.xml.*
def writer = new StringWriter()
new MarkupBuilder(writer).html {
head {
title ( 'Hello World' )
}
body {
table {
new Sql(connection).eachRow("SELECT CUS_CUSTOMERNAME, CUS_CREDITLIMIT FROM T_AGG_CUSTOMER") { row ->
tr {
td(row.CUS_CUSTOMERNAME)
if(row.CUS_CREDITLIMIT > 100000)
td(style:'color:#F00', row.CUS_CREDITLIMIT)
else
td(row.CUS_CREDITLIMIT)
}
}
}
}
}
renderer.get('html').render(writer.toString())As a final example we want to create a simple dynamic report which uses a dynamic list as its backend. For dynamic reports the logic will be written in JavaScript and the Groovy script will be mostly a container serving the JavaScript code. Create the following script as report3.groovy in /bin/reports and create a fresh script report pointing to it.
import groovy.sql.Sql
import groovy.xml.*
def writer = new StringWriter()
new MarkupBuilder(writer).html {
head {
title ( 'Dynamic World' )
script(language: 'javascript', type: 'text/javascript', """\
alert('Hello Dynamic World')
""" )
}
body {
}
}
renderer.get('html').render(writer.toString())If you call the report you will be greeted with a JavaScript alert message. Let us now add some content to our report. For this we will access the dynamic list T_AGG_CUSTOMER (from the demo package). We assume that the list has key "customer". We will use jquery to easily modify the DOM. We query the dynamic list to retrieve two attributes and build a table of the results.
import groovy.sql.Sql
import groovy.xml.*
def writer = new StringWriter()
new MarkupBuilder(writer).html {
head {
title ( "Dynamic World" )
script(language: "javascript", type: "text/javascript", src: "http://www2.datenwerke.net/files/blog/js/jqplot/jquery.min.js", " " )
}
body {
h1("Dynamic World" )
table (id: "content", " ")
script(type: "text/javascript") { mkp.yieldUnescaped("""
$.getJSON( 'http://127.0.0.1:8888/reportserver/reportexport?key=customer&c_1=CUS_CUSTOMERNAME&c_2=CUS_CREDITLIMIT&format=json', function(data) {
$.each( data, function( key, val ) {
$( "<tr><td>" + val['CUS_CUSTOMERNAME'] + "</td><td>" + val['CUS_CREDITLIMIT'] + "</td></tr>").appendTo("#content");
});
});
""" ) }
}
}
renderer.get('html').render(writer.toString())So, what have we done? First, note that self-closing script tags are likely to produce errors. Thus, we added a dummy space as content to the script tag that is loading jquery. A second change we introduced is to call mkp.yieldUnescaped to write our javascript code. This is to ensure that entities such as "&" are not escaped. Now, we get to the actual content retrieval. We used jquery's getJSON function to load data from the following URI
Here we specified to use the report with key customer, and selected two columns: CUS_CUSTOMERNAME and CUS_CREDITLIMIT. Additionally, we told ReportServer to return data in the JSON format. Now all we did was to loop over the retrieved data and added an entry to our table.
So far, we have only created a static script, so let us add some dynamic elements to it. We will add an input field that allows to specify a minimum credit limit and on changes retrieve the filtered data from the server.
import groovy.sql.Sql
import groovy.xml.*
def writer = new StringWriter()
new MarkupBuilder(writer).html {
head {
title ( "Dynamic World" )
script(language: "javascript", type: "text/javascript", src: "http://www2.datenwerke.net/files/blog/js/jqplot/jquery.min.js", " " )
}
body {
h1("Dynamic World" )
input (id: "filter", type: "text", value: "10000")
input (id: "btn", type: "button", value: "Retrieve Filtered Data" )
table (id: "content", " ")
script(type: "text/javascript") { mkp.yieldUnescaped("""
$("#btn").click(function(event){
var val = $("#filter").val();
$("#content").html("<tr><td>loading data</td></tr>");
$.getJSON( 'http://127.0.0.1:8888/reportserver/reportexport?key=customer&c_1=CUS_CUSTOMERNAME&c_2=CUS_CREDITLIMIT&format=json&or_2=ASC&fri_2=' + val + ' -', function(data) {
$("#content").empty();
$.each( data, function( key, val ) {
$( "<tr><td>" + val['CUS_CUSTOMERNAME'] + "</td><td>" + val['CUS_CREDITLIMIT'] + "</td></tr>").appendTo("#content");
});
});
});
""" ) }
}
}
renderer.get('html').render(writer.toString())First, you should notice that we added a static textbox and a button. In our javascript code we added a callback to click events on the button. If the button is clicked, we first clear the content of our table (that we address by its id content) and read out the value of the textbox. We then perform an ajax call to URI
As val contains the value of the textfield (say 10000) this would result in the following URL
With this URL we have introduced two changes. First, we order the resulting data by column 2, that is by CUS_CREDITLIMIT. Secondly, we added a range filter and use the result of the filter value as a lower bound.