ReportServer

ReportServer zhang Fri, 12/30/2016 - 17:26

ReportServer is a business intelligence platform that offers various reporting and visualisation tools. It comes both in a free Community edition and an Enterprise edition. More information can be found from their website. The tool most useful to MonetDB users is probably ReportServer's Dynamic List (its ad-hoc reporting component), which allows you to easily transform large MonetDB data tables into meaningful reports to be exported, for example, to Excel.

To set up ReportServer to talk to your MonetDB instance you will need to register the MonetDB JDBC driver. To register the JDBC driver with ReportServer you need to place the monetdb-jdbc-X.Y.jar (where X and Y are major and minor version numbers) into ReportServer's lib directory. If you installed ReportServer via one of the installers then the lib directory will be located in INSTALL_DIR/apps/reportserver/reportserver-conf/lib. If you did a manual installation then the lib directory will be /opt/reportserver/lib (on UNIX systems), or C:\Program Files\reportserver\lib on Windows. Once the driver is in the correct location, restart ReportServer.

In the following we quickly go through the basic steps to set up a Dynamic List on top of a MonetDB database table. Basically, you will need to do two things for this:

1) Create a datasource in ReportServer that points to your MonetDB installation
2) Create a Dynamic List based on that datasource.

 

To create a datasource for MonetDB go to the Administration Module and then to Datasources. In the datasource tree right click on a folder and select Insert > Relational Database. In the "Edit datasource" window, provide a Name for the datasource and select MonetDB as the Database (if the JDBC driver was not put into the right location, the MonetDB item will state "driver missing"). Username and Password are the username and password of the MonetDB database user with which you would like to connect to your database. Finally, the URL should be in the form jdbc:monetdb://HOST:PORT/DATABASE_NAME. For example, if your MonetDB instance is running on the same machine and on its default port, and your database is named voc, then the following URL could be used: jdbc:monetdb://localhost:50000/voc.

Save your changes by clicking the "Apply" button. To test whether the stored changes allow you to properly connect you can use the "Test Connection" button from the top toolbar (remember to always first save the changes before testing).

Now that we have a database, let us create a Dynamic List. For this we go to Administration > Reports and right click on a folder within the report tree to select Insert > Dynamic List. The basic configuration of a Dynamic List only needs a name, a database connection and a base query. That is, once you select your previously created MonetDB datasource, ReportServer will ask you to provide a query. This query forms the base of the Dynamic List. You can use any SQL statement that can be used within an inner statement. In case you simply want to base a report on a single data table, say, voc.passengers from the voc database, the query would be: SELECT * FROM passengers.

Once you've configured the Dynamic List you can start using it by simply double clicking the item in the tree. 

Here is an introduction to the Dynamic List. Documentation of ReportServer is available here.