6.2. The Dynamic List

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 data source other than a relational database has been selected for your report, the data will be buffered in an internal database as described in section ""Data Sources"". 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 data source, 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 data source (see Section 4.3.1.), 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
6.2.1. Variants of the Dynamic List

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.

6.2.2. Output Formats of the Dynamic List

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.

6.2.3. Configuring the Dynamic List

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 data source and configure it (configuration depends on the data source type, refer to chapter 4.). Normally, you will use data from relational databases (you can use the demo data source 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 data source and only refer to other data source types if the configuration appears to be fundamentally different.

Based on the demo data source, 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 data source 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 data source configuration you can easily test the currently entered query.
6.2.4. The Metadata Data Source

The optional metadata data source serves to further define the report's base data fields. If there is no metadata data source given, the users will only see the technical database name each when selecting attributes/columns. By using the metadata data source it is possible to enter an additional plain text name and description per attribute. ReportServer expects the data source output to consist of three columns where the first one shows the technical column name, the second one gives the appurtenant plain text name and the third one the appurtenant description.

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

SELECT column_name, default_alias, description FROM METADATA

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
6.2.5. Customizing Dynamic Lists via Report Properties

There are several report properties (see Section 6.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.

For an overview over all report properties see Section 6.12.

6.2.6. Theming HTML and PDF Output

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>]]>      
      </pre>
      <post><![CDATA[</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
Using the Formula Language

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
parameterSet The parameterSet object

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