Data Source processing for an indefinite (dynamic) Report, report

Data Source processing for an indefinite (dynamic) Report, report

Dynamic Column reports are a type of dynamic reports, which are common in report applications. Because these types of reports cannot determine the number of columns in advance, they cannot use a fixed column expression in the report. What's more tricky is the preparation of the report data source, because SQL does not support the result set of an indefinite column, it is necessary to dynamically spell out SQL statements for execution. Sometimes the business logic is not very simple, but also needs to be completed step by step using the stored procedure. However, the storage process does not support set operations and it is difficult to use intermediate results (use temporary tables). As a result, it is not easy to complete such operations.

Like a http://bbs.csdn.net/topics/390937222? The problem mentioned in page = 1 # post-398564938 is a typical dynamic column computing problem, which is often used for foreground reports, it is difficult to use SQL and stored procedures. However, a general report tool cannot complete such computing because it does not have powerful computing capabilities.

It is relatively simple to use the rundry computing report. The set computing report not only inherits the ability of the rundry report to process dynamic column data sources at the presentation level, but also has a powerful data source computing engine built in, both computation and format support dynamic column reports.

The actual business in the above link is used as an example to provide the implementation scheme of the computing report.

Report background

The source data is as follows:

In the report, you need to arrange the category and output horizontally in units. The style is as follows:

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 ). The preceding report requirements can be completed using the set computing report as follows:

Compile the computing script

UseComputing Script Editor,Create a set computing script, write a script to complete data computing, and output the computing result set for the report.

Meaning of the above script:

A1: connect to the data source;

A2: Number of SQL statements executed from the output table;

A3: grouping by unit, the grouping result (Members) is retained in the grouping result of the Set calculator for future use and computation. The grouping result is as follows:

A4: calculates the maximum number of members in a group to determine the number of columns in the result set;

A5-A6: Create a result sequence table with empty records;

A7-B8: loop the grouping results in A3, writing the categories and outputs in each grouping into the new result order table;

A9: return result set of the report.

Edit a Report Template

Use the computing report editor to edit a report template for data presentation. 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, and the relative path is the dfx home directory configured in the relative options.

Edit the report expression and use the Dynamic Data Source Mechanism of the computing report to prepare the report.

Because it is A dynamic column, you must use the ds. field () function in the report to Dynamically Retrieve column data based on the number of columns. The first row and column A are auxiliary columns, and you can set them to hide.

 

The report results are as follows:

As you can see, this type of Dynamic Column report data source can be quickly completed compared to the SQL statement using the cube script. 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 data source emos defined in the report is directly used 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.