Business Challenge
Our customer works with a huge amount of data from various data sources. The project’s main goal was to support reports on Actuate and Jasper technologies.
Another major requirement was to migrate all reports from Actuate to a more powerful and easily extendable reporting platform, which is based on Jasper technology. The new reporting system manages the entire reports lifecycle including development, deployment, report status tracking, data presentation and delivery to the end-user in a suitable file format.
Technologies Used:
- Sybase IQ
- Sybase ASE
- Actuate Reporting
- JasperServer
- T-SQL
For the reporting system, Sybase IQ and Sybase ASE relational database management systems were used. All the most significant reports had their equivalent in Sybase IQ and Sybase ASE to meet the requirement of handling large amounts of data in a low-cost, fast, available environment. For these purposes, Sybase IQ worked better than simple optimization changes on Sybase ASE. All stored procedures were written in Transact-SQL (T-SQL) language because Sybase ASE was used earlier. Thus, T-SQL was chosen in order to reduce the time and expense of transferring RDMS to Sybase IQ.
Data Workflow:
- The logic begins from the processing of trade files (.csv files) by trading servers and batches. Once these files are processed, they flow to Middle Office Database.
- Feeder picks up trades from the trades table and sends them to BackOffice Database.
- Once data comes to BackOffice Database, Engine picks them up and performs calculations of market values, prices, fees, accruals, rates and other variables. It then breaks the data down into different BackOffice Databases.
Technical Approach
Reports are created from stored procedures. Many different clients can use the same reports because they are built according to standard requirements and general approaches. Report logic can be separated using stored procedure and reporting tools (Actuate or Jasper). When a new client brings additional changes, there is no need to create a new stored procedure or a new report. In most cases, a new column or slightly different logic can be controlled by parameters. Thus, clients that used earlier reports will not be affected. This approach allowed us to reduce time and cost spent on fulfillment of requirements. The report view can be changed dramatically using a different set of parameters, meaning report columns (adding or removing fields) and data presentation when grouping can be amended or presented in different layouts. Using parameters, it is also possible to establish custom settings on some fields, for example removing commas from .csv files or changing scale on numeric columns.
Reports provide Excel Macros support and an Excel formula can be implemented on a particular column. This approach allows a user to automate some calculations when, for instance, two columns should be populated manually and the third one is populated based on the previous two.
Dynamic Reports
A completely new approach was implemented within new dynamic reports creation. A core report must be identified for every group of reports–such as holdings, tax, profit and loss, and so on–in order to do this. All columns within the core report are separated into core and common columns. The set of core columns is unique for each group of dynamic reports and common columns are the same for all dynamic reports. A user can choose any subset of columns, and also group, sort and display the output in any order desired. It is also possible to save this set and re-use it as a new report. With more flexibility and customization, this approach allows for merging outputs into a dynamic report.
To provide one corporate style within all reports, standards were applied to strictly regulate all font sizes, font styles, recommendations for cross tabs and totals, and more. To maintain transparency across reports and make comparison more accessible, a data dictionary was applied to track column headers, which show the same information under different column names on Actuate reporting.
Intermediate Layer
For reports that provide information from multiple sources, Intermediate Layer was developed. This means that data can be collected from several sources, processed and merged into one result set.
Report Generation Structure
Each report has two main parts:
- Stored procedure is the part of a report that is responsible for the report’s logic: data quality, sorting, grouping, and so on.
- Jrxml. This file is the part of a report that is responsible for the appearance of the report: layout, fonts, sizes, alignment, column names, order of columns in the report output, and more.
When a user begins running a report, it calls upon the appropriate stored procedure, which communicates with the Back Office DB and takes data for a report from different tables.
When stored procedure returns data to the application, it processes data with jrxml and produces a report which can be downloaded in different formats such as .xls, .csv, .pdf and others.