Run-Dry Set calculation report using scripts to implement dynamic SQL delivery

In most cases, the data source SQL for the report is fixed, but sometimes it is necessary to dynamically make certain parts of it dynamically, such as allowing the user to query the report data that they care about by selecting tables and fields in the report application, and when the report is developing, you want to use the same template for the same kind of report (detail or summary). At this point you need to receive the corresponding parameters in the report stitching out dynamic SQL to query the production of dynamic reports.

Unlike the general reporting tools that need to be implemented in third-party programs (such as Java), the built-in scripts (datasets) are provided in the run-dry set of reports, which can be done quickly, as illustrated below.

In a class of reports, when a user specifies a table and column, some columns are required, even if the user does not have a selection, and is still displayed after the query. So you need to be in the stitching of SQL in advance to determine whether the required column in the user's chosen column, if not, then add, and also to ensure that the user to select the order of the column.

For example, the order ID, order date, shipping date, arrival date are required fields, and the script is completed using the following steps.

Set Report Parameters

The table name, field name, and query criteria are passed to the report in the specified parameter format, so the report parameter is set as follows:

where columns value: The name of the shipper, shippers, freight charges;

conditions Value: Where order date >= ' 1996-01-01 ' and order date <= ' 1996-12-31 '

set up datasets

Script data sets are used to script SQL stitching and fetching, and the result set is returned.

using a set-up script allows you to step through the calculation logic, and because of its good support for set operations, it is easy to make all the query fields in A2 with the selected field collection and the required set of fields. which

A1: A collection of all required fields;

A2: Sets the collection of fields selected by the user with the set of required fields to ensure that all required fields are selected;

A3-A4: Performs the concatenation good SQL to fetch the number;

A5: Returns a result set for the report.

Edit a report expression

Dynamic column functions are required because report columns are dynamic and cannot be pre-crafted for fixed-column reports.

Dynamic column reports are implemented using the Ds.fname () and Ds.field () functions, which can be used to refer to a set of report related documents. Finally, the auxiliary column A and the first row are hidden to complete the report production.

Run-Dry Set calculation report using scripts to implement dynamic SQL delivery