Conference PaperPDF Available

Abstract and Figures

We have come to a point in time when there is an abundance of database usage in almost all aspects of our lives. However, most of the end users have neither the knowledge nor the need to manage the databases. Even more important, they are unable to generate the ever changing reports they need, based on the data in their databases. Our Applicative Solution for Generating Reports from Templates (ASGRT) tries to deal efficiently with this issue. It has a simple yet effective architectural design aimed to give power to the more experienced administrators and simplicity to common end users, to generate reports with their own criteria and design, from their databases. The presented software enables creation of templates containing text and tags that are recognized and substituted by values retrieved from the database, therefore enabling creation of customized reports with varying ease of use and flexibility. KeywordsTemplate–Reports–Generator–Database Management
Content may be subject to copyright.
M. Gusev and P. Mitrevski (Eds.): ICT Innovations 2010, CCIS 83, pp. 369–376, 2011.
© Springer-Verlag Berlin Heidelberg 2011
ASGRT – Automated Report Generation System
Dejan Gjorgjevikj, Gjorgji Madjarov, Ivan Chorbev, Martin Angelovski,
Marjan Georgiev, and Bojan Dikovski
University Ss Cyril and Methodius, Faculty of electrical engineering and information
technology, P.O. BOX 574, Skopje, R. of Macedonia
{dejan,madzarovg,ivan}@feit.ukim.edu.mk,
{mangelovski,marjan.georgiev}@gmail.com, b.dikovski@yahoo.com
Abstract. We have come to a point in time when there is an abundance of
database usage in almost all aspects of our lives. However, most of the end
users have neither the knowledge nor the need to manage the databases. Even
more important, they are unable to generate the ever changing reports they
need, based on the data in their databases. Our Applicative Solution for
Generating Reports from Templates (ASGRT) tries to deal efficiently with this
issue. It has a simple yet effective architectural design aimed to give power to
the more experienced administrators and simplicity to common end users, to
generate reports with their own criteria and design, from their databases. The
presented software enables creation of templates containing text and tags that
are recognized and substituted by values retrieved from the database, therefore
enabling creation of customized reports with varying ease of use and flexibility.
Keywords: Template, Reports, Generator, Database Management.
1 Introduction
The advancement of technology and information society is evident in our everyday
life. Seeing from the end user perspective, the various IT services that we use daily
seem simple and easy functional. However, in the background, there are complex
databases, data warehouses and service based technologies that become more and
more widespread. Databases are used to store various types of information in
hospitals, schools, universities, municipalities, government agencies and almost in all
businesses services. In these complex systems, the management and presentation of
the information stored in the databases are strictly defined within the software
applications and information systems. Only a small room for customization is left to
the people who actually use the information.
The document and report templates that are an essential parts of any software
application or information system are most often predefined, and the user can only
choose from the predefined templates to generate documents or reports. With this
concept, the template generating process is limited to the programmers and database
administrators. The common software user can use only the previously defined
document and report templates. In this paper we propose an applicative solution for
generating documents and reports from user defined templates - ASGRT.
370 D. Gjorgjevikj et al.
The applicative solutions that we propose enable the common user to create his
own templates and gather the info he needs from the database. Following an easy step
by step wizard allows users to gather new types of information from the database
without knowing any query languages or database design issues whatsoever.
The proposed applicative solution can be used by two types of users. The first type
of users is the one lacking sufficient knowledge of database architectures, database
management or database query language. The majority of users fall into this category.
Therefore it is crucial for this application to be user friendly so that the users do not
find the application too complex to use. The second type of users are more
experienced database administrators, users with higher application access permissions
and people with better knowledge of this system. For this type users it is essential to
provide a way for faster creation of document and report templates without the
redundancy of the user friendly GUI.
One of the advantages of ASGRT is its interoperability. With minor changes it can
be made to work well with all kinds of SQL databases and thereby be used in
different institutions, organizations and even in some types of software.
This paper is organized as follows. The next section presents a short overview of
similar software products. In section 3 the overall program architecture is presented,
while more details about the Database Architecture and ways of interoperability are
given in section 4. Section 5 explains the concept of Tags and Templates, their
structure and use. Section 6 explains the way the system is used, and the conclusion is
given in section 7.
2 Overview of Similar Products
Automated generation of reports from databases and other data sources like XML
files has been a task set to software developers ever since information systems were
first introduced. There are various ways of achieving this goal, with varying
simplicity of use or flexibility of the results, usually two opposed demands.
One of the most powerfull database platforms - Oracle [1] includes a tool named
Oracle Reports. The layout models included allow the creation of reports for both
paginated output, such as printing, as well as Web-oriented output. Oracle Reports
consists of two components - Reports Builder and Reports Services. Various data
sources can be used - relational databases, text files, XML or OLAP. The tool
contains a WYSIWYG reports editor and templates to design the report. There is also
the flexibility to define a report layout by placing the fields anywhere in a page.
Microsoft also includes a less versatile reporting tools in their database platform
MS SQL Server. MSSQL Reporting services [2] and SQL Server Business
Intelligence development studio include features like support for multiple data
sources, dynamic end-user sorting, cascading, and multivalue parameters. Thre is also
the tool named Report Builder, which is an ad-hoc reporting tool that allows business
users to create their own reports and explore corporate data. Its query model
supposedly lets end users build reports without a deep technical understanding of the
underlying data sources.
ASGRT – Automated Report Generation System 371
An additional powerfull tool provided by SAQP is Crystal reports [7]. Crystal
Reports is a business intelligence application used to design and generate reports from
a wide range of data sources. Its version is integrated in software development tools
from Microsoft. Crystal Reports allows users to graphically design data connections
and report layout. Same as all other previously described tools, software development
experience is necessary for using Crystal reports. Also, knowledge of the database in
question is needed.
Various other tools and aproaches to automated reporting have been described in
literature [3], [4], [5], [6]. They all use templates and have various levels of
complexity vs ease of use. The more flexibility is added to the final result, the work in
the tool becomes more complicated and more knowledge of the database in question
is needed.
When analyzing similar products we must not forget the MS Word mail merge
feature readily available in MS Office. However, its shortcomings, among other
things being a desktop application, opposed to a more flexible contemporary web
based approach, limit its usefulness. Also, this approach suffers from inefficient joins,
is limited to a single source, there are versioning issues and most important,
complicated documents are hard to process.
There are three main factors when selecting a tool for automated generation of
reports from databases. Firstly obviously the cost of the tool is of essence, and then
the opposing demands for simplicity of use and abstraction from the database design
versus the flexibility of the generated reports and the features available. The
aforementioned tools score differently in all criteria, focusing on separate goals.
3 Program Architecture
The architecture solution that we propose for solving this problem is composed of 3
main modules: document parser, tag engine and tag generator. The first two modules,
the document parser and tag engine, are the core of our application. The last module,
the document parser, is an optional module intended to make the interface for creating
tags more friendly and easy to understand. If the client does not need this kind of
redundancy, the application can be used without the last module and tags can be
created in a more direct way.
The document parser module is designed to import a report template - a document
containing tags (more about tags in the following sections), go through its content and
identify the tags. The parser can be customized to parse through various kinds of
documents containing text (txt, xml, pdf, word, excel, html, etc.), depending on the
user’s needs.
A part of the document is identified as a tag if it has the following structure:
<#tag_name attribute1_name = ”attribute1_value”
attribute2_name = ”attribute2_value” #>
The symbols “<#” and “#>” mark the beginning and the end of the tag
respectively. The tag name along with its attributes uniquely identifies the tag. One
tag can have an arbitrary number of attributes including no attributes. Two tags with
the same name can exist, as long as they have different attributes.
372 D. Gjorgjevikj et al.
The parser can recognize the expected result from the tag location and the
surrounding contents. For instance, if the tag is placed in a table cell, the resulting
records will be placed in consecutive rows, vertically. Otherwise, if the tag is placed
alone, the resulting records will be presented concatenated one after the other,
horizontally.
Fig. 1. Data flow in the ASGRT application
The processor goes through each of the tags that the document parser finds. It then
checks whether the particular tag exists in the database. If it does, the program
executes the tag’s predefined SQL query stored in the database, formats the result and
returns it as the value that will replace the tag in the document. The SQL query can
incorporate the tag’s attributes as variables, and use their stated values. If the tag does
not exist, it transfers control to the tag generator, where the user can define the SQL
query for the tag, then executes it and returns the result. The tag is then saved in the
database for future use. The attributes can be interoperable in various tags. For
example, an attribute can be defined in one tag, and then the same attribute can be
used in other tags that tend to use the same value from the database. The visual
presentation of the data flow is shown on figure 1.
After the whole document is processed and all identified tags are replaced with
retrieved data from the database, the document is saved and offered to the user for
download.
4 Database Architecture
The program works with 2 databases. The first one is for its internal use: for storing
the user data and tags. The second database is the one that contains the actual data the
program uses to generate the reports. This can be any kind of database which the
program can connect to and run queries over. The type and quantity of the data in this
ASGRT – Automated Report Generation System 373
database is not important for the application, as it is made to work with any kind of
database using minor configurations.
The SQL_query attribute of the tag contains an SQL query that should be executed
over the part of the database containing the data used in the document. This design
allows the program to be connected to any database and start working right away.
There is no extra set-up, and the user can start creating tags, provided he is familiar
with the database structure.
Fig. 2. Databases used by the ASGRT application
The first database shown on figure 2 is the ASGRT application database. This
database is used to store information about the users and tags. The general
information about each user, his user group, and the permissions and access rights that
the user has when using the application are all stored in this database. For each tag,
the name of the tag is stored as well as the database query that the tag will execute
during report generation. Some notes and explanations about the tag can also be
stored. Some of the data stored are shown on figure 3. The ASGRT application
database contains all the documents and reports that the users previously developed,
that can be used again. Also, there is a log of events and errors that might have
happened during the application execution.
Fig. 3. Types of data stored in the ASGRT database
374 D. Gjorgjevikj et al.
5 Tags
Tags define the columns and contents of the reports to be generated. Tags are the
fields that a client inserts into a text document before using that document for report
generation. These fields contain information about what clients want to be put into a
report. When a document with tag fields is passed to the application, the parser goes
through the whole text and reads all the tags. In order for the parser to be able to set
aside tags from what is otherwise ordinary text, every tag needs to have the standard
construction defined by the application. A tag effectively consists of two parts. The
first part is the field that is inserted into a document (the name of the tag), and
optionally some values for the tag’s attributes (there can be more than one field in the
same document referencing the same tag). The second, and main part, is the
information stored for each tag in the database.
There are two different ways to create tags in the database. The first one is through
a user friendly wizard which explains each step of the process. The user needs to
insert a tag name, tag database query, and notes about the tag which are optional. It is
important to state that a tag cannot have the same name as another tag that already
exists in the ASGRT database. In the first step of the tag creation, the SELECT part of
the query is created. The wizard shows the user each table name and attribute that he
can select, so only minor knowledge about the client database is needed. Next comes
the FROM part of the query where in a similar way the user selects his options. Other
parts of the query follow, such as WHERE, GROUP BY and HAVING, but they are
optional and do not have to be included in the query. The second way to generate a
tag is to directly type or insert the query into a text box, and just add the name and
notes for the tag. This is intended for users that are familiar with the client database
and are experienced with database usage, or already have obtained the database query
in another way and they just need to insert it into the text box.
The attributes that are part of the database query can have static or dynamic values.
If a dynamic value is used, the value is specified in the tag written in the text file used
to generate a report. If a static value is used, it needs to be specified in the query
during the tag creation. Dynamic values are stated simply by adding ‘@’ in front of
the attribute name. The dynamic values are then used as attribute values (ex.
Name=@Name, Date=@Date, etc.).
As noted in the Architecture part, attributes can be taken from various sources.
Attributes are hierarchical meaning that after their first definition within a tag they
can be used in new tags without redefinition. Every newly defined tag can use
previously defined attributes within previous tags.
6 Reports Generating
A report is the final product of this application. For a report to be generated, a text file
template is imported in the application. After the file is imported and selected for
report generating, the application parses the whole text in the file. When a part of the
text is recognized as a tag, it is identified by its name and looked up into the ASGRT
database. If the tag name doesn’t exist in the database, its name is added in a special
table used to collect all unknown tags. If the tag exists in the database, the tag
ASGRT – Automated Report Generation System 375
database query is executed, and the tag in the text file is replaced by the value
returned by its database query.
After the whole file is parsed and there are no unknown tags found, the process of
report generation is finished, and the user is allowed to download his finished report
from the application. If any unknown tags were found, the user is informed about
these tags and asked to create them into the ASGRT database. After all of the
unknown tags are created, the file is parsed again and all of the previously unknown
tags are replaced with the values which the execution of their database queries
returned. Once all steps have finished, the user can download the completed report.
7 Conclusion
The main goal of the presented program is to simplify the work of clerks that do not
have any knowledge in database management - they could still use this application to
easily get vast amounts of different reports generated from data stored in databases.
They can retrieve reports as an automated process where little or no human influence
is needed whatsoever. The ASGRT can be implemented in numerous places where
databases are used for storage, where various unpredictable reports need to be
generated and where database data gathering needs to be automated. It can be used in
public administration, in hospitals for patient’s medical record generating, at schools
to easily generate student reports when needed, in warehouses and accounting firms
for various tasks.
ASGRT main advantage is that it is not limited to desktop applications and can be
easily incorporated in applications that use reach web interface. Using this application
it is very easy to make a template for a new report that should be generated from a
given database without change in the application logic. Only very limited knowledge
of the database is needed for the process of tags generation that can be reused to
gather information in different templates. We had in mind the reusability of the
application so it can be implemented with only minor configuration changes on any
kind of database, thus making it interoperable and widely available.
The users of ASGRT interact only with its interface, a web-based GUI (Graphical
User Interface). It is made of several web pages allowing the user to login to the
application, list all the available tags, their notes and explanations, create new tags,
browse and select files for the application to process and download a finalized report.
References
1. Oracle Reports (June 29, 2010),
http://www.oracle.com/technetwork/middleware/reports/
overview/index.html
2. Microsoft SQL Server Reporting Services (June 29, 2010),
http://www.microsoft.com/Sqlserver/2005/en/us/
reporting-services.aspx
3. Chan, D.K.C.: A Document-driven Approach to Database Report Generation. In:
Proceedings of the 9th International Workshop on Database and Expert Systems
Applications (DEXA 1998), pp. 925. IEEE Computer Society, Los Alamitos (1998)
376 D. Gjorgjevikj et al.
4. Mario Guillén, R., Victor, J., Sosa, S., Mario Guillén, Ma., del Rosario Vázquez, A.,
Humberto Hernández, G.: GARP: A Tool for Creating Dynamic Web Reports Using XSL
and XML Technologies. In: Proceedings of the Fourth Mexican International Conference on
Computer Science (ENC 2003), Tlaxcala, Mexico, September 08-12, p. 54 (2003)
5. Chen, W.-K., Chung, K.-H.: A Table Presentation System for Database and Web
Applications. In: 2004 IEEE International Conference on e-Technology, e-Commerce and
e-Service (EEE 2004), pp. 492–498 (March 2004)
6. Zhou, C.-S., Lin, L.: Research and Design of Task Driven Based Web Report Model.
In: 2009 Ninth International Conference on Hybrid Intelligent Systems, pp. 359–362
(August 2009)
7. SAP Crystal Reports (June 29, 2010), http://www.crystalreports.com/
... As it was mentioned in (Gjorgjevikj et al., 2011), most of database users are not familiar with technology basics. In an information system we are working on no more then one percent out of 15 thousand users are able and willing to develop SQL queries. ...
Article
Full-text available
Popular web application frameworks, such as Django, do not provide efficient tools for rapid report development. Specialized reporting software are targeted at visual representation and can generate perfect printed versions of the report. In this paper we describe a minimalistic but quite powerful reporting system developed for a web-based university information system. This reporting system supports zero-programing report development, parametrization of SQL queries, interactive results processing by means of client-side JavaScript libraries, and cross-report references. Main features are similar to well-known reporting systems, such as JasperReports, with more attention to reuse of developed reports, simplicity and interactivity.
Article
Data transformation, an important part of report generation, converts the layout of source data into a new layout suitable for presentation. Many report tools have been developed for end-users to specify data transformation. However, current report tools only support a limited set of report layouts. This paper proposes a visual dataflow programming language, called VisualTPL, to resolve this problem. Data transformation is accomplished by writing graphical dataflow programs, which manipulate tables as first-class objects with a set of extendable table operations. A report tool, called VisualTPS, has been developed to offer an easy and intuitive end-user programming environment. Reports with sophisticated layouts can be created through top-down decomposition and incremental development. An evaluation has been conducted to assess end-users' performance with VisualTPL. The results indicated that end-users could learn VisualTPL in a short time and create complicated report layouts all by themselves. And, in comparison with a commercial report tool, VisualTPL offered end-users similar performances and was preferred over the commercial tool.
Conference Paper
In modern Web Applications, how to design a flexible and changeable report is always one of the key problems to web software designers and developers. Here by analysis the web report tasks and its characteristics, based on the research of the software bus technologies, component technologies and template technologies, with software bus and template technologies illustrates a task driven based generic web report model design methodology to provide a possible solutions for the problem.
Conference Paper
Report generation is arguably the most important task for database applications. It is especially important for e-commerce applications, since dynamic Web pages consisting of database reports are essential to almost every transaction. When a report is generated, the layout of the data sources must be converted to the layout of the report. This conversion is called data transformation. It has been shown that data transformation can be simplified by using table operations. In this paper, we propose a set of new extendable table operations. We show that these operations can be applied visually so as to support visual programming for data transformation. We also implement a visual application called table presentation system (TPS) to demonstrate how these operations are used. With the aid of TPS, one can create sophisticated database reports without writing data transformation programs. Therefore the complexity of data transformation is greatly reduced.
Conference Paper
This paper describes a software tool termed GARP (an acronym for Generador Automatico de Reportes) aimed to generate automatically Web reports from a database scheme. This goal is achieved by the creation of a set of JSP (Javaserver page) files containing all the information required b the reports. These files are generated using the XML (eXtensible Markup Language) and XSL (eXtensible Sheet Language) languages, considered as the standard languages for information exchange using the WWW. The developed tool is applied for the generation of a Web report, which demonstrates the main features of the tool and the advantages of using XML and XSL for the generation of the required set of JSP files.
Conference Paper
It can be argued that report generation is the most frequently performed task in database applications. Therefore the efficacy and efficiency of a database report generation mechanism has a significant impact on productivity. This paper introduces a document-driven approach as opposed to the traditional schema-driven approach to report generation resulting in more flexibility. In this approach, generating a report involves specifying it in terms of a user defined SGML-based report model. Contents of a report can be specified using a transformation language together with queries that retrieve data from different databases. A report is output as a SGML document which can be further edited as well as translated to other formats, for instance to HTML to be viewed using an Internet browser. This paper presents the approach using an example and discusses the features and usage of the transformation language which is a small but expressive language. Despite of the fact that we have only investigated the transformation against relational databases, we believe that this approach can unify report generation for different database models
Article
It can be argued that report generation is the most frequently performed task in database applications. Therefore the efficacy and efficiency of a database report generation mechanism has a significant impact on productivity. This paper introduces a document-driven approach as opposed to the traditional schema-driven approach to report generation resulting in more flexibility. In this approach, generating a report involves specifying it in terms of a user defined SGML-based report model. Contents of a report can be specified using a transformation language together with queries that retrieve data from different databases. A report is output as a SGML document which can be further edited as well as translated to other formats, for instance to HTML to be viewed using an Internet browser. This paper presents the approach using an example and discusses the features and usage of the transformation language which is a small but expressive language. Despite of the fact that we have only inves...