17 April 2018

POC in partnership with Randstad – Dynamic RDL generation

This article will address the design of a Report Definition Language (RDL) generation solution in light of the need to follow the market and support operations that provide services to Randstad customers.

Introduction

Currently, for each campaign in Randstad clients, a ticket must be opened in Jira, which can take 1 or 2 days to be addressed, which has an impact on the absence of reports when the telephony campaigns in Randstad clients come into production, having an impact in the operators monitoring.

After a brief analysis, it was found that the reports used were for listings only, and were, therefore, very similar.

Due to the need to follow the market and support operations that provide services to Randstad customers, a Dynamic Report Definition Language (RDL) generation solution was designed with the following to achieve:

1. Reduce the response time in the provision of operational reports;
2. Give greater autonomy to the team responsible for the campaigns and monitoring of operations (NOC) to configure them;
3. Ease to create operational reports;
4. Reduce costs with fte allocated for the elaboration of a tailored report.

Solution Description

To respond to customer needs, it was proposed an interface and a mechanism for generating dynamic RDLs - A Dynamic Report Definition Language (RDL) is a report that is generated using Microsoft's libraries and using C # or Visual Basic.

It was created a front-end that allows end-users to select the data that they want and generate the report. The front-end turns all processes user-friendly and faster due the rapid selection of data and report creation.

In order to guarantee the client’s involvement, we elaborated mockups and a sequence diagram to present the proposed solution.

Mockups

We have chosen to design a minimalist interface because it is a proof of concept and the solution cannot be complex due to the low user experience in creating reports.

The design includes 3 stages of creation, "Data Source Configuration", with choice of server, datasource and source object of the report (table, stored procedure or view), "Report information and Report Prompts", with information of the report content and "Preview Report".

The first screen is used to configure server and database.

RDL randstad business intelligence report definition language BI4ALL

Figure 1 – Screen Mockup for database selection

 

The second screen is used to configure the report data.

RDL randstad business intelligence report definition language BI4ALL

Figure 2 – Mockup for report configuration and column selection

 

The third screen is a report preview.

RDL randstad business intelligence report definition language BI4ALL

Figure 3 – Mockup for custom report preview

 

Sequence Diagram

In order to ensure that the client properly perceived the screens flow, a sequence diagram was developed.

RDL randstad business intelligence report definition language BI4ALL

Figure 4 – Sequence Diagram

 

Dynamic RDL generation engine

The core of this POC focuses on the dynamic RDL generation engine. On this basis, Microsoft's best practices are followed closely and are available at the following address

https://technet.microsoft.com/en-us/library/ms170667(v=sql.105).aspx

The dynamic RDL generation is essentially divided into the following parts:

1. XML object instantiation

Figure 5 – Object Instantiation example

 

2. Adding elements to the report

RDL randstad business intelligence report definition language BI4ALL

Figure 6 – Adding elements to the report example

 

3. Adding data sources to the report

RDL randstad business intelligence report definition language BI4ALL

Figure 7 – Adding elements to the report example

 

4. Adding datasets

RDL randstad business intelligence report definition language BI4ALL

Figure 8 – Adding datasets to a report example

 

5. Adding columns

RDL randstad business intelligence report definition language BI4ALL

Figure 9 – Adding columns to a report example

 

6. Adding tablix

RDL randstad business intelligence report definition language BI4ALL

Figure 10 – Adding a tablix to a report example – part I

 

RDL randstad business intelligence report definition language BI4ALL

Figure 11 – Adding a tablix to a report example – part II

 

At the end of each iteration, the newly generated report will be saved and published to the report server, becoming available to users.

RDL randstad business intelligence report definition language BI4ALL

Figure 12 – Save the report example

 

Final version of the front end

The front-end was developed in .net core 2.0 and was based on the design of the previous mockups.

The easy customization of reports allows the user to, quickly and intuitively, create their on-demand reports.

Screen 1 – Select the database

RDL randstad business intelligence report definition language BI4ALL

Figure 13 –Screen that allows selecting a database

 

Screen 2 – Report Configuration

RDL randstad business intelligence report definition language BI4ALL

Figure 14 – Report configuration screen

 

Screen 3 – Select the columns to be presented on the report

RDL randstad business intelligence report definition language BI4ALL

Figure 15 – Screen that allows the selected columns to be displayed on the report

 

Databases

Application tables:

RDL randstad business intelligence report definition language BI4ALL

Figure 16 – Database diagram

 

Example of some reports created and saved on a applicational database:

Figure 17 – Three configured reports example

 

Tools/Technologies used

• Databases
- Microsoft SQL Server 2016 Suite
• C# 7.0
• .Net Core 2.0

Report generated - Example

This example contains dummy data, due to the report containing sensitive data.

XML

RDL randstad business intelligence report definition language BI4ALL

Figure 18 – XML sample of a generated report

 

Table SSRS (This data is purely exemplary due to the confidentiality agreement with the customer)

RDL randstad business intelligence report definition language BI4ALL

Figure 19 – Tablix Report

 

Conclusion

The first release of the product has a database and a back office that allows customizing the reports, permitting a limited number of templates.

In spite of the complexity in the development of the solution, it can add value to the creation of SSRS on-demand reports, in a fast and intuitive way for the end user.

The implementation of the solution presented can contribute to a 200-hour reduction of effort and, in turn, will reduce project costs.

The next product release will include the customization of new templates, editing of existing reports, creation of report visualization profiles and voice controls.

RDL randstad business intelligence report definition language BI4ALL

 

 

 

 

RDL randstad business intelligence report definition language BI4ALL

 

 

 

 

RDL randstad business intelligence report definition language BI4ALL

Blog