Dynamic Reports in Administrative Applications Mohammed Said Desouki HIAST Damascus ,Syria [email protected] Abstract Building the reporting portion of an information system is usually one of the most time consuming, tedious and endless tasks. We discuss here our experiences in building a complex reporting system. We present an approach for a dynamic report based on a careful design of the database, generic reports, and an easy flexible reporting front-end, to satisfy a large set of (we don’t pretend to satisfy all) an information system reporting requirements.

1. Why dynamic reports? The reporting needs in an information system are not always clear in the mind of its users and their managers. Also, they are susceptible to change frequently during the system operation. The decision makers prefers to have a flexible tool that respond to their various statistic needs. The huge number of reports required in an ordinary administrative system is another paradox; a traditional information system may require 100 to 200 reports [1]. These reports must be modified or rewritten several times if the data model changed or some new requirements appeared. An easier and more efficient approach would be to use dynamic reports. The time spent on creating a dynamic report using generic reports and an easy user interface is justified by the reuse of this tools in other applications.

2. From requirements to design A thorough analysis of the reporting requirements is needed during the requirement analysis phase. In most of the systems we have developed the reporting needs were not clear in this phase. This is accepted when you find other systems having no clear main requirements! However, even if the reporting requirements were clear in the managers and users minds, it is susceptible to change frequently during the system operation. Two type of reports can be found in any administrative system: • OLTP Reports: daily operation reports such as purchase orders, invoices, letters to clients. • Decision support reports: summary reports that support managers decision making.

The daily operation reports (named functional reports in [1]) depends on the functions provided by the different components of the system. They are used very often but they are rarely modified. Summary reports (named managerial reports in [1]) are more susceptible to modifications. This type of reports are frequently used in analyzing sales data by store, region, state, or country [2]. Managers are not able to precise in advance what are the reports they need, what information to include in these reports, and how they will be formatted. They would rather like to be able to display the data in many different forms depending on their decision making needs. It will be heaven to them if they have various ways of sorting, grouping, and organizing the data. In other words, they simply want dynamic reports. We propose a mechanism for building dynamic reports based on the addition of some tables to the data model, building some generic reports, and presenting to the end users an easy to understand tool where they can formulate their reports them self. 2.1. The data model The data model must be built to support this philosophy. Generic data models lend themselves more easily to support generic reporting techniques. A thorough analysis of the reporting requirements is needed even before the data model is frozen. This analysis usually reveals any data model weakness that may make application and reports development much more difficult later in the process. In general, you should use generic data modeling techniques to support flexible reporting systems. Generic modeling gives models with relatively few tables that easily support flexible reporting. Rather than having lots of small tables with similar structures, with a generic system, a single table that is typed that stores all objects of a similar structure. This enables you to write a single reporting system that can simultaneously support many different types of objects. We will present our approach using a simple but reach example for simplicity reasons. The data model consist of three tables EMP, DEPT, and SALGRADE. These tables contain virtual data describing the employment and financial position of some employees (tables 1,2,3).

Table 1. The EMP table data. data.

Table Table 2. The DEPT table data. data.

Table 3. The SALGRADE table data. data.

2.2. The additional tables We add two tables for dynamic reporting purposes. These tables define the schema we want to report on. They will contain meta data (data about data) that describe each original table or view structure. This type of information exists already in the data dictionary of many database management systems (we use Oracle), but here we need some other information related to the national language used in the application. First, we define a table named "MY_TABLES" (the table of tables). It contains three columns (the number of the table, its name and its Arabic name). Other columns may be added, for example if we develop a multi language application we can add a column for each language. Table 4 shows the structure of "MY_TABLES". For our example we insert a line in this table that indicates we have a view named "EMPLOYMENT" and precise its name in Arabic (table 5).

Table 4. "MY_TABLES" struct structure. ure.

Table 5. "MY_TABLES" data. data.

We construct a view named EMPLOYMENT to join the different columns of these tables (program 1).

CREATE OR REPLACE VIEW employment AS SELECT EMP.empno, EMP.ename, EMP.job, EMP.hiredate, EMP.hiredate, EMP.sal, EMP.comm, SALGRADE.grade, DEPT.dname, DEPT.loc FROM

EMP, SALGRADE, DEPT

Secondly, we define a table named "MY_COLUMNS" (the table of columns). It contains five columns (the number of the column in the table, its name, its Arabic name, its data type and its table number). Table 6 shows the structure of "MY_COLUMNS". In our example we insert 9 lines in this table each for one column of the EMPLOYMENT view (table 7).

WHERE EMP.sal BETWEEN losal AND hisal

Table 6. "MY_COLUMNS" structure. structure. AND

EMP.deptno=DEPT.deptno

Program 1. the EMPLOYMENT view definition. definition.

The EMPLOYMENT view contain the employees numbers, names, jobs, hire dates, salaries, commissions, grades, department names and department locations. It is more efficient (in Oracle) to prefix each column by the corresponding table (Database Management System DBMS depending).

SELECT

&col1 col1 co1 co1, &col &col2 col2 co2 co2, &col &col3 col3 co3 co3, &col4 col4 co4 co4, &col5 col5 co5 co5, &col &col6 col6 co6 co6, &col7 col7 co7 co7, &col &col8 col8 co8 co8, &col &col9 col9 co9 co9, &col10 col10 co10 co10, 10, &col &col11 col11 co11 co11, 11, &col &col12 col12 co12 co12, 12, &col13 col13 co13 co13, 13, &col &col14 col14 co14 co14, 14, &col15 col15 co15 co15, 15, &col16 col16 co16 co16,& 16,&col ,&col17 col17 co17 co17, 17, &break1 break1 break

FROM

&tab1 tab1

WHERE

&where1 where1

Table 7. "MY_COLUMNS" data. data.

ORDER ORDER BY &sort1 sort1

Program 2. the list report query. query.

3. From design to development To satisfy the reporting needs (100 to 200 reports) in conventional ways, an army of developers may be needed. In addition, after the system is operational, the development team will continue to build new reports as they are needed, or some establishments decided to employ some permanent programmers to reply their continuous reporting needs. What happens when the data model changes? The same change needs to be made to hundreds of reports. When the first version of the system is shown to the users, there are usually significant modifications required. This leads to changes in the data model, user interfaces, and, of course, to all of the reports. As development progresses, more changes are usually necessary. The same reports must then be modified or rewritten several times. Again, an army of developers is needed to maintain such a system. 3.1. Generic reports We propose to group the required reports into sets by similarity. When the system analyst discuss the reporting needs of the client enterprise, he obtains a large number of reports. The first step to do is to group the similar reports. Many reports can be satisfied by one parameterized report. Usually, we obtain between five to ten sets. Each set can be satisfied by a single generic report. These 5-10 generic reports should be built carefully and their queries should be parameterized. In our example, we choose a list report with the possibility of grouping and ordering data. The dynamic component of SQL allows programs to construct and submit SQL queries at run time [3]. To follow this illustration it is important to describe some dynamic SQL features. A dynamic SQL query is a query that contains the symbol "&", which is a place holder for a value that is provided when the SQL query is executed [3]. In the list report this query can be as shown in the program 2. below.

The previous query is fully dynamic, it define a list of 17 columns (there is always a limit) and a breaking column (break) from the table (tab1) using the condition (where1) and ordered by the columns in (sort1). These variables are replaced at run time by real columns, table, conditions, and sorts. The generic report must have a parameter for each of these variables. The report parameters are demanded before running the report. Then we must build a front-end that allows the user to select the sort, break, and filter options they desire. 3.2. The front-end The front-end will gather the users preferences and launch the report with the right parameters. When the user hits the print button, the front-end builds valid query clauses to fit the choices made by the user. The front-end then passes these clauses to the report using a parameter list, and the report automatically conforms to the users selections. A flexible reporting front-end that allows users to select sorts, breaks and filters dynamically is a powerful addition to any major reporting system, but is usually a very complicated and time consuming application to create, even for the most experienced developer [4]. The tool shown below (figure 1) allows the user to specify a report title (one of the generic report parameters), then he can choose a table (from the tables defined in "MY_TABLES"). After that he choose some of the columns of that table (from the table "MY_COLUMNS"). The user dose not have to know SQL or even English. He use Arabic (or any national language) and he only click on lists and try suggestions. The conditions can be specified easily. All famous comparison operators are possible (=,<,>,<=,>=,<>, between, like, not like, in, not in). Logical operators between conditions (and, or) suppose parenthesis among each condition and the rest of the expression. Only eight conditions can be specified (another limitation). Three sorting columns are possible. They columns of sorting and those in the conditions may be chosen from all the table columns not only those selected to be shown.

For each column we may precise the type of sort (ascending or descending). The grouping column is also another way of sorting wile grouping. We added a choice of the paper size to allow more columns for large papers. In the example of figure 1, we request to show a list of the employees names, jobs ,hire dates, salaries, grades

and work locations for those employees whose salaries is grater or equal to 13000 (Syrian pounds I suppose). We group them by department, then sort them by salaries from the most rich to the poor, and by hire dates when the salaries are equals.

Figure 1. the front end tool. tool.

Figure 2. the resulting report.

4.

Conclusion

Finally, it is time to push the magic button and see the resulting report. If the user had the courage to click the "report show" button, he will see a great beautiful picture (figure 2). But it is no more than what he constructed and requested. The previous work was useful in many real applications where the reports were not fixed and users change their minds frequently. It can be followed in many ways. We may first think of overcoming the limitations (number of tables, columns, conditions, sorts and groups). Joining many tables and specifying conditions related to many tables may be another extension. Also we can try to use the data dictionary information to minimize external customizations needed before the use of the tool. These works need more patience than reading this article. 5.

References

[1] I. Bâlcu, “Don’t Build 200 Reports, Build Just One!”, Dulcian Inc, April 2002. [2] M. S. Desouki, “Constructing Very Large Data Warehouses”, IT Magazine, NICE, Damascus, NO.79, May 1999, pp. 37-45. [3] A. Silberschatz, H. F. Korth, and S. Sudarshan, Database System Concepts, fifth edition, McGraw-Hill, May 2005. [4] J. Strano, and P. Dorsey, “Building a Flexible Reporting System Template Using Developer/2000”, Dulcian Inc, July 1997.