• Business documents (invoices, delivery notes and so on)
• Queries (information screens without printing functionality)
• Standard reports that allow both viewing and printing

• Standard reports. In these reports information is provided in predefined form only with no possibility for users or developers to change the report layout. The effective usage of such type of reports requires good user experience on combining input report options and parameters that limit data volume to be received and define grouping and sorting of records within a report. Some of these reports can be useful but others are not. In other case, users can be satisfied with the report’s contents but not how it looks like.
• Partially customized reports. In Scala, they are reports in the Statistics module and the internal report generator of the General Ledger module (GL). Why I said «partially»? It is because all available data sets are predefined and presentation logic is hard-coded in the program, so, other data sources cannot be used there and a user has not so many options when changing the appearance of the report.
• Specially developed external reporting programs. These are standalone software products developed by the software vendor, consultants or 3rd-party vendors. Using such a tool, users can often get started creating relevant financial reports right away using the building-block approach and auto-build functionality, without much help from IT resources or other technically-minded personnel. Not to mention, these products rely on their own logic and also have various functional limitations.
• External reports. It is any report developed with one of available programs having report building functionality or programming capabilities and can connect to various data sources. These reports are highly flexible and customizable. Possibility of a particular report creation is limited only by availability of correct data and requires the developer to be experienced in functionality and data structure of the base ERP system.

Division of users based on a principle of «Input/Reception» of information.

Reporting - top of an iceberg!

Our requirements:

• Common data storage for both business data and reports
• Ease of creation and maintenance
• Ease of dissemination/publishing
• On-demand report generation and subscriptions
• Access rights management for individual users and groups
• Common technology and runtime environment
• Thin-client remote access to reports
• Report usage analysis
• Combining different data sources into one report
• Reports that not only read data but perform actions

So, let's consider each of the items of our requirements, which we formulated when selecting the system for creation of our «Reporting Centre» on base thereof.

Common data storage for both business data and reports

Nothing to discuss! It's stored in MS SQL Server Database of course!!

Ease of creation and maintenance

To illustrate, we shall address the report on generating new reports:

As you can see, 18 new reports have been generated for the last 2 weeks, i.e. about two reports per working day on the average. It is clear, that in order to have a more adequate statistics, a bigger time sampling would be required; I’m presenting an example of fortnight sampling solely for the reasons of a nicer diagram.

Easy to distribute

Other external reports require a «starter-programme» to be available in a user’s computer, or at least a reports browser. I.e. if a report was made in Access, the user would have to have Access, if it was Crystal, then the installed components of Crystal would be required. But nothing is required here in addition, only Internet Explorer, which is, by definition, is available in users’ computers operating under OS Windows (and this OS, too, by definition, is available in the computer, since we are talking about ERP system iScala already used by the company).

On-demand report generation and subscriptions

As much as you like! To illustrate that, we shall address the report of «audit log» which starts automatically at 23:30 every day, and is delivered as a message to accountant who monitors the process of changing of the stock items, customers, suppliers records, etc.:

Access rights management for individual users and groups

There’s nothing to comment here, just look at the picture:

That is, you simply put linked reports in certain folders, and assign the rights to these folders to a respective group. And that is all!

Common technology and runtime environment

Speaking of a uniform mechanism, I mean, in the first place, that there won’t be variegated reports; for example, a user has reports written in Access, another one – in Crystal, the third user – in Delphi, the fourth one – in something else. Thus, to support such a variegated «menagerie», experts of various profiles would be required, and the components developed in one environment could not be used in another one.

Thin-client remote access to reports

So this is a «fine client»! There is an html page at the output of reporting server. Our reports are used by employees in three offices, one of which is 65 km apart. In addition to the users inside the network, the reporting can be opened «outwards» (certainly, subject to solving all the necessary issues associated with security. Here integration with Share Point can be used).

Report usage analysis

Here it would be necessary to get back to the issue of a uniform residence. In fact, all the reports and information about their calling is stored in SQL Server! And, hence, reports can also be generated based on this information, for example, such as:

«Trend of the number of calls to the Reporting Centre by days»:

or «Full list of reports by usage»:

or «Users’ statistics»:

or «Statistics of the number of folders, reports, linked reports and other files of the «Reporting Centre»»:

Combining different data sources into one report

We use enquiries to various databases on various servers. Besides it is possible to address different types of stored data, thus, for example, our telephone directory is a report, too, inquiring about the information from account of the user of a computer network. There are reports where the data is requested from tables of .dbf files.

Reports that not only read data but perform actions

This theme is closely related to the theme of a uniform mechanism. It proved very convenient not to create a separate interface for starting the various utilities for data copying, communication and changing, but use the mechanism of report for starting of stored procedure. For example, we make it when communicating data to a remote server (for their subsequent use in a local program of production planning). Before sending the data, a report is printed out, checking availability of the necessary quantity of plastic for manufacturing of the ordered articles. If even for a single position in the order there is no required quantity of plastic, nothing occurs. The salesperson discusses situation with the customer, modifications are made to the order, or its execution is postponed.

If there is enough plastic for manufacturing all that was ordered, then a link appears below, having pressed on which, the user starts the data transfer initialization mechanism:

Scala Business documents (invoices, delivery notes and so on)

It is necessary to specifically tell about documents printing in Scala. To tell the truth, it was always a bottleneck. The technology that is used for printing of documents, even with the account of every sort and kind of its modifications, is still a rarity. I shall repeat, earlier this was, like the reporting, a bottleneck of the Scala. Then it was. But now it’s not like that. The Microsoft SQL Server 2005 Reporting Services technology has left this bottleneck in the past. We have also realised all the documents based on new technology, and now we have much more variants of these, than it is provided in Scala as standard.

You can have a look at the other samples of reports and documents here (sorry, only in Russian as yet).

So, why did we decide to stop using standard Scala reports and documents and choose the MS SQL Server Reporting Services as a unified tool for building our corporate reporting system?

I think, now you can easily answer this question. And I have absolutely no doubt you could do the same things that we already did. It’s a matter of your choice and there are no significant technical issues on the way.

And now we shall try to answer the following question:

What can you get having replaced standard Scala reports and documents with reports based on the technology of MS SQL Server Reporting Services?

• Save Scala workplaces due to users who do not input information in it, but only use the same in the form of reports, since they won’t have to enter Scala any more and occupy a workplace therein.
• User-friendlier reports which would give the information he requires, and exactly in the form which is necessary. As against Scala standard reports, you could also create reports combining data from the various sources.
• A convenient uniform technology of management of creation, storage, distribution, delivery of reports and access rights management for individual users and groups, and, as consequence, absence of necessity to support the various technologies in case of use of the reports realised in different systems of generation of the same.
• Possibility of starting the report as per the schedule. The result can develop in the form of «snapshot» for the moment of release into a separate folder, or be sent as a message to your mail box.
• Possibility of access to reports through the Intranet and/or the Internet. To view the report, no additional components are needed to be available in the computer of a user, Internet Explorer is only required.
• An up-to-date reports generating technology, allowing to use the entire set of tools, such as conditional formatting, a possibility to «come down» or, on the contrary, «rise up» from one report to other levels of details (so-called drill down and drill up) and many other things.
• A possibility of use of reports as the means allowing not only view the information, but also start any mechanism, for example, initiate the process of data transfer into another system.
• Possibility of analysis of the usage of reports, i.e.:
• who used, when, which report;
• which reports are used more often than others;
• who from the users is the «champion» in viewing reports;
• who from the users, on the contrary, strived for getting a somewhat complex report from the IT department, but viewed the same only once.
• Possibility of creating documents not provided in Scala as standard.

Alexey Vasilyev. March-April-May 2007.