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
In this example the user has chosen the columns
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
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 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.
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:
|addressLine1||First line of the address|
|addressLine2||Second line of the address|
|amount||Amount of the payment effected|
|checkNumber||Payment reference number|
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.
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:
The configuration consists of six high level elements
|title||The page title|
|head||Additional content to go in the HTML head element|
|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|
|parameterSet||The parameterSet object|
One should be careful when working with parameters within the template, as not compiling templates may cause unexpected errors.