Switching your ReportServer archive tables off

ReportServer uses Hibernate Envers (https://hibernate.org/orm/envers/) as an archiving / versioning solution for entity classes. This might result in an unexpected and unwanted growth of size of the ReportServer repository database.

A new revision is created on every single entity change, so the archive tables grow with each entity modification. Thus, these tables get larger and larger, making your DB occupy a large amount of space after some time. If you don’t need the archive tables, you can easily turn this behaviour off.

For each table in the ReportServer repository there is a shadow table which has the same as the original tables plus the suffix _A. All entity versions can be found in the these tables. The “_A” suffix stands for “archive” or “audit”. So, e.g., your User’s revisions are found in the RS_USER_A archive table, since the respective actual entity versions are located in the RS_USER table.

To stop this behaviour in your ReportServer installation, open your persistence.xml and locate the section containing the string <!– Envers –>.
Then add the following:

<property name="hibernate.integration.envers.enabled" value="false"/>

The result would be similar to:

<!-- Envers -->
<property name="org.hibernate.envers.audit_table_suffix" value="_A"/>
<property name="org.hibernate.envers.audit_table_prefix" value=""/>
<property name="hibernate.integration.envers.enabled" value="false"/>

After a ReportServer restart, you can make sure that the archive tables are switched off by opening a ReportServer terminal session and typing a rev command for a given entity, as in this example:

Making sure the archive tables are switched off

If you get the message “Service is not yet initialized”, the archive tables are correctly turned off.

Once your archive tables are switched off, you can modify your “_A” tables: you can either leave them as they are or delete the entries.

Attention:
Once the archive tables are switched off, they cannot be switched on again unproblematically.
Pls. do not confuse the archive tables with the audit log ReportServer maintains in the RS_AUDIT_* tables. These are not impacted by the operations described above.

ReportServer 3.1.1

The 3.1.1 version is now available for all users.
In the following some important features in this version:

Allow to change lost password email texts via config file

ReportServer 3.1.1 allows you to edit the text a user receives per email when the user’s password is lost. The following is a configuration file for this purpose (/etc/security/lostpassword.cf)

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
      <lostpassword>
         <email>
            <subject>Password lost</subject>
            <text>Dear user ${user.getFirstname()} ${user.getLastname()},

A password request was issued for:

   User: ${user.getUsername()}
   Password: ${password}
	    </text>
         </email>
      </lostpassword>
</configuration>

Add “config echo” command for printing out individual current configuration parameters

In order to read the current active value of a configuration parameter, you can use “config echo”, e.g. for reading the default charset in the main.cf configuration file:

config echo main/main.cf default.charset

would return you e.g. “UTF-8”. For reading an attribute in the form:

<mailaction html="false">

you can write:

config echo scheduler/scheduler.cf scheduler.mailaction[@html]

More details on the syntax can be found in the Apache Commons Configuration documentation

Allow to configure the width of the columns in the dynamic list preview

You can now set the column widths of your dynamic lists via dynamic list configuration, refer to the next screenshot for an example.

You can also set defaults for all dynamic lists in the “/etc/ui/previews.cf” configuration file (defaultColumnWidth, maxColumnWidth):

<?xml version="1.0" encoding="UTF-8"?>
<!--
 ReportServer Configuration File
 filename: ui/previews.cf

 Configures how previews are rendered
-->
<configuration>
   <pdf>
      <mode>native</mode>
   </pdf>
   <dynamicList>
      <defaultColumnWidth>200</defaultColumnWidth>
      <maxColumnWidth>800</maxColumnWidth>
   </dynamicList>
</configuration>

Allow to configure if user account existence should be shown in the lost password dialog

You can now configure if the lost password dialog should reveal if the given username is existent or if no information should be disclosed. Per default, no information is disclosed. The configuration setting (indicateWrongUsername) is found in the /etc/security/lostpassword.cf file:

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
      <lostpassword indicateWrongUsername="false">
         <email>
            <subject>${msgs['net.datenwerke.rs.passwordpolicy.service.locale.PasswordPolicyMessages']['lostPasswordSubject']}</subject>
            <text>${msgs['net.datenwerke.rs.passwordpolicy.service.locale.PasswordPolicyMessages']['lostPasswordSalutation']} ${user.getFirstname()} ${user.getLastname()},

${msgs['net.datenwerke.rs.passwordpolicy.service.locale.PasswordPolicyMessages']['lostPasswordIntro']}

   ${msgs['net.datenwerke.rs.passwordpolicy.service.locale.PasswordPolicyMessages']['lostPasswordUsername']}: ${user.getUsername()}
   ${msgs['net.datenwerke.rs.passwordpolicy.service.locale.PasswordPolicyMessages']['lostPasswordPassword']}: ${password}

${msgs['net.datenwerke.rs.passwordpolicy.service.locale.PasswordPolicyMessages']['lostPasswordEnd']}

	    </text>
         </email>
      </lostpassword>
</configuration>

Library Deletions and Upgrades

In ReportServer 3.1.1 we deleted 52 libraries and upgraded 8. This removes many external dependencies in ReportServer.

For a list of all changes please refer to the release notes. The upgrade guide is available in the documentation area.
Happy reporting!

ReportServer 3.1.0 is now available

We are pleased to announce that the 3.1.0 version of ReportServer is now available for download. In the following some important features in this version:

JXLS2 Support

ReportServer 3.1.0 supports JXLS2 reports and templates together with the legacy JXLS1. This means that you can decide which version you want to use. You can even use both versions together. This flexibility should help you with your JXLS migration: you can leave all your existing reports in the legacy JXLS1 version, while you create new reports with the JXLS2 version.

As you can see in the following screenshot, ReportServer 3.1.0 uses by default JXLS2. You can use JXLS1 by ticking the option “Use Legacy JXLS 1”.

The same holds for JXLS1 and JXLS2 templates, as you can see in the following screenshot:

Amazon Redshift Support

Amazon Redshift is a fast, fully managed data warehouse that makes it simple and cost-effective to analyze all your data. It allows you to run complex analytic queries against petabytes of structured data.

ReportServer supports Amazon Redshift by its official JDBC driver As stated in the JDBC documentation, you have to obtain your JDBC URL from your AWS. An example URL is

jdbc:redshift://examplecluster.abc123xyz789.us-west-2.redshift.amazonaws.com:5439/dev

Google BigQuery Support

Google BigQuery is a serverless, highly-scalable, and cost-effective cloud data warehouse with an in-memory BI Engine and machine learning built in.

ReportServer 3.1.0 supports Google BigQuery datasources by the official Simba JDBC driver. Your URL should look similar to this:

jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=MyBigQueryProject;OAuthType=0;OAuthServiceAcctEmail=bqtest1@data-driver- testing.iam.gserviceaccount.com;OAuthPvtKeyPath=/SecureFiles/ServiceKeyFile.json;

Teradata Support

Teradata is a fully scalable relational database management system produced by Teradata Corp. It is widely used to manage large data warehousing operations.

ReportServer 3.1.0 supports Teradata datasources by the official Teradata JDBC driver. Your URL should look similar to this:

jdbc:teradata://IP/DATABASE=myDatabase

Library Upgrades

The following libraries are upgraded in ReportServer 3.1.0:

Apache POI
upgraded to 4.1.0
JXLS2
upgraded to 2.6.0
BIRT
upgraded to 4.8.0
Jasper
upgraded to 6.9.0
UserAgentUtils
upgraded to 1.21

Exporting Parameters

The parameter export of dynamic lists was largely improved in ReportServer 3.1.0. For this, you can now set the following report properties:

  • output_parameters
  • output_include_hidden_parameters
  • output_complete_configuration

Please note that these report properties are only available in Enterprise Edition.

If only “output_parameters” is 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, 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 the following, “output_parameters” was set to true. In the first screenshot, you can see the Excel Export result. A new sheet “Parameters” is created and a list of parameters with their respective values is printed out:

For the HTML and PDF exports, this must be further configured in etc/dynamiclists/htmlexport.cf and pdfexport.cf, respectively, by using the variable ${parameterMapSimple}. As an example, refer to the following screenshot (pdfexport.cf):

You can see the result in the following screenshot. The parameters are printed in the specified position together with the dynamic list data in the exported PDF. The analogous configuration can be made for the HTML export.

For a list of all changes please refer to the release notes. The upgrade guide is available in the documentation area.

Happy reporting!

ReportServer 3.1.0 BETA release

Dear ReportServer users,

we are happy to release the BETA Version of ReportServer 3.1.0. You can download it from here:  http://www2.datenwerke.net/rsupdate/RS3 … ver-ee.zip

We made numerous improvements / changes. The complete list can be found here: https://reportserver.net/releasenotes/RS3.1.0.html

The most important changes that are relevant for testing are:

– RS-3576    New Feature     – Upgrade to POI 4.1.0Upgrade to POI 4.1.0 -> Test if the Excel Export of your Reports works fine
– RS-3579    New Feature     – Support JXLS2 reports -> Test if your JXLS 1 reports / templates for the dynamic list work fine
– RS-3588    New Feature     – Upgrade to BIRT 4.8.0 -> Test if your BIRT reports work fine.
– RS-3592    New Feature –  Upgrade to Jasper 6.9.0 -> Test if your JASPER reports work fine.
– Improvement    Combine offset and order queries in MySQL / MsSQL / PostgreSQL / Oracle dynamic list reports -> Test your dynamic lists

Please let us know if you have any problems with the BETA version, especially if some report was working in 3.0.8 but is not working correctly in 3.1.0-BETA. Of course, as this is a BETA version, it should not be installed in production environments. Always backup your data completely before installing the BETA release.

For testing, just unzip it into the webapps directory of your Tomcat installation. As always, you need to set the persistence.properties file and run the corresponding DDLs. As always, please restart your tomcat and delete your browser’s cache including temp files.

If you are upgrading from a previous installation, you first need ReportServer 3.0.8 correctly installed. Then, just run manually the following SQL script:
After running the script, please restart your tomcat and delete your browser’s cache including temp files.

MySQL 5:

ALTER TABLE RS_JXLS_REPORT ADD jxls_one BIT(1);
ALTER TABLE RS_JXLS_REPORT_A ADD jxls_one BIT(1);

UPDATE RS_JXLS_REPORT SET jxls_one = 1;

ALTER TABLE RS_JXLS_REPORT MODIFY jxls_one BIT(1) NOT NULL;

INSERT INTO RS_SCHEMAINFO(KEY_FIELD, value) VALUES('updated', date_format(now(), '%Y-%m-%d %T'));
INSERT INTO RS_SCHEMAINFO(KEY_FIELD, value) VALUES('script', 'RS3.0-14-MySQL5_UPDATE');
INSERT INTO RS_SCHEMAINFO(KEY_FIELD, value) VALUES('schemaversion', 'RS3.0-14');

Oracle:

ALTER TABLE RS_JXLS_REPORT ADD jxls_one number(1,0);
ALTER TABLE RS_JXLS_REPORT_A ADD jxls_one number(1,0);

UPDATE RS_JXLS_REPORT SET jxls_one = 1;

ALTER TABLE RS_JXLS_REPORT MODIFY jxls_one number(1,0) NOT NULL;

INSERT INTO RS_SCHEMAINFO(ENTITY_ID, KEY_FIELD, value) VALUES((select max(i.ENTITY_ID)+1 ENTITY_ID from (select ENTITY_ID from RS_SCHEMAINFO union select 1 ENTITY_ID from dual)i), 'updated', to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO RS_SCHEMAINFO(ENTITY_ID, KEY_FIELD, value) VALUES((select max(i.ENTITY_ID)+1 ENTITY_ID from (select ENTITY_ID from RS_SCHEMAINFO union select 1 ENTITY_ID from dual)i), 'script', 'RS3.0-14-Oracle_UPDATE');
INSERT INTO RS_SCHEMAINFO(ENTITY_ID, KEY_FIELD, value) VALUES((select max(i.ENTITY_ID)+1 ENTITY_ID from (select ENTITY_ID from RS_SCHEMAINFO union select 1 ENTITY_ID from dual)i), 'schemaversion', 'RS3.0-14');

PostgreSQL:

ALTER TABLE RS_JXLS_REPORT ADD COLUMN jxls_one boolean;
ALTER TABLE RS_JXLS_REPORT_A ADD COLUMN jxls_one boolean;

UPDATE RS_JXLS_REPORT SET jxls_one = true;

ALTER TABLE RS_JXLS_REPORT ALTER COLUMN jxls_one SET NOT NULL;

INSERT INTO RS_SCHEMAINFO(KEY_FIELD, value) VALUES('updated', to_char(now(), 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO RS_SCHEMAINFO(KEY_FIELD, value) VALUES('script', 'RS3.0-14-PostgreSQL_UPDATE');
INSERT INTO RS_SCHEMAINFO(KEY_FIELD, value) VALUES('schemaversion', 'RS3.0-14');

SQL Server:

ALTER TABLE RS_JXLS_REPORT ADD jxls_one bit;
ALTER TABLE RS_JXLS_REPORT_A ADD jxls_one bit;

UPDATE RS_JXLS_REPORT SET jxls_one = 1;

ALTER TABLE RS_JXLS_REPORT ALTER COLUMN jxls_one bit not null;

INSERT INTO RS_SCHEMAINFO(KEY_FIELD, value) VALUES('updated', format(getdate(), 'yyyy-MM-dd HH:mm:ss'));
INSERT INTO RS_SCHEMAINFO(KEY_FIELD, value) VALUES('script', 'RS3.0-14-SQLServer_UPDATE');
INSERT INTO RS_SCHEMAINFO(KEY_FIELD, value) VALUES('schemaversion', 'RS3.0-14');

We will be happy to get numerous feedback on the BETA version. For this purpose, we created this forum thread: https://forum.reportserver.net/viewtopic.php?id=1541

Best regards,

Your ReportServer Team