Simplifies complex set operations of report data sources and computation of reports

Simplifies complex set operations of report data sources and computation of reports

Set operations are often used in computing report data sources. SQL supports set operations so that most simple set operations can be completed easily. However, when the set operation is complex and needs to be completed using a stored procedure, it is very difficult to implement such operations because the stored procedure does not support the set operation and cannot use intermediate results.

Images. Generally, report tools do not have strong computing capabilities, and such computing cannot be completed.

It is relatively simple to use the rundry set computing report. The actual business in the above link is used as an example to provide an implementation solution for the set computing report.

Report background

The source data is as follows:

You need to count the number of days that are not repeated for different IDs in the report, for example:

The difficulty of this report is that it is difficult to write a report using SQL or stored procedures. Generally, report tools do not have the data source computing capability and cannot be implemented at all.

The computing report itself has built-in scripts suitable for structured computing, allowing you to easily write data preparation calculations (equivalent to a simpler user-defined dataset ).

Compile the computing script

UseComputing Script Editor,Create a set computing script and set script parameters, such as the ID range.

Write a script to complete data computing and output the computing result set for the report:

A1: connect to the data source;

A2: Number of SQL statements executed based on the start and end ranges of IDs;

A3: adds the start date and end date fields based on the date range field;It must be noted that, Similar string splitting can also be completed using SQL, but the SQL string splitting function is not very convenient. Therefore, the implementation method of the Set Computing script is also provided here, which can be used by users;

A4: group by id first, merge the dates in all the date segments under each id (only one repetition is displayed), and then count to get the days that are not repeated; the principle is to convert these time periods into a set of dates (the period function), and then calculate the union of these sets. In this case, duplicate operations will be removed, finally, you only need to count the number of members in the Set (len ).

If the number of days without repetition is counted only once, that is, the overlapped dates are not counted, it is easy to use the set computing script. The following code can be used to complete the calculation:

A3.group (id ;~. Conj (periods (start date, end date, 1). group (). count (~. Len () = 1): No repeated days)

The principle is to count the number of subsets with a number of members of each subset After grouping by date, that is, the date that appears only once.

A5: return result set for the report

Edit a Report Template

Use the computing report editor to edit a report template for data presentation. Create a parameter and set the default value.

Create a report and set the dataset, and call the edited script file.

The dfx file path can be either an absolute or relative path. The relative path is the dfx home directory configured in the relative options. The B _id and e_id parameters are the report template parameters, begin and end are the Script Parameters. In fact, they can have the same name.

Edit the report expression and directly use the result set returned by the Set Computing script to complete report creation.

The report results are as follows:

As you can see, you can use the set calculator script to quickly complete the preparation of data for the report. In addition, the external computing script has a visual editing and debugging environment, and the edited script can be reused (called by other reports or programs ). However, if the script has been debugged and does not need to be reused, It is troublesome to maintain consistency between the two files (the Set Computing script and Report Template, in this case, it is easier to directly use the script dataset of the Set Computing report.

In the script dataset, you can write scripts to complete computing tasks step by step. The syntax is the same as that of the Set calculator. You can also directly use the data sources and parameters defined in the report. In this example, the script dataset can be used as follows:

1. Click "add" in the dataset settings window. The dataset Type dialog box is displayed. Select "script dataset ";

2. Compile the script in the pop-up script dataset editing window;

We can see that the parameters B _id and e_id are defined in the report directly in the script dataset, which is simpler and more direct than the separate set computing script.

3. The report template and expression are the same as those using the dataset of the cube.