A SOLUTION FOR INTEGRATED REPORTING IN ORGANIZATIONS

Gianina RIZESCU, Ioan I. ANDONE

“Dunarea de Jos” University of Galati, Romania

“Al. I. Cuza” University of Iasi, Romania

,

Abstract

Integrating separate systems was always a challenge and still is. For years, organizationshave neededto integrate information from different databases and systems. Nowadays, the need to fulfill this necessitybecomes more and more stringent, consequently to the appearance of some less pleasant aspects of data-related operational challenges. In this paper, we shall present a solution for integrating different enterprise systems and databases with the help of an enterprise portal.

Keywords:Enterprise portal, Intranet, integrated system, reporting system, integrated reporting system.

  1. INTRODUCTION

Intranets have become popular during the nineties, when access to the enterprise information through a web browser became the working standard. In time, Intranets have grown in size and complexity and the management of content and users has become extremely complex and difficult. In addition for users, an enhanced image of the organization information was not enough. They started to wish increasingly complex communication and personalization features. Thus, the ninetiesrepresent the developing period of the enterprise portal concept. Many organizations started implementing tools to help network administratorsmanage data, applications and users more easily and providing personalized views. Thus, nowadays portal solutions are able to integrate different organization's legacy applications, to support thousands of user requests and to provide new functionality capable of adding value to businesses (management workflows, improved collaboration between workgroups and users being able to manage their own information). In this article, we shall present such a solution. In addition, most portal type solutions enable internal and external access and retrieval of the enterprise information using secure authentication single sign-on type. These features make the portal an ideal gateway to information sharing within the organization. In our opinion, the portal represents the fastest improvement that can be made in the information system of an organization, without the need to modify existing applications and systems implemented in thatorganization.

  1. REASON TO USE AN ENTERPRISE PORTAL IN ORDER TO SUPPORTTHE REPORTING PROCESS IN AN ORGANIZATION

In our opinion, obtaining viable and relevant information in an organizational environment is compulsory in any decision taking process and this cannot be accomplished without integrating and getting all structures and subsystems within that environment to collaborate.

In Romanian organizations, integrated software applications are very poorly represented. Beside that, in many organizations where they are present, they are only partially implemented (certain modules only). Due to the lack of enterprise integrated software applications, the reporting process is very difficult, both from the transmission of information point of view and the inflexibility in getting that information. The process often consists only of limited reports provided by different, separate applications and, at most, of the possibility to export them in Ms Excel for further processing. The lack of integration between different enterprise applications and subsystems leads to delays in gathering and spreading the information, which renders it irrelevant for the intended users.

The adopting of integrative and collaborative technologies by enterprises can help them eliminate these problems. Considering the development of these technologies, obtaining relevant information in an optimal period of time should not be a problem anymore for any organization. And yet, these technologies have an extremely low presence in software applications implemented and used in Romanian enterprises.

We chose to integrate the reporting solution in an enterprise portal for several reasons:

  • A portal offers not only unique integrated access to the information sources and applications of the enterprise, but also a collaborative working environment;
  • A portal offers security, searching, collaboration and workflow features, all the same time;
  • The high scalability of portal technologies allows them to be used in creating solutions for a very large range of enterprises, from little to big ones, independently of their field of activity or their special features;
  • The flexibility in developing, which allows a soft transition from a simple, reduced-size solution to extremely complex solutions without having to replace previous versions;
  • The portalcan be implemented over the existing enterprise application structure, without having to perform any modifications. The portal can be developed according to the field of activity of the enterprise.

The integrated reporting solution which provides real-time access to the data contained by different enterprise subsystems and which integrates them is just a component of the system we designed. The other components of the portal are designed to achieve the following goals:

  • Management and sharing in a secure and controlled environment of documents and situations for the whole enterprise;
  • Automating economic processes that occur within the enterprise by usingworkflows;
  • Sharing in a secure and controlled environment situations and reports created in MS Excel;
  • Communication and collaboration within the enterprise by using the newest collaborative technologies: blogs and wiki;
  1. IDEAS REGARDING THE REPORTING PROCESS WITHIN THE PORTAL

The reporting process must become an intelligent one. An intelligent reporting system transforms gathered data in information and information in knowledge so that the users should perform the most appropriate actions. The reporting solution we proposed represents such and intelligent reporting system, as it provides solutions for the following problems:

  • Integration and consolidation of different data sources.In Romania, many enterprises, even small ones, own several software applications which they use for managing their businesses: an accounting application, anadministration application, an employee-management and salary-calculation application, a production application, an inventory application and so on.The number of applications varies according to the size and type of the enterprise, but, independently of the number, in order to gather relevant information and improve the efficiency, it takes to integrate all the data provided by all these applications. Even enterprises owning integrated ERP systems, often use other applications beside the implemented ERP system.
  • Calculating and reporting the key performance pointers. Any organization monitors its activity by the means of performance pointers. Is it profitable? Do sales improve? How many new employees hired during the last year? Much better choicescan be made if such questions can be answered. If such data is integrated and presented in one single, unitary view for the whole enterprise, the performance pointers become more relevant inside the decision chain. If sales drop, but the number of employees raises, obviously there is a problem. Thus, the intelligent reporting features provided by our reporting solution can be used both for integrating the data provided by different enterprise software applications and for calculating and publishing relevant performance pointers.
  • Eliminating duplicate data. Different applications used by an organization actually mean that the same data should be saved in several locations. Using our solution, an enterprise can actually eliminate duplicate data.

For all these organizational problems, the reporting functions of the enterprise portal we designed will help the enterprise become more efficient and offer better information to the decisional factors,to the employees and also to external users.

  1. THE INTEGRATED REPORTING SOLUTION

The integrated reporting solution was built by integrating the Microsoft SharePoint Server withSQL Server 2005 Reporting Services. SQL Server 2005 Reporting Servicesis a server-side reporting platform which can be used for generating and managing tables, matrix, graph or any other type of data within a relational or multi dimensional database.It contains the following components:

  • A complete set of instruments for creating, managing and viewing reports (Microsoft Visual Studio Business Intelligence);
  • A server-side component, Report Server, which hosts and processes reports in a variety of formats: HTML, PDF, TIFF, Excel, CSV;
  • An API interface which allows to integrate and / or extend the reporting process with particular applications or to create personalized tools for generating and managing reports within the portal(Reporting Services Add-In for SharePoint Server 2007);

By integrating the SQL Server 2005 Reporting Services with the SharePoint Server, the elements and properties of the reports are stored in the SharePointcontent databases, offering a stronger integration between the two server technologies. This integration determines the way in which the content is stored, secured and accessed.

Storage of the data and properties of the reports in the SharePoint databases allows to:

  • Manage reports usingSharePoint libraries;
  • Secure the access to reports stored in the portal using the same rights and authentication levels which control the access to other documents hosted within the portal;
  • Employing the document collaborative and managing functions offered by the portal in order to control the modifications performed on the reports;
  • Employing automatic notifications about changes that occurred on the reports generated and published in the portal;
  • Including and personalizing Report Viewer web-parts in the pages and sites of the portal.

Implementing the integrated reporting solution by integrating the reports server with the SharePoint server requires the completion of the following stages (assuming the existence of a functional enterprise portal):

  • Installing and configuring SQL Server 2005 Reporting Services report server;
  • Installing the Microsoft Reporting Services Add-in for SharePoint 2007, a necessary component for integrating the reports server with the SharePoint server;
  • Configuringthe report server in order to function integrated with the SharePoint server;
  • Activating the portal Reporting Services features;

The image in figure 1 illustrates the way the two servers (the SharePoint Server andthe Report Server) are integrated and are working together:

Fig. 1.Functional diagram of the integration of the SharePoint Server with the Microsoft Reporting Services Server

When a report is opened in the portal, the SharePoint server connects to the reports server, creates a session, gets the data, configures the report according to the displaying properties and displays it in the portal, in a Web part called the Report Viewer. As long as the report is still opened, it can be exported to different formats, or the data can be detailed according to different criteria, filtered or linked to another report by means of included links. The export and interaction with the report are also managed by the reports server.

The reports server synchronizes operations and data to Windows SharePoint Services and monitors information on the files being processed. When properties or settings for any report modify, the changes are stored into the SharePoint database and then copied to the report server database.

By using the built in reporting services of the portal, the management and the employees of an enterprise can also share, beside the .xls, .doc or .pdf reports, SQL Server Business Intelligence Development Studio reports. These are installed with the reports server. The image in figure 2 shows how the reporting solution creates reports with the SQL Server Business Intelligence Development Studio applications and publishes them in the portal.

Fig. 2.Reports being created and published in the portal

The applications offered in this suite (Crystal Reports for reporting, Analysis Services for multidimensional reports, Workflow Applications –for workflows etc.)provide unlimited developing possibilities due to their perfect integration with the SharePoint Server.

  1. REPORT LIBRARIES AND DATA SOURCES

Most enterprises use different applications for different fields of activity, for several reasons:

  • Legacy applications and reluctance to replace them with new ones, because of a lack of information on the features of the new ones;
  • A relatively high complexity required by the implementation of an integrated system that would replace all these applications;
  • Reluctance of the users towards new software applications;
  • Enterprise fields of activity not covered by existing integrated software products;
  • The rather expensive cost to implement an integrated software solution, especially if it requires certain features adapted to the organization’s specific activities;

The central component of the integrated reporting solution, which allows integrated access to the enterprise application data is the Reporting Centre. The Reporting Centre is a SharePoint site model containing report libraries, frameworks, key performance pointers, data source libraries.Inside the Reporting Centre, information can be refined before being displayed to the users.For instance, accountants canonly see reports, pointers and frameworks related to the enterprise accountancy, managers can only see information and synthesis reports etc. Inside the portal, information is refined at the page level, by the means of web part connections. The portal allows the employees to choose refining criteria, like date, supplier, client or a certain report category.

Within the report libraries there can be published reports created with data gathered from any enterprise application. In the data connection library, together with the report, there is also published the database connection (figure 2). These connections can be used at all times for creating new reports. Thus, all interested users who have proper rights can have real-time access to data in any enterprise application.

The image in figure 3 presents the reports library of the Reporting Centre,in which there are published reports obtained by using data from three different applications:

  • Saga – accounting application, Visual FoxPro database;
  • ContabSQL – management application, Paradox database;
  • GestBal – employee management and salary calculation software, MySQL database.

Fig. 3. Integrating reports from different applications in the portal

Despite the different applications and data sources, the integrated reporting solution allows creating reports from all these applications and publishing them in the portal, in one library. Access from one point is provided to data belonging to all implemented enterprise applications. Once they are published in the reports library, the access to them is controlled and secured. They can be viewed only by users with proper rights. They are displayed directly into the web browser, with a click. Beside that, reports can be exported to pdf, xls, html, tiff, csv, in order to be further used and processed offline (outside the portal).

The image in figure 4 presents the report containing the current merchandise store obtained from the ContabSQL management application. Any employee with proper rights can see the merchandise store at all times, without having access to the management application or having to wait for a report from the commercial department.

Fig.4. The current merchandise store report

The image in figure 5 presents a synthetic report on the evolution of annual personnel hiring, obtained based on the data provided by the personnel management and salary calculation application, the GestBal.

Fig. 5. The evolution of annual personnel hiring report

Connecting to external data sources, like excel or external databases is a must in an enterprise in order to fulfill the lack of certain features from the implemented software applications.

Beginning with the 2007 version, Microsoft Office includesmany new features for creating and managing connections, including saving connections in separate files, named data connection files. By default, these connections are stored in the files that use them and can’t be used in other files. The integrated reporting solution that we proposed uses data connection libraries that can manage connections both with the Microsoft Office suite (odc files – office data connection), and with SQL Reporting Services (rds files – report data source). Once saved in the data connection libraries, other users can access these connections in order to create new excel reports. Knowledge about connections to external databases is not necessary. Also, these libraries can control and monitor access to these data sources.

The image in figure 6 presents the data connection library of the Reporting Centrein which there can be seen the connections required to create reports from the three applications presented earlier: Saga, Gestbal and ContabSQL (figure3), and also an odc connection, which is required in order to create the report called Input situation in MS Excel, using data from the accounting application.

Fig. 6. Data connection library

Using the data connection libraries allows employees to share connections to the enterprise applications data sources, reduces the effort it takes to create new reports and improves control over the use of this data. When the application data modifies, it only takes to update one file, the one belonging to the connection and not all the reports using that data.

Implementing the integrated reporting solution will provide full-time access to the enterprise data without problems and the benefits will be substantial.

  1. CONCLUSIONS

Computer systems and software applications used in Romanian enterprises are still characterized by a strong degree of fragmentation. Even where there are ERP systems implemented, a unique, integrated system is still a utopia. There are always jobs or fields of activity in an enterprise that cannot be covered by only one integrated system, no matter how advanced it is. That is why additional integrated solutions will always be necessary.