DE102010064167A1 - Method for generating dynamic report for representing commercial data for selected articles in e.g. database management system oracle in computer system, involves generating report by using report template and adjusted database statements - Google Patents

Abstract

The method involves providing a configuration table (4) with configuration gaps, and recording articles in one of the gaps. Positions of the articles are recorded in another gap. A configuration program (61) comprising database statements (DA) e.g. structured query language command, is provided for accessing commercial data of a central database (2) and recordings (ET) in the gaps. The database statements in the program are adjusted based on the recordings in the gaps. A dynamic report is automatically generated by using a report template (7) and the adjusted database statements. Independent claims are also included for the following: (1) a computer system for generating dynamic report (2) a configuration program for executing a method for generating dynamic report.

Description

Technical field of the invention

The invention relates to a method and system for generating dynamic reports and to a corresponding configuration program for carrying out the method in the system.

State of the art

One task of a centralized database, such as an MS Access database, for distribution or direct shipping is to provide management reports to control distribution channels. The information is usually queried by small programs as instructions, such as SQL statements, and written to a template, such as an MS Excel template. For example, a VBA method can be used for the entries in the template. VBA here refers to "Visual Basic for Applications" and is object-oriented. This means objects such. Sheets, cells or whole areas can be treated in a certain way, the so-called methods and properties. This is usually followed by postprocessing by VBA in Excel as well as dispatch and filing of the report.

The templates and the SQL statements of the reports are essentially determined by the sales channel's product portfolio. As part of a modification, therefore, the MS Excel templates and the static SQL statements must be adapted to a changed product portfolio. This is done by the developers of the database and requires a retrofit and revision cycle for the user. This means an increased effort for any change or adaptation of a report to the wishes and needs of the user, usually the management to run the sales organization. Therefore, it would be desirable if the creation of reports could be adapted by users, such as managers, without much effort and without special software knowledge.

Summary of the invention

It is an object of the present invention to provide a method by which the creation of reports can be adapted by the users without much effort and without special software knowledge.

• – Bereitstellen einer Konfigurationstabelle mit mehreren Konfigurationsspalten, auf die ein Konfigurationsprogramm zugreifen kann; Eintragen mehrerer in dem dynamischen Bericht abzubildender Artikel in eine erste Konfigurationsspalte und eine oder mehrere Positionen zu den jeweiligen ausgewählten Artikeln in eine zweite Konfigurationsspalte;
• – Bereitstellen eines Konfigurationsprogramms umfassend mehrere anzupassende Datenbankanweisungen, das geeignet ist, auf die kommerziellen Daten der Datenbank und auf die Eintragungen in der Konfigurationstabelle zum Anpassen der Datenbankanweisungen zuzugreifen;
• – Anpassen der Datenbankanweisungen im Konfigurationsprogramm gemäß der Eintragungen in den Konfigurationsspalten der Konfigurationstabelle;
• – nachfolgendes automatisches Erzeugen des dynamischen Berichts mittels einer Berichtsvorlage und der angepassten Datenbankanweisungen für die Artikel, zu denen in der Konfigurationstabelle mindestens eine Position eingetragen wurde.

This task is solved by a method for creating dynamic reports for displaying commercial data for selected articles comprising the steps

• Providing a configuration table having a plurality of configuration columns accessible by a configuration program; Entering a plurality of articles to be mapped in the dynamic report into a first configuration column and one or more locations to the respective selected articles into a second configuration column;
• Providing a configuration program comprising a plurality of database instructions to be adapted, which is adapted to access the commercial data of the database and the entries in the configuration table for adjusting the database instructions;
• - adjusting the database statements in the configuration program according to the entries in the configuration columns of the configuration table;
• - subsequent automatic generation of the dynamic report by means of a report template and the adapted database instructions for the articles for which at least one item has been entered in the configuration table.

In the method according to the invention, no static but variable database instructions are used. The method according to the invention allows reports to be generated dynamically. By generating the concrete database statements only after accessing the configuration table based on the entries in the configuration table, the report can be customized. For example, the configuration table is adjusted to the needs of the user prior to each report creation. A changed configuration table results in a changed report compared to the last report creation. In this respect, the report produced by the method according to the invention is referred to as a dynamic report, since a report creation of multiple reports at different times with the same configuration program does not necessarily lead to identical reports, since in the meantime the entries in the configuration table may have been changed. Thus, for example, reports can be easily adapted to a changed product portfolio by using the in the Changed configuration table listed items, removed some items and added other new items. The length (number of lines) of the configuration table may be different in various embodiments. The configuration table is available, for example, in the database and can be edited with a corresponding access form. In an alternative embodiment, the configuration table can also be exported to Excel in Excel spreadsheet format, edited with Excel and imported back again. In this way, the configuration table could in principle be edited by any computer on which MS-Excel is installed. The configuration table can be changed by manual entry or by using an Access form that creates the configuration table. Since the configuration table is a simple table in whose columns only position information (and possibly further information) must be entered, these changes can be made by anyone without programming knowledge. Such position information can be entered in the table in the form of numbers which numerically determine the positions in the report, for example. These entries can be entered in the configuration table, for example via a computer keyboard or other suitable means of entry. This type of registration does not require any special knowledge and can be done by anyone. Thus, no developers of the database for retrofitting and revision cycle are required by the user. In one embodiment, access to the configuration table for entering changes to a particular group of people may be limited by means of an access authorization.

The term "report" here refers to all types of documents which at least partially provide commercial data in a clear form for the control and / or evaluation of operations. Creation is the process by which the report is generated from a set of data that includes commercial data. The term "commercial data" here refers to all types of data that are associated with the purchase, sale, distribution, transportation, etc. of articles these articles. The term "article" here refers to all types of products that are commercialized in the operations.

For example, the configuration program refers to a source code that includes, among other things, database statements that can be executed variably due to the entries in the configuration table. The designations "first configuration column", "second configuration column" and optionally further configuration columns do not denote the exact order of the columns in the configuration table. For example, the order could be first column - third column - second column. Here, the entries are assigned to the respective article line by line. The configuration columns may also be arranged in any other permutation. The term "column" refers to a systematic arrangement of the entries in a two-dimensional scheme. Accordingly, the entries would then be assigned to the respective article column by column. The term "configuration column" in the present invention includes both possibilities described above.

The report template hereby describes an external format for the appearance of the report, in particular in what form the commercial data should be presented at all. The report template can specify which commercial data is displayed in which context. For example, the report template can be a list to fill, for example in MS Excel format. The report template does not define a rigid framework for the report, so the report template should also be considered dynamic. For example, the report template may specify desired business metrics line by line at which the corresponding commercial data is entered in the report in accordance with the claimed method.

The database can be any suitable database for the execution of this method. A database usually consists of two parts (a) of the management software, called database management system (DBMS) and (b) the amount of data to be managed, the actual database (DB). The management software internally organizes the structured storage of data and controls all read and write accesses to the database. For querying and managing the data, a database system offers a database language. Such a database language is for example the "Structured Query Language (SQL)". It is a database language for defining, querying and manipulating data in relational databases. SQL is standardized by ANSI and ISO and is supported by almost all common database systems, such as DBMS Oracle or IBM DB2 / UDB.

In one embodiment, the order of commercial data presented to the articles in the dynamic report is determined by the registered positions for the articles. When defining in order, gaps in the entered positions can be ignored when creating the dynamic report. Thus, one needs for a desired other order of commercial Data of the articles in the report do not exchange any lines in the configuration table, but only a different position information to the respective articles in the second configuration column to enter.

In one embodiment, registering a same item for multiple items bundles the commercial data for those items in the dynamic report. Bundling data can, for example, enable the presentation of commercial data for a generic term. In a preferred embodiment, therefore, the bundling of the commercial data is cumulative.

In one embodiment, the method further includes the step of entering an identifier to the respective article to designate the article in the dynamic report into a third configuration column. This identifier allows, for example, an assignment of different articles to a generic term. The identifier can correspond, for example, to this article generic term.

In one embodiment, when bundling the commercial data, only one identifier for these items is displayed in the dynamic report, preferably the identifier of the first item from the group of items having the same position. In the case of article terms as identifiers, when bundling the data for articles into a generic term, only this generic term would appear in the report as the identifier of the bundled article. Furthermore, it would be inexpedient for reasons of clarity, if in a bundling of commercial data of several articles with possibly different identifiers all identifiers are given together in the report.

In one embodiment, a missing entry of the item to an item results in the commercial data for that item not being displayed in the dynamic report. Thus, articles that are not desired in the report can be hidden from the report simply by leaving the position field in the second column of the configuration table for that particular article. Based on the existing and non-existent entries in the second column, the configuration table makes it easy to check which items should be included in the report and which items should not.

In one embodiment, multiple registered positions result in the presentation of the same commercial data of the article in question at various locations in the dynamic report. If the report is intended to quantify different commercial processes, it may be necessary to present the same commercial data in different places in the report, for example in a different context (eg in different bundles). Registering multiple items for an item allows this.

In one embodiment, the database statement is an SQL statement. Most database systems in use today implement more or less large parts of the SQL language standard. This makes it possible to create application programs that are independent of the database system used. In the pre-SQL time, this was only possible with the system of compatible interfaces. Many SQL implementations also offer vendor-specific extensions that do not conform to the standard language scope.

In one embodiment, the report template is an MS Excel template. Excel templates are easy to customize. In addition, the data contained in an Excel template can easily be used to generate graphics for / in the report and possibly also be displayed in the report.

In one embodiment, the customized database statements are intended to transfer the commercial data from a row-by-row mapping of the data to an article in the database into a column-by-column mapping in the dynamic report. The column-by-column representation of commercial data for an article allows a simple overview of a portfolio of articles for a particular business parameter. Business parameters designate, for example, sales, profit quantities (for articles) or other such quantities. The commercial data of different articles are then assigned line by line to the business parameters.

The invention further relates to a computer system for generating dynamic reports for displaying commercial data for selected articles comprising at least one database for storing commercial data for articles, at least one input unit for entering a plurality of articles to be imaged in the dynamic report into a first configuration column a configuration table and one or more positions to the respective selected articles in a second configuration column of the configuration table, an output unit for outputting the generated report and at least one working unit with a configuration program comprising a plurality to be adapted A database statement that accesses the commercial data of the database and the entries in the configuration table for customizing the database statements and that generates the dynamic report for the articles in the configuration table with at least one registered position using a report template and the customized database statements. The entry of several items to an article could for example be done by the article in question is entered multiple times in the configuration table and in the second configuration column each one of the several items desired for this article. For example, items 2 and 4 should be entered for an article A. Then article A would appear twice in the configuration table, with position 2 at the first entry of article A and position 4 at the second entry of article A respectively being entered in the second configuration column.

The computer system can be a local computer system or a branched computer system consisting of several interconnected servers. The connection of the components of the computer system can be made by means of data cable or wireless data connection. An input unit may be any component capable of inputting data to a computer system, for example, an input unit is a computer keyboard. For example, an output device may be a printer, a screen, or a folder on a data store where the report is readable. A work item refers to any type of component that is suitable for executing computer programs, such as a processor.

In one embodiment, the configuration program is intended to bundle the commercial data for those items in the dynamic report when registering a same item for multiple items.

In a further embodiment, the configuration program is provided to display the commercial data of the article in question at various locations in the dynamic report for several items entered for an article.

The invention further relates to a configuration program for execution in the method according to the invention and in a computer system according to the invention.

Brief description of the drawings

These and other aspects of the present invention are illustrated in detail in the drawings.

1 : An embodiment of the method according to the invention in a system according to the invention.

2 : Transfer the data associated with the items to the report according to their location in the configuration table.

Detailed description of embodiments

1 shows an embodiment of the method for creating dynamic reports B for displaying commercial data for selected articles AR1, AR2, AR3, AR4. This will be a configuration table 4 provided with a plurality of configuration columns S1, S2, S3 into which by means of an input means 3 (For example, a computer keyboard) to the images to be imaged in the dynamic report B in a first configuration column S1 multiple selectable articles AR1, AR2, AR3, AR4 and in a second configuration column S3 one or more positions P1, P2, P3, P4 registered ET. The entry ET may additionally include inputting an identifier BZ1, BZ2 to the respective articles AR1, AR2, AR3, AR4 for designating the articles AR1, AR2, AR3 in the dynamic report B into a third configuration column S2. The positions P1, P2, P3, P4 may be numbers, for example. The configuration program 61 includes several database statements DA to be adapted, for example SQL statement, and is suitable for the commercial data of the database 2 and the entries ET in the configuration table 4 to adjust A of the database statements DA Z2, Z4. After access, the database statements DA are in the configuration program 61 according to the entries ET in the configuration columns S1, S3 of the configuration table 4 adapted so that in the subsequent automatic generation ER of the dynamic report B the commercial data for the articles AR1, AR2, AR3 in the configuration table 4 with at least one registered position P1, P2, P3, P4 according to the business key figures defined in the report template, for example an MS Excel template, at the desired location in dynamic report B are presented. This is where the computer system leads 1 this procedure by having at least one database 2 for storing commercial data for articles AR1, AR2, AR3, AR4 (for example, a DBMS Oracle or IBM DB2 / UDB database), at least one input device 3 for entering ET items, identifiers and items in the configuration table 4 , an output unit 5 to the output AG of the created report B and at least one work unit 6 with the configuration program installed on it 61 includes. The adapted database statements DA thereby transfer the commercial data from a row-by-row assignment of the data to an article AR1, AR2, AR3, AR4 in the database 2 into a column-wise assignment in the dynamic report B.

2 schematically shows the transfer of the commercial data associated with the articles AR1, AR2, AR3 according to their position P1, P2, P3, P4 in the configuration table 4 from the database (not explicitly shown here) into the dynamic report B. The articles AR1, AR2, AR3 and AR4 are in the configuration table 4 in the first configuration column S1, all of these articles have identifiers BZ1, BZ2, the identifiers BZ1 and BZ2 being the same for the articles AR1 and AR2, and AR3 and AR4, respectively. In a further configuration column S2, the positions P1, P2, P3, P4 are entered, according to the report B, the commercial data to the articles.

AR1, AR2, AR3 are displayed accordingly. Here, only 1 position is entered in the configuration column S2 per line. If several items are to be entered into the configuration column S2 for an article, the article may, for example, be present multiple times in the table, with its positions in column S2 differing in each case. A concrete example of articles, identifiers and positions is given in the following table: items identifiers position Package brand 10 kg Paketmarken 1 Package brand 10 kg Paketmarken 2 Package brand 20 kg Paketmarken 1 Package brand 20 kg Paketmarken 3 Päckchenmarke Päckchenmarke 4

The entry ET of the same position P1 for the articles AR1 (parcel label 10 kg) and AR2 (parcel label 20 kg) leads to a bundling of the commercial data for these articles AR1, AR2 in the dynamic report at the first position P1 under the identifier BZ1 (parcel labels ). In position P2, only the commercial data of article AR1 (10 kg package mark) and in item P3 the commercial data of article AR2 (20 kg package mark) are reported in report B. In this example, the report also contains a bundling of the commercial data of the articles AR1 and AR2 as parcel labels as well as their separate representation for the two articles AR1 and AR2 under the article names Paketmarke 10 kg and Paketmarke 20 kg. At the fourth position P4 then the commercial data of the article AR3 are displayed. The presentation of the commercial data is carried out according to the economic parameters K1, K2, K3. In this case, the bundled data can be represented cumulatively, for example.

The number of articles, identifiers, positions, characteristics in 2 is only an example and may vary depending on the application. The selection of the names of the commercial data in the report with the identifier or with the article name can be made, for example, by the existence of a single position or a common position of several articles. If a position is only 1 × in the configuration column, the product name is used as the name in the report. If the same position is entered in the configuration column for several articles, the identifier is used for designation in the report.

When bundling the commercial data in the first position P1, the dynamic report B shows only the identifier BZ1 for these articles AR1, AR2. For example, according to the above table, in the first position one wants only the common one. Sales of parcel brands, ie sales for the parcel brands 10 kg plus sales for the parcel brands 20 kg. In the following two items, the turnover of the two different parcel labels should be displayed separately. In this respect, it is expedient to use a generic term for the articles as an identifier. For the description of the commercial data in the report, of course, the article name itself can be used. When bundling commercial data for articles with different article names and identifiers, for example, the identifier BZ1 of the first article AR1 from the group of articles AR1, AR2 with the same position P1 can be included in the report B. Alternatively, all identifiers can be specified, but this is only clear with a small number of bundled articles.

The missing entry of an item to the article AR4 results in that the commercial data for this article AR4 is not displayed in the dynamic report B. There may be different reasons for releasing or not posting a position for certain items. In the case of several registered positions P1, P2, P3, the same commercial data of the relevant article AR1, AR2 are displayed at different points in the dynamic report B, as already described for the positions P1-P3. Here, the order of the commercial data to be written in the dynamic report B to the articles AR1, AR2, AR3 by the position P1, P2, P3, P4 becomes the articles AR1, AR2, AR3 in the configuration table 4 established. The commercial data in Report B is in 2 shown schematically as dots, as well as other entries in the configuration table 4 ,

The detailed description of the invention, in this section and in the figures, is to be understood as an example of possible embodiments within the scope of the invention and therefore not restrictive. Therefore, all statements are only to be understood as an example of certain embodiments.

Alternative embodiments that may be considered by those skilled in the art within the scope of the present invention are also included within the scope of the present invention. In the claims, terms such as "a" include the plural. Reference signs indicated in the claims are not to be construed restrictively.

LIST OF REFERENCE NUMBERS

1

Embodiment of the computer system according to the invention

2

Database

3

input unit

4

configuration table

5

output unit

6

work unit

61

configuration Utility

7

report template

A

Customizing the database statement DA

AG

Output the report

AR1-AR3

items

BZ1-BZ2

identifiers

B

dynamic report

THERE

Database Statement

HE

Creation of the dynamic report

ET

Entry / entry in the configuration columns of the configuration table

K1-K3

Business parameters

P1-P4

positions

S1-S3

Configuration columns in the configuration table

Claims (15)

A method for creating dynamic reports (B) for displaying commercial data for selected articles (AR1, AR2, AR3, AR4) comprising the steps of providing a configuration table ( 4 ) having a plurality of configuration columns (S1, S2, S3); - Entering (ET) a plurality of articles to be imaged in the dynamic report (B) (AR1, AR2, AR3) into a first configuration column (S1) and one or more positions (P1, P2, P3, P4) to the respective selected articles (AR1 , AR2, AR3) into a second configuration column (S3); - Providing a configuration program ( 61 ) comprising a plurality of database instructions (DA) to be adapted to the commercial data of the database ( 2 ) and the entries (ET) in the configuration table ( 4 to access (A) the database instructions (DA) (Z2, Z4); - adapt (A) the database statements (DA) in the configuration program ( 61 ) according to the entries (ET) in the configuration columns (S1, S3) of the configuration table ( 4 ); - subsequent automatic generation (ER) of the dynamic report (B) by means of a report template ( 7 ) and the customized database statements (DA) for the articles (AR1, AR2, AR3) to which in the configuration table ( 4 ) at least one position (P1, P2, P3, P4) has been entered The method according to claim 1, characterized in that the order of the commercial data presented in the dynamic report (B) to the articles (AR1, AR2, AR3) by the registered positions (P1, P2, P3, P4) for the articles ( AR1, AR2, AR3), The method according to claim 2, characterized in that the entry (ET) of an identical item (P1) for several items leads to a bundling of the commercial data for these items (AR1, AR2) in the dynamic report (B). The method according to claim 3, characterized in that the bundling of the commercial data takes place cumulatively. The method of claim 3 or 4, characterized in that the method further comprises the step of entering (ET) an identifier (BZ1, BZ2, BZ3) to the respective article (AR1, AR2, AR3, AR4) to designate the article ( AR1, AR2, AR3, AR4) in the dynamic report (B) into a third configuration column (S2) The method according to claim 5, characterized in that in the bundling of the commercial data only one identifier (BZ1) for these articles (AR1, AR2) is represented in the dynamic report (B), preferably the identifier (BZ1) of the first article (AR1 ) from the group of articles (AR1, AR2) with the same position (P1). The method according to one of the preceding claims, characterized in that a missing entry of the item to an article (AR4) results in that the commercial data for this article (AR4) is not displayed in the dynamic report (B). The method according to one of the preceding claims, characterized in that a plurality of registered positions (P1, P2, P3) for displaying the same commercial data of the article in question (AR1, AR2) at different points in the dynamic report (B) lead. The method according to one of the preceding claims, characterized in that the database statement (DA) is an SQL statement. The method according to one of the preceding claims, characterized in that the report template ( 7 ) is an MS Excel template. The method according to one of the preceding. Claims, characterized in that the adapted database statements (DA) are provided to transfer the commercial data from a line-by-line assignment of the data to an article (AR1, AR2, AR3, AR4) in the database ( 2 ) into a column-wise assignment in the dynamic report (B). A computer system ( 1 ) for generating (ER) dynamic reports (B) for displaying commercial data for selected articles (AR ', AR2, AR3) comprising at least one database ( 2 ) for storing commercial data for articles (AR1, AR2, AR3, AR4), at least one input unit ( 3 ) for entering (ET) a plurality of articles (AR1, AR2, AR3) to be imaged in the dynamic report (B) into a first configuration column (S1) of a configuration table ( 4 ) and one or more positions (P1, P2, P3, P4) to the respective selected articles (AR1, AR2, AR3) into a second configuration column (S3) of the configuration table ( 4 ), an output unit ( 5 ) for the output (AG) of the prepared report (B) and at least one working unit ( 6 ) with a configuration program ( 61 ) comprising a plurality of database statements (DA) to be adapted which are based on the commercial data of the database ( 2 ) and the entries (ET) in the configuration table ( 4 ) for adapting (A) the database instructions (DA) and that the dynamic report (B) for the articles (AR1, AR2, AR3) in the configuration table ( 4 ) with at least one registered position (P1, P2, P3, P4) by means of a report template ( 7 ) and the customized database statements (DA). The computer system ( 1 ) according to claim 12, characterized in that the configuration program ( 61 ) is intended, when registering (ET) a same position (P1) for several articles (AR1, AR2), to bundle the commercial data for these articles (AR1, AR2) in the dynamic report (B). The computer system ( 1 ) according to claim 12 or 13, characterized in that the configuration program ( 61 ) is provided for several items (P1, P2, P3) for an article (AR1, AR2) represent the commercial data of the article in question (AR1, AR2) at various points in the dynamic report (B). A configuration program ( 61 ) for execution in a method according to claim 1 and in a computer system ( 1 ) according to claim 12.

Images