|«Till the middle of 2005 we did not have a unified tool to build the centralized system that fits all the corporate reporting process needs. Business users had to use separate reports created with MS Access and MS Excel in addition to built-in reports in other business applications. Also a very limited number of Crystal reports was in use in those days.»
IT expert, head of business applications support department of «SoyuzBaltKomplekt» in 2005-2007
|Under the words «built-in reports in other business applications» Alexey Vishnyakov means the financial reporting programs delivered with iScala and other traditional back-office financial management and accounting applications.|
|Not going into details and without evaluating pros and cons of doing that way, I have to notice that now our information system is in the process of permanent changes and organized as a «star» with the core program (iScala 2.2) at center while other business applications stay around it as «satellites».|
|Like any other traditional ERP system, iScala provides different ways for outputting data:
There are different types that can also be treated as «levels» of available reports:
If we cast a glance at the above-listed «reporting levels», then we might draw attention to the fact that the first 2 of them are conditionally «internal» ones, whereas the second 2 are «external» ones. This means that you necessarily should enter the system in order to receive the first ones (and hence, occupy a workplace therein), whereas in order to receive the second ones, you would have to be possessed of the required access rights, however, you won’t have to enter the system, and moreover, you may not even know how does it’s workplace look like. Considering the fact that an ERP system’s workplace may cost quite a few thousands of Euros, many interesting ideas spring up there… It was just the thoughts that occurred to us, when we faced the challenge of famine of workplaces in the iScala (we had a license for 30 concurrent users then).
|The decision we made proved to be fairly simple and logical:
Division of users based on a principle of «Input/Reception» of information.
|Reporting constitutes a shell enveloping the kernel of a system, like the atmosphere of a planet. The number of data generators (those performing operations of its primary input or changing, transformation) is considerably lower than the number of consumers.|
Reporting — top of an iceberg!
|«When selecting the systems for generation and distribution of reports we compared opportunities of a number of existing systems. The final decision was made based on the criterion of optimal functionality-cost.»
IT expert, head of business applications support department of «SoyuzBaltKomplekt» in 2005-2007
|One of essential factors, while selecting the system for construction of reports was the use of MS SQL Server database in our company, since the ERP-system iScala 2.2 operates on the basis of this product.
Alexey Vishnyakov has done an enormous work getting acquainted and comparing such systems for construction of reports, like Microsoft SQL Server 2000 Reporting Services, Business Objects Reporting Solution (Crystal Reports and Crystal Enterprise), iScala Business Intelligence Server, Active Reports.NET, IntelliVIEW reports construction tool, and Synaptrics web-platform. In the course of the analysis, systems’ capabilities, as well as the cost of introduction and support of the solutions were compared.
|Based on summary parameters, the solution based on Microsoft SQL Server 2000 Reporting Services proved to be the most attractive to the company. One should especially emphasize, that the users of Microsoft SQL Server 2000 don’t have to buy the Reporting Services component. You can obtain it free-of-charge from your supplier, from whom you purchased the license for Microsoft SQL Server 2000 itself. In Microsoft SQL Server 2005 this is a built-in service already. Microsoft SQL Server Reporting Services has met all the above requirements. Hereunder, I shall try to present practical examples of that. In fact, we proved to be the pioneers in using the new technology, having introduced the Microsoft SQL Server 2000 Reporting Services first (which has been mentioned in the Microsoft website), and upon release of Microsoft SQL Server 2005, we switched to the same. The product is developing, and our experience of working with this technology is reaching 2 years at present. Hopefully, from this article you would get an idea of what can be obtained from iScala with the help of this technology.|
|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:
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:
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:
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:
iScala Business documents (invoices, delivery notes and so on)
It is necessary to specifically tell about documents printing in iScala. 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 iScala. 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 iScala 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 iScala 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 iScala reports and documents with reports based on the technology of MS SQL Server Reporting Services?
Alexey Vasilyev. March-April-May 2007.
Other procedures and articles in English:
- Структура таблиц / Table structure Epicor iScala 3.2 (x64) — 3.2.0317
- AFR: Как сделать расчёт значения в столбце зависимым от номера строки? :: AFR: How can I calculate the value in a column dependent on the line number?
- Query execution failed for dataset ‘DataSet0’
- Структура таблиц / Table structure Epicor iScala 3.1 — 3.1.0511
- SQL Server: OLE Automation Procedures
- Знаете ли Вы, что? Новая фишка в последней версии AFR: перенумерация строк :: Do you know that? The new feature in the latest version of AFR: renumbering rows
- Multi Level Approvals for Requisitions: How it works?
- MSRS channel — what to use instead in case of very old version of Scala/iScala?
- How to add a document template for MSRS Output Channel
- Заявление / Memorandum
- Deploy existing AFR reports on another server
- How to delimit user access on the Reporting Server?
- Standard steps when working with the «Purchase Order» module
- Изменение структуры таблиц iScala 3.1 по сравнению с iScala 3.0 FSP4 / Table structure changes between iScala 3.0 FSP4 and iScala 3.1
- История изменения структуры таблиц разных версий iScala / Tables structure changes history from iScala 2.2 SR2 to iScala 3.0 FSP4
- Процесс создания отчёта фиксированной формы с помощью Epicor AFR :: The process of creating a fixed-form report using Epicor AFR
- The most common mistake when working with AFR reports
- Структура таблиц БД / DB Tables Structure: Epicor iScala 3.0 FSP4 — 3.0.4267
- AFR: что это такое, из чего состоит, с чем взаимодействует? :: AFR: what is it, what does it consist of, what it interacts with?
- Usage of external tables with primary data. How to design Daily Budget Report.
- How can I create an AFR report in 2 currencies using fixed rate?
- How to create AFR report with daily budgets from iScala?
- Reporting tools usage survey
- Sent PO electronically to Supplier’s FTP site
- Requisition Management module quick user guide
- What can you get from your iScala by using new technologies?