CN104461531B - A kind of implementation method of reporting system SQL - Google Patents

Abstract

The invention discloses a method for realizing a self-defined function of a report system, which includes the following steps: step 1): registering a self-defined function and parameters; step 2): creating a stored procedure related to a self-defined function; step 3): using a self-defined function in a report ; Step 4): Identify the custom function in the report; Step 5): Identify and replace variables in the custom function parameter list; Step 6): Prepare parameters and call the custom function associated stored procedure; Step 7): Execute custom The function associates the stored procedure and returns the result; Step 8): Receive the returned result of the custom function associated stored procedure and apply it to the report. The technical threshold for creating self-defined functions in the present invention is low, and report users only need to be familiar with the design of stored procedures to create self-defined functions, as long as the name and parameter information of the self-defined functions are registered in the designer and submitted and created in the database After the associated stored procedure is completed, the creation and deployment of the custom function is completed.

Description

A Realization Method of Custom Function in Reporting System

technical field

The invention relates to the field of computer software applications, in particular to a method for realizing a self-defined function of a reporting system.

Background technique

The custom function function is one of the important functions of the commercial report system, which allows the report designer or report user to package the format conversion function and business processing logic into a function form for use when designing reports, realizing code reuse and the characteristics of specific application fields need.

Reports are an essential part of enterprise application software. In order to facilitate the development of reports, various report development systems provide a certain number of system functions to complete some common functions, such as mathematical processing, string processing, date format conversion, etc. . However, in practical applications, according to the specific needs of the application field, report designers often need to use some functions that are not available in the system functions, such as date format conversion or number format conversion in specific formats, and processing functions related to application business logic , these functions need to be designed by the report designer as a custom function for repeated use.

Most of the current mainstream commercial reporting systems provide methods for designing custom functions. In the Crystal Report (Crystal Report) system in Microsoft's Visual Studio integrated development environment, a custom function editor is provided for users to design custom functions. After the design is completed, it can be published to the report system's repository and shared with all other designers. use. The design and publishing of crystal report custom functions are relatively simple, but they need to be written in VISUAL BASIC or a programming language dedicated to crystal report, and there are many restrictions on code functions. For example, report or database fields cannot be used in custom functions, which greatly The scope of application of custom functions is limited.

Runqian report system, Jiebiao report system and X5 report platform are all analytical engines for developing WEB reports in JAVA language. Therefore, they all require self-defined functions to be developed in JAVA language, and their configuration and use must also be configured according to certain rules. to edit. For example, the process of adding a custom function in Runqian report system is as follows:

1. Write the java class of the custom function and inherit the com.runqian.report4.model.expression.Function class.

2. In the config directory of the java class path, find the customFunctions.properties file, and register the custom function class and function name in it.

3. On the designer side, copy the customFunctions.properties file to \designer\web\WEB-INF\classes\config, and copy the class files to designer\web\WEB-INF\classes. If there is a package name, corresponding The build path of the package.

4. On the web side, copy the customFunctions.properties file to \webapps\demo\WEB-INF\classes\config, and copy the class files to \webapps\demo\WEB-INF\classes. If there is a package name, corresponding The build path of the package.

5. Restart the designer.

6. Use custom functions in the designer.

The advantage of the method for realizing the above-mentioned self-defined function is that it is powerful in function, can make full use of the programming capability provided by the JAVA programming language, and is relatively easy to realize the format conversion function and business logic processing function for the user.

There is also a custom function implementation scheme that can simply design and apply a custom function [1]. In this solution, the user-defined function is defined by the quaternion Fun={custom function name, SQL query statement, number of input parameters, number of output parameters}, and the function of the function is realized by the SQL query statement. It is necessary to write this SQL query statement without using other programming languages. After the above-mentioned custom function quadruples are determined, they can be submitted to the database for shared use by other report users. When used, it can be embedded in an expression like a system function. The custom function parsing process of this scheme is as follows:

1. Variable identification and replacement in expressions containing custom functions, all variables are replaced with specific values to prepare for the evaluation of custom functions.

2. Identify custom functions in expressions.

3. Through the function name of the user-defined function stored in the database, the corresponding SQL query statement and parameter number information are compared with the obtained variable number to verify whether the variable definition number is correct.

4. Replace the parameter variable in the SQL query statement with the actual parameter value of the custom function in the expression.

5. Execute the SQL query statement after variable substitution in the previous step, and get the return value, which is to replace its appearance in the expression as the evaluation result of the custom function.

The main disadvantages of JAVA-based custom function implementation methods such as Runqian Reporting System, Jiebiao Reporting System and X5 Reporting Platform are:

The technical threshold for creating custom functions is high. Various report systems require users to use a specified programming language and code in a prescribed format when designing custom functions, and master SQL programming for custom functions that require database operations.

The deployment of custom functions is complex, and when there are many custom functions, the workload is heavy and error-prone.

For the self-defined function scheme realized by the quaternion Fun={custom function name, SQL query statement, number of input parameters, number of output parameters}, its main disadvantage is that the functions are limited, and the execution of the custom function and the acquisition of results are difficult. It is implemented through SQL query statements, and it is difficult to implement custom functions including business logic rules. In addition, it also has the disadvantage of low efficiency, that is, the database server needs to check the syntax of the SQL query statement every time it is executed.

A comprehensive analysis of the implementation of custom functions in current mainstream reporting systems reveals the following problems:

The technical threshold for creating custom functions is high. Various report systems require users to use a specified programming language and code in a prescribed format when designing custom functions, and master SQL programming for custom functions that require database operations. This means that ordinary report designers and users may need to learn a new programming language and its development tools to design custom functions.

Deployment of custom functions is complex. In the process of custom function deployment of various reporting systems, the creation and editing of multiple files are usually involved, and they must be placed in the specified file path. Some report systems also need to restart the designer for the custom function to take effect , the process is complex, and when there are many custom functions, the workload is heavy and error-prone.

Contents of the invention

The purpose of the present invention is to provide a reporting system custom function implementation method that solves the technical problems mentioned in the background art with a low technical threshold and a simple deployment method.

The technical solution provided by the present invention is: a method for realizing a self-defined function of a reporting system, comprising the following steps:

Step 1): Custom function and parameter registration. This step is done on the functional interface of the report designer. The report designer needs to provide the name of the custom function, the number of parameters, and the parameter name and description of each parameter, and then submit it to the system database to complete the registration process. Report system functions are identified with the prefix $, while custom functions are identified with the prefix $$. For example, if a function is in the form of $FUNC(), it is a system function, and if it is in the form of $$FUNC(), it is a custom function. This method of adding a prefix to the function name helps to distinguish between ordinary parenthesized expressions, system functions, and custom functions.

Step 2): Create stored procedures related to custom functions. This step is done in the database management system. The functions of all custom functions are realized by their related stored procedures. The name of the stored procedure related to the custom function is obtained by removing the prefix $$ from the name of the custom function. For example, the name of the custom function is $$myFunc(), and the name of the associated stored procedure is myFunc(). This function name is the same as the stored procedure The relative definition method of the name guarantees that the related stored procedure name can be directly obtained from the custom function name when parsing the custom function without searching from the database. The parameter list of all custom function-related stored procedures adopts the unified format myFunc(inParams,outParam). No matter how many parameters the custom function contains, its related stored procedures only contain one input parameter and one output parameter. The input parameters are used to The stored procedure passes parameter values. If the custom function has multiple parameters, the multiple parameters are combined and passed to the input parameters, and the output parameters are used to return the execution result of the stored procedure. The processing logic in the stored procedure related to the custom function includes the following steps: A) receiving input parameters, decomposing multiple parameters combined into a single parameter; B) performing type conversion for each parameter, and converting it into the required data type; C) Execute the processing logic of the custom function; D) Set the execution result to the output parameter and return it.

Step 3): Use the custom function in the report. This step is done in Report Designer. It is relatively simple to use a custom function in a report, and it can be used alone or in an expression according to the format defined in step 1.

Step 4): Identify the custom functions in the report. This step is done in the report parsing and creation engine. In the process of parsing and evaluating report expressions, according to the prefix naming rules of custom function names, the custom functions are identified and extracted for parsing.

Step 5): Identify and replace variables in the custom function parameter list. This step is done in the report parsing and creation engine. The parameter list of the custom function extracted in step 4 may contain variables, constants and other functions. This step includes the following sub-steps: identify the variables and other functions in it; obtain the actual value of the variable in the parameter, calculate the function in the parameter to obtain the result value; replace the actual value of the parameter variable in the parameter list occurrences, replaces its occurrences in the parameter list with the value of the function's execution result in the parameter. After the above steps, the parameter list of the custom function only contains constants.

Step 6): Prepare parameters and call the custom function associated stored procedure. This step is done in the report parsing and creation engine. The method includes the following steps: preparing the input parameters of the stored procedure associated with the custom function. Remove the parentheses before and after the constant from the parameter table obtained in step 5, and assign it to the input parameter; set the output parameter of the stored procedure associated with the custom function; remove the prefix $$ of the custom function name, and use it as the stored procedure name; use the stored procedure The procedure name and parameters set the database access component, and submit the stored procedure to the database server for execution.

Step 7): Execute the custom function associated stored procedure and return the result. This step is implemented in the database management system.

Step 8): Receive the result returned by the custom function associated stored procedure and apply it to the report. This step is done in the report parsing and creation engine.

The beneficial effects of the present invention are:

The core idea of the self-defined function processing scheme of the report system provided by the present invention is that all the self-defined functions are realized by database stored procedures. Various commercial database management systems provide mechanisms for designing stored procedures, which include various process control mechanisms, a large number of format processing functions, mathematical functions, and date functions, etc., and can directly operate SQL statements. Another advantage of the stored procedure is that after one compilation, it does not need to be recompiled for repeated execution in the future, and the execution efficiency is high, while the SQL query statement submitted to the database system in other programming languages must be compiled before each execution.

The advantages of the present invention include: (1) The technical threshold for creating a custom function is relatively low, and report users only need to be familiar with the design of stored procedures to create custom functions, while the methods for creating stored procedures in various database management systems are simple and easy to use. near. Users do not need to use other programming tools and programming languages. (2) The self-defined function is easy to deploy, as long as the name and parameter information of the self-defined function are registered in the designer and submitted, and the associated stored procedure is created in the database, the creation and deployment of the self-defined function is completed.

Description of drawings

A more complete and better understanding of the invention, and many of its attendant advantages, will readily be learned by reference to the following detailed description when considered in conjunction with the accompanying drawings, but the accompanying drawings illustrated herein are intended to provide a further understanding of the invention and constitute A part of the present invention, the exemplary embodiment of the present invention and its description are used to explain the present invention, and do not constitute an improper limitation of the present invention, wherein:

Fig. 1 is the overall flow diagram of the report system self-defined function realization method of the present invention;

Figure 2 is a flowchart of the internal processing flow of the custom function associated stored procedure;

Fig. 3 is a flow chart of variable identification and replacement in the custom function parameter list;

Fig. 4 is a flow chart of preparing parameters and calling a custom function associated stored procedure.

detailed description

In order to make the above objects, features and advantages of the present invention more comprehensible, the present invention will be further described in detail below in conjunction with the accompanying drawings and specific embodiments.

Referring to the accompanying drawings, the technical solution of the present invention will be described below with a specific process of definition and analysis of a self-defined function.

Assume that a custom function is needed to convert the default date format "08/25/2014" into a Chinese date format, such as "August 25th, 2014". According to attached drawing 1, the following steps will be taken: chnDate( today(), 'YYYYYYYYYMMDDDD') will format the date of the day into the Chinese date format.

Step 1): Custom function and parameter registration. Register the custom function as $$chnDate(oldDate,format), where $$chnDate is the name of the custom function, oldDate is the date to be converted in the default format, and format is the desired format.

Step 2): Create stored procedures related to custom functions. Create a stored procedure in the database management system, in the form of chnDate(inParams, outParam), inParams is the input parameter, and outParam is the output parameter, both of which are string types. Implement the processing logic in Figure 2 in the stored procedure chnDate, decompose the comma-separated old date and expected format parameter values in inParams into two string-type parameter values, and convert them into date type and structure type respectively , and then process it and set the execution result to the output parameter outParam to return.

Step 3): Use the custom function in the report. Examples of use such as

$STRCAT("Print date:", $$chnDate($TDODAY(), 'YYYY, MM, DD')), the system function $STRCAT() is used to concatenate two strings into one string, and the system The function $TDODAY() is used to obtain the current date in the default format. If the current date is "08/25/2014", the above example will get the string "printed date: August 25, 2014". The number of parameters of the user-defined function should be the same as that registered in step 1.

Step 4): Identify the custom functions in the report. According to the custom function prefix and bracket matching relationship, it is easy to identify the custom function $$ in $STRCAT("print date:",$$chnDate($TDODAY(),'YYYYYYYYYMMDDDD')) chnDate($TDODAY(), 'MM, DD, YYYY').

Step 5): Identify and replace variables in the custom function parameter list. In $$chnDate($TDODAY(), 'MM, DD, YYYY'), the parameter list is "$TDODAY(), 'MM, DD, YYYY'", where $TDODAY() is a system function and needs to be Evaluate, assuming that the result of the evaluation is: 08/25/2014, use it to replace the occurrence of $TDODAY() in $$chnDate($TDODAY(),'YYYYYYYYYMMDDDD'), and get $$chnDate( '08/25/2014', 'MM DD YYYY'). So far, the parameter list of the custom function contains only constants.

Step 6): Prepare parameters and call the custom function associated stored procedure. It includes the following steps: 1. The input parameter value of the stored procedure associated with the custom function is "'08/25/2014', 'MM, DD, YYYY'". 2. Set the output parameters of the stored procedure associated with the custom function; 3. Use chnDate as the stored procedure name; 4. Use the stored procedure name and parameters to set the database access component, and submit the stored procedure to the database server for execution.

Step 7): Execute the stored procedure set in step 6) and return the result, where the returned result is "August 25, 2014".

Step 8): Receive the result returned by the stored procedure in step 7) and apply it to the report expression, that is, replace the result returned by step 7) with the custom function $$chnDate($TDODAY(), 'MM, DD, YYYY') in The occurrence of the expression $STRCAT("print date:", $$chnDate($TDODAY(), 'YYYY, MM, DD, YYYY')) gets $STRCAT("print date:", "2014 August 25"), so far, the custom function analysis is over.

The present invention can also have other alternative technical solutions, such as:

In step 1), the name of the custom function can also be without a special prefix, then when the report engine parses, it can first obtain the name list of all system functions and custom functions from the system database, and then compare them with the names used in the report function for comparison.

In step 2), the stored procedure name associated with the custom function can also be saved in the record of the custom function in the database, then there may be no derivation relationship between the stored procedure name and its associated custom function name, and its association relationship can be obtained from The custom function is found in the registration information in the database. Then, when calling an associated stored procedure for a custom function, the name of the stored procedure associated with the custom function must first be obtained from the database. In addition, the processing logic of the user-defined function can also be realized by the user-defined SQL function in the database system. The difference from using the stored procedure is that the processing result is returned through the output parameter in the stored procedure, while the result is processed in the user-defined SQL function Return by function return value.

The description of the above examples is only used to help understand the core ideas of the present invention; meanwhile, for those of ordinary skill in the art, according to the ideas of the present invention, there will be changes in the specific implementation methods and application ranges. , the contents of this specification should not be construed as limiting the present invention.

Claims (2)

1. A report system self-defined function realization method, is characterized in that, comprises the following steps: Step 1): Registration of custom functions and parameters; on the function interface of the report designer, the report designer provides the name of the custom function, the number of parameters, and the parameter name and description of each parameter, and then submits it to the system database; The definition function is identified with the prefix $$; Step 2): Create the stored procedure related to the custom function; in the database management system, the functions of all custom functions are realized by its related stored procedure; the name of the stored procedure related to the custom function is the name of the custom function minus the prefix $$ And get; the parameter list of all custom function-related stored procedures adopts a unified format; the relevant stored procedures only contain one input parameter and one output parameter, and the input parameter is used to pass parameter values to the stored procedure. If the custom function has multiple parameters , the multiple parameters are combined and passed to the input parameters, and the output parameters are used to return the execution result of the stored procedure; Step 3): Use the custom function in the report; in the report designer, use it alone or in an expression according to the format defined in step 1 of the custom function; Step 4): Identify the custom functions in the report; in the process of parsing and evaluating report expressions, identify the custom functions in them according to the prefix naming rules of the custom function names, and extract them for parsing; Step 5): Identify and replace variables in the custom function parameter list; step 4) identify the variables and other functions in the parameter list of the self-defined function extracted in step 4); obtain the actual value of the variable in the parameter, and the parameters The function in is calculated to obtain the result value; replace its appearance in the parameter list with the actual value of the parameter variable, and replace its appearance in the parameter list with the execution result value of the function in the parameter; after the above steps, the parameter of the custom function The list contains only constants; Step 6): Prepare parameters and call the custom function associated stored procedure; including the following steps: Prepare the input parameters of the custom function-associated stored procedure, remove the parentheses before and after the constant-only parameter table obtained in step 5), and assign it to the input parameter; Set the output parameters of the stored procedure associated with the custom function; Remove the prefix $$ of the custom function name as the stored procedure name; Use the stored procedure name and parameters to set the database access component, and submit the stored procedure to the database server for execution; Step 7): Execute the custom function associated stored procedure and return the result; Step 8): Receive the result returned by the custom function associated stored procedure and apply it to the report. 2. a kind of reporting system self-defined function implementation method according to claim 1, is characterized in that, in step 2), the processing logic in the relevant stored procedure of self-defined function comprises the following steps: A) receiving input parameters, decomposing multiple parameters combined into a single parameter; B) perform type conversion on each parameter, and convert it into the required data type; C) Execute the processing logic of the custom function; D) Set the execution result to the output parameter and return it.