Chapter 6. Dynamic Lists

6. Dynamic Lists

ReportServer's dynamic list is the method of choice, if you need fast, user specific reports/analyses, or if the focus is on subsequent processing of the selected data.

Dynamic lists can handle almost any reporting requirement ranging from simple filter criteria, sorting, grouping and sub-totals to more advanced functionality like analytical functions, first order logic or preprocessing methods.

Once specified, you can store your report as a so called variant and share it with your coworkers via a TeamSpace.

In this chapter we discuss the basic functionality of dynamic lists.

If you double click on a dynamic list you open the report view, which allows you to configure the opened list. Every dynamic list is based upon a source data table provided by an administrator. This table is usually huge and can easily comprise several hundred columns and millions of entries. To mine information from this huge amount of data, is essential to be able to select appropriate subsets. To this end, ReportServer offers a variety of powerful yet intuitively operable tools.

6.1. Column Selection

To first step in every dynamic list is to select a set of columns. You'll find the column selection in the toolbar of the aspect list configuration. The opening dialog is based upon the common selection (see Chapter First steps) dialog and displays the existing columns on the left and the selected columns on the right.

The selected columns are loaded into the aspect list configuration where they can be further fine-tuned. For every selected column you have one entry in the grid displaying the following information:

Column The technical name of the column.
Default name A clear text name for the column if configured by the administrators.
Name Allows you to provide a custom name for the column.
Description A description if configured by the administrators.
Options Hints at various properties that you configured for the column such as filters or sort directions.
Aggregation Configure an aggregation function for this column (see Section 6.8.).
Sorting Allows to enable sorting the report by data from this column.
Hidden Allows to hide the column.
Type Contains the underlying data type.
6.2. Datatypes

Every column in a database has a fixed datatype that describes the form of data this column can hold. There are different datatypes, for example, for text, numbers or dates. Common datatypes are:

VARCHAR Text with a maximal length.
INTEGER Integer numbers.
DOUBLE/FLOAT Floatingpoint Numbers.
DECIMAL Decimal numbers.
CLOB/BLOB Arbitrary long text / binary data.
DATE Dates.

The order in which columns appear in your report is identical to the order of columns in the aspect list configuration. You can change the order by simply dragging and dropping columns to the desired positions or moving them via the context menu (right click).

To get a first impression of your current configuration change to the aspect Preview. The preview displays the first 50 records of your report. The toolbar at the bottom of the window contains information to the number selected entries and columns and allows you to pagethrough the entries of the list. You are further given the runtime of the report divided into the runtime on the server only and the time it took from the request until the data is displayed in your browser. A double click on an entry opens the corresponding record in a new window. Via the context menu (right click) you can access many of the functionality from the aspect list configuration directly from the preview page.

6.3. Alias, Sorting and Hidden Columns

To change a column's name go back to the list configuration and click on the cell in the name column corresponding to the desired column. If the administrator provided additional metadata for a column you'll find a default cleartext name as well as a description for each column. If you provide a custom name this name however takes precedence over the default.

The order of records in the final report can be changed by selecting an order for individual columns (under "order"). If an order is configured for more than one column, the order of the earlier column is obeyed first.

Under Hidden you can decide whether individual columns should not be part of the final report. This might be preferable in case the column should only be used for sorting or filtering of the data.

6.4. Filters

Filters allow to constrain the records contained in the final report. For this you can on each column create filters that explicitly include certain records or filters that exclude records. In this section we cover the basics of filtering with ReportServer. Advanced options such as wildcard filtering or filtering using a expression language are discussed in detail in later sections of this chapter. To specify a filter on a column, go to the aspect list configuration, select the column and chose the filter tool (from the toolbar) or simply double click the column.

You now have two basic approaches to describe the set of records that your report should contain:

  1. Selection of those records that should be in your final report (inclusion)
  2. Selecting those records that should be ignored (exclusion). In this case all records except for the specified records will be contained in the final report.

In case you define both, inclusion and exclusion filters then the exclusion does not any more apply to the entirety of the records but only upon the subset defined by the inclusion filters.

This means, for example, that if you have included and excluded the same value, then the corresponding record is not part of the result set.

The filter dialog allows you to define inclusion as well as exclusion filters either on the basis of individual values or by defining value ranges. For this use the respective tabs.

The tabs are structured similarly and they resemble the common selection dialog. On the left you find the values of the current column filtered according to the entirety of records that are configured via different filters (filters on other columns as well as filters on the current column).

Selection works analogous to the selection dialog either by double click or drag-and-drop. To define a range of values, you need to provide two consecutive values thus defining the set (from A to B).

Alternatively to selecting the values from the list of available values you can switch the view to a text input field instead which allows you to simply type in the desired values or to use the clipboard (i.e., copy and paste). In case you define ranges you need to separate the two values by a minus ("A - B"). Do note the spaces to the left and right of the minus. By not specifying one of the bounds you create open intervals. For example the interval "all values greater or equal to 5" would be written as "5 - ".

To load all existing values of the current column without taking existing filters into account into the selection panel on the left click on the chain symbol (enforce consistency) next to the search field. (Usually you will not need to use this functionality.)

By default filters are case sensitive, that is values in the records have to exactly match the filter expression. In case you want to ignore upper and lower case, disable the option observe case sensitive. Note that this may negatively impact the performance of the resulting query.

6.4.1. Empty Cells (NULL)

A peculiarity of databases that you should be aware of when defining filters is their handling of empty cells. By empty cell we understand a cell that does not contain any value (in database speak this is called a NULL value). NULL is, in particular, different from the empty string "" or the number 0.

Since a NULL is different from any other value, you'll automatically exclude NULL values by defining any filter on that column. This might be counter intuitive in case there is only an exclusion filter defined, yet is the default handling in the relational algebra which provides the basis for any modern database system.

Consequently, if you defined a filter on a column but you do not want to remove empty cells you explicitly have to include them. Conversely, you only have to explicitly exclude empty cells if you do not have any other filters on a column. To configure the handling of empty cells use the respective drop-down box in the filter dialog.

6.4.2. Filtering Floating Point Numbers (Float/Double)

Note that by definition, testing floating point numbers for equality is problematic. Thus, in case you want to filter on a column of type float or double you should instead define a filter range. For example, instead to filter the value 5.1, use instead a range filter of 5.0009 - 5.1001.

6.4.3. Distinct: Remove Duplicates

Via the option Remove Duplicates in the aspect list configuration you can suppress the inclusion of duplicate records in the result set.

6.5. Format

In ReportServer you can define the output format for each column individually in the aspec list configuration. For this select a column and click on the button format in the toolbar or choose format from the context menu. This opens the format dialog which slightly differs depending on the underlying datatype.

The following formats are available:

Number The value is interpreted as a number and displayed with the configured number of decimal places. Optionally you can enable a thousands separator.
Percent The value is interpreted as a percent.
Scientific Numbers are written in scientific notation.
Currency Values are interpreted as numbers and a currency symbol is appended.
Date The value is interpreted as a date and output in the specified target format. In case the underlying datatype is not a date type, you have to additionally specify the source format. In this case also need to specify how to treat erroneous dates. Via the options clean erroneous dates and replace erroneous dates you can configure how this is done. If clean erroneous dates is enabled, then a date which is in the correct format but not a valid date (e.g., 35/03/2012) is turned into the most probable correct date (in this case 04/04/2012). For values that are not in the correct format (e.g., "last tuesday") the second option allows you to define a replacement. Here you can also provide an expression using the ${}-expression language with the replacement value for the existing value. See Appendix Expression language.
Text Interprets the value as text - In this way, for example, preceding zeros are kept.
Template Allows to use an ${}-expression language expression. See Appendix Expression language.

Note that in case the report is exported to Excel the formats are configured directly in Excel.

6.6. Report Documentation

Via the button documentation you can access the documentation for the current report. This gives you an overview of the entire configuration of your report. Note, however, that the documentation is always based upon the latest stored version of your variant. For more information on the report documentation, see Appendix A.

6.7. Filtering with Wildcards (*, ?)

Besides specifying ranges in the filter dialog you can also specify multiple values using wildcards. The wildcard * matches any arbitrary sequence of characters. This includes the empty sequence. For example: "fish*" matches the value "fishing" as well as the value "fish". In contrast, the wildcard ? matches exactly one arbitrary character.

Wildcards can be used in simple inclusion and exclusion filters as well as in range filters. Furthermore, you can use wildcards also in the search mask of the filter dialog.

Note that when using wildcards in filter ranges then ReportServer tries to select the largest possible range. That is, in case the expression matches multiple values ReportServer selects the "lowest" value as the lower limit of the interval and the "biggest" as the upper limit. Further note, that if for one of the boundaries no value matches then the expression describes an empty interval. This may lead to unexpected results when defining an inclusion range: for example, "a* - z*" is empty, if no value exists that starts with a "z".

6.8. Aggregation

Aggregation describes the process of summarizing or compacting of data that is equivalent according to some grouping property. For every group present in the underlying data the result set will contain one data record. Let us exemplify this by a list of persons with the features sex and age. A possible aggregation is the average age by gender. In this case the list with n data records will be aggregated to a result with one record per gender.

Thus, when using aggregation, we need to distinguish between attributes that describe to which group a record belongs (gender, in the example) and those that are to be summarized to a single value using an aggregation function.

ReportServer provides the following aggregation function

  • Average: Computes the average value per group
  • Count: Counts the number of records per group
  • Maximum: Outputs the maximum value per group
  • Minimum: Outputs the minimum value per group
  • Sum: Computes the sum of all values per group
  • Variance: Computes the statistical variance
  • Count Distinct: Counts the records per group, however, only considers distinct values

If an aggregation function is selected for one of the columns, then all other columns that do not have an aggregation are considered as grouping columns. It is not possible that a report contains columns that are neither grouping columns nor aggregated columns. To define an aggregation for a column go to list configuration and chose an aggregation function from the list clicking in the appropriate cell in the grid.

6.8.1. Subtotals

If you enable aggregation then only a single entry per group will be present in the resulting report. In case you also wish to output the underlying entries in every group you can use the function "subtotals" from the toolbar of the list configuration. In the dialog you can specify the columns that are to be used for grouping. All columns that are not part of this list and that do not have an aggregation function will be considered for displaying individual group records.

6.8.2. Filtering and Aggregation

All filters in the aspect list configuration act on the visible end result. When filtering on an aggregated column this means that the aggregation comes first and the filter acts on the result of the aggregation. If we placed a filter on the aggregated age column in our example this would allow to filter the average age, not the values that go into the aggregation. Thus the filter "30 - " does not change the aggregated value of any of the two groups but suppresses any group in the result set that has a value less than 30.

If you are familiar with the database language SQL, then filters on aggregated columns correspond to HAVING filters in SQL.

To deviate from the described behavior and to filter the values going into the aggregation instead of filtering the result of the aggregation you can use so called pre-filters which are described in the next section.

6.8.3. Pre-Filter

Pre-filters are a powerful tool to select the data going into your report. Their range of application goes far beyond that of the simple filters available in the aspect list configuration. The three main differences are:

Combining filters with ANDs and ORs

Filters in the aspect list configuration are always working in conjunction (AND), that is, a record is in the result set if and only if all filter conditions are fulfilled. For pre-filters, on the other hand, you can create arbitrary combinations of conjunctions and disjunctions (ANDs and ORs).

Comparing values between columns

A column comparison allows to create filter conditions that are based on a relation between two attributes of a data record. An example could be all records where the value in column A is different from the value in column B.

Filtering values going into an aggregation

As described above, filters in the aspect list configuration always act on the visible result which means, in case of an aggregation, that the filter acts on the result of the aggregation. In contrast, pre-filters are always evaluated before any aggregation thus allowing to filter values going into the aggregation. In our example this allows you to, for example, compute the average age of all persons older than 21. Note that if no aggregation is selected there is no difference in this respect.

6.8.4. Composition of pre-filter expressions

The pre-filter contains a set of filter expressions that are arbitrarily nested and combined with ANDs and ORs.

Let us consider an example with four filter expressions (note that the last filter is a column comparison):

	A := Age > 30
	B := sex = female
	C := place of residence = New York
	D := Age < 5
	E := place of birth = place of residence

The expression

(B AND C AND (A OR D OR E))
describes the set of all females living in New York that are either above 30, less than 5 years old or that have also been born in New York.

ReportServer visualizes this expression as a so called tree:

AND B
C
OR A
D
E

The AND and OR nodes are called blocks that enclose the nodes lying below. In the example, the first AND-block thus contains nodes B and C as well as the OR-block. The OR-block, in turn, contains blocks A, D, and E.

To evaluate the tree, that is, compute the truth value for a particular data record, ReportServer first evaluates the truth values for the individual filter expressions. Each evaluation yields the value TRUE or FALSE.

Example record:

	Age = 25
	Sex = female
	place of residence = New York
	place of birth = Boston

this yields the following tree

AND TRUE
TRUE
OR FALSE
FALSE
FALSE

In a next step ReportServer computes the values of blocks. For this, ReportServer recursively identifies blocks that do not contain any further blocks and computes the logical operation (AND or OR) on its values.

An AND-block is thus evaluated to TRUE if and only if all enclosed values are TRUE. An OR-block, on the other hand, is TRUE if at least one of its values is TRUE.

When a block has been evaluated it is replaced by the corresponding value. This process is repeated until the root block has been evaluated. In our example this is done in two steps:

AND TRUE
TRUE
FALSE
FALSE

Thus, the example record is not part of result set.

You can add filters and blocks to the pre-filter via the toolbar. These are always inserted beneath the currently selected block (or the root block if no block is selected). You can move nodes by simply dragging them to their new position. Note that the order of filters within a block is irrelevant to the result. Further a block beneath an AND-block is automatically converted into an OR block and vice versa, a block beneath an OR-block is converted into an OR block.

Via "And/Or toggle" you can exchange all AND blocks for OR blocks and vice versa.

6.9. Computations in filters

The ${}-expression language can also be used in filter expressions. Instead of a value you can provide an expression using the text mode. In addition to the default replacements (see Appendix B.) the following objects are available:

today A calendar object that allows to write expression based on the current date. This can be used to, for example, filter all invoices not older than 7 days.
agg Provides to access computations based on all values of the current column. This can, for example, be used for outlier detection..
analytical Provides access to analytical functions. For example you can filter the top 10 percent of a column..
6.9.1. Using the today object

The "today"-object is a calendar object with which you can specify dates relative to the current date, that is, the object is initialized with the current date and time. With the help of the following functions you can manipulate the date (resp., time)

firstDay Sets the calendar object to midnight at the first day of the current month
lastDay Sets the object to the last second of the last day of the current month
addDays Adds the number of days given as argument to the current date (the argument can be negative to subtract days)
addMonths Adds the specified number of months to the current date (the argument can be negative to subtract days)
addYears Adds the specified number of years to the current date (the argument can be negative to subtract days)
setDay Sets the calendar to a specific day
setMonth Sets the calendar to a specific month
setYear Sets the calendar to a specific year
clearTime Clears the time field, that is, the time is set to midnight
addHours Adds the specified number of hours to the current date (the argument can be negative to subtract days)
addMinutes Adds the specified number of minutes to the current date (the argument can be negative to subtract days)
addSeconds Adds the specified number of seconds to the current date (the argument can be negative to subtract days)
setHours Sets the hour field
setMinutes Sets the minutes field
setSeconds Sets the seconds field
format This function formats the date according to a specified mask. This may be necessary if the underlying datatype is not a date type but a text type (see Appendix C. for further information).
Example

You want to filter all invoices of the last month. This can be done using the following inclusion range:

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

In case the column is of type VARCHAR (i.e., a text column) and the data, for example, formatted as day/month/year you have to specify the format using the format function:

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

More information on using the date format is available at http://docs.oracle.com/javase/6/docs/api/java/text/SimpleDateFormat.html.
6.9.2. Using the agg object

The "agg"-object provides access to aggregation functions working on the data of the entire column. This allows you to define filter expressions that are, for example, based on the average value of the column.

The following functions are available:

  • avg: Computes the average value.
  • count: Counts the number of values.
  • countDistinct: Counts the number of distinct (i.e., different) values of the the current attribute.
  • sum: Sums up the values of the column.
  • variance: Computes the variance of the column.
  • max: Computes the maximum.
  • min: Computes the minimum.
The formula ${agg.avg()-10} - ${agg.avg()+10} defines an interval of width 20 around the average value of the column. This expression can, for example, be used as a range filter to find values that deviate too much from the average value.
There cannot be any spaces between a minus (subtraction) and the functions if the formula is used in a range filter as ReportServer would then recognize the minus as the range delimiter.
To compute the aggregate functions ReportServer needs to scan the entire table. In case the report is build on a large table this may take a while.
6.9.3. Using the analytical object

The analytical-object, similarly to the agg-object, provides access to functions that compute their result on all values of a given attribute. In contrast to the agg-object which always returns a single value, the analytical object computes a set of values. Thus the analytical-object can only be used in simple inclusion and exclusion filter expressions; not in range expressions.

The following functions are made available by the analytical object:

  • top(n): Selects the top n values
  • bottom(n): Selects the smallest n values
  • topGrouped (n,'column name'): Selects the top n values of this column grouped by column "column name"
  • bottomGrouped (n,'column name'): Selects the smallest n values of this column grouped by column "column name"

If n is an integer number then the n is interpreted as the number of values to return. If n is a decimal number between 0 and 1 then it will be interpreted as percent. Thus top(0.1) matches the top ten percent.

For the "grouped"-functions you must additionally supply the name of a column. The values will first be grouped according to this column and the top (resp., bottom) values will then be computed per group.

The formula ${analytical.topGrouped(5,'CUS_COUNTRY')} defined on an attribute containing sales values will filter the top 5 sales per country (if CUS_COUNTRY is the name of an attribute containing the country of a customer). Thus, the resulting report contains all customers that are within the top 5 of their country.
Similarly to the agg-functions ReportServer needs to scan the entire table when computing any of the analytical functions. In case the report is build on a large table this may take a while.
6.10. Computed Columns

With computed columns you can enhance your report by creating new attributes that are not already present in the base data, but that can be somehow computed. The computation rule is defined directly in SQL which allows for very fast processing directly on the database. This, on the other hand, means that depending on the database used (please ask your administrators for more information) available expressions and functionality can slightly differ. In this section we cover only a very basic subset of the possibilities. For further information please refer to any good introduction to SQL.

If you add a computed column (aspect computed columns, add attribute) you can henceforth use it as any other column in your report. This means that you also need to add the computed column to the list of selected columns (aspect list configuration) if it should be included in the result.

When defining computed columns you can base your computation only on attributes available in the reports base data, that is, computed columns cannot depend on one another.

To configure computed columns go to the aspect computed columns. Here you can add new attributes and change existing ones. Note that the name of a computed column needs to be alphanumeric without spaces and unique.

SQL provides a huge number of possibilities to define a computed column and describing only a small subset of these would go beyond the scope of this manual. However, we want to give at least a small number of code examples for often occurring use cases.

We base the following examples on a table with four columns:

A, B: Columns containing text

C, D: Columns containing integers

For simply calculations you can use the basic arithmetic operations, such as, C + D to define the sum of two attributes. To concatenate two text attributes you can (with most databases) use the ||-operator. In some cases also the +-operator or the function call CONCAT(A,B) can be used.

With CASE-expressions you can define conditionals. For example, this allows you to classify values according to their value.

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

Please note that the first matching expression wins and that all possible return values need to be of the same data type.

6.11. Templates

Besides the standard export formats like EXCEL or CSV, dynamic lists allow to export the result data directly into a preprepared template. In this section we will cover the basic templating functionality.

In the aspect list configuration you can access the functionality via the button edit template. The opened dialog displays the currently available templates on the left and details to the currently selected template on the right. Via the toolbar you can add new templates, download the currently selected template or remove templates.

A template consists of a name, a description and a type. Currently, the following template types are available:

JXLS Allows the definition of templates using Microsoft Excel.
XDoc Allows the definition of templates using Microsoft Word.
Velocity Allows the definition of plain text-templates.
XSLT Allows the definition of XML-templates.

If you added a new template you need to upload the corresponding file. In the following paragraphs we provide a small example for each template type. A complete documentation is out of the scope of this manual. More information can be found on the net:

JXLS http://jxls.sourceforge.net, http://jxls.sourceforge.net/1.x/
XDocReport https://github.com/opensagres/xdocreport
Velocity http://velocity.apache.org/
XSLT http://www.w3.org/TR/xslt
6.12. Excel Templates with JXLS

ReportServer integrates the JXLS template engine which allows you to prepare sophisticated templates directly in Microsoft Excel and filling them directly from your reporting platform. Via specialized directives you manage how and where data is to be inserted. A detailed description of the possibilities offered by JXLS is beyond the scope of this document, thus we will only quickly go over the basics. Both JXLS2 and the legacy JXLS1 are currently supported in ReportServer. These are described in the following.

6.12.1. Excel Templates with JXLS2

A complete JXLS2 documentation is given on the jXLS-project page at http://jxls.sourceforge.net. Here, we will show an overview of the most important components for working with ReportServer.

Note that you can export dynamic lists into JXLS templates from 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.

In JXLS2 (http://jxls.sourceforge.net/index.html), you define the JXLS2 commands via Excel comments. The JXLS2 engine parses these comments and transforms the template accordingly. Here, we will show a basic example of a template and briefly explain its components. For more details, please refer to the Administration Guide (JXLS Reports) and to the JXLS2 documentation.

Consider the following example The cell A1 contains an Excel comment with the following text: jx:area(lastCell="B1"). It defines the boundaries of our template to be the range A1:B1.

It also contains a Jxls Each-Command with the following text: jx:each(items="data" var="customer" lastCell="B1"). The Each-Command will iterate the collection of objects in the ''data''-collection and print the corresponding information. The body area of the Each-Command is A1:B1 (defined by the lastCell attribute), which means the cells will be cloned and processed with each new Customer object in the context.

Note that the ''data'' variable contains the data selected by the dynamic list. This variable is set automatically 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}

Note that you have to enter the fields in lower case so that these are correctly mapped by the JXLS engine.

6.12.2. Excel Templates with JXLS1

A complete JXLS1 documentation is given on the JXLS1-project page at http://jxls.sourceforge.net/1.x/reference/tags.html. Here, we will show an overview of the most important components for working with ReportServer.

Note that you can export dynamic lists into JXLS templates from 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.

Templates are formed using two basic constructs: tags and replacements. A so called tag surrounds an area and defines how the cells within are to be interpreted:

	<jx:forEach items="${data}" var="department">
		${department.name} | ${department.chief}
	</jx:forEach>
In the example each line of the example becomes one line in the Excel document and the pipe symbol (|) marks the end of cell, that is the second line consists of two cells. It is also important to note that commands should start at the very beginning of a cell and that no leading whitespace may exist.

In the example the tag <jx:forEach ...> marks the beginning of a block. The block is closed by the corresponding end tag </jx:forEach>. All cells within the block are repeated for every record in the data (the records are given to the template-engine via the replacement "data"). To access the individual attributes within the "forEach-block" the variable defined in the "var" attribute can be used: in the example "department". To access an individual attribute use $ where "department" is the variable bound to the current datarow and "colname" is the name of the attribute (i.e., name of the corresponding colum). Note that variable names are case sensitive.

The following example is a template which uses multiple nested groups. The attribute groupBy in the forEach-tag defines the grouping attribute. The elements within a forEachblock can be accessed via the variable "group.items". Using an extra forEach-block allows processing all elements of a group. Finally, the outline-tag defines a retractable area.

6.13. Text Templates with Velocity

Velocity https://velocity.apache.org/ is a templating language for plain text documents. This, however, allows you to generate any sort of output format based on ASCII-text. To access data from the report you again use the replacement object "data".

The following example outputs a list of customers, assuming the report contains a column "CUS_CUSTOMER_NAME".

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

You can also download this template from our reportserver-samples GitHub: https://github.com/infofabrik/reportserver-samples

6.14. Word Templates with XDocReport

XDocReport https://github.com/opensagres/xdocreport brings the velocity templating language to Microsoft Word. In order to use templating constructions such as

	#foreach ( $customer in $data )
you need to wrap them into so called MergeFields. For this, choose "Insert/Quick Parts/Field..." and then choose the category MergeField. You'll specify the command in the the input field below the field functions. The above velocity example would thus look like
All customers:
MERGEFIELD[#foreach($customer in $data)]
MERGEFIELD[$customer.CUS_CUSTOMERNAME]
MERGEFIELD[#end]

where MERGEFIELD[XX] denotes a MergeField for command XX.

In the following screenshot you can see the resulting template in Word. You can also download this template from our reportserver-samples GitHub: https://github.com/infofabrik/reportserver-samples

6.15. XML Templates with XSLT

The data going into the XSL-transformation is ReportServer's HTML export. The following code fragment shows a sample transformation which outputs the data in a simple XML format which contains an overview of all attributes of the table and a single "record"-element per data record in the result.

	<?xml version="1.0"?>
	<xsl:stylesheet version="1.0"
	xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
	xmlns:xhtml="http://www.w3.org/1999/xhtml">
	<xsl:template match="/">
		<myXmlFormat>
			<xsl:apply-templates select="//xhtml:tr"/>
		</myXmlFormat>
	</xsl:template>
	<xsl:template match="xhtml:tr[position() = 1]">
		<attributes>
		<xsl:apply-templates mode="attributes" />
		</attributes>
	</xsl:template>
	<xsl:template match="xhtml:th" mode="attributes">
		<attribute>
			<xsl:value-of select="."/>
		</attribute>
	</xsl:template>
	<xsl:template match="xhtml:tr[position() > 1]">
		<record>
			<xsl:apply-templates mode="values"/>
		</record>
	</xsl:template>
	<xsl:template match="xhtml:td" mode="values">
		<value>
			<xsl:value-of select="."/>
		</value>
	</xsl:template>
	</xsl:stylesheet>

The result could then look like this:

	<?xml version="1.0" encoding="UTF-8"?>
	<myXmlFormat xmlns:xhtml="http://www.w3.org/1999/xhtml">
		<attributes>
			<attribute>CUS_CUSTOMERNAME</attribute>
			<attribute>OR_ORDERDATE</attribute>
			<attribute>OD_ORDERLINENUMBER</attribute>
			<attribute>OD_ORDERNUMBER</attribute>
			<attribute>OD_PRICEEACH</attribute>
			<attribute>OD_PRODUCTCODE</attribute>
			<attribute>OD_QUANTITYORDERED</attribute>
			<attribute>OR_CUSTOMERNUMBER</attribute>
			<attribute>OR_ORDERNUMBER</attribute>
			<attribute>OR_STATUS</attribute>
			<attribute>PL_PRODUCTLINE</attribute>
			<attribute>PRO_PRODUCTCODE</attribute>
			<attribute>PRO_PRODUCTNAME</attribute>
			<attribute>CUS_CUSTOMERNUMBER</attribute>
			<attribute>EMP_LASTNAME</attribute>
			<attribute>OFF_CITY</attribute>
		</attributes>
		<record>
			<value>Australian Collectables, Ltd</value>
			<value>2010-09-27 21:18:00.0</value>
			<value>1</value>
			<value>10193</value>
			<value>87.13</value>
			<value>S18_2949</value>
			<value>28</value>
			<value>471</value>
			<value>10193</value>
			<value>Shipped</value>
			<value>Vintage Cars</value>
			<value>S18_2949</value>
			<value>1913 Ford Model T Speedster</value>
			<value>471</value>
			<value>Fixter</value>
			<value>Sydney</value>
		</record>
		<record>
			<value>Australian Collectables, Ltd</value>
			<value>2010-09-27 21:18:00.0</value>
			<value>2</value>
			<value>10193</value>
			<value>97.39</value>
			<value>S18_3136</value>
			<value>23</value>
			<value>471</value>
			<value>10193</value>
			<value>Shipped</value>
			<value>Vintage Cars</value>
			<value>S18_3136</value>
			<value>18th Century Vintage Horse Carriage</value>
			<value>471</value>
			<value>Fixter</value>
			<value>Sydney</value>
		</record>
	</myXmlFormat>