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

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.

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.

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:

COLUMN_NAME	DEFAULT_ALIAS	DESCRIPTION
addressLine1		First line of the address
addressLine2		Second line of the address
amount		Amount of the payment effected
buyPrice		Purchase price
checkNumber		Payment reference number

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.:

COLUMN_NAME	DEFAULT_ALIAS	DESCRIPTION	DEFAULT_WIDTH
addressLine1		First line of the address
addressLine2		Second line of the address
amount		Amount of the payment effected
buyPrice		Purchase price
checkNumber		Payment reference number

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 shown below.

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:

etc/dynamiclists/htmlexport.cf	Configuration file customizing the HTML export
etc/dynamiclists/pdfexport.cf	Configuration file customizing the PDF export

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

title	The page title
head	Additional content to go in the HTML head element
script	Script elements
style	Custom CSS
pre	Custom HTML going before the table export
post	Custom HTML going after the exported table

For all templates (i.e., pre, post, etc.) you have access to a ReportServer expression language object (see Appendix A.) with some predefined replacements:

columncount	The number of columns
report	The current report
user	The current user
now	The current date as a formatted string
page	A localized text for page
of	A localized text for of
parameterMap	A map containing parameters
parameterMapSimple	A user-friendly text representation of the parameter map
parameterSet	The parameterSet object

One should be careful when working with parameters within the template, as not compiling templates may cause unexpected errors.