KR102499832B1 - An online report creation system with query binding function - Google Patents

Abstract

When creating and providing a report on the web by bringing a basic dataset from a database through a query statement, it supports writing a query statement by setting a processing variable, and binding the processing variable to a variable value in the query statement to create a basic dataset. It relates to an online report creation system having a query binding function, which edits a grid object on a report screen on the web, saves information about one grid object in one Excel document, and uses an extended Excel tool a grid editing unit for editing a grid object through editing of an Excel document; a control editing unit that creates and edits a control object on a report screen on the web; When the Excel tool is called, an Excel document that is mapped to the grid object is created, and an Excel document composed of a view area sheet representing the shape of the grid, a data area sheet for storing basic data sets, and a parameter area sheet for setting processing variables. a worksheet initialization unit that creates a document; a processing variable setting unit that sets processing variables on a parameter area sheet of the Excel document; a query statement creation unit that creates a query statement by mapping processing variables on the worksheet to query variables; a second query binding unit provided in the extension module and binding a query variable of the query statement to a variable value of a processing variable; a data set loading unit for loading a basic data set on a worksheet of the Excel document, binding the query statement through the second query binding unit, and loading the bound query statement; a first query binding unit provided in the server and binding a query variable of the query statement to a variable value of a processing variable; Display the contents of the corresponding grid object on the web according to the contents of the Excel document mapped to the grid object, bind the query statement through the first query binding unit, load the basic dataset with the bound query statement, and load a grid display unit for displaying contents of a grid object from a processed basic data set to a processed data set; and a report viewer unit displaying a report composed of a grid object and a control object on the web and displaying the grid object through the grid display unit.
According to the system as described above, by making a query statement using a processing variable and binding the variable value to the processing variable of the query statement during execution, the user can call the basic data set from the database under various conditions, and through this, It is easier to implement interactive functions for the condition of the dataset.

Description

Online report creation system with query binding function { An online report creation system with query binding function }

In the present invention, when a basic data set is imported from a database through a query statement, and a report on the web is created and provided, the query statement is supported by setting a processing variable, and the processing variable is bound to a variable value in the query statement. It relates to an online reporting system with query binding capabilities for importing datasets.

In addition, the present invention supports writing in an Excel tool when creating each grid in a report on the web, supports writing a query statement in an extension module of the Excel tool, sets processing variables on a sheet in the Excel tool, and sets the corresponding processing on the web An online reporting system with query binding functionality supporting mapping of variables to control objects.

In general, among office workers' working hours, the time required for document preparation occupies a high proportion of the total work. Reduction of costs due to the time and manpower required to prepare these documents is an essential part in terms of corporate productivity. To this end, a system that manages and automates tasks in an integrated manner is being introduced.

As an example, a reporting technology for querying and analyzing a database in a web environment has been proposed [Patent Document 1]. According to the prior art, a developer designs a report using a WYSIWYG method and a drag & drop method using data of a database server, the web server registers the designed report, and the report settings Accordingly, necessary data is imported from the database server and a report is created, and the user requests and views the report on a web browser.

In addition, a technique for outputting a report in real time by accessing and integrating data distributed in several places using a web service has also been proposed [Patent Document 2]. In addition, a technique in which an administrator selects table information received from a database on the web to create a mapping table, uses it to generate reports in various formats, and selects and confirms the table information on the web has also been proposed [Patent Document 3].

In addition, a technique for creating a report in the form of Excel on the web has also been proposed [Patent Document 4]. In the prior art, an Excel-type grid must be directly created on the web, and an interface of grid work on the web is provided similarly to that of Excel.

However, since it is difficult to provide various interfaces such as Excel on the web, and interface methods are different for each service, users may be uncomfortable with the interface on the web. That is, the interface on-line is not the same as the interface of the Excel program on-line.

However, users are more familiar with the interface of the (offline) Excel tool installed and used on their computer terminals. Therefore, when a user creates a report on the web, a technology that supports data processing with an offline application such as commercial Excel is required.

Korean Patent Registration No. 10-0497811 (Announced on July 1, 2005) Korean Patent Registration No. 10-0697155 (Announced on March 21, 2007) Korean Patent Publication No. 10-2013-0126012 (published on November 20, 2013) Korean Patent Registration No. 10-1951719 (2019.02.25. Notice)

An object of the present invention is to solve the above-described problems, and when a basic data set is imported from a database through a query statement and a report on the web is created and provided, processing variables are set to support the creation of the query statement , To provide an online report writing system equipped with a query binding function that binds processing variables to variable values in a query statement and brings in a basic data set.

In addition, an object of the present invention is to support writing in an Excel tool when creating each grid in a report on the web, support writing a query statement in an extension module of the Excel tool, and setting processing variables on a sheet in the Excel tool. To provide an online report writing system equipped with a query binding function that supports mapping of processing variables to control objects on the web.

In order to achieve the above object, the present invention relates to an online report creation system having a query binding function performed by an extension module installed in an Excel tool of a user terminal and a server, and editing a grid object on a report screen on the web , a grid editing unit that stores information about one grid object in one Excel document and edits the grid object through editing of the Excel document by an extended Excel tool; a control editing unit that creates and edits a control object on a report screen on the web; When the Excel tool is called, an Excel document that is mapped to the grid object is created, and an Excel document composed of a view area sheet representing the shape of the grid, a data area sheet for storing basic data sets, and a parameter area sheet for setting processing variables. a worksheet initialization unit that creates a document; a processing variable setting unit that sets processing variables on a parameter area sheet of the Excel document; a query statement creation unit that creates a query statement by mapping processing variables on the worksheet to query variables; a second query binding unit provided in the extension module and binding a query variable of the query statement to a variable value of a processing variable; a data set loading unit for loading a basic data set on a worksheet of the Excel document, binding the query statement through the second query binding unit, and loading the bound query statement; a first query binding unit provided in the server and binding a query variable of the query statement to a variable value of a processing variable; Display the contents of the corresponding grid object on the web according to the contents of the Excel document mapped to the grid object, bind the query statement through the first query binding unit, load the basic dataset with the bound query statement, and load a grid display unit for displaying contents of a grid object from a processed basic data set to a processed data set; and a report viewer unit displaying a report composed of a grid object and a control object on the web, and displaying the grid object through the grid display unit, wherein the control editing unit maps the processing variable to the control object, and One query binding unit is characterized in that the variable value is set as an input value of a control object mapped to the processing variable.

In addition, according to the present invention, in an online report creation system having a query binding function, a query variable in the query statement is formed by combining a variable identifier and a name of a processed variable, and the first or second query When the binding unit recognizes a variable identifier in the query statement, it is characterized in that it recognizes a string connected to the variable identifier as a processing variable.

In addition, the present invention is an online report creation system having a query binding function, when a query variable is used in the query statement, a line deletion function is provided, and a symbol or a character indicating a dynamic line deletion function is added to the query variable. and the first or second query binding unit deletes and binds the line in the corresponding query statement when the query variable has a line deletion function, if the variable value of the corresponding processing variable is not present or is an empty value. do.

In addition, according to the present invention, in an online report writing system having a query binding function, when a row executed according to a condition in the query statement is different, a comment statement is inserted in the corresponding row, and a query having a line deletion function in the inserted comment statement. Inserting a variable and having the variable value of the query variable be entered as an empty value or an arbitrary value according to the condition, if it is an empty value, the corresponding row is deleted, and if it is an arbitrary value, the corresponding row is provided to be executed. to be characterized

In addition, in the present invention, in the online report writing system having a query binding function, the processing variable setting unit sets the name of the variable definition cell to the name of the processing variable, and the processing variable is a table to be displayed in a grid on the Excel document. Characterized in that it is used in formulas in the cells of.

In addition, according to the present invention, in an online report creation system having a query binding function, a table on the view area sheet refers to a basic data set input on the data area sheet, and formulas of cells in the table on the view area sheet are referred to. refers to the cell of the data area sheet, and the grid display unit retrieves the basic data set from the database and stores it on the virtual sheet, and when each cell of the table of the grid object is reflected according to a formula, the referenced data area It is characterized in that the content of a corresponding cell on a virtual sheet is reflected instead of the content of a cell of a sheet.

In addition, in the present invention, in an online report creation system having a query binding function, the editing result storage unit stores template information for a table on the view area sheet, a header row structure of the table, and a data row structure of the table. However, the structure of the data row of the table is saved as attribute information including the format and formula of the first data row, and if the data row of the table is a cross row, the format of the first data row and the second data row of the table Characterized in that all of them are stored.

In addition, in the present invention, in an online report creation system having a query binding function, the grid editing unit, control editing unit, report viewer unit, grid display unit, and first query binding unit are provided in the server, and the worksheet initialization unit and processing variables are provided in the server. A setting unit, a dataset loading unit, an editing result storage unit, a second query binding unit, and a query statement creation unit are provided in the extension module.

As described above, according to the online report writing system having a query binding function according to the present invention, a query statement is created using a processing variable and the variable value is bound to the processing variable of the query statement during execution, so that the user can access the data in the database. The basic data set can be loaded under more diverse conditions, and through this, an effect of more easily implementing interactive functions for the conditions of the data set is obtained.

In addition, according to the online report writing system having a query binding function according to the present invention, by mapping processing variables of a query statement to control objects on the web, an effect that can provide an interface that more easily sets query conditions of an underlying dataset. is obtained

1 is a configuration diagram of the entire system for implementing the present invention.
2 is a block diagram of the configuration of an online report creation system having a query binding function according to an embodiment of the present invention.
3 is a diagram illustrating an interface screen for editing a report on the web according to an embodiment of the present invention.
4 is a diagram illustrating a report edited on the web according to an embodiment of the present invention.
5 is a diagram illustrating an initialized Excel document according to an embodiment of the present invention.
6 illustrates a screen for defining processing variables on a parameter area sheet according to an embodiment of the present invention.
7 is a diagram illustrating a screen for loading a dataset according to an embodiment of the present invention.
8 is a diagram illustrating a screen of a data area sheet loaded with a basic data set according to an embodiment of the present invention;
9 is a diagram illustrating an editing result screen of a view area sheet of an Excel document according to an embodiment of the present invention.
10 is an exemplary diagram illustrating template information of a pivot table according to an embodiment of the present invention, and is an exemplary diagram for (a) a pivot table and (b) template information.
11 is an exemplary view of a general query statement prepared according to an embodiment of the present invention.
12 is a table showing types of processing variables of a query statement according to an embodiment of the present invention.
13 is an exemplary view of a query statement using processing variables according to an embodiment of the present invention.
14 is a diagram illustrating a query statement and a bound query statement of a dynamic line deletion function according to an embodiment of the present invention.
15 is an example code for two query statements that differ only in part in content according to an embodiment of the present invention.
16 is an example code in which two query statements are created as conditional statements by programming work according to the prior art.
17 is a code illustrating a conditional statement of a query statement by a dynamic line deletion function according to the first embodiment of the present invention.
18 and 19 are codes illustrating conditional statements of a query statement according to a second embodiment of the present invention.
20 is an exemplary view of formula automatic calculation according to an embodiment of the present invention.

Hereinafter, specific details for the implementation of the present invention will be described according to the drawings.

In addition, in explaining the present invention, the same reference numerals are assigned to the same parts, and the repeated explanation thereof is omitted.

First, the configuration of the entire system for implementing the present invention will be described with reference to FIG.

As shown in FIG. 1, the entire system for implementing the present invention includes an Excel tool 20 and an extension module 60 installed in the user terminal 10, and a creation server that supports report creation by dataset on the web ( 30), and a database or DB server 40 for storing data sets. The user terminal 10 and the creation server 30 perform data communication through a network 80 such as the Internet.

First, the user terminal 10 is a computing terminal having a computing function, such as a personal computer (PC), a laptop computer, a tablet PC, a phablet, a PDA, and a smart phone. The user terminal 10 may have a communication function and transmit/receive data with the creation server 30 through the network 80 .

In addition, the user terminal 10 may install and run applications such as the Excel tool 20 and the extension module 60 .

Next, the Excel tool 20 is an application that performs Excel (a spreadsheet software trademark of Microsoft Corporation) and is a program system that is installed and driven in the user terminal 10 .

The Excel tool 20 provides a normal spreadsheet function and, in particular, provides a WYSIWYG or Drag & Drop interface. That is, the Excel tool 20 displays an Excel sheet on the screen and provides functions for performing normal spreadsheet operations on the Excel sheet.

In addition, the Excel tool 20 creates an Excel document written on an Excel sheet (or worksheet). An Excel document consists of a number of worksheets, and each worksheet is composed of objects, formats, and data (or cells/regions, cell formats, cell formulas, and cell values) created on the sheet.

Preferably, the Excel tool 20 is a spreadsheet application manufactured by Microsoft, and has a trademark of Excel.

In addition, the function of the Excel tool 20 is extended through the extension module 60. At this time, the function-extended Excel tool 20 will be referred to as an extended Excel tool.

Next, the extension module 60 is a program system installed in the computer terminal 10 and added in the form of a plug-in to the Excel tool 20, a function of loading a data set from a database, and a function of setting processing variables of a data set. provide etc.

That is, the extension module 60 is a program system that is added to the Excel tool 20 in a plug-in method or an add-in method and is executed. In particular, the extension module 60 is registered as at least one command (hereinafter referred to as a plug-in command) of the Excel tool 20, and is executed when the corresponding plug-in command is called from the Excel tool 20. As an example, when a plug-in command is registered on a menu on an Excel sheet and a corresponding command is selected from the menu, the extension module 60 is operated. As another example, when a control object is registered as an event operation and a corresponding event occurs, the extension module 60 may be operated.

In particular, the extension module 60 has an initial worksheet creation function, a processing variable setting function, a data set loading function, and the like, and commands for calling each function are registered in the Excel tool 20.

First, when the Excel tool 20 is called, the extension module 60 creates each worksheet representing the view area V, the data area D, and the parameter area P in the Excel document.

The worksheet of the view area (view area sheet) is a worksheet for designing the display form of a grid or table to be displayed on the screen on the web.

In addition, the worksheet of the data area (D) (hereafter referred to as a data area sheet) is a worksheet in which a data set loaded from a database is entered in each cell and stored. At this time, the data set loaded from the database is referred to as the basic data set.

Meanwhile, the dataset displayed on the view area sheet is a dataset processed by referring to the basic dataset. Therefore, the data set displayed on the view area sheet is referred to as a processing data set.

In addition, the worksheet of the parameter area P (hereinafter referred to as parameter area sheet) is a worksheet for setting processing variables of the data set. The processing variable is set as the name of one cell, and the variable value of the processing variable is set as the input value in the cell.

In addition, the extension module 60 provides a function of setting processing variables. At this time, since the name of the processing variable (variable name) is defined as the name of the cell, the cell value entered into the corresponding cell (variable definition cell) becomes the variable value of the corresponding processing variable. Therefore, processing variables on the Excel tool 20 are used as cell names. in other words. The variable value of the processing variable is the value entered in the variable definition field (cell) of the processing variable.

In addition, the extension module 60 supports a function of creating a query statement, loading a basic dataset from a database by the query statement, and storing the load information. In addition, the extension module 60 stores load information of the basic dataset. Basic dataset load information consists of DB connection information for connecting to the database and query statements (SQL, etc.) to be executed in the database.

Next, the creation server 30 is a normal server connected to the network 80 and supports creation of a report on the web, but also supports creation of a report analyzing a data set imported from the database 40 .

A report on the web is composed of a grid object that displays the processed data set and a control object that processes the processed variable of the data set. The creation server 30 provides services for creating grid objects and control objects.

First, the creation server 30 provides grid objects to be created or edited through the extended Excel tool 20, and correspondingly creates or stores an Excel document linked to each grid object. That is, in order to create or edit one grid object, the user creates or edits it in the extended Excel tool 20. In addition, the Excel document created or edited in the extended Excel tool 20 is stored, and is stored in association with the corresponding grid object.

In addition, the editing results stored by the extended Excel tool 20 include templates, dataset load information, and the like, in addition to Excel documents. The template is a data structure that extracts and stores information about the view area sheet of an Excel document, and the data set load information is information for loading a data set (basic data set) from the database, such as DB connection information and query statements.

In addition, when the creation of the Excel document is completed by the extended Excel tool 20, the creation server 30 reads the saved Excel document (or template) and creates or modifies a grid object according to the content defined in the Excel document. .

At this time, the creation server 30 reads the processing variables defined in the parameter area sheet, defines them as processing variables, and sets them. In other words, declare the processing variable to be used in the control object.

In addition, the creation server 30 creates/edits the grid shape of the grid object according to the table shape of the view area sheet of the corresponding Excel document, imports the basic data set, and reflects it to the corresponding grid. In particular, the creation server 30 binds processing variables of the query statement to bring a data set (basic data set) from the database.

Also, the creation server 30 provides an interface to create a control object. Control objects include objects for receiving commands or data, such as buttons, combo boxes, check boxes, and input boxes.

The creation server 30 maps control objects to processing variables. When a control object is mapped to a processing variable, the input value of the corresponding control object is automatically mapped to the variable value of the corresponding processing variable. That is, when the creation server 30 detects an event in which an input value of a control object is updated (input), all objects are updated by binding the variable value of the mapped processing variable to the updated (input) input value. In addition, the creation server 30 binds the processed variable of the query statement to a variable value, and retrieves the data set from the database again with the bound query statement and updates it.

Next, the database 40 is a normal database (DB) for storing datasets, and is provided with a database management system (DBMS) for managing data, and queries operations such as saving, deleting, and searching of datasets. (or query statement). In particular, the database 40 is a commercialized database and performs a data query service using a general query function for processing a dataset.

In particular, the database 40 is a database that stores big data. Also preferably, the database 40 is comprised of a relational database (RDB).

In addition, the database 40 may be implemented as a database directly connected to the creation server 30, or may be implemented as an independent DB server on the Internet.

In particular, the database 40 may be implemented as one server driven by a DBMS. That is, the database 40, as one server, provides a service of viewing/providing a requested data set according to a request of the creation server 30. Therefore, the database and DB server use the same reference number.

Next, an online report creation system equipped with a query binding function according to the present invention will be described with reference to FIG. 2 .

As shown in FIG. 2, the online report writing system 30 having a query binding function according to an embodiment of the present invention includes a screen editing unit 31 providing a report screen design service on the web, and a grid editing grid. An editing unit 32, a control editing unit 33 that edits control objects, a report viewer unit 34 that displays reports composed of grids and control objects on the web, and a grid display unit that displays the contents of grid objects according to Excel documents or templates. (35), a first query binding unit 36 that binds query statements, a worksheet initialization unit 61 that initializes the worksheet of an Excel document, a processing variable setting unit 62 that sets processing variables, and a data set A dataset loading unit 63 for loading, an editing result storage unit 64 for storing editing results, a query statement creation unit 65 for creating query statements, and a second query binding unit 66 for binding query statements. consists of In addition, it may be configured to further include a storage unit 38 for storing necessary data.

At this time, the screen editing unit 31, the grid editing unit 32, the control editing unit 33, the report viewer unit 34, the grid display unit 35, and the first query binding unit 36 are provided in the creation server 30 , Worksheet initialization unit 61, processing variable setting unit 62, data set loading unit 63, editing result storage unit 64, query statement creation unit 65, and second query binding unit 66 It is provided in the extension module 60.

First, the screen editing unit 31 provides a report screen design service on the web.

A report on the web is composed of a grid object displaying a processed data set (processed data set) and a control object mapped to a processed variable to receive the value of the processed variable.

As shown in FIG. 3 , the screen editing unit 31 creates a grid object or a control object using a WYSIWYG method and a drag & drop method, and supports arranging them on the screen.

In addition, the screen editing unit 31 provides a detailed editing service for a grid object or control object through the grid editing unit 32 or the control editing unit 33 .

Next, the grid editing unit 32 edits the grid object, stores and manages information about one grid object in one Excel document, etc., and edits the grid object by editing the Excel document by the extended Excel tool 20. Edit.

That is, the grid editing unit 32 creates or stores an Excel document or the like mapped to each grid object. Excel documents (or editing result information) are composed of Excel documents, templates (grid templates), and data set load information. Template, data set load information, etc. will be referred to as additional information.

The corresponding Excel document is composed of a view area sheet, data area sheet, parameter area sheet, etc., and is an Excel file containing grid object information. A template (or grid template) is a data structure that stores the form, format, formula, etc. of a table (or grid) in a view area sheet. This is to analyze and use the contents of the view area sheet more quickly. In addition, data set load information consists of DB connection information for connecting to the database and query statements (SQL, etc.) to be executed in the database.

In particular, when receiving an editing request for each grid object, the grid editing unit 32 determines whether an Excel document mapped (linked) to the corresponding grid object exists. If present, the extended Excel tool 20 is executed while opening the corresponding Excel document. If it does not exist, a new Excel document is created, and the expanded Excel tool 20 is executed while opening the created Excel document. At this time, the expanded Excel tool 20 or the worksheet initialization unit 61 initializes the corresponding Excel document. In particular, an initial worksheet is created and initialized by the worksheet initialization unit 61 .

In addition, when the creation of the Excel document is completed by the expanded Excel tool 20, the grid editing unit 32 refers to the stored Excel document (or editing result information) and updates the grid object according to the content. At this time, preferably, the content of the grid object is displayed on the screen through the grid display unit 35 according to the content of the Excel document.

Next, the control editing unit 33 provides an interface to edit the control object.

Control objects are objects used to express web reports, such as buttons, combo boxes, shapes, and text boxes. In particular, control objects include objects for receiving commands or data, such as buttons, combo boxes, check boxes, and input boxes. At this time, the input command or data will be referred to as an input value of the control object.

The control editing unit 33 reads processing variable information from an Excel document of the grid object and declares processing variables (that is, defines and sets processing variables). That is, in the Excel document of the grid object, processing variables are declared in the parameter area sheet. The control editor 33 reads the processing variables stored in the Excel document of each grid object and declares the processing variables to be used in the control object.

In addition, the control editing unit 33 provides a function of mapping control objects to processing variables. That is, the user can map a specific processing variable to a specific control object. Preferably, by defining the control object as the name of the process variable, the process variable and the control object are mapped.

When a control object is mapped to a processing variable, the input value of the corresponding control object is automatically mapped to the variable value of the corresponding processing variable.

In addition, when the update (input) of the input value of the control object is detected by the report viewer view 34 (update event), the variable value of the mapped processing variable is bound to the updated (input) input value. All objects are updated. In addition, when an update event is detected, the query statement of the dataset load information is bound to the variable value of the processed variable by the report viewer view 34, and the basic dataset is brought back to the bound query statement.

Next, the report viewer unit 34 outputs the edited report on the screen.

That is, the report viewer unit 34 outputs the contents of the control object and the grid object on the screen, and when an input event (update event) of the input value occurs in the control object, the variable value of the processing variable mapped to the control object It is updated to update all objects on the screen. In particular, the query statement is bound to the variable value of the mapped processing variable, and the dataset is also updated by the bound query statement. That is, the underlying dataset is retrieved from the database with the bound query statement and updated.

Specifically, the report viewer unit 34 outputs the contents of each grid object, and reads and outputs the contents defined in the Excel document of the corresponding grid object. At this time, the report viewer unit 34 edits the shape of the grid according to the contents of the Excel document or the like (template) through the grid display unit 35, reflects the processed data set on the corresponding grid, and outputs it.

In addition, the report viewer view 34 detects an event in which an input value of a control object is updated (input). And when an event is detected, all objects are updated by binding the variable value of the mapped processing variable to the updated (input) input value.

At this time, the report viewer view 34 binds the query statement to the variable value of the processed variable through the first query binding unit 36, and retrieves and updates the basic data set by the bound query statement.

Preferably, report viewer view 34 includes a viewer that displays a grid. The grid viewer is composed of DOM (Document Object Model)-based components based on web format (HTML 5.0 or higher) or Canvas. Therefore, it supports extremely fast rendering speed.

4 illustrates displaying a report on the web.

Next, the first query binding unit 36 sets the variable value of the processing variable mapped to the control object as the input value of the corresponding control object, and binds the processing variable to the variable value (or input value) in the query statement. Generates a query statement (bound query statement).

The binding of query statements will be described in more detail while describing the second query binding unit 66 below. A process of binding a query statement by the first query binding unit 36 is the same as a binding process of the second query binding unit 66 .

Next, the worksheet initialization unit 61 initializes the worksheet of the Excel document.

As shown in FIG. 5, the worksheet initialization unit 61 creates each worksheet representing a view area (V), a data area (D), and a parameter area (P) in an Excel document.

The worksheet initialization unit 61 is executed when the Excel tool 20 is called.

The worksheet of the view area (hereinafter referred to as the view area sheet) is a worksheet for designing the display form of a grid or table to be displayed on the screen on the web. The user designs a table or the like on the view area sheet. The designed table is used in the form of a grid object. In particular, the user designs a table or the like using the interface of the Excel tool 20 .

In addition, each area or cell in the table on the worksheet in the view area refers to a cell in the data area sheet or a cell in the processing variable. That is, the user creates a view area sheet by utilizing an Excel function on the Excel tool 20 . At this time, a numerical value is directly input into each cell or area of the table, or another cell referred to is input. That is, the cell/region reference method is directly entered as the location of the cell (region) or as the cell name. Processing variables are entered as cell names of processing variables.

In addition, the data set displayed in the view area is a processed data set obtained by processing the basic data set.

In addition, the worksheet of the data area D (hereinafter referred to as a data area sheet) is a worksheet in which a data set loaded from a database by a query statement is received and stored in each cell. At this time, the data set loaded from the database is referred to as the basic data set.

In addition, the worksheet of the parameter area P (hereinafter referred to as parameter area sheet) is a worksheet for setting processing variables of the data set. The processing variable is set as the name of one cell, and the variable value of the processing variable is set as the input value in the cell.

The worksheet initialization unit 61 creates a table defining processing variables on a parameter area sheet. The process variable table consists of a number of records each representing a process variable.

Each record consists of fields such as variable name (variable name), variable definition, initial value, and description. The variable name field is a cell to input the name (or variable name) of the processing variable, and the variable definition field is a cell to set its own cell name with the name (or variable name field value) of the processing variable. In addition, the initial value field and the description field are cells for inputting the initial value or description of each processing variable.

In particular, since the cell name of the variable definition field is set as the variable name (processing variable name), the value of the cell entered in the variable definition field becomes the variable value of the corresponding processing variable.

That is, the user can use the name of the processing variable in the form of a cell name on the Excel tool 20. For example, if the name of a processing variable is written in a cell formula on the Excel tool 20, the processing variable in the corresponding cell formula is replaced with a value set in the variable definition field (cell) of the processing variable. Therefore, the name of the processing variable on the Excel tool 20 can be used as a processing variable.

In addition, processing variables can be used as variables in the binding syntax of query statements. At this time, the variable value of the processing variable is a value entered in the variable definition field (cell) of the corresponding processing variable.

On the other hand, the worksheet initialization unit 61 refetches the basic data set by utilizing the data set load information when there is already stored data set load information, and updates the contents of the data area sheet.

At this time, if the worksheet initialization unit 61 is defined by the processing variable in the query statement, the processing variable is bound to the variable value of the processing variable, and the data set is retrieved from the database with the bound query statement. update the dataset

Next, the processing variable setting unit 62 sets processing parameters on the parameter area sheet.

As shown in FIG. 6, when a variable name (variable name) is input in the variable name field of a specific record, the processing variable setting unit 62 converts the cell name of the variable definition field of the record to the input variable name (variable name). ) is set.

On the other hand, since the name (variable name) of the processing variable is defined as the name of the cell, the cell value input to the corresponding cell becomes the variable value of the corresponding processing variable. That is, the cell name is used as a variable.

That is, each processing variable is managed as a Name object in Excel, and the value of the corresponding Name is replaced with the value entered by the user in the corresponding cell. Name in Excel defines a unique name for cell addresses in Excel, and you can use that name. Then, the corresponding name corresponds to the corresponding cell (cell or cell address corresponding to the name), and the corresponding name is finally replaced with the cell value and calculated.

For example, define the name of cell A1 as "address", enter the value "90 Hyehwa-dong, Jongno-gu, Seoul" into cell A1, and use the formula "=address" in cell B1. At this time, "address" in the formula is a name, and the name is replaced with the value of the corresponding cell (corresponding cell) ("90 Hyehwa-dong, Jongno-gu, Seoul"). Therefore, in cell B1, the value of “90 Hyehwa-dong, Jongno-gu, Seoul” is finally calculated.

On the other hand, processing variables are also used in query statements, but variable identifiers are attached to the names of defined processing variables.

Next, the dataset loading unit 63 creates a query statement through the query statement creation unit 65, binds the query statement through the second query binding unit 66, and retrieves the data from the database by the bound query statement. Provides a service that loads the basic dataset and stores the load information. At this time, preferably,

As shown in FIG. 7 , the dataset loading unit 63 provides a function or interface for inputting connection information such as a key for connecting to the database 40 . The user accesses the database 40 by inputting connection information such as a key.

In addition, the dataset loading unit 63 provides a function or interface for creating a query statement referring to an underlying dataset in the database 40 through the query statement creation unit 65 . In addition, the dataset loading unit 63 binds the prepared query statement through the second query statement binding unit 66 .

And, as shown in FIG. 8 , the dataset loading unit 63 loads the basic dataset from the database using the bound query statement and outputs it to the data area sheet. At this time, the output position may be determined in advance or set by the user. At this time, preferably, the field name of the data set is output on the first row of the corresponding data area sheet, and the data set is output from the next row.

In addition, the dataset loader 63 stores load information of the basic dataset. Basic dataset load information consists of DB connection information for connecting to the database and query statements (SQL, etc.) to be executed in the database. Preferably, output location information indicating a location address to output the basic dataset may be further included.

DB connection information manages database connection information in the system, and includes the DB server address and key values for accessing the information. Database connection information is extracted internally with the corresponding key.

A query statement is a normal SQL statement to be executed in the database.

In addition, the output location information includes an address value for a location to output a query execution result. As an example, it is displayed as "D1$A1", which means to output from cell A1 of the D1 worksheet.

Next, the editing result storage unit 64 stores editing results performed on the worksheet when the Excel tool 20 is terminated. In particular, the editing results are Excel documents, templates, and data set load information.

An Excel document is a document worked in the Excel tool 20, and is saved as a document itself.

As described above, an Excel document is composed of a view area sheet, a data area sheet, and a parameter area sheet. In the data area sheet, the basic data set is entered. At this time, the user edits the table for the grid in the view area sheet, and edits the analyzed data to be displayed in the table with reference to the basic data set.

As shown in Fig. 9, in a table in a view area sheet, a cell refers to data (or cells) in a data area sheet. Cell C5 of the view area sheet contains a formula using the function sumif. At this time, some of the variables of the function sumif refer to cells or regions of the data area sheet D1.

Viewing the data area sheet, users can clearly see the structure of the underlying dataset pulled from the database. Therefore, the user can easily design a table in the view area sheet by using the data in the data area sheet.

In addition, data set load information consists of DB connection information for connecting to the database and query statements (SQL, etc.) to be executed in the database.

Also, a template (or grid template) is a data structure that stores the form, format, formula, etc. of a table (or grid) of a view area sheet in an Excel document. This is to interpret and use the contents of the view area sheet of the Excel document more quickly. Preferably, the template information is stored in a standard script data format such as JSON (JavaScript Object Notation).

In particular, each cell of the table on the view area sheet references a cell of the underlying dataset on the data area sheet as a formula. Therefore, the cell formula information in the template includes information referring to cells (or basic data sets or data of basic data sets) on the data area sheet.

Meanwhile, as another embodiment, it may be implemented by directly interpreting an Excel document without using a template. In this case, it is not created according to the template.

In particular, the editing result storage unit 64 extracts grid structure information from the table on the view area sheet of the Excel document and stores it as template information.

That is, grid structure information such as the structure of each table and the properties (format, formula, etc.) of each table or cell is extracted from the table on the view area sheet and stored as a template. Preferably, the template is written in a format having a hierarchical structure such as JSON. The table structure is converted into a hierarchical structure of the template, and the attributes of each cell of the table are converted into the attributes of the leaf nodes of the hierarchical template structure. The properties of each cell consist of cell values, formats, and formulas.

10 is a diagram illustrating grid structure information of a template for a table. That is, FIG. 10A illustrates a pivot table of a view area sheet of an Excel document, and FIG. 10B illustrates template information (grid structure information) of the pivot table of FIG. 10A. In particular, template information is stored in JSON (JavaScript Object Notation) format. That is, the pivot table is decomposed into a hierarchical structure, and is composed of elements of each hierarchy and attribute values of the elements.

In addition, if the object is a table, only the structure information of the header row of the corresponding table and the first row of data is stored as a template. If the format of data rows in a table is cross-row, all formats (attributes) of two consecutive data rows are saved.

Next, the query statement builder 65 provides a function or interface for creating a query statement referring to the underlying data set in the database 40 .

In particular, as shown in FIG. 7 , the query sentence writer 65 provides a function of directly writing a query sentence in text form or writing a query sentence through a drag-and-drop GUI (Graphic User Interface). The query sentence writer 65 generates a text form query sentence even when a query is created through a GUI interface.

As shown in FIG. 11, the query statement in text form is composed of a normal query statement by syntax such as SELECT ~ FROM ~ WHERE.

Meanwhile, the query statement writer 65 may create a query statement by using processed variables as query variables.

A query variable in a query statement is formed by combining a variable identifier and a name of a processing variable. That is, query variables are actually mapped with processing variables, and actually mean the same variables. However, the notation format of query variables and processing variables is different. That is, a query variable is made including the name of a processing variable and used by being marked in the query statement, but actually represents the same variable. In the following, the terms query variable and processing variable are used interchangeably.

Preferably, variable identifiers are combined into prefixes of query variables, as follows.

[Query variable in query statement]

<variable identifier> + <name of processing variable>

That is, a query variable is mapped by adding a variable identifier to a processed variable, but actually represents the same variable.

In addition, the variable identifier may include symbols or characters indicating the type of variable.

As an example, variable identifiers are denoted by ":VS_". At this time, it is identified by combining a colon (:) and “V” of Variable, and “S” represents the type of variable. Also, an underscore ("_") represents a combination with the name of a processing variable.

Types of variables are shown in FIG. 12 .

In addition, an example created using query variables (process variables) in the query statement is shown in FIG. 13 . As shown in FIG. 13, "product name", "branch name", "sales month", and "sales month_TO" are names of processing variables set as processing variables. These are the names of processing variables set in the parameter sheet (P) above.

In addition, the query statement writer 65 includes a line deletion function (hereinafter, a dynamic line deletion function) when using a query variable (process variable) in a query sentence. At this time, it is distinguished by adding a symbol or character representing the dynamic line deletion function to the query variable of the query statement. As an example, the dynamic line deletion function is distinguished by putting "@" in front of the variable name.

Query variables of the dynamic line deletion function, unlike processing variables, additionally have a binding function of line deletion.

The dynamic line deletion function deletes and binds the line in the query statement when the variable value of the corresponding processing variable does not exist or is empty.

14 illustrates query variables of the dynamic line deletion function used in the condition clause (WHERE). FIG. 14(a) is a written query statement, and FIG. 14(b) shows a query statement generated by binding to variable values in FIG. 14(c).

Meanwhile, query variables of the dynamic line deletion function may implement conditional statements for query statements that execute different query statements according to conditions.

As in the example of FIG. 15, each team is divided into two query statements (a) and (b) according to whether or not it is a "purchase team". That is, in the case of a team other than the purchasing team, a query statement that does not display the unit price and amount must be executed, as shown in FIG. should run

16 illustrates codes for creating query statements for the two cases of FIG. 15 according to a conventional method. That is, in the conventional method, programming work is required for conditional branching.

The query statement writer 65 can be implemented in the following two ways using query variables of the dynamic line deletion function.

In the first method, the query variable of the dynamic line deletion function is inserted as a comment in all rows of the query statement corresponding to the condition. This is shown in Figure 17.

As shown in FIG. 17, if there is no variable value of the query variable “purchasing team (@:VN_purchasing team)”, the corresponding line (the line in which the query variable is written), that is, the corresponding line showing unit price and amount is deleted. In addition, if the variable value of the query variable "Other team (@:VN_Other team)" exists, the query variable is replaced with the variable value, but since it is a comment, it has no effect. Also, since the corresponding line exists, the query statement showing the unit price and amount as 0 is bound.

In addition, in the example of FIG. 17, conversely, if there is a variable value of the query variable "Purchase Team (@: VN_ Purchase Team)" and there is no variable value of the query variable "Other Team (@: VN_ Other Team)", The row showing the unit price and amount as 0 is deleted, and only the corresponding line showing the unit price and amount exists and is bound to the query statement showing the unit price and amount.

Next, the second method is a method of using reserved words in comments. An example of this method is shown in FIGS. 18 and 19 .

As shown in FIG. 18, query variables are set before and after rows performed according to conditions. That is, the query variables of "Other team start and end (@:VN_Other team_START, @:VN_Other team_END)" before and after the rows showing the unit price and amount as 0 in the case of "Other team" write Then, create query variables of "Purchasing team start and end (@:VN_Purchasing team_START, @:VN_Purchasing team_END)" before and after the rows showing the unit price and amount in the case of "Purchasing team" do.

And, as shown in FIG. 19a, in the case of other teams, query variables of other teams do not have variable values (or null, blank, etc.), and the query variables of the purchasing team use the start and end symbols of comments as variable values, respectively. Enter Then, the query statement as shown in the lower part of FIG. 19A is bound.

In addition, as shown in FIG. 19B, in the case of the purchasing team, the query variables of the purchasing team do not have variable values (or null, blank, etc.), and the query variables of the other teams use the start and end symbols of comments as variable values. enter each Then, the query statement as shown at the bottom of FIG. 19B is bound.

On the other hand, the second method, a method using a reserved word of a comment, may use a general query variable without using a query variable of the dynamic line deletion function. That is, it is possible to define a query variable as a string, and insert a blank instead of entering the variable value of the query variable.

Next, the second query statement binding unit 66 binds the prepared query statement to variable values of processing variables.

The second query statement binding unit 66 detects a variable identifier in the written query statement and recognizes a string connected to the variable identifier as a name of a processed variable when recognizing the variable identifier.

Next, the second query statement binding unit 66 reads the variable value of the processed variable. The variable value of the processing variable is mapped to the cell value of the cell of the name of the processing variable defined in the parameter sheet (P).

Then, the second query statement binding unit 66 replaces the variable value of the processing variable with the query variable of the query statement and binds it. In this case, the expression format of the variable value may vary according to the variable type of the query variable. That is, variable values are converted and displayed according to a format of variable values determined in advance.

In addition, when the symbol of the dynamic line deletion function is detected in the query variable, the second query statement binding unit 66 deletes the corresponding line when there is no value of the processing variable.

Next, the grid display unit 35 generates a grid by analyzing an Excel document or the like. That is, a grid structure is created and output according to the template, the basic data set is imported according to the loading information of the basic data set of the template, and the processed data set is reflected on the grid and output.

First, the grid display unit 35 creates a grid shape according to the grid structure of the template. As described above, since the template file is written in a standard script data format such as JSON, it is analyzed using the parser of the script data format.

That is, in the case of a pivot table object, the pivot table is restored by interpreting the object's JSON data, and in the case of a general table object, the object's JSON data is interpreted to restore the header row of the table and the first row of data (or the first and second rows). row) is restored. Preferably, the grid herein may be data loaded onto memory.

In particular, if there is a user request in the server, it is interpreted in real time. On the other hand, template files are lighter than Excel documents and can be loaded at high speed.

Also, the grid display unit 35 loads basic data from the database according to the load information. Preferably, the latest query result of the database is reflected in real time.

The grid display unit 35 refers to the load information, accesses the database using the DB connection information, and generates and requests a query statement using the query statement information. In other words, access to the database after being authenticated with the key value of the DB connection information. In addition, a query statement is created by reflecting processing variables using a binding statement. At this time, the variable value of the processing variable is set as the input value of the control object mapped to the corresponding processing variable.

Preferably, the grid display unit 35 loads the basic data set retrieved from the database (DB) into memory. At this time, the basic dataset is loaded in the form of a virtual sheet having rows and columns. In other words, it is loaded so that the data of the underlying dataset can be referenced based on the rows and columns of the worksheet.

In addition, the grid display unit 35 reflects and inserts the basic data set into the data area of the grid.

In particular, if the form of the dataset and the corresponding grid is a table, as many rows as the number of records (data rows) of the dataset are dynamically created in the corresponding grid. After identifying the binding location, dynamically create as many rows as the number of records.

In addition, when the cell of the table is a formula, the grid display unit 35 interprets the formula and inserts the calculated value. In particular, if the cell formula refers to a cell of the data area sheet, the cell value is calculated by referring to the updated basic data set (or the basic data set loaded in memory).

In particular, since the basic data set (updated basic data set) imported from the database is stored in the form of a virtual sheet, formulas referring to cells of the data area sheet described in the Excel document can be used as they are. At this time, the name of the data area sheet is replaced with a virtual sheet (virtual sheet loaded in memory) and interpreted.

That is, the grid display unit 35 automatically generates formulas according to the number of records.

As shown in FIG. 20, after identifying the binding position in the template, as many rows as the number of records are dynamically created. At this time, if a formula exists in the data area by determining the header and data area, the formula is automatically created along with the row creation. .

The formula is created by automatically increasing/decreasing based on the address of the row created by interpreting the absolute and relative addresses of Excel. An absolute address represents a fixed, unchanging cell address. In the relative address, the address value changes according to the relative position of the position of the reference cell and the position of the corresponding cell.

Also, as shown in FIG. 20 , the grid display unit 35 supports cross rows and styles (forms). That is, it supports the case where the formats of the cross rows are different. That is, after identifying the binding position in the template, the style (format) of the data area is reflected in the newly created row. When the size of a row in the data area is 2, the style (format) of the cross row can be supported and the style (format) can be reflected alternately.

In addition, the grid display unit 35 supports formulas in Excel on the grid. The expression is defined in the template. Formulas can be newly created according to data binding rules. Data binding means that if a formula exists at the last address of this location when a processed data set is output in the form of a table, the corresponding formula is automatically generated for each row.

In the above, the invention made by the present inventors has been specifically described according to the above embodiments, but the present invention is not limited to the above embodiments, and various changes can be made without departing from the gist of the present invention.

10: user terminal 20: excel tool
30: writing server 31: screen editing unit
32: grid editing unit 33: control editing unit
34: report viewer unit 35: grid display unit
36: first query binding unit 38: storage unit
60: extension module 61: worksheet initialization unit
62: processing variable setting unit 63: data set loading unit
64: Editing result storage unit 65: Query statement writing unit
64: second query binding unit
80: network

Claims (7)

In the online report creation system using an Excel tool, including a creation server and a user terminal,
The creation server,
A grid editing unit that edits a grid object on a report screen on the web, stores information about one grid object in one Excel document, and edits the grid object through editing of the Excel document by an extended Excel tool;
a control editing unit that creates and edits a control object on a report screen on the web;
a first query binding unit;
a grid display unit displaying the contents of the grid object on the web according to the contents of the Excel document mapped to the grid object; and,
A report viewer unit displaying a report composed of a grid object and a control object on the web and displaying the grid object through the grid display unit;
The extension module installed in the Excel tool installed in the user terminal,
When the Excel tool is called, an Excel document that is mapped to the grid object is created, and an Excel document composed of a view area sheet representing the shape of the grid, a data area sheet for storing basic data sets, and a parameter area sheet for setting processing variables. a worksheet initialization unit that creates a document;
a processing variable setting unit that sets processing variables on a parameter area sheet of the Excel document;
a query statement creation unit that creates a query statement by mapping processing variables on the worksheet to query variables;
a second query binding unit that binds query variables of the query statement to variable values of processed variables; and,
A data set loading unit for loading a basic dataset on a worksheet of the Excel document, binding the query statement through the second query binding unit, and loading the bound query statement;
The first query binding unit binds query variables of the query statement to variable values of processing variables,
The grid display unit binds the query statement through the first query binding unit, loads the basic data set with the bound query statement, and displays the content of the grid object from the loaded basic data set to the processed data set.
The control editing unit maps the processing variable to the control object,
The online report writing system having a query binding function, characterized in that the first query binding unit sets a variable value as an input value of a control object mapped to the processing variable.
According to claim 1,
The query variable in the query statement is composed and displayed by combining a variable identifier and a name of a processing variable,
The online report writing system having a query binding function, characterized in that, when the first or second query binding unit recognizes a variable identifier in the query statement, it recognizes a string connected to the variable identifier as a processed variable.
According to claim 1,
When using a query variable in the query statement, a line deletion function is provided, and a symbol or character indicating a dynamic line deletion function is added to the query variable to distinguish it,
The first or second query binding unit, when the query variable has a line deletion function, if the variable value of the corresponding processing variable does not exist or is an empty value, the query binding function characterized in that for binding by deleting the corresponding line from the corresponding query statement Online report writing system with.
According to claim 3,
If the row executed according to the condition in the above query statement is different, a comment statement is inserted in the corresponding row, a query variable having a line deletion function is inserted in the inserted comment statement, and the variable value of the query variable is entered as an empty value according to the condition. An online report creation system having a query binding function, characterized in that by inputting an arbitrary value, the corresponding row is deleted if it is an empty value, and the corresponding row is provided to be executed if it is an arbitrary value.
According to claim 1,
The processing variable setting unit sets the name of the variable definition cell to the name of the processing variable, and the processing variable is used in the formula in the cell of the table to be displayed in the grid on the Excel document. Report writing system.
According to claim 5,
A table on the view area sheet refers to an underlying data set entered on the data area sheet, and a formula of a cell in the table on the view area sheet refers to a cell of the data area sheet;
The grid display unit fetches the basic data set from the database and stores it on the virtual sheet, and when each cell of the table of the grid object is reflected according to a formula, instead of the content of the cell of the data area sheet to be referred to, the corresponding data area on the virtual sheet is reflected. An online report creation system having a query binding function, characterized in that the content of the cell is reflected.
delete

Images