SDL WorldServer: Getting Started with Custom Reports

The Report Center was completely upgraded with the last major release of WorldServer. Overall the new offering was very good, with a more modern interface and a more powerful underlying technology.

Yet upgrading the Report Center and starting to make the most of its full potential required a certain amount of effort. This is mostly because while it is accessible through WorldServer, the Report Center is in fact a completely separate application. Moreover,  it is made-up of three distinct elements, for which no integrated documentation exists: the queries, the reports layout and the repository site.

This post reviews the basic functionality of the current version and suggests ideas to improved it in the future.

Managing the Report Center

Adding Reports

WorldServer Reports are designed offline (see next section). Once ready, they need to be imported:

1. Open the Report Center (Tools > Report Center)
2. Click View > Repository
3. Right-click on the folder where you want to add a report
4. Click Add Resource > JasperReport
5. Enter a name for your report and upload the JasperReport file (.jrxml)

At first glance this works well and certainly is easy. There are however a few ways I think it could be made more efficient:

• Add Version Control and Roll-back functions for successive uploads of the same JRXML. This is essential since the queries and layout cannot be edited via the Report Center.
• Automatically read the Report Name and Resource ID from the JRXML file, to save manual steps and prevent typos.
• Batch JRXML upload: this would be very useful to support upgrade effort, as well as to transfer reports from a Test server to a Production environment.
• JRXML Download would help with future migration and simplify back-up processes
• Finally some editable JRXML samples should be provided to show users how JasperReports can be used in WorldServer.

Data Sources

Next, each Report has to be connected to a database. The setup steps may differ slightly depending on whether WorldServer uses Oracle or SQL, and drivers may need to be installed.

First, create a Data Source:

1. Right-click on the folder where the reports are located
2. Click Add Resource > Data Source
3. Enter your Database details and test the connection before clicking Submit

You can now connect Reports to the Data Source:

1. Right-click on a Report
2. Choose Data Source > Select data source from repository
3. Browse to the database you just connected

Here again, there is room for improvement in my opinion. The possibility to connect a location within the Report Center to a database would be helpful. Instead we have to connect each report one at a time.

Permissions

User access can be managed at both user or role-level. You can also setup different access for each report separately or for a folder within the repository. This is in keeping with one of WorldServer’s strengths, where permissions are extremely flexible, and relatively easy to fine-tune.

You could for instance have some reports only visible to Project Managers and others to Language coordinators. You could show linguists reports where the data only relates to their own work, or create Customer or Business Unit specific reports and then only grant access to them to people in selected groups.

Permissions can be edited by right-clicking on a report and choosing Permissions. Roles and Users are accessed via the Manage menu.

Inputs Controls

A dialog box can easily be created to allow users to filter their searches or, more precisely, to set up the value of parameters to use when the report runs. In an SQL-based setup, percentage signs can be used for wildcards. A parameter added to the report during layout design is associated to each Input Control setup in the Report Center.

To create an Input Control:

1. Right-click on Input Controls in the Repository
2. Click Add Resource > Input Control and follow the steps on-screen

Note: the parameter name must match that from the Jasper Report (case-sensitive).

Once an Input Control is created it can be re-used for any number of reports:

1. Right-click on the Report
2. Click Edit > Controls & Resources > Add Input Controls and follow the steps on-screen

A Report can have several input controls, so the user could for example set a value for Project Name, Language and Workgroup themselves, before running the Report. The Input Controls dialog also lets you save commonly used search parameters.

Overall this too works very well and is also  relatively easy to setup. My only criticism is the lack of documentation: there is no Online Help or Report creation guide apart from the Samples in the View menu.

Designing the Reports Layout

JasperSoft

The layout of WorldServer Reports cannot be designed or changed from WorldServer. The best way to do that is to embed your SQL query within a Jasper Report (.jrxml) using the JasperSoft iReport Designer. There is a free version available for download, which provides everything needed to design a WorldServer Report. Once again though, there doesn’t seem to be any WorldServer-related documentation available.

Here are a few pointers to get you started:

1. Connect iReport to your WorldServer database
2. Create new report (File – New)
3. Copy your query into the Query viewer
4. Click Read Fields
5. Go back to the Designer and drag 1 or more fields from the Report Inspector into the Detail area. This will automatically create headers which you can then rename, align etc.

Online vs. Offline

It can be time-consuming to pretty-up reports in iReport. The first way to gain efficiency is to make a choice between the way they look online and how clean the exports are. If you expect your users to consult the reports online, you may want to spend time making the report look good online, and load fast for example by breaking the output into pages. By opposition, if you expect the reports to be downloaded and their data further manipulated in Excel, you should instead make sure that the output doesn’t have empty lines or columns.

Re-using layouts

If you are creating several variations of a report, or migrating a number of reports between successive versions of the Report Center, it is worth trying re-use some of this tedious layout work:

1. Open an existing JRXML in iReport
2. Save it under an alternate name
3. Overwrite or edit the query
4. Click Read Fields  to update the list available in Designer
5. Edit the Fields, Descriptions, and Parameters which need to be changed in the layout.

Editing Queries

SQL server management studio

The query viewer in iReport is useful to a point but it doesn’t provide much feedback regarding syntax errors or other issues in queries. Another big limitation is that it doesn’t give any visibility into which Tables and Views are available in the database.

If your WorldServer uses an SQL database, you should consider using SQL server management studio when writing the queries. You can create and test your queries there before copying them to iReport, and browse through the database to get familiar with how the data is structured.

One thing to remember is that active and completed projects are in two separate locations. Just like in WorldServer you have a view for Active projects and another for Completed and Cancelled Projects, under Assignements, the database has the latter in dbo.archive Tables, and the former in dbo.active Views. Performance is much better when querying active projects, and the way the data is structured can also differ in the two locations.

You must have a very clear understanding of the following 3 WorldServer concepts.

1. Project Group: all files, all languages, 1 file submission
2. Project: all files, 1 language, 1 file submission
3. Task: 1 file, 1 language

Their ID numbers are essential within queries because they link information associated with each of them. For example the language name is Project data, but the current owner is Task information. The 2 will need to be joined in order to create a report on current Task owners which lists the languages.

Lastly, any search parameter is better created directly in iReport once the query is finalised. Just replace them with an arbitrary value for testing purposes while working in the server management studio.

Learning resources

There are plenty of tutorials available for beginners and less-experienced database users, and a lot of them are free. I found SQL course.com very clear and concise. The interactive SQL interpreter is great for practicing and experimenting safely. W3Schools is another very good resource.

Advertisement