Chapter 6 Dynamic lists

ReportServer allows you to set the desired output format for each column directly in the list configuration. To do this, select the desired column or columns and select the Column format button.

You can set the column format to be used in the dialog box that opens.

Depending on the data type of the column, the following formatting options are available:

Use the Show report documentation button to open the report documentation. This almost clearly summarizes all the settings made. Please note that the report documentation always refers to the last saved version of the variant. The illustration shows an example of report documentation. For more information on report documentation, please also read the section Report documentation.

In the filter dialog in text input mode, you can work with wildcards in addition to entering an exact value.

The placeholder * represents a character string of any length. This also includes the empty character string. Example: Roof* includes both attic, roof truss and roof alone.

The wildcard ?, on the other hand, denotes exactly one character.

Wildcards can also be used in the search mask of the filter dialog.

For wildcards in range filters, the largest possible range is selected. If the mask described by the wildcard matches several values, the smallest matching value is selected for the lower interval limit and the largest matching value is selected for the upper limit.

Please note that if there is no value corresponding to the pattern for one of the interval limits, the interval is empty. This can lead to unexpected problems, particularly with inclusion, e.g. a* - z* is empty if there is no value starting with z.

Aggregation refers to the combining or summarizing of data that is identical with regard to a grouping characteristic and thus forms a group. The result set contains one data record for each group present in the source data.
As an example, let's look at a list of people with the characteristics of gender and age. A possible aggregation would now be the average age grouped by gender. Here, a list with n data records is summarized to a result with one row per gender.
In aggregation, we therefore distinguish between attributes that determine which group a data record belongs to (gender) and those that are summarized into a single value using an aggregate function.

The aggregate functions implemented in ReportServer are

Ist in ReportServer für eine Spalte eine Aggregatsfunktion eingestellt, so werden automatisch alle Spalten, ohne Aggregation, als Gruppierungsmerkmal betrachtet. Es ist nicht möglich, dass eine Liste Spalten enthält die weder Teil der Aggregation, noch der Gruppierung sind.

Um Aggregationen in Auswertungen zu nutzen, legen Sie im Aspekt Listenkonfiguration für einzelne Spalten/Attribute die zu verwendende Aggregatsfunktion fest.

To additionally display the underlying individual data records for each group when using the Aggregation function, use the Partial results function from the list configuration toolbar. In the dialog box that opens, select the columns to be used for grouping from the non-aggregated columns. All non-selected and non-aggregated attributes are displayed in the individual data records.

Filters in the List configuration aspect always work on the visible end result. In conjunction with the Aggregation function, this means that the data is first aggregated and then filtered on the result. In our example with the average age, this means that a filter on the Age column filters the average age in the result, but is not suitable for excluding individual data records from the averaging.

A filter on the "Age" column with the definition "30 - " therefore does not change the average value of the two groups, but causes only groups with an average value greater than or equal to 30 to be displayed. If you are familiar with the SQL database language, you can remember that filters on aggregated columns are implemented as HAVING filters.

To filter the data records included in the aggregation rather than the result of the aggregation as described here, you can use the Prefilter function described below.

Pre-filters are a powerful tool for limiting the database of an evaluation. Their possible uses go far beyond the filters in the list configuration aspect. The three main differences are

The pre-filter contains a set of filter expressions which can be combined with AND and OR and nested as required.

Let's look at an example with the following four filters
A := age > 30
B := Gender == female
C := Place of residence == Berlin
D := age < 5
E := place of birth == place of residence

The combination of these individual filters to the following combined expression

(B UND C UND (A ODER D ODER E))

now describes the selection of all women from Berlin who are either younger than 5 or older than 30 years.

ReportServer's prefilter displays such an expression as a tree, as follows:

AND and OR represent blocks that enclose the underlying expressions. The AND block in the example therefore contains B, C and the OR block. The OR block in turn contains A as well as D and E.

When evaluating the tree, the individual filters (A, B, C, D, E) are first evaluated for each data set. The result of each such evaluation provides a truth value: TRUE or FALSE.

Example data set:
Age = 25
Gender = Female
Place of residence = Berlin
Place of birth = Stuttgart

If the filter conditions A to E mentioned at the beginning are evaluated against this example data set, the following tree is obtained:

In the following step, the truth values of blocks are determined. To do this, the truth values of all blocks that do not contain any other blocks are combined with the logical operation of the block.

An AND block is TRUE if all enclosed expressions are TRUE. An OR block is TRUE if at least one of the enclosed expressions is TRUE.

The block is then replaced by its truth value. This process is repeated until the root block is determined.

In the example, this is done in two steps:

This means that the sample data set is not part of the result set.

Use the toolbar to add blocks and filters to your expression. These are inserted below (as part of) the currently selected block. Blocks and filters can be moved to another block using drag-and-drop. The order of expressions within a block is irrelevant. AND blocks can only contain OR blocks directly and OR blocks can only contain AND blocks. When inserting new blocks or moving them, the correct type is automatically selected.

Note that the complete pre-filter tree can be exported to a DOT file with the "Export to DOT" button (renderable via any Graphviz tool, incl. ReportServer REST dot-renderer) or to the Excel export with the output_filters or output_complete_configuration report properties. Further information can be found in the administration manual.

The $ formula language already mentioned several times can also be used in the filter dialog. Instead of a value, you can enter any formula in text mode. In addition to the standard replacements (see Formula language), the following additional objects/replacements are available in the formulas in the filter for your own calculations:

The today object provides access to a complete calendar. This calendar is initialized with the current date and time of execution. You can use the following functions to manipulate the date and time stored in the calendar.

Example: You want to filter all invoices from the previous month. To do this, you can define the following inclusion filter:

${today.firstDay().addMonths(-1)} - ${today.firstDay().addSeconds(-1)}

If the column is of type VARCHAR (i.e. a text column) and the format is specified in the form day.month.year, you must extend the formula expressions by calling the format function:

1. ${today.firstDay().addMonths(-1).format("dd.MM.yyyy")}
2. -
3. ${today.firstDay().addSeconds(-1).format("dd.MM.yyyy")}

The agg object provides access to calculations for all values in the current column. For example, a filter expression that refers to the average value of the column can be defined.

The following functions are available:

Calculated fields allow you to add columns to your analysis that do not appear in the source data, but result from a calculation rule from the existing columns.

The calculation rule is specified in the form of an SQL expression that is executed directly on the database. This means in particular that the available expressions depend on the database used. You can find out which functions are available in detail from your administrator. This section only explains some general options.

If you have defined a calculated field in ReportServer, you can then use this just like any other column in your evaluations. You must also add calculated fields in the List configuration aspect of your report in order for them to be displayed in the results.

When defining calculated fields, you cannot access the values of other calculated fields.

Calculated fields are configured in the Calculated fields aspect. Here you can create fields and edit their definition. The name you assign serves as the name for the column created and must therefore comply with the specifications for column names (alphanumeric, no spaces). Column names must be unique.

SQL offers a multitude of possibilities to define a field, which clearly exceed the scope of this manual. Nevertheless, here are some code examples that are frequently used in calculated fields.

In the following we assume a table with the following columns
A, B: Columns with text
C, D: Columns with whole numbers

For simple calculations, you can use the basic arithmetic operations, e.g.
C + D as the definition for a field with the sum of both columns.

Concatenation, the joining of two text columns, is usually done with the || operator. The + operator or the call of CONCAT(A, B) are also common.

CASE expressions can be used to formulate conditions. For example, values can be classified depending on their size.

CASE
	WHEN SPALTE < 500 THEN 1
	WHEN SPALTE < 1000 THEN 2
	WHEN SPALTE < 2000 THEN 3
	WHEN SPALTE < 3000 THEN 4
	ELSE 0
END

With CASE expressions, please note that the statement of the first applicable condition is selected regardless of whether a subsequent condition may also apply. The return values of all conditions must be of the same data type.

In addition to the familiar export formats such as EXCEL or CSV, dynamic lists can also be inserted directly into predefined templates. In this section, we present the basic functionality.

Templates are managed via the Edit template button when executing a dynamic list in the List configuration aspect. In the dialog that opens, you will see a list of the templates that currently exist for this variant. You can use the toolbar to add new templates or download existing ones for editing.

In addition to a name and description, templates also have a type. The following types are currently available:

If you have created a new template, you can then upload the corresponding file. In the following, we provide an example for each template type. However, a complete documentation of the functionality would go beyond the scope of this manual. Further documentation on the individual template formats can be found at:

By integrating the JXLS Template Engine into ReportServer, it is also possible to create sophisticatedly formatted Excel worksheets directly from the reporting platform. The JXLS approach is that the template, which determines the appearance of the actual document, is itself an Excel worksheet and can therefore be created and edited directly with Microsoft Excel. Special instructions in the template document control where data is inserted in the template.

Examples of JXLS in ReportServer can be found here: https://github.com/infofabrik/reportserver-samples/tree/main/src/net/datenwerke/rs/samples/templates/jxls.

A detailed description of all the functions of JXLS would go beyond the scope of this document, so here is just a brief explanation of the basics. JXLS2 is supported in ReportServer and is briefly described here. The legacy JXLS1 version is not supported.

You can find complete JXLS2 documentation on the JXLS project page at http://jxls.sourceforge.net. Here we provide an overview of the most important components for working with ReportServer.

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

In JXLS2 (http://jxls.sourceforge.net), you define the JXLS2 commands using Excel comments. The JXLS2 engine analyzes these comments and transforms the template accordingly. Here we show you a simple example of a template and briefly explain its components. Further information can be found in the administration manual (JXLS reports) and in the JXLS2 documentation.

Consider the following example:

Cell A1 contains an Excel comment with the following text: jx: area (lastCell = "B1"). It defines the boundaries of our template as A1:B1. It also contains a Jxls Each command with the following text: jx: each (items = "data" var = "customer" lastCell = "B1"). The Each command iterates the collection of objects in the ''data'' variable and prints the corresponding information. The main range of the Each command is A1:B1 (defined by the lastCell attribute). This means that the cells are cloned and processed with each new customer object in the context.

Note that ''data'' contains the data selected by the dynamic list. This is automatically provided by ReportServer and can be used directly in JXLS templates when used as a template for the dynamic list.

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

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

Please note that you must enter the fields in lower case so that they are correctly assigned by the JXLS engine.

Now that we have learned how templates generally work as templates in ReportServer, let's look at an example.

In order to get a practical feel for the use of templates in ReportServer, we will now illustrate a practical example of templates with JXLS.

First, we create a dynamic list and select the columns that we want to return via the template in template-defined form. In this example, we start from the sample report ''T_AGG_EMPLOYEE'' and select the columns ''EMP_EMAIL'', ''EMP_EMPLOYEENUMBER'', ''EMP_FIRSTNAME'', ''EMP_JOBTITLE'', ''EMP_LASTNAME'', ''EMP_OFFICECODE'', ''OFF_CITY'', ''OFF_COUNTRY'' and ''Y_SALES_AMOUNT''.

Here we can see a preview of all the columns with your data that we consider important for each data record.

Now we need to create the template. This is best done with Excel and can be done using an official example like this: http://jxls.sourceforge.net/reference/each_command.html. In relation to our example, you could download the templates here: https://github.com/infofabrik/reportserver-samples/tree/main/src/net/datenwerke/rs/samples/templates/jxls/jxlsdynamiclist.

Now all that remains is to load the template onto the ReportServer. To do this, follow these instructions in exactly the order described:

1. Please click on Configure list
2. Click on the Edit template button
3. Then click on Add template
4. Enter the format as JXLS format in the Template Type pull-down menu
5. Now just upload the previous excel file

Now the dynamic list should enable a new export format called template. Thanks to the previous example template, the Excel file should generate the following output.

Velocity https://velocity.apache.org/ is a template language for text documents. You can use it to create any output format that uses plain ASCII text as the file format.

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

Hallo Welt, dies ist eine Velocity Template!

The replacement $data can be used to access the individual data records (the example assumes that the dynamic list contains the field "CUS_CUSTOMER_NAME").

Customer list:

Customer List:
----------------------------------------------------------
#foreach ( $customer in $data )
	$customer.CUS_CUSTOMERNAME
#end

This template can also be downloaded from our reportserver-samples GitHub: https://github.com/infofabrik/reportserver-samples/tree/main/src/net/datenwerke/rs/samples/templates/velocity

You can use/display your report parameter values by using the available ''parameters'' object. For example, if your report has a parameter ''myparameter'', you can access it with the following:

$parameters.myparameter

You can also access all the special parameters explained here: https://reportserver.net/en/guides/admin/chapters/using-parameters/ by using the available ''meta'' object. The following example shows the name of the report:

$meta._RS_REPORT_NAME.value

Note that, unlike the report parameters explained above, ''value'' is required to retrieve the values of the special parameters.

XDocReport https://github.com/opensagres/xdocreport brings the Velocity template language together with Microsoft Word.

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

To use template commands, such as

#foreach ( $customer in $data )

you must embed them in so-called MergeFields. To do this, go to "Insert/Quick blocks/Field..." and then select the MergeField field from the Mail merge category. The command is then entered in the input field under Field functions. For example, the above velocity example could be mapped as follows, where MERGEFELD[XX] stands for a command as a merge field.

Alle Kunden:
MERGEFIELD[#foreach($customer in $data)]
MERGEFIELD[$customer.CUS_CUSTOMERNAME]
MERGEFIELD[#end]

In the following screenshot you can see the resulting template in Word. This and the results of the template execution can also be downloaded from our reportserver-samples GitHub: https://github.com/infofabrik/reportserver-samples/tree/main/src/net/datenwerke/rs/samples/templates/xdoc

You can use/display your report parameter values by using the available ''parameters'' object. For example, if your report has a parameter ''myparameter'', you can access it with the following:

MERGEFIELD[$parameters.myparameter]

You can also access all the special parameters explained here: https://reportserver.net/en/guides/admin/chapters/using-parameters/ by using the available ''meta'' object. The following example shows the name of the report:

MERGEFIELD[$meta._RS_REPORT_NAME.value]

Note that, unlike the report parameters explained above, ''value'' is required to retrieve the values of the special parameters.

To create XML based on dynamic lists, you can use XSLT (Extensible Stylesheet Language Transformations).

The input data for the XSL transformation is the HTML export from ReportServer. Below is a simple generic transformation that converts the data into a simple XML format by first enumerating all attributes and then inserting a "record" element for each data record:

This example can be found here: https://github.com/infofabrik/reportserver-samples/blob/main/src/net/datenwerke/rs/samples/templates/xslt/.

The result could then look like this, for example: