The dynamic SQL delivery of the diversity data source of the run-dry set report

in most cases, the data source for the report SQL is fixed, but it is sometimes necessary to dynamically make certain parts of it dynamically, such as allowing users to query their own report data by selecting tables and fields in a report application, and when developing a report, they want to use the same template for similar reports (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.

the run-dry set of reports provides two processing dynamics SQL methods, using macros and set-up scripts, can quickly complete such reports, as illustrated below.

Using Macros

The Collection report provides a macro mechanism for stitching in dynamic macros SQL is handed over to the dataset query results, and finally the field names and field values are obtained using dynamic expressions in the report to complete the report production. The steps are as follows:

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:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/57/0C/wKiom1SP4IzQPldBAACw9Pr5ap4181.jpg "style=" float: none; "title=" report5_multisource_sql_1.jpg "alt=" Wkiom1sp4izqpldbaacw9pr5ap4181.jpg "/>

      where cols id, customer id, order date , shipping date , freight charges;

where value:where cargo area = ' North ' and Shipper City in (' Beijing ', ' Tianjin ', ' Zhangjiakou ', ' qinhuangdao ').

Setting up macros

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/57/0C/wKiom1SP4I3zTDeUAAB8UXifqU0635.jpg "style=" float: none; "title=" report5_multisource_sql_2.jpg "alt=" Wkiom1sp4i3ztdeuaab8uxifqu0635.jpg "/>

where the value is:"select" +cols+ "from order " +where, the macro type is a dynamic macro.

Set up datasets

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/57/0A/wKioL1SP4S6B0DPaAADlLqXlC9s185.jpg "style=" float: none; "title=" report5_multisource_sql_3.jpg "alt=" Wkiol1sp4s6b0dpaaadllqxlc9s185.jpg "/>

Refer to the above defined dynamic macro in the dataset.

Edit a report expression

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

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/57/0C/wKiom1SP4JCQZIvDAABXwd32dG8320.jpg "style=" float: none; "title=" report5_multisource_sql_4.jpg "alt=" Wkiom1sp4jcqzivdaabxwd32dg8320.jpg "/>

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.

Dynamic queries can be quickly stitched up by using dynamic functions of macros and reports SQL to complete the production of dynamic reports, macros compared to the simple logic of the case, if the splicing of SQL is more complex, need to do some calculation, then the use of macros is more difficult, this time you need to use the set of the script to complete.

using the Set -up script

In a collection report, you can introduce the goal of the set-up script to achieve dynamic SQL delivery through the collector DataSet and the script DataSet , which is done directly using the script dataset, because it does not involve reuse and excessive debugging.

The set-up script allows simple splicing of code to achieve the role of macros in the data source. In addition, the script can also complete a more complex operation, such as when the user in the specified table and column, some columns are required columns, even if the user does not have a choice, after the query is still displayed, it is necessary to splice SQL in advance to determine whether the required column in the user's chosen column, if not, then add later , and also to ensure that the user selects the order of the columns.

the data for the order form is still queried here, where the order ID, employee ID, and order Date are required fields, use the script dataset to complete the following steps:

Set Report Parameters

Similar to the example above, the selection column and condition parameters are still received in the report.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/57/0A/wKioL1SP4TGgBjqtAACxqVGmE-Q227.jpg "style=" float: none; "title=" report5_multisource_sql_5.jpg "alt=" Wkiol1sp4tggbjqtaacxqvgme-q227.jpg "/>

value: Order id, customer id, order date , arrival date , freight cost

Where value:where cargo area = ' North ' and Shipper City in (' Beijing ', ' Tianjin ', ' Zhangjiakou ', ' qinhuangdao ') .

Set up datasets

Scripting using the script data set SQL Stitching and fetching work, and returns the result set.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/57/0C/wKiom1SP4JSyf4xSAAEykulVZRk176.jpg "style=" float: none; "title=" report5_multisource_sql_6.jpg "alt=" Wkiom1sp4jsyf4xsaaeykulvzrk176.jpg "/>

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 get all the query fields by A2 the selected field collection with the Required field collection. Among them:

A1: Collection of all required fields

A2: Sets the collection of fields selected by the user to the set ofrequired fields to ensure that all required fields can be selected

a3-a4: Performing a concatenation of good SQL fetch

A5: Returning a result set for a report

To set a report expression

Report expression settings are consistent with the settings in the previous use macro, and are not repeated here.

The two ways to work with dynamic SQL in a set report can easily accomplish similar tasks, where macros are suitable for simple replacement scenarios, while complex situations require calculations to get the final SQL The scenario for a statement requires the use of a set of accounting scripts (the script dataset), in which the user can choose according to the situation.

This article is from the High performance report data calculation blog, so be sure to keep this source http://report5.blog.51cto.com/8028595/1590714

The dynamic SQL delivery of the diversity data source of the run-dry set report