Run scripts to transfer dynamic SQL statements and Report SQL statements.

Run scripts to transfer dynamic SQL statements and Report SQL statements.

In most cases, the SQL statements of the report data source are fixed, but sometimes some of them need to be dynamically spelled out, for example, in a report application, you can select tables and fields to query the report data you are concerned about. When developing a report, you want to use the same template for similar reports (details or summaries. In this case, you need to receive corresponding parameters in the report and splice them into dynamic SQL statements for query, and then create a dynamic report.

Unlike general report tools, which need to be implemented in third-party programs (such as JAVA), a built-in script (Dataset) is provided in the rundry set computing report to quickly complete such reports, the following is an example.

 

In a report, when you specify tables and columns, some columns are required. Even if you do not select them, they are still displayed after query. Therefore, when splicing SQL statements, you need to determine whether the required columns are in the user's selected columns in advance. If not, you need to add them later and ensure the order of the columns selected by the user.

The following describes how to query the order information for a certain period of time. The order ID, order date, delivery date, and arrival date are mandatory fields. You can use the script to complete the query by following these steps.

 

Set Report Parameters

The application has passed the table name, field name, and query conditions to the report in the specified parameter format. Therefore, the report parameter settings are as follows:

Columns value: goods owner name, freight provider, and freight fee;

Conditions value: where order date> = '2017-01-01 'and order date <= '2017-12-31'

 

Set Dataset

Use the script dataset to write a script for SQL concatenation and fetch, and return the result set.

You can use the set computing script to write the computing logic step by step. Thanks to its excellent support for the set operation, you can combine the selected field set and the required field set in A2 to obtain all the query fields, very simple. Where:

A1: a set of all required fields;

A2: calculates and merges the selected field set and the required field set to ensure that all required fields can be selected;

A3-A4: Execute the number of Concatenated SQL statements;

A5: return result set of the report.

 

Edit Report expression

Because the report columns are dynamic, you cannot create a report with fixed columns in advance. You need to use a dynamic column function.

The ds. fname () and ds. field () functions are used to implement a dynamic column report. For detailed usage, see the related documentation of the Set Computing report. At last, you can hide column A and the first row to complete report creation.