A Technical Obstacle to Traditional Reporting

The reporting tool is the software to present the table and statistics chart dynamically. It is the basic requirements for business, a basic means of running business, and the basis for boosting competitiveness. It is the most common tools for business personnel. However, using the traditional reporting tool requires a relatively stronger technical background. The average business personnel cannot handle it independently, and thus lots of problems are brought about: The business personnel is quite inefficient in preparing the report, the time-spanning is quite great, and the commercial opportunity can be slipped away easily; The report designed by technical personnel can easily bring about the errors on business logics, and mislead the enterprise decision; As the result of cooperation between the technical personnel and the business personnel, the report may deviate from the actual business, and fails to reflect the actual state of the businesses affairs.

For example, Bill is the sales director of a certain pharmaceutical company. He is in urgent need to prepare a certain product sales report to impress the clients in an important bidding event. This report requires presenting the monthly sales value, monthly growth, and year-over-year growth of these 3 products, as shown in the below figure:

However, he found that this report does not exist in the information management system of the company. Therefore, he himself and sales team tries to prepare this report by themselves. Quite soon, they found that many obstacles are unconquerable, for example, they needs SQL statements to retrieve the data, the complex expressions for filtering and processing, and the abstract expression for month-over-month comparison or link relative ratio calculation. Bill can do nothing but abandon the report, which greatly reduced the eventual effect of presentation. There are still other similar cases available:

A retailer newly launched a supply-chain management system, and arranged the business personnel for fabricating the report. The business personnel have grasped a great many of fabrication skills for traditional reports. However, they still act slowly when preparing the simple report, and cannot fabricate the complex table.

A bank improves its business procedure that requires the modification on the corresponding reports. However, from the IT team point of view, the report modification so complex that the procedure will have to be simplified. As a result of compromising, the procedure is partly simplified, and some reports are modified.

When preparing the report related to Clinical Practice, the IT technician from the report vendor mixed up with the AE (Adverse Event) and SAE (Serious Adverse Event) concepts, which leads to the error of the key indicators.

As can be seen from the above examples, the technical obstacles have hindered the development of traditional reporting tools because of the native drawbacks of traditional reporting tools:

1.To retrieve the numbers, SQL statements become a must

The 1st step to prepare the report is to retrieve the data, and the data is usually stored in the database. Therefore, we need to compose SQL statements.
　　
In the case of “Specific Product Sales Report”, the relevant data is stored in the 2 physical tables. The primary data of mediSales table is as follows:

The primary data of mediInfo table is as follows:

There are multiple ways to retrieve the data through SQL. In the extreme cases, although a long SQL statement can be used to calculate out the final report result, it is a bit too complex and the existence of reporting tool becomes ultimately meaningless. The simplest way is to split it into 2 statements. Firstly, retrieve the sales data from Jul to Dec of this year from mediSales table, as shown below:

　　SELECT mediSales.OrderID,mediSales.OrderDate,mediSales.Quantity,mediSales.mediID from mediSales
　　where ( mediSales.OrderDate>=#7/1/09# and mediSales.OrderDate<=#2009-12-31#) 　　or( mediSales.OrderDate>=#7/1/10# and mediSales.OrderDate<=#2010-12-31#); The result is:

Then, retrieve the specific 3 products from mediInfo, as shown in the below figure:
　　SELECT mediInfo.MediID, mediInfo.MediName ,mediInfo.UnitPrice from mediInfo
　　where mediInfo.MediName in(‘ApXXXX’,’GpXXXX’,’JiXXXX’);

The efficiency will be much higher if combining both of these 2 actions, and SQL makes it a bit more complex:
　　SELECT mediSales.OrderID,mediSales.OrderDate,mediSales.Quantity,mediInfo.MediName,mediInfo.UnitPrice from mediSales,mediInfo
　　where(( mediSales.OrderDate>=#7/1/09# and mediSales.OrderDate<=#2009-12-31#) 　　or( mediSales.OrderDate>=#7/1/10# and mediSales.OrderDate<=#2010-12-31#)) 　　and mediInfo.MediID=mediSales.MediID and mediInfo.MediName in('ApXXXX','GpXXXX','JiXXXX'); SQL is the commonest database query language. Theoretically, they can be used to complete any complex data processing procedure. However, since it is a tool specially designed for IT technician, it becomes the impassable technical obstacle for business personnel as they will have to learn the primary key and foreign key, BCNF, association, nested query, etc. For the business personnel, learning these contents out the range of their major expertise is not only wasting their energies, but also of the bad effect. It is useless for their professional careers. In addition, as for the data from text file and other non-databases, the senior languages like Perl and Java are often required to retrieve the data that requires the relatively higher technical standards. 　　 The obstacle to data retrieval blocks in the way to reporting by the business personnel themselves.

Data management requires the complex scripts

The retrieved data must be arranged to get used in the calculation easily, and the procedure of arrangement requires scripting.
　　
Take the above case for example, we’ve got the sales data from Jul to Dec of this year (designated as salesData) and the “specific 3 products” (designated as mediData). The typical result of arrangement is as shown below:

For the data from the column Name, you will need to filter SalesData by the data from mediData, and only keep 3 products; then group by mediID; Lastly, perform lookup and then mediName will be displayed.

In these two columns of Year and Month, the SalesData are required to group in 2 levels according year and date.

In the column Quantity, the total sales of each month are to be summarized.
In the column UnitPrice, we will need lookup the UnitPrice from mediData.

The traditional report tools usually have the systematic scripts of their own and therefore implement various procedures for the above arrangement. For example, some scripts for a reporting tool are as follows:

　　=Cell(b3,0)+Cell(b3,3)
　　String value1 = expression(“=A2″).toString() ; mediData.setDisplay(value1+”xx”); =row(EA_SETFUNDASSIGN,toField(“YEAR_”+d3),FINANCINGSORT=b8&&UNITCODE=a8)
　　{ reportLink.setParam(“wfdd”,expression(“=B3”)); }
　　String value = expression(“=:MediName”); int value = VariableTool.intValue(maxCell.getValue); if (value == 2009) maxCell.setColVisible(false); int value =maxCell.Row(); if (value>=3) maxCell.setRowVisible(false); maxCell.setDisplay(new String(” “));
　　String value = maxCell.getValue(); if (value.equals(“A2”)) maxCell.setRowVisible(false);

To implement the above-mentioned data arrangement, the reporting tools without exception need a great deal of scripting. The number of function types and complexities of these scripts are far beyond that of the SQL. Although some of them may resemble JavaScript, they are not compatible and usable to each other, which means, users will have to learn over again once the reporting tools have changed. General speaking, only the professional technician can grasp these scripts.

The complex scripting involved in the data arrangement can keep the business personnel away.

The commonest calculation cannot be implemented without involving the IT technicians

Once arranged, the data will undergo the manipulation that often requires the expression composing and the involvement of IT technicians.
　　
Take the above case for example: users want to calculate the year-over-year comparison and the link relative ratio. The result is shown in the below figure:

In which, the monthly sales of Amount follows this arithmetic logic:Quantity*UnitPrice.

The arithmetic logic of link relative ratio comparison: (Sales in this month – sales in the previous month)/sales in the previous month. This formula will handle the comparisons of every product in each month of each year. Users will have to consider the cases that no data is available in the previous month of the July of 2009, and the data of July in 2010 together with the corresponding data of Dec in 2009 cannot undergo such calculation.

The arithmetic logic of the year-on-year comparison is:(Sales in this month – Sales of the same month in the previous year)/Sales of the same month in the previous year. This formula is to use the data of each product in 2010 to minus the data of that in 2009. We may need to consider the case that no data is available for the previous year of 2009, and the 2nd product categories of 2009 together with the 1st product of 2010 cannot undergo such calculation.
　　
The year-on-year comparison and the link relative ratio is the common calculation demands on the report. The similar calculations also include the sales rankings of each month in the current year, the calculation when adding clients and quarters, and the calculation on the months in which the sales of every product is rising consecutively, etc.
　　
The business personnel cannot handle such calculation without the support from IT technician. In fact, confronting to such abstract and complex formula, even the highly skilled IT technician may feel it tough to complete.
　　
The common calculation in the report is the greatest obstacle. IT technician and business personnel shall collaborate closely to overcome it.

There are many limitations on the band-style report
For the business personnel, the simplest reporting model for them to grasp is the band-style report. As long as the requirements on data retrieval, data arrangement, arithmetic logic, and report style are simple enough, the business personnel can use it to fabricate some simple report, such as the below report of 2:

The band-style report can be composed of header band, footer band, body band, and the 2nd-level-details band. As an ideal tool to present the simple line-style report in the above chart, the band-style report is clearly structured, and only requires a relatively low technical competence on users. However, it suffers from a great limitation.

Firstly, it usually only support the data of a single group, and does not support 2 groups of data, just as shown in the above cases. Of course, the drawbacks of model can be remedied through technological means. In the procedure of data retrieval, these 2 groups of data can be merged into 1 through the ever more complex SQL.

Secondly, the band-style reporting tool can be used to barely distribute the data into 1 level. However, it would be hard for the band-style reporting tool to distribute the data into 2 levels, just as the scenario in the above case, unless the users are capable to use the complex report script language, familiar with the hierarchy expression, and have the clear logical thoughts in mind. In addition, the more levels involved in the scripts, the more complex it becomes.

Then, the band-style report does not support the inter-row calculation directly, and is not an easy means to calculate the year-on-year comparison or link relative ratio comparison. In fact, the universal solution is to calculate through stored procedure before hand, and the report will just get the data as the data used to be. However, the technical requirements on the stored procedure are much higher than SQL.

Lastly, the band style report can easily incur messy format. The data entry of each band in the band-style report is separately set. Take the above case for example, if the band of details is set to auto adapt to the field width, then it would be hard for table header to align with it; If the details are set to the same width to the table header, then the data will be truncated once the width of a certain piece of data is greater than the set width.
　　
All in all, for the traditional reporting tools, the data retrieval relies on SQL, the data arrangement relies on the complex scripts, and the common computation relies on the IT technician. Even the simplest band-style report still suffers from a bit too many limitations. These technical obstacles block the way to the gate of reporting for business personnel, and hinder the further development of reporting tools.