Chapter 7. Report Management

Reporting is a highly dynamic field. Data need to be consolidated, prepared and evaluated at increasingly shorter intervals. Due to the communication overhead, these dynamics can, however, only be met to a limited extent with the typical approach in reporting where a report is first specified by the specialist department and then implemented by the IT department. ReportServer is designed to get the responsibility just as well as the opportunities to create new, relevant information to the greatest possible extent back to whom it concerns and where it is needed, but also where the knowledge about how to generate data is available: To the specialists respectively to the ultimate user.

ReportServer first distinguishes between (base) reports and variants. Base reports are comparable to a shell. They define the basic data structure and, depending on the format, the design how to present these data. In addition, they provide the users with the options that enable them to adapt this data basis to their requirements. Depending on the report format, various options are here available. However, variants represent reports which are fully configured by the user.They include the necessary settings to execute the report or the schedule it. Users can save their configurations---the variants---to assure prompt access to their data.

All report types supported by ReportServer have in common that they are configurable by setting the so-called parameters. For instance, a parameter may be a simple text field where the user can enter an invoice number, or a date range to limit a time span. The parameters applicable for a report are invariably specified by the report designer/administrator.The users can select from the parameter values and save them as a variant. Depending on the report type, the ultimate user is given additional options to control the output of the report. We will discuss them more closely in the following depiction of the single report types. Naturally, you will find a detailed description in the user manual. The ultimate goal is to empower the users to gather the data required for their task independently.

Of course, it must be ensured to document the reports in an audit-proof manner if they can be adjusted by a user after a possible acceptance in the data warehouse. ReportServer here supports you in two ways. First, ReportServer automatically creates detailed documentation for each report/variant outlining all user defined settings. Then, history objects will be created for any changes to objects. They enable to trace the type and time of a modification executed on an object.

As with users and datasources, ReportServer manages reports in a hierarchical tree structure. You will find it in the Administration modules under Report management. Report management exclusively provides the administrative management of reports. By using TeamSpaces the users may create their own view to report objects released to them. For further information on TeamSpaces refer to the User manual.

As you are used to you may structure objects in folders. Apart from folders, there will be one object per report type as well as variant objects. Variant objects will not be created in the tree itself but automatically by ReportServer as soon as a user creates a new variant for a report. ReportServer provides you with the following report types:

Dynamic list

The dynamic list shifts the major part of report logics to the ultimate user who can compile the data relevant for it individually. Here users can draw from simple filters and complex aggregations up to computed fields. In this respect, the dynamic list can be regarded as ad-hoc reporting. The user may, of course, save all settings in a variant. The dynamic list is designed to be highly performant when dealing with large data volumes. The dynamic list outputs data primarily as a table which can be exported to Microsoft Excel just as well as to PDF, HTML or CSV. In addition, by using templates data can directly be uploaded to pre-defined Excel spreadsheets, or transferred to any XML dialects. We will look at the dynamic list from the administrator's viewpoint in section The Dynamic List. The description of the dynamic list from the user's viewpoint will be given in the User manual. The dynamic list is discussed in detail in Section 7.2.

Graphical Reports

The dynamic list supplies data in raw format, and therefore it is ideally suited for daily control. To generate graphically sophisticated analyses, ReportServer supports reports in the JasperReports and Eclipse BIRT report formats. These popular and open libraries enable to create reports with elaborate graphics. We often refer to these as graphical reports. In addition to the open formats BIRT and Jasper, ReportServer also provides support for the commercial SAP Crystal Reports engine which similarly allows you to create pixel perfect reporting.

JasperReports

JasperReports Library (http://community.jaspersoft.com/) designed by JasperSoft is a powerful report engine to generate graphical reports. Reports are defined in an XML dialect which will be translated to Java Source Code for report execution. Apart from the function to directly generate XML sources, JasperSoft provides the report designer IReports. It helps to create reports in the style of WYSIWYG applications (what you see is what you get). Jas- perReports are particularly suited to output reports in the PDF format (e.g. for pixel precise printing). However, Jasper reports can be provided in other formats such as HTML or RTF. JasperReports are discussed in detail in Section 7.4.

Eclipse BIRT

Eclipse BIRT (http://www.eclipse.org/birt) is the second open report engine next to JasperReports. Actuate (http://www.birt-exchange.com/be/home/) takes the lead in further developing Eclipse BIRT which offers functionalities comparable to JasperReports. BIRT is based on the Eclipse platform (http://www.eclipse.org/) and includes a comprehensive designer for visualizing reports. Just as well as with JasperReports, Eclipse BIRT defines reports in an own XML dialect which transfers to an executable Java Code at report runtime. The primary output format for BIRT reports is PDF as well. BIRT is discussed in detail in Section 7.5.

Crystal Reports

Crystal Reports is a commercial reporting engine developed by SAP AG (http://www.crystalreports.com/) and hence not directly part of ReportServer. ReportServer, however, comes with everything you need to run reports generated with Crystal Reports given that you have a Crystal license allowing you to use SAP Crystal Reports for Java runtime components (or short, the Java Reporting Component JRC). Similarly to Jasper and Birt the primary output format of Crystal reports is PDF.

Versions of the Report Engines

With every new release of ReportServer, the libraries in use will also be updated so that normally the latest JasperReports and Eclipse BIRT versions will be integrated. The current versions are given in the license documentation which is part of the download package. As JasperReports are designed as a simple library, you can easily exchange it yourself by a current or an older version. To do this, copy the corresponding .jar files to the ReportServer lib directory. Further information on this you will also find in the ReportServer Configuration guide. Unfortunately, Eclipse BIRT is not as easy to handle in this respect because it requires multiple libraries in specific versions. Therefore, we advise you not to upgrade the BIRT engine on your own without any support.

Saiku Reports -- Multi-dimensional Reporting

Saiku reports allow you to access Mondrian datasources (see Section 4.21.). The user interface is provided by Saiku (http://meteorite.bi/saiku) who created beautiful OLAP UI that we adopted in ReportServer. Saiku reports are the preferred way if you want to access multi-dimensional data that is organized with Mondrian.

JXLS Reports -- Excel Reporting

JXLS (http://jxls.sourceforge.net/) is a template engine for Microsoft Excel. ReportServer allows to use JXLS templates, for example, with dynamic lists such that users can directly insert their data in a predefined Excel sheet (for further information have a look at the ReportServer User guide). Besides being available as a template engine for ReportServer's dynamic list, JXLS is also available as a first class report object. In this form it provides further reporting capabilities, as you can directly use SQL queries within your Excel templates. Note that JXLS2 is supported in ReportServer. The legacy JXLS1 is not supported.

Script Reports

Beside the dynamic list there is another native ReportServer report type, the script report. Script reports are written in Groovy and offer full Java VM flexibility and functionality. They are primarily used to generate dynamic analyses allowing user interaction. But they can also access the ReportServer object model, and therefore they are particularly suited for the reporting of warehouse metadata as well as of ReportServer itself. An example for a meta report is a documentation report which generates an up to date documentation for all kinds of reports.

In the following sections we will provide you with in-depth information on the four different report types. Even if you intend to primarily work with the graphical report engines (Jasper or BIRT) we recommend you to read the Dynamic list section as we will explain here some of the principle techniques such as working with parameters. All of the following sections show a similar structure. First, we describe the principle techniques and concepts on which the report engine is based. This is not necessarily required for the creation and configuration of reports, and may be skipped on first-time reading. In the next step, we will explain the single configuration options in detail. For Jasper and BIRT we will additionally elaborate on the interoperability between ReportServer and the report designers offered by the manufacturers.

Grid Editor Reports

The grid editor component allows users to change data within database tables. The grid editor is backed by ReportServer scripts to define what data is loaded and how changed data is to be stored. In that way it is a very flexible component when you want to provide a simple data editor for users.

The dynamic list is very easy to configure as compared to the graphical report engines BIRT and Jasper. Basically, the administrator defines a table (which might be very concise at times) with the basis data. From this table the users can then independently arrange their data of relevance. In many cases the definition of a dynamic list only requires a single SQL query (provided the data basis is stored in a relational database). By setting parameters you can provide the users with predefined filter and configuration options.

To obtain highest possible efficiency, ReportServer relocates all filtering steps to the reference database, if possible. If a datasource other than a relational database has been selected for your report, the data will be buffered in an internal database as described in Section ''Datasources''. Basically, the execution of a dynamic list report is as follows. Proceeding on a basic SQL statement, ReportServer constructs a complex SQL query considering all filter options set by the user. This SQL statement will either be predefined by you if you have chosen a relational database as your datasource, or will automatically be generated by ReportServer if the data are buffered in an internal database. Resulting from this, there will be a few consequences with regard to the formulation of your basis SQL statement that we want to discuss in the following.

If you have chosen the demo datasource (see Section ), your base query could be as follows:

SELECT * FROM T_AGG_CUSTOMER

It would provide all processed customer data of the model company "1 to 87" (the imaginary company behind our demo data) as a data basis for the dynamic list. The query assembled by ReportServer could then be as follows (depending on the configuration of the variant):

SELECT * FROM
    (SELECT
        xx__rs_col_0,
            xx__rs_col_1,
            xx__rs_col_2,
            xx__rs_col_3,
            xx__rs_col_4,
            xx__rs_col_5
    FROM
        (SELECT
        *
    FROM
        (SELECT
        CUS_CONTACTFIRSTNAME AS xx__rs_col_0,
            CUS_CONTACTLASTNAME AS xx__rs_col_1,
            CUS_CUSTOMERNUMBER AS xx__rs_col_2,
            CUS_PHONE AS xx__rs_col_3,
            CUS_CITY AS xx__rs_col_4,
            CUS_POSTALCODE AS xx__rs_col_5
    FROM
        (SELECT
        *
    FROM
        T_AGG_CUSTOMER) colQry) filterQry
    WHERE
        xx__rs_col_4 IN ('Barcelona' , 'Auckland', 'Bern')) aliasQry) limitQry
LIMIT 50 OFFSET 0

Please consider that ReportServer will adapt the generated SQL source text to the database used. In the given example the syntax LIMIT 50 OFFSET 0 is not generally applicable for all databases.

In this example the user has chosen the columns

• CUS_CONTACTFIRSTNAME
• CUS_CONTACTLASTNAME
• CUS_CUSTOMERNUMBER
• CUS_PHONE
• CUS_CITY
• CUS_POSTALCODE

and defined a simple inclusion filter on the CUS_CITY column.

You see here how ReportServer builds the SQL statement around the basic query SELECT * FROM T_AGG_CUSTOMER. As a consequence for the formulation of the basic query, the syntax has to allow to be used as inner SELECT statement. Here only the line limiting instructions such as LIMIT and OFFSET basically provide a problem. Please observe that depending on the database dialect used the formulation has to be adapted. Surely, some attention has also to be paid to more complex basic queries. For example, if you wish to specify an aggregation (this is usually not required as users can aggregate data by themselves), your query could be as follows:

SELECT SUM(CUS_CREDITLIMIT) FROM T_AGG_CUSTOMER GROUP BY OFF_CITY

Here the sum of the customers' credit limit per location is defined. When executing the report, ReportServer would translate the above query as follows (here an additional filter was added on values greater than ).

SELECT * FROM
    (SELECT
        xx__rs_col_0
    FROM
        (SELECT
        *
    FROM
        (SELECT
        SUM(CUS_CREDITLIMIT) AS xx__rs_col_0
    FROM
        (SELECT
        SUM(CUS_CREDITLIMIT)
    FROM
        T_AGG_CUSTOMER
    GROUP BY OFF_CITY) colQry) filterQry
    WHERE
        xx__rs_col_0 >= 4000000) aliasQry) limitQry
LIMIT 50 OFFSET 0

If you manually run the statements on a database you will see that the original statement is valid whereas the one built by ReportServer will not be valid at this place. In line 4 ReportServer assigns to column SUM(CUS_CREDITLIMIT) a unique (internal) name. However, the database interprets this statement as a further summation. This means that an admissible attribute name should already have been assigned to the aggregation in the basic query. In this case the basic query should have been as follows:

SELECT SUM(CUS_CREDITLIMIT) AS SUMME FROM T_AGG_CUSTOMER GROUP BY OFF_CITY

ReportServer would then correctly translate the following query:

SELECT * FROM
    (SELECT
        xx__rs_col_0
    FROM
        (SELECT
        *
    FROM
        (SELECT
        SUMME AS xx__rs_col_0
    FROM
        (SELECT
        SUM(CUS_CREDITLIMIT) AS SUMME
    FROM
        T_AGG_CUSTOMER
    GROUP BY OFF_CITY) colQry) filterQry
    WHERE
        xx__rs_col_0 >= 4000000) aliasQry) limitQry
LIMIT 50 OFFSET 0

If you need to modify the query before it is being executed, take a look at the net.datenwerke.rs.base.service.dbhelper.hooks.StatementModificationHook.

Common Table Expressions (CTEs)

Because of the same reasons as explained above, you have to take special care if you wish to use Common Table Expressions (CTEs) together with dynamic lists. Refer to the following example in MSSQL syntax

WITH USERS_CTE (username, firstname, lastname)
AS  (
SELECT username, firstname, lastname from RS_USER
)
SELECT * from USERS_CTE

If you write this valid query into your dynamic list's query, you will get an error. As explained above, this error appears because of the query generated by ReportServer. In order to solve this problem, you can mark the CTE expression as follows.

/*<rs:cte>*/
WITH USERS_CTE (username, firstname, lastname)
AS  (
SELECT username, firstname, lastname from RS_USER
)
/*</rs:cte>*/
SELECT * from USERS_CTE

Using dynamic lists, users have far reaching options to design the report according to their needs. In a first step, the users select a sub-set from the available report attributes (columns). This sub-set forms the basis for their report. In the following steps, they can then specifically reduce the data basis to the number they actually need for their analysis by setting various filter options. A detailed description of all options would go beyond the scope of this instruction. Therefore, we make reference to the User manual which details all options how to adapt a report by the user.

The underlying output format of the dynamic list is tabular. Therefore, the data can be exported to Excel or CSV (comma separated value, http://en.wikipedia.org/wiki/Comma-separated_values). In addition, when using dynamic lists ReportServer provides the possibility to export to PDF and HTML.

Beside outputting the data in a simple tabular structure, users can upload them to predefined Excel spreadsheets by means of templates (JXLS templates), transform them in any text format (velocity templates), or issue them in any XML dialect (XLS templates). You will find a short introduction to the various template languages in the User manual.

All reports have in common that you may assign a name and a description to them. Note that these will be indexed for ReportServer's search engine. In addition, you can assign a key to reports. This key represents a unique (plain text) name for referencing the report, for instance, in URLs.

To define the data basis select a datasource and configure it (configuration depends on the datasource type, refer to chapter 4.). Normally, you will use data from relational databases (you can use the demo datasource as an example which refers to the internal H2 database). To complete the configuration, you have to enter an SQL query. In the following we will base our description on a relational database serving as a datasource and only refer to other datasource types if the configuration appears to be fundamentally different.

Based on the demo datasource, a report could provide, for instance, all data of the table T_AGG_CUSTOMER (i.e., all processed data concerning the customers of the company "1 to 87") as described above. Here you use the query

SELECT * FROM T_AGG_CUSTOMER

Once the datasource is specified, the dynamic list is executable. To start executing it, double click on the respective node in the tree. You will find a detailed explanation how to use the dynamic list from the viewpoint of a user in the User manual.

By actuating the Execute button in the tool bar of the datasource configuration you can easily test the currently entered query.

The optional metadata datasource serves to further define the report's base data fields. If there is no metadata datasource given, the users will only see the technical database name each when selecting attributes/columns. By using the metadata datasource it is possible to enter an additional plain text name and description per attribute. Further, the default column width in the dynamic list preview can be set here (per attribute). ReportServer expects the datasource output to consist of three or four columns, where the first one shows the technical column name, the second one gives the appurtenant plain text name, the third one the appurtenant description, and the fourth (optional) the appurtenant default column width in the dynamic list preview.

A metadata example will also be supplied with the demo data. The appurtenant query is:

SELECT column_name, default_alias, description FROM METADATA

Note that this query has only three columns. As mentioned above, the fourth one is optional (default column width in the dynamic list preview).

The result of this call, for instance, will look as follows:

If you want to set the default column width in the dynamic list preview, you can write a similar query analogous to:

SELECT column_name, default_alias, description, default_width FROM METADATA

The query would then result in e.g.:

Please note that the demo data does not include a default column width, as this is optional, so the query above will not work with the demo data. If you need a default column width in your datasource, create a similar query as the above in your metadata datasource definition.

The metadata datasource further allows you to link your dynamic list report to a URL. Details of this can be found in the next section.

ReportServer allows you to link your dynamic lists to a (possibly external) URL. This may be very practical if the link contains additional information of the record clicked on. Useful examples include opening a new report based on a given id (e.g. by httpauthexport, refer to Section 7.11.3. for more details), performing a Google search based on a given value, etc.

This is achieved in ReportServer by using the metadata datasource described in Section 7.2.5. Along with the four columns described in Section 7.2.5., the metadata datasource query may contain a fifth column for this purpose. Therefore, the fourth column (default column width in the dynamic list preview) is required in this case although it is normally optional. Its value may be of course NULL if no default width is required.

An example query containing the five columns could be:

SELECT column_name, default_alias, description, default_width,
'linkto|http://URL/reportserver/httpauthexport?&user=myuser&id=20616&format=PDF&p_id=$' + '{_value}' as link
FROM METADATA

Note that the string begins with a linkto| substring. In this case, ReportServer recognizes the rest of the string as a link. The example link points to a PDF report which expects an ''id'' parameter. You can use ${_value} for passing the value of the cell where the user right-clicked on to your URL. The ${_value} is being concatenated in the query in order to avoid ReportServer handling it as a parameter.

Also note that in order for the example to work, the user has to right-click a column in the dynamic list preview containing the id to be passed as a parameter to the URL. The context menu will contain a ''Link to...'' entry which opens a new window containing the URL for the given entry.

If you need to pass the value of another (visible) column to your URL, you can use the analogous syntax, passing the column name or its alias. Thus, the string passed can be either the technical name of the column or its given alias. In the example below, ${id} is being used to pass the value of the ''id'' column. The value passed is independent of the cell right-clicked by the user.

SELECT column_name, default_alias, description, default_width,
'linkto|http://URL/reportserver/httpauthexport?&user=myuser&id=20616&format=PDF&p_id=$' + '{id}' as link
FROM METADATA

Of course, you can also combine values passed. An example is given below.

SELECT column_name, default_alias, description, default_width,
'linkto|http://URL/reportserver/httpauthexport?&user=myuser&id=20616&format=PDF&p_id=$' + '{id}' + '&p_name=$' + '{name}' as link
FROM METADATA

If you need further customization, you can always implement the hook net.datenwerke.rs.base.client.reportengines.table.hooks.TableReportPreviewCellEnhancerHook. Refer to the Scripting Manual for more information on implementing hooks.

Similarly to linking dynamic lists to URLs as described in Section 7.2.6., you can use the metadata datasource to link geolocation data in a dynamic list to the corresponding Google Maps location.

For this purpose, the fifth column of the metadata query can again be used. An example is show

SELECT column_name, default_alias, description, default_width, column_geolocation
FROM METADATA

If the ''column_geolocation'' value is the string ''geolocation'', the corresponding column will link to Google Maps when right-clicking the value. The entry ''Display on Map'' will appear, which jumps to the corresponding Google Maps location. E.g., if the value right-clicked on is ''48.860294,2.338629'', the linked location will be http://maps.google.de/?q=48.860294,2.338629, which corresponds to the Louvre Museum in Paris.

If you need further customization, you can always implement the hook net.datenwerke.rs.base.client.reportengines.table.hooks.TableReportPreviewCellEnhancerHook. Refer to the Scripting Manual for more information on implementing hooks.

There are several report properties (see Section 7.12.) that can influence how the dynamic list. By default, the preview view, as well as filter views count the number of rows in the results. When working with very large database tables, this can become a performance bottleneck and you can thus disable this behavior. Furthermore, you can control whether filters are, by default, in linked mode, or not. The ReportServer standard is to have filters in linked mode, as this is the more intuitive when using a dynamic list: filters will only show those results which are still valid results given all the other filters. As this again may be a performance bottleneck, you can disable this behavior via a report property.

Further, you can control if the parameters and their respective values should be exported along with the dynamic list export. Even the complete configuration may be exported together with the dynamic list export. This may be a huge help in order to being able to analyze thoroughly the values exported. For this, you can use the properties output_parameters, output_complete_configuration, and output_include_hidden_parameters. For more information, refer to Section 7.2.9.

For an overview over all report properties see Section 7.12.

For exporting the parameter and filter configuration along with the dynamic list, you can set the following report properties:

• output_parameters
• output_filters
• output_complete_configuration
• output_include_hidden_parameters

Please note that these report properties are only available in Enterprise Edition.

If only output_parameters is set to true, then only the ''real'' parameters are being printed out, without separators, text, headers, etc. If output_complete_configuration is set, everything will be printed out. This includes user information, report information, filters, pre-filters, report metadata, global variables, separators, headers, parameters, etc. With other words, everything that you can possibly include in the query.

Both output_parameters and output_complete_configuration take output_include_hidden_parameters into account. output_include_hidden_parameters controls whether hidden parameters are being exported (defaults to false).

For exporting filters and pre-filters, you can use the output_filters variable. output_complete_configuration includes filters and pre-filters as well.

Note that only the Excel export prints this information without further settings. If you want to export this information into the HTML and/or PDF export as well, you have to also configure your etc/dynamiclists/htmlexport.cf and/or etc/dynamiclists/pdfexport.cf files.

For example, the following prints the filters and parameters:

<div class="parameters">
${parameterMapSimple}
</div>
<div class="filters">
${filterMapSimple}
</div>

More information on configuring these files on Section 7.2.10.

ReportServer Enterprise Edition allows to customize the PDF and HTML exports of dynamic lists via the configuration files:

Following is a sample configuration for the HTML export:

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
   <htmlexport>
      <!--
        <title>Some title</title>
        <head>Some additional content for the header</head>
        <script>Some Javascript</script>
       -->
      <style><![CDATA[
         @page {
                size: A4 landscape;
                @top-left {
                    content: "${report.name}";
                    font-family: DejaVu Sans, Sans-Serif;
                    font-size: 8pt;
                 }
                @top-right {
                    content: "${now}";
                    font-family: DejaVu Sans, Sans-Serif;
                    font-size: 8pt;
                 }
                @bottom-right {
                    content: "${page} " counter(page) " ${of} " counter(pages);
                     font-family: DejaVu Sans, Sans-Serif;
                    font-size: 8pt;
                }
          }
          ]]>
      </style>
      <pre><![CDATA[
<div class="wrap">
<div class="header">
<span class="logo">
<img src="data:image/png;base64,SOMELOGOINBASE64=" alt="logo"></img>
</span>
<div class="reportdata">
<span class="name">${report.name}</span>
<span class="date">${now}</span>
</div>
<div class="clear"></div>
</div>

<!-- output parameters / filters before report data -->
<!-- Activate per report with output_parameters, output_filters, or output_complete_configuration report property.
	The property has to be set to true in order to activate. -->
<!-- <div class="parameters">
You can export parameters with the parameterMapSimple variable.
</div>
<div class="filters">
You can export filters with the filterMapSimple variable.
</div> -->
<!-- end output parameters / filters before report data -->

]]>
      </pre>
      <post><![CDATA[

<!-- output parameters / filters after report data -->
<!-- Activate per report with output_parameters, output_filters, or output_complete_configuration report property.
	The property has to be set to true in order to activate. -->
<!-- <div class="parameters">
You can export parameters with the parameterMapSimple variable.
</div>
<div class="filters">
You can export filters with the filterMapSimple variable.
</div> -->
<!-- end output parameters / filters after report data -->

</div>
]]></post>
   </htmlexport>
</configuration>

The configuration consists of six high level elements

By setting parameters you can specify additional configuration options for the users. First, we want to discuss the basics of parameters based on a simple example. Then we will look more closely to the various parameter types.

In the following we will again use the supplied demo data and set up a report on the orders of the company "1 to 87". Here we want to enable the users of the report to restrict the data basis to certain customers. To do this, we will create a new dynamic list, select again the demo datasource (refer to Chapter 4.), and first apply the following basic query:

SELECT * FROM T_AGG_ORDER

Run the report in its actual state to get better acquainted with the demo data. In the following we would like to introduce a parameter which enables the users to filter the report by customer numbers. To do this, we return to Report management, select the report and switch to the parameter tab (at the bottom). From the tool bar we add a new datasource parameter. The parameter properties will open by double clicking on the icon of the parameter. We will assign the following properties:

Now we switch to the Specific properties of the parameter. Here we enter/activate the settings valid for the parameter type (datasource parameter). datasource parameters draw their data from a datasource. Here we enter the demo datasource again. ReportServer expects a result giving two columns: the value column (this value can later be used in the query) and the display column (this column will be shown to the user on selection). We place the following query.

SELECT DISTINCT
    OR_CUSTOMERNUMBER, CUS_CUSTOMERNAME
FROM
    T_AGG_ORDER
ORDER BY 2

You may keep the default values of the other settings. Apply the settings by clicking on Apply. If you now run the report (double click in the tree) you will discover that the aspect displayed first is the page Report parameters, and no longer Lists configuration. To set the parameter data, double click into the corresponding data grid.

Now we added a parameter, and it can be configured by the users, but so far this parameter had no effect on the underlying data volume. We still have to embed it in the base query of the report. To do this, in Report management we return to the report settings and modify the query as follows:

SELECT * FROM T_AGG_ORDER WHERE $X{IN, OR_CUSTOMERNUMBER, P_CUSTNUM}

ReportServer interprets the syntax $X{IN, OR_CUSTOMERNUMBER, P_CUSTNUM} as an IN-Clause where the field OR_CUSTOMERNUMBER must correspond to a value selected in the parameter P_CUSTNUM (key of the parameter). Translated to SQL, the query would be as follows:

SELECT
    *
FROM
    T_AGG_ORDER
WHERE OR_CUSTOMERNUMBER IN ('WERT_1','WERT_2','WERT_3')

Now, if you execute the report again, you will discover that when selecting the parameter it will have the desired effect on the data volume. Please observe that if no parameter is selected, this will by default translate in "All values are valid".

If you are familiar with the JasperReports report engine, you will find out that you can use the parameter syntax applied there for ReportServer dynamic list as well. We will discuss the syntax more closely at a later point.

In the following we will first introduce the parameters supported by ReportServer individually and then look at them in detail. ReportServer distinguishes two types of parameters. True parameters are the ones that enable the user to make settings. So-called separators enable the administrator to design the aspect report parameter, i.e. to add descriptive texts, etc.

The following parameters are available:

• Text entry parameter Enables the user to make an entry to a text field
• Date parameter Enables to enter a date or a time field
• Datasource parameter Enables to select from a number of possible pre-set values
• User variable Enables to readout so-called user variables. User variables are discussed in detail in Chapter 9.
• File-selection parameter Allows users to upload files, which can then be used in the report creation process. This is especially powerful in combination with script reports or custom export targets.
• Script parameter Allows you to specify a parameter in HTML and JavaScript. This gives you the flexibility to master almost any requirement.

The following separators are available:

• Display text Enables to display text
• Heading Enables to integrate a sub-heading
• Separator Enables to set a division

In the following we will present a close look at the individual parameter type settings. Then we will explain how to use parameters in datasources.

You add further parameters or separators to a report via the respective buttons in the tool bar in tab Parameters. By clicking on the Remove button you can remove either the selected or all report parameters. In addition, by using copy & paste you can add parameters to your current report or to another one. To copy one or more parameters to the Clipboard, activate the respective parameters and press CTRL+SHIFT+C (you will get a short notice about the successful move to the Clipboard). To add parameters, first click on the parameters list (if there is no parameter available click on the header), then press CTRL+SHIFT+V.

To edit the parameter properties, double click on the parameter icon, or activate the parameter and select "Edit" from the tool bar. Parameter name and key can directly be edited in the list. To do this click on the respective cell.

There are properties specific to each parameter type, but all parameters have the following properties in common:

When a user allocates parameters and saves their configuration in a report variant, the parameter settings made will be stored in parameter instances. If you modify a parameter subsequently, the instances of the variants will not automatically change. This can be best explained by giving an example:

We assume that we have created a multiple choice list parameter following the above example. The appurtenant basic query was as follows:

SELECT * FROM T_AGG_ORDER WHERE $X{IN, OR_CUSTOMERNUMBER, P_CUSTNUM}

In the meantime, some variants have already been created, and the users have edited the variants' parameter P_CUSTNUM. If you modify the parameter definition so as to select only one value, and adapt the query as follows:

SELECT * FROM T_AGG_ORDER WHERE OR_CUSTOMERNUMBER = $P{P_CUSTNUM}

This would cause problems with the existing variants as here a list of values is given to the query and not only a single value. In this case, by applying the button "Adapt instances", you could reset the instances for the selected parameter to the initial value. However, this is not necessary for all parameter modifications, and as a reset will lead to the loss of the user's selection, ReportServer will not automatically delete the instances with every parameter change. Let us assume you did not set the parameter from multiple choice to single choice, but you only rearranged the query to offer less selection options. Here it is not necessarily required to adapt the existing instances.

ReportServer supports the creation of graphical reports by using the OpenSource library JasperReports Library (http://www.jaspersoft.com). It is primarily designed to specify pixel perfect reports to directly print them, or to export them as a PDF at a later time. In Jasper re- ports, reports are defined in a specific XML dialect (JRXML). Reports can be directly written by using a text editor, however, in practice reports are mostly drawn up by using the open report designers IReports (http://community.jaspersoft.com/project/ireport-designer).

A JasperReport always consists of exactly one master report and one or several sub-reports. There is an XML file for the master report and for each sub-report with the extension .jrxml. These files are required to embed the report in ReportServer.

For further information on the creation of reports by using JasperReports refer to the relevant documentation of JasperSoft (http://community.jaspersoft.com/documentation).

To embed Jasper reports in ReportServer switch to the Report management and create a Jasper report in a folder. As with dynamic lists, in the following dialogue you can give a name and a description to the report as well as a unique key to call up the report by URL, for instance.

To complete the configuration you have to upload the master report (i.e. the corresponding .jrxml file) as well as all sub-reports.You can create sub-reports either by the Add sub-report button, or by dragging and dropping it to the respective window.

During report execution, sub-reports will be loaded exclusively via the subreport's name; possible path information will be ignored. Therefore, it doesn't matter how you locally organized the files belonging to a report, you need not adapt them in order to use them in ReportServer. However, you must ensure that no two sub-reports have the same name. Conversely, however, you note that ReportServer never loads sub-reports from the local server file system. Accordingly, all sub-reports always have to be registered together with the master report. Furthermore, it is not possible to share sub-reports between several reports.

Finally, you have to assign a datasource to the report. Here, you can exclusively draw from relational databases as the source of your data. In ReportServer there is no need to enter queries as you do with dynamic lists for they have already been included in the .jrxml files.

Just like dynamic lists, Jasper reports can be controlled by parameters. They will be created in the reports themselves (for detailed information refer to JasperReports/Ireport Ultimate Guides http://community.jaspersoft.com/documentation). In ReportServer you have to additionally configure all parameters entered in the report by the parameter management (tab at the bottom of the screen). Here, ReportServer will support you by automatically creating parameters from the master report. This function can be activated via the Suggest parameter button in the tool bar. We note that the automatic extraction of parameters is rudimentary and for may need manual adjustments for more complex parameter settings. The configuration of the parameters is otherwise identical to the configuration of dynamic list parameters.

JasperReports attempts to present pixel perfect reports in all output formats.As a result, when outputting it to Excel they are usually nicely made up, however, numbers will not appear as such but as text, this complicates further data processing unnecessarily. Here, ReportServer provides the option to add an additional datasource to a Jasper report. If this function is activated you will be offered the additional output format Excel (unformatted) when executing the report. Here the ResultSet of the datasource execution will be presented as an Excel file.

Eclipse BIRT is a reporting system to generate pixel perfect reports in diverse output formats. A BIRT report is defined in form of an XML document. For the creation and editing of reports usually the BIRT Report Designer is used which simplifies your draft report by a graphical presentation. Some of the sample reports realized in Eclipse BIRT are given in the demo data supplied with ReportServer as well as in the annex. For further information on Eclipse BIRT refer to http://www.eclipse.org/birt/.

Apart from the XML documents defining the actual report, BIRT reports can additionally include resource bundles and library files. These will be stored in a specifically identified folder in the file server separate from the actual report.

To create a new BIRT report in ReportServer, proceed as follows. Switch to the Administration module and then to the section Report management. From the context menu of a folder you create a new BIRT report. The configuration mask presents the same fields as all other report types do and allows to enter a name, description and key. In addition to these data, it is required to upload the Rpt-design-file of the report.

Possibly available report libraries or resource bundles need to be copied to a folder in ReportServer' internal file system. Which folder is controlled by the configuration file etc/reportengines/reportengines.cf. For further information on this refer to the ReportServer configuration guide.

Finally, you may select the datasource to be used. If a datasource is provided in ReportServer, it will be used instead of a datasource possibly defined in the report itself. But if you wish to use the datasource given in the report, you may not select a datasource in ReportServer.

Similarly to parameters in JasperReports, parameters are defined directly in Eclipse Birt, but have to additionally be specified within ReportServer. ReportServer supports you by trying to extract parameter settings from your report. This will, however, only capture basic parameters and for more sophisticated parameter settings (for example cascading parameters), manual adjustments will be necessary.

Birt allows to specify datasets directly within the report. Theses data sets are often used as data basis for parameters. ReportServer allows to use these data sets as ReportServer datasources. For further information see Chapter 4. (Datasources).

ReportServer comes with a basic support for SAP Crystal Reports  SAP Crystal Reports is the only proprietary reporting component supported by ReportServer and thus, we are not allowed to ship the necessary libraries with the ReportServer release. If you do have a proper Crystal Reports license you are, however, good to go and in the following we describe the necessary steps to work with Crystal from ReportServer.

In order to use Crystal Reports with ReportServer you must first install the SAP Crystal Reports for Java runtime components (short, the Java Reporting Component (JRC)). You'll find these in the SAP website. You may have to register here: https://www.sap.com/cmp/td/sap-crystal-reports-eclipse-trial.html and then click on the ''Runtime libraries'' link. Download the there located archive (around 67 MB). At the time of writing the version available is CR4ERL27_0-80004572. Unzip the archive and locate the lib directory. This includes all the additional libraries you have to install to get going with Crystal Reports. The following jars are needed:

• com.azalea.ufl.barcode.1.0.jar
• commons-configuration-1.2.jar
• commons-lang-2.1.jar
• CrystalCommon2.jar
• CrystalReportsRuntime.jar
• cvom.jar
• DatabaseConnectors.jar
• icu4j.jar
• jai_imageio.jar
• JDBInterface.jar
• jrcerom.jar
• keycodeDecoder.jar
• logging.jar
• pfjgraphics.jar
• QueryBuilder.jar
• webreporting-jsf.jar
• webreporting.jar
• XMLConnector.jar
• xpp3.jar

That is, all jars except some commons-*.jar and the log4j*.jar. Note that these should not be included as ReportServer already ships with newer versions of these components. Copy the above mentioned jars to the ReportServer WEB-INF/lib directory and start ReportServer.

Since ReportServer contains log4j libraries as of 4.3.0, you need the log4j adapter (log4j-1.2-api), available here: https://logging.apache.org/log4j/2.x/log4j-1.2-api/index.html. More information can be found here: https://logging.apache.org/log4j/log4j-2.14.1/manual/compatibility.html

Regarding this critical security issue: CVE-2021-44228. Crystal, on its current version CR4ERL27_0-80004572, is affected. For avoiding using the affected Crystal log4j libraries, use the log4j adapter (log4j-1.2-api) as described above. Please refer to this for more information: https://forum.reportserver.net/viewtopic.php?id=2926

If your Linux distribution is based on Debian (this is the case with the Bitnami packages), you can run the following commands for installing the MS TrueType core fonts:

echo "deb http://deb.debian.org/debian buster contrib" >> /etc/apt/sources.list
apt-get update && apt-get install ttf-mscorefonts-installer
ln -s /usr/share/fonts/truetype/msttcorefonts /opt/bitnami/java/lib/fonts

The above should be run by a user with sudo/root permissions.

Note that the above applies for the Bitnami containers, VMs and cloud images. In case you use the Bitnami Linux Linux installer to install the application in your own server, the installation directory might be different (/opt/reportserverenterprise-VERSION by default). In this case, you'll need to create the symlink using a similar command as the following example:

ln -s /usr/share/fonts/truetype/msttcorefonts /opt/reportserverenterprise-4.0.0.6055-0/java/lib/fonts

Of course, if a different installation directory was set, you need to adapt the command to your specific installation.

Depending on your Linux distribution, you may also have to check the following directory:

/usr/lib/jvm/default-jvm/jre/lib/fonts

When you have successfully installed the ttf-mscorefonts package, you will be able to run Crystal reports on a Linux-based environment.

Reports for Crystal Reports come in the .rpt file format. All that is needed to configure a Crystal report in ReportServer is to upload the corresponding .rpt-file. Similarly to Birt, you can either configure a datasource via the interface or leave it open in which case Crystal will use the datasource configured within the report.

Example use:

Either use your Crystal Reports designer to patch up a small demo report or simply download a demo report here http://scn.sap.com/docs/DOC-6922 (click on View Document for the download to start). If you downloaded the demo report, unzip the archive and you'll find a report file called jrc_view_report.rpt.

Log into ReportServer and go to the report management area in the administration module. Create a new ''Crystal Report'' (right click on a folder) fill in some dummy properties and select the jrc_view_report.rpt file for upload.

Saiku reports allow you to generate multi-dimensional OLAP style reports. The name Saiku stems from the beautiful open source user interface for Mondrian called Saiku (https://github.com/OSBI/saiku) that we use to display such OLAP reports. For an introduction to OLAP and Mondrian we refer to the Mondrian documentation available online at http://mondrian.pentaho.com/documentation.

Saiku reports are easily configurable once you have your Mondrian datasource (see Section 4.21.). A Mondrian datasource defines one or more cubes. A Saiku report takes a Mondrian datasource as datasource and you additionally select the cube the report should use. This is already everything you need to use OLAP with ReportServer. For an introduction to the OLAP UI we refer to the ReportServer user guid

JXLS (http://jxls.sourceforge.net) is a templating engine for Microsoft Excel and can be used together with the Dynamic List in order for users to export data into a predefined Excel spreadsheet (see the ReportServer User Guide). JXLS can, however, also be used as a first class report type which offers some advantage over the use as a Dynamic List templating engine: In particular you can define SQL queries directly from within the template and work with parameters.
In order to define a JXLS report, all that is needed in terms of configuration is an Excel file which serves as the template and a datasource which can be accessed from within the template. In addition as with any other report type you can specify parameters that can also be accessed from within the template.
JXLS is available in two different versions in ReportServer: the current JXLS2 version and a legacy, JXLS1 version. We will discuss both in the corresponding sections below.

In JXLS2 (http://jxls.sourceforge.net), you define the JXLS2 commands via Excel comments. The JXLS2 engine parses these comments and transforms the template accordingly. We will first explain some JXLS2 concepts and then we will show some examples to make it work together with ReportServer. For a complete JXLS2 documentation refer to http://jxls.sourceforge.net/.

You can find JXLS examples in ReportServer here: https://github.com/infofabrik/reportserver-samples/tree/main/src/net/datenwerke/rs/samples/templates/jxls.

Note that you can export dynamic lists into JXLS templates as of ReportServer 3.4.0. This functionality may help you with manual JXLS template creation, since manual creation may be cumbersome in some cases. Many thanks to Karolina Boboli for sending us this script and allowing us to use it.

XLS Area

A XLS area represents a rectangular area in an Excel file which needs to be transformed. This basically defines the boundaries of your JXLS2 template in your Excel file. Each XLS Area may have a list of transformation commands associated with it and a set of nested child areas.

jx:area(lastCell = "<AREA_LAST_CELL>")

jx:area(lastCell="D4")

In the example we have an area covering the A1:D4 cell range.

jx:<command_name>(attr1='val1' attr2='val2' ... attrN='valN' lastCell=<last_cell> areas=["<command_area1>", "<command_area2", ... "<command_areaN>"])

Example commands include the following:

• each
• if
• image

attr1, attr2,..., attrN are the command specific attributes.

<last_cell> defines the bottom-right cell of the command body area, analogously to the XLS area.

<command_area1>, <command_area2>, ... <command_areaN> - define XLS areas to be passed to the command as parameter.

Note that in a single cell comment you can define multiple commands. For example, in a single cell comment, you can have the following:

jx:each(items="department.staff", var="employee", lastCell="F8")
jx:if(condition="employee.payment <= 2000", lastCell="F8", areas=["A8:F8","A13:F13"])

Consider the following example.

jx:area(lastCell="B1")
jx:each(items="data" var="customer" lastCell="B1")

${customer.cus_customername} | ${customer.cus_phone}

jx:area(lastCell="B2")
jx:each(items="jdbc.query('select CUS_CUSTOMERNAME name, CUS_PHONE phone FROM T_AGG_CUSTOMER')" var="customer" lastCell="B2")

${customer.name} | ${customer.phone}

${parameters.parameterKey}

jx:area(lastCell="B2")
jx:each(items="jdbc.query('SELECT CUS_CUSTOMERNAME name, CUS_PHONE phone FROM T_AGG_CUSTOMER WHERE CUS_CUSTOMERNAME = ${parameterKey}')" var="customer" lastCell="B2")

Name | Phone
${customer.name} | ${customer.phone}

jx:area(lastCell="C10")
jx:each(items="jdbc.query('SELECT EMP_FIRSTNAME as firstname, EMP_LASTNAME as lastname FROM T_AGG_EMPLOYEE WHERE EMP_EMPLOYEENUMBER=${employee}')" var="employee" lastCell="B3")
jx:each(items="jdbc.query('SELECT CUS_CUSTOMERNAME as name, CUS_CUSTOMERNUMBER as num, Y_VOLUME as volume FROM T_AGG_CUSTOMER WHERE EMP_EMPLOYEENUMBER=${employee}')" var="customer" lastCell="C8")

Employee Number: | ${parameters.employee}
First name: | ${employee.firstname}
Last name: | ${employee.lastname}

List of Customers

Customer Name | Customer Number | Volume
${customer.name}  | ${customer.num}  | ${customer.volume}
TOTAL:	| =SUM(C8)

In this section we give you a short introduction to script reports. A detailed documentation on scripts and script reports will be given in the ReportServer scripting guide. This section builds upon the introduction of scripts given in Chapters 15. and 16. On a first read it might thus be advisable to skip this section.

Script reports allow to create complex and interactive reports. Moreover, they are suited for the generation of documentation reports that directly process metadata from ReportServer. So, for instance, the report documentation supplied with the demo data has been realized in form of a script report. To run a script report you only need a script. Create the following Hello World script in the directory bin/tmp (in the File system).

"Hello World"

Now, in the Administration module switch to Report management, create a new script re- port and assign the newly created script. If you run the report, you will see the output "Hello World". Note that for the execution of a script report, the user does not only need to have the (execute) right to execute the report, but also needs execute rights on the underlying script. Script reports will be displayed in an IFrame by default, and therefore, the HTML output is appropriate. In the following we will accordingly give our "Hello World" script a makeover.

Basically, you could simply return HTML. Instead of displaying "Hello World", for instance

"<html><body><h1>Hello World</h1></body></html>"

In the following we will use Groovy's Markup Builder to create HTML comfortably:

import groovy.xml.*
def writer = new StringWriter()

new MarkupBuilder(writer).html {
	head {
		title( "Hello World" )
	}
	body {
		h1("Hello World")
		p("This is a hello world script")
		p("The time is: " + new Date())
	}
}
writer.toString()

While configuring the script report you can add arguments to it that you can process via the variable args just like command line arguments.

import groovy.xml.*
def writer = new StringWriter()

new MarkupBuilder(writer).html {
	head {
		title ( "Hello World" )
	}
	body {
		h1("Hello World")
		p("This is a hello world script")
		p("The time is: " + new Date())
		p("Arguments:" + args.join(", "))
	}
}
writer.toString()

Parameters specified at the report will be passed to the script via the parameterMap. If there is, for instance, the parameter with the key param, you can access it by using the following statement.

parameterMap['param']

Beside arguments and parameters you can define multiple output formats with the report. They can be entered separated by commas, e.g. "HTML,PDF,DOCX". If you now execute the report, you will see that the output formats were changed to export options. In the script itself, you can query the output format by using the variable outputFormat. The output format for preview is "preview":

import groovy.xml.*
def writer = new StringWriter()

new MarkupBuilder(writer).html {
	head {
		title ( "Hello World" )
	}
	body {
		h1("Hello World")
		p("This is a hello world script")
		p("The time is: " + new Date())
		p("Arguments:" + args.join(", "))
		p("Output format:" + outputFormat)
	}
}
writer.toString()

By using the open source library Flying-Saucer you can easily create PDF documents from HTML. ReportServer supports you here by providing a pre-set renderer for PDF creation. In the following example a PDF object will be returned provided you have selected pdf (please ensure to use the format in lower case letters inside the script).

The script report below supports PDF, HTML and DOCX outputs.

import groovy.xml.*
def writer = new StringWriter()

new MarkupBuilder(writer).html {
	head {
		title ( "Hello World" )
	}
	body {
		h1("Hello World")
		p("This is a hello world script")
		p("The time is: " + new Date())
		p("Arguments:" + args.join(", "))
		p("Output format:" + outputFormat)
	}
}
if("pdf".equals(outputFormat.trim()))
	return renderer.get("pdf").render(writer.toString())
if("html".equals(outputFormat.trim()))
	return renderer.get("html").render(writer.toString())
if("docx".equals(outputFormat.trim()))
	return renderer.get("docx").render(writer.toString())
writer.toString()

Beside parameters, arguments and output formats you can also add a datasource to a script. The datasource object will be passed to the script via the "connection" variable. The following example uses the internal demo data (refer to Section ) to present a customer list.

import groovy.xml.*
import groovy.sql.Sql

def writer = new StringWriter()

new MarkupBuilder(writer).html {
	head {
		title ( "Hello World" )
	}
	body {
		h1("Hello World")
		p("This is a hello world script")
		p("The current time is: " + new Date())
		p("Arguments:" + args.join(","))
		p("Output format:" + outputFormat) h1("Customers: ")
		ul {
			new Sql(connection).eachRow("SELECT DISTINCT CUS_CONTACTLASTNAME
														FROM T_AGG_CUSTOMER ORDER BY 1 ASC" ){
				li(it.CUS_CONTACTLASTNAME)
			}
		}
	}
}
if("pdf".equals(outputFormat.trim()))
	return renderer.get("pdf").render(writer.toString())
if("html".equals(outputFormat.trim()))
	return renderer.get("html").render(writer.toString())
if("docx".equals(outputFormat.trim()))
	return renderer.get("docx").render(writer.toString())
writer.toString()

The Grid Editor component is not a report type in the classical sense. It is rather a very flexible spreadsheet like database editor that can be used in situations where you want to enable a user to do some basic data administration. Once defined the grid editor can be used as any report, that is, it can be used by users in their TeamSpace and users can even export the underlying data to Excel and schedule the report.

The grid editor component is configured by providing a datasource  and a ReportServer script. ReportServer scripts are covered in greater detail in Chapter 16. and in the separate ReportServer scripting guide and it might be helpful to skip the following details on a first read and come back to grid editors once you have a basic understanding of ReportServer scripts.

The simplest use case for a grid editor is when you have a database table and you want to give a user the possibility to edit the data in that table. At the very basis you need to generate a GridEditorDefinition which handles the interaction with the user. For relational databases, ReportServer provides a helper class called DbGridEditorDefinition (located in net.datenwerke.rs.grideditor.service.grideditor.definition.db) which tries to handle as much of the interaction (loading data, storing values, etc.) as possible. Consider the following example script:

import net.datenwerke.rs.grideditor.service.grideditor.definition.db.*

def definition = GLOBALS.getInstance(DbGridEditorDefinition)

def adapter = definition.adapter
adapter.tableName = 'T_AGG_CUSTOMER'

return definition

Here we obtain a new instance of a DbGridEditorDefinition and load an adapter object which is used for most of the configuration. At the very least you need to specify which database table you want to work on. With the above configuration ReportServer will attempt to load the data as

SELECT * FROM T_AGG_CUSTOMER

and display the data paged with a page size of 100 rows. To change the number of rows per page you can call setPageSize() on the adapter and set the number of rows. By default the editor will allow the user to edit every cell, to delete entire rows and to insert new rows. The entire editing process is cached on the client and only if the client calls save will the data be stored.

If you need to filter the data on your table, you can use set the whereClause property as shown in the following example:

import net.datenwerke.rs.grideditor.service.grideditor.definition.db.*

def definition = GLOBALS.getInstance(DbGridEditorDefinition)

def adapter = definition.adapter
adapter.tableName = 'T_AGG_CUSTOMER'
adapter.whereClause = 'CUS_CUSTOMERNUMBER > 100'

return definition

Consider the following data table (a small extract of the demo data):

Now suppose the user changed the credit limit of Microsale to 50000. In this case ReportServer builds the following update statement

UPDATE T_AGG_CUSTOMER
    SET
        CUS_CUSTOMERNUMBER = 456 ,
        CUS_CUSTOMERNAME = 'Microsale Inc.' ,
        CUS_CREDITLIMIT = 50000
    WHERE
        CUS_CUSTOMERNUMBER = 456 AND
        CUS_CUSTOMERNAME = 'Microsale Inc.' AND
        CUS_CREDITLIMIT = 39800

Assuming that CUS_CUSTOMERNUMBER is the table's sole primary key, this statement would be a bit of an overkill as the WHERE clause lists fields which are not part of the primary key. You should thus always tell ReportServer the primary key of a table. In addition we might want to only display a fraction of the table:

import net.datenwerke.rs.grideditor.service.grideditor.definition.db.*

def definition = GLOBALS.getInstance(DbGridEditorDefinition)

def adapter = definition.adapter
adapter.tableName = 'T_AGG_CUSTOMER'
adapter.primaryKey = 'CUS_CUSTOMERNUMBER'
adapter.addColumns('CUS_CUSTOMERNUMBER', 'CUS_CUSTOMERNAME',  'CUS_CREDITLIMIT')

return definition

Now if a user updates the table the following statement will be generated behind the scenes:

UPDATE T_AGG_CUSTOMER
    SET
        CUS_CUSTOMERNUMBER = 456 ,
        CUS_CUSTOMERNAME = 'Microsale Inc.' ,
        CUS_CREDITLIMIT = 50000
    WHERE
        CUS_CUSTOMERNUMBER = 45

def adapter = gridHelper.initDbGridEditor()

adapter.configure(report,"TABLE_NAME")
    .setPk('id')
    .columns()
        .add('id')
			.setEditable(false)
			.setHidden(true)
        .add('your_column')
    .done()

return adapter;

import net.datenwerke.rs.grideditor.service.grideditor.definition.db.*

def definition = GLOBALS.getInstance(DbGridEditorDefinition)

def adapter = definition.adapter
adapter.tableName = 'T_AGG_CUSTOMER'
adapter.addColumns('CUS_CUSTOMERNUMBER', 'CUS_CUSTOMERNAME',  'CUS_CREDITLIMIT')

return definition

UPDATE T_AGG_CUSTOMER
    SET
        CUS_CUSTOMERNAME = 'Microsale Inc.' ,
        CUS_CREDITLIMIT = 50000
    WHERE
        CUS_CUSTOMERNAME = 'Microsale Inc.' AND
        CUS_CREDITLIMIT = 39800

Besides the standard API to configure the grid editor there exists a compact, fluid API. First we can make it easier to obtain an adapter object. For this there in the scope of the script you have access to an object called gridHelper which provides the method initDbGridEditor. Then, to initiate the API one needs to call the configure method on the adapter object. The above example can be rewritten in the fluid API as

def adapter = gridHelper.initDbGridEditor()

adapter.configure(report,'T_AGG_CUSTOMER')
   .columns()
      .add('CUS_CUSTOMERNUMBER')
      .add('CUS_CUSTOMERNAME')
      .add('CUS_CREDITLIMIT')
   .done()

return adapter

The configure method takes as parameter a report object (the corresponding report object is present in the script's scope) and the table name. From there you can access various configuration, amongst others, column configuration. By calling the columns method you start the column configuration which you end again by calling done.

For the remainder of the description of the grid editor we present features first with the "classical" API and then how to do the same with the fluid API.

A Grid Editor's adapter object provides several additional configuration options that we discuss next.

Note that filtering and sorting can also be specified on a per column basis.

All the above configuration can also be done via the fluid API by calling, for example,

def adapter = gridHelper.initDbGridEditor()

adapter.configure(report,'T_AGG_CUSTOMER')
   .setPaging(false)
   .columns()
      .add('CUS_CUSTOMERNUMBER')
      .add('CUS_CUSTOMERNAME')
      .add('CUS_CREDITLIMIT')
   .done()

return adapter

In order to further configure how columns are presented to the user, you can specify so called column config objects which provide column specific configurations. Basic column configs are specified via instances of class GridEditorColumnConfig located in net.datenwerke.rs.grideditor.service.grideditor.definition:

import net.datenwerke.rs.grideditor.service.grideditor.definition.db.*
import net.datenwerke.rs.grideditor.service.grideditor.definition.*

def definition = GLOBALS.getInstance(DbGridEditorDefinition)

def adapter = definition.adapter

adapter.tableName = 'T_AGG_CUSTOMER'
adapter.primaryKey = 'CUS_CUSTOMERNUMBER'
adapter.addColumns('CUS_CUSTOMERNUMBER', 'CUS_CUSTOMERNAME',  'CUS_CREDITLIMIT')

def nameConfig = new GridEditorColumnConfig()
nameConfig.displayName = 'NAME'
adapter.setColumnConfig('CUS_CUSTOMERNAME', nameConfig)

return definition

Or more compactly via the fluid API

def adapter = gridHelper.initDbGridEditor()

adapter.configure(report,'T_AGG_CUSTOMER')
    .setPk('CUS_CUSTOMERNUMBER')
    .columns()
        .add('CUS_CUSTOMERNUMBER')
        .add('CUS_CUSTOMERNAME')
            .setDisplay('Name')
        .add('CUS_CREDITLIMIT')
    .done()

return adapter;

For setting the display name you may also use the shortcut .add('columnName','distplayName').

Besides changing the name of columns you can also specify a number of display options

Without further configuration ReportServer will only enforce that entered data is of the correct type. For example, if a field is of type INTEGER, then a user can only type in digits into the text field. In order to further restrict what users can enter you can add one or more Validators to each column. Validators are located in package net.datenwerke.rs.grideditor.service.grideditor.definition.validator. The following validators are available

In order to configure a validator you instantiate the corresponding object and provide the necessary configuration in the constructor. All Min/Max validators take as configuration the bound as well as an error message that is displayed in case a user enters a value that violates the bound. For example

new MaxIntegerValidator(15, "Values must be less than 15");

The RegExValidator takes as configuration a regular expression (see https://docs.oracle.com/en/java/javase/11/docs/api/java.base/java/util/regex/Pattern.html for an introduction) and an error message. To, for example, restrict data to conform to a date format of type "yyyy-mm-dd" you could use

new RegExValidator("^\d{4}-\d{2}-\d{2}$", "Value should be of format yyyy-mm-dd");

By default the Grid Editor constructs form fields matching the type of a column. That is, for a text columns a text field is created, for date columns a date picker, for booleans a checkbox. For certain fields you can change the default behavior and specify a custom editor. In order to tell a column that it should use a custom editor use the setEditor method of a GridEditorColumnConfig option. All custom editors are located in package net.datenwerke.rs.grideditor.service.grideditor.definition.editor.

The following is an example assuming the column names textbool and intbool.

def textbConf = new GridEditorColumnConfig();
textbConf.setEditor(new TextBooleanEditor());
adapter.setColumnConfig('textbool', textbConf);

def intbConf = new GridEditorColumnConfig();
intbConf.setEditor(new IntBooleanEditor());
adapter.setColumnConfig('intbool', intbConf);

Using the fluid API, we can define quasi-boolean editors for columns by calling withIntBooleanEditor, or withTextBooleanEditor.

def adapter = gridHelper.initDbGridEditor()

adapter.configure(report, 'TABLE')
    .setPk('...')
    .columns()
        .add('column')
            .withIntBooleanEditor()
    .done()

return adapter;

def dateConf = new GridEditorColumnConfig()
dateConf.editor = new TextDateEditor('yyyy-mm-dd')
adapter.setColumnConfig('textdate', dateConf)

For integer columns the configuration works identical with the only difference that you assign a IntSelectionListEditor instead of a TextSelectionListEditor and that values are of type integer. Following is an example using a simple list to define values:

def ddIntConf = new GridEditorColumnConfig();
def intEditor = new IntSelectionListEditor();
intEditor.setValues([2,3,5,7,11])
ddIntConf.setEditor(intEditor);
adapter.setColumnConfig('intdd', ddIntConf);

And an example with custom labels.

def ddIntConf = new GridEditorColumnConfig();
def intEditor = new IntSelectionListEditor();
intEditor.addEntry('foo',2)
intEditor.addEntry('bar',7)
ddIntConf.setEditor(intEditor);
adapter.setColumnConfig('intdd', ddIntConf);

Within the edit mode for the editor you have the very same methods addValue and addEntry. In addition you can add multiple values via the method from which either takes a list of values (corresponding to addValue) or a map (corresponding to addEntry).

Note that we did not specify the type of selection editor with the fluid API. The type is, instead, recognized by the provided values. This means, that in case you want to add, for example, "long" values you need to typecast.

Finally, a frequent objective is to construct the values for the selection list from a database query. To this end, you can use the fromSql which takes either a SQL statement or a connection object and a SQL statement. In case you provide no connection, the same connection as to the grid editor is used. In case you provide a connection, be sure to close the connection after usage. The SQL statement needs to return either two columns (key,value) or a single column.

Within your script you can access a couple of predefined variables that allow you to access the report object, as well as the current user and the parameters. The following variables are available:

You can easily obtain a database connection for the datasource that is configured at the report by using the getConnection() method from the DbGridEditorDefinition object.

import net.datenwerke.rs.grideditor.service.grideditor.definition.db.*
import net.datenwerke.rs.grideditor.service.grideditor.definition.*

def definition = GLOBALS.getRsService(DbGridEditorDefinition.class)
def connection = definition.getConnection(report)

connection.close()

In the following we explain how you can use foreign key relationships to make editing easier. Consider a database table Products that has the following structure

In this case, if you wanted to switch the category of a product, you would need to know the category number. In such cases, it might be helpful, to instead be able to choose a category using the category name rather, but this is stored separately from the Products table. For such cases you can specify foreign key columns which allow you to display different information in place of the information that is within the table. On update and insert this information is then replaced again by the correct value.

In order to specify a foreign key column you addForeignKeyColumn() methods provided by the adapter. They take four or five parameters:

We could thus specify the relationship as follows in groovy code:

import net.datenwerke.rs.grideditor.service.grideditor.definition.db.*

def definition = GLOBALS.getRsService(DbGridEditorDefinition.class)

def adapter = definition.getAdapter()
adapter.setTableName("Products")
adapter.setPrimaryKey('productNumber')
adapter.addColumns('productNumber', 'productName')
           .addForeignKeyColumn('productCategory','Categories','categoryNumber','categoryName','Category')
           .addColumns('productSupplier')

return definition;

Similarly, you could additionally define the foreign key relationship also for the productSupplier column.

Usually, you would additionally define an editor that allows users to select a category. For this, you can specify a config object that is used as fifth argument. For example something along the following lines (note also the additional imports).

import net.datenwerke.rs.grideditor.service.grideditor.definition.db.*
import net.datenwerke.rs.grideditor.service.grideditor.definition.*
import net.datenwerke.rs.grideditor.service.grideditor.definition.editor.*

import groovy.sql.Sql

def definition = GLOBALS.getRsService(DbGridEditorDefinition.class)

def adapter = definition.getAdapter()
adapter.setTableName("Products")
adapter.setPrimaryKey('productNumber')

// define config for categories
def categoryConfig = new GridEditorColumnConfig(displayName: 'Category')
def categoryEditor = new TextSelectionListEditor()
def connection = definition.getConnection(report)
try{
    new Sql(connection).eachRow('SELECT categoryName AS name FROM Categories ORDER BY 1' ){
        categoryEditor.addValue(it.name)
    }
} finally{
    connection.close();
}
categoryConfig.setEditor(categoryEditor);

adapter.addColumns('productNumber', 'productName')
           .addForeignKeyColumn('productCategory','Categories','categoryNumber','categoryName',categoryConfig)
           .addColumns('productSupplier')

return definition;

SELECT CONCAT(supplierLastName, ", ", supplierFirstName) FROM Suppliers

Our example would, thus, change as follows (note the changes in the second to last line)

import net.datenwerke.rs.grideditor.service.grideditor.definition.db.*
import net.datenwerke.rs.grideditor.service.grideditor.definition.*
import net.datenwerke.rs.grideditor.service.grideditor.definition.editor.*

import groovy.sql.Sql

def definition = GLOBALS.getRsService(DbGridEditorDefinition.class)

def adapter = definition.getAdapter()
adapter.setTableName("Products")
adapter.setPrimaryKey('productCode')

// define config for categories
def categoryConfig = new GridEditorColumnConfig(displayName: 'Category')
def categoryEditor = new TextSelectionListEditor()
def connection = definition.getConnection(report)
try{
    new Sql(connection).eachRow('SELECT categoryName AS name FROM Categories ORDER BY 1' ){
        categoryEditor.addValue(it.name)
    }
} finally{
    connection.close();
}
categoryConfig.setEditor(categoryEditor);

adapter.addColumns('productCode', 'productName')
           .addForeignKeyColumn('productCategory','Categories','categoryNumber','categoryName',categoryConfig)
           .addForeignKeyColumn('productSupplier','Suppliers', 'supplierNumber', 'CONCAT(.supplierLastName, ", ", .supplierFirstName)','Supplier')

return definition;

The change should be somewhat unexpected as we additionally added the string twice. This becomes necessary since behind the scenes ReportServer needs to create a complex SELECT statement that joins together the foreign key tables. The replacement is then used to plug in the correct temporary table name. Of course, we could also for the supplier add an editor. Here we would not need replacement. Instead, the code for the editor is straight forward:

def supplierEditor = new TextSelectionListEditor()
def connection = definition.getConnection(report)
try{
    new Sql(connection).eachRow('SELECT CONCAT(supplierLastName, ", ", supplierFirstName) AS name FROM Suppliers ORDER BY 1' ){
        supplierEditor.addValue(it.name)
    }
} finally{
    connection.close();
}

def adapter = gridHelper.initDbGridEditor()

adapter.configure(report, 'Products')
    .setPk('productCode')
    .columns()
        .add('productCode')
        .add('productName')
        .add('productCategory')
           .fk('Categories','categoryNumber','categoryName')
           .withFkEditor()
        .add('productSupplier', 'Supplier')
           .fk('Suppliers', 'supplierNumber', 'CONCAT(.supplierLastName, ", ", .supplierFirstName)')
    .done()

return adapter;

If you wish to integrate reports in external applications, you can use a specific URL to directly link the report export.

http://SERVER:PORT/reportserverbasedir/reportserver/reportexport

Here the following parameters control the export:

Depending on the format, additional properties are available.

In addition to the control options stated above, you can set further properties for dynamic lists:

Here you will find an example for a possible configuration (spaces in URLs will be coded as %20, further information on URL encoding you wilL find, for instance under http://www.w3schools.com/tags/ref_urlencode.asp):

https://127.0.0.1:8888/reportserver/reportserver/reportexport?id=4&c_1=ENTITY_ID|FOO_ID&fri_1=2\%20-\%205|-\%207&c_2=action&c_3=key_field&h_1&or_1=DESC&format=pdf

In addition to exporting reports by URL, you can directly open pre-configured reports in ReportServer by URL. Here the URL is

https://SERVER:PORT/reportserverbasedir/ReportServer.html#reportexec/

Here you can use the above parameters to pre-configure the report. Please keep in mind to separate parameters from the appurtenant value by setting a colon (:) (instead of using the equal sign "=").

The above parameters are supplemented by "v:preview" to directly jump to the preview. Here we give you an example for a possible configuration:

https://SERVER:PORT/reportserverbasedir/ReportServer.html#reportexec/key:customer&c_1:CUS_CUSTOMERNUMBER&c_2:CUS_CUSTOMERNAME&c_3:CUS_ADDRESSLINE1&c_4:CUS_ADDRESSLINE2&c_5:CUS_CITY&fi_1:187&v:preview

In some cases it is helpful to execute reports without having to login first. Here ReportServer's solution is an easy-to-use servlet. The httpauthexport servlet allows to execute reports without the user being logged in. The URL to be used is:

https://SERVER:PORT/reportserverbasedir/reportserver/httpauthexport

Apart from the usual parameters, you have to enter

• Note that the apikey can be found as plain text in the URL, so make sure you use HTTPS. Treat the apikey like any password.

The user does not require a password, or any unusual permissions. The only permissions required are the execute permission on those reports that you plan to embed. Suppose, that we have specified the apikey 79PKXGScP8r8 on a fresh user exportuser which has no permissions except the permission to execute report 5000. Then, when everything goes right, then

http://SERVER:PORT/reportserverbasedir/reportserver/httpauthexport?id=5000&user=exportuser&apikey=79PKXGScP8r8&format=HTML&download=false

should execute the report with id 5000 and user exportuser.

In the previous section we have seen how to execute reports directly via the URL. It is also possible to detach the report execution view (i.e., including parameter configuration or the complete configuration for dynamic lists) to, for example, embed it into a portal. The syntax is analogous to the reportexec functionality described above. The base URL for embedding the report view is

http://SERVER:PORT/reportserverbasedir/ReportServer.html#inlinereport/

Thus, to display report with id 29 you would use the URL

http://SERVER:PORT/reportserverbasedir/ReportServer.html#inlinereport/id:29

If you only want to display the preview view, then you can add the "type:preview" parameter, that is

http://SERVER:PORT/reportserverbasedir/ReportServer.html#inlinereport/id:29&type:preview

You can even specify exactly which views to display. Assuming that report 29 is a dynamic list, then the following would select the list config as well as the preview

http://rstest.datenwerke.net/ReportServer.html#inlinereport/id:29&views:listconfig|preview

The following views are available

Note that you can still completely configure the report via the URL as seen in the following example where we configure a dynamic list:

http://SERVER:PORT/reportserverbasedir/ReportServer.html#inlinereport/key:customer&c_1:CUS_CUSTOMERNUMBER&c_2:CUS_CUSTOMERNAME&c_3:CUS_ADDRESSLINE1&c_4:CUS_ADDRESSLINE2&c_5:CUS_CITY&fi_1:187&views:preview

Report properties provide a means to further customize how report server treats reports. You can access report properties via the report management perspective in the administration module by selecting a report and then selecting Report Properties. You are then presented with a grid that allows to view and change the current properties for that particular report.

It is often very useful to save additional data with the report object. Depending on the process, it might be helpful to save when and by whom a report was checked and accepted, or, maybe you wish to file the link leading to the documentation of a report.The type of metadata required, therefore, depends to a large extent on the processes applied in an enterprise.

ReportServer supports you to file report specific metadata by allowing to maintain a key value list for each report. In Report management you can recall it in the relevant report by clicking on the Metadata tab. With the Add and Remove buttons new key pairs can be added or removed. By clicking on the arrow next to the Add button you may choose from a list of key words already used.You can directly and easily edit the values in the list.

ReportServer provides support in creating drill down and drill across reports with Jasper- Reports and BIRT. For Drill Down and Drill Across operations, reports will be linked with each other to show more detailed information on specified data. ReportServer provides the special parameters _RS_BACKLINK_ID und _RS_BACKLINK_URL to each report execution . So when you are in a JasperReport and you want to link it to the report with the key myKey enter the link as follows:

"http://localhost:8888/reportserver/reportserver/reportexport?key=myKey&"
+ "format=html&bid=" + $P{_RS_BACKLINK_ID}

Of course, you have to replace the first part of the link by the server address. By entering the parameter bid you can easily set a backlink. You will get the required URL via the parameter _RS_BACKLINK_URL. If there is no backlink, the parameter will be empty.