Data source processing for an indeterminate column (Dynamic column) report

Dynamic column reports are a form of dynamic reporting that is common in report applications. Such reports cannot use fixed column expressions in the report because they cannot determine the number of columns in advance, and it is even more tricky to prepare the report data source because SQL does not support indeterminate column result sets, needs to be dynamically spelled out SQL statements to execute, sometimes the business logic is not very simple, and you need to step through the use of stored procedures. The stored procedure does not have enough support for the set operation, and it is difficult to use intermediate results (to use temporal tables), which makes it not easy to complete such operations.

As mentioned in the http://bbs.csdn.net/topics/390937222?page=1#post-398564938, is a more typical dynamic column computing problem, which is often for the front desk Reporting Services, The use of SQL and stored procedures is difficult to complete, and the general reporting tools because of the lack of strong computing power, for this kind of calculation is more impossible to complete.

The use of run-dry set to calculate the report to do is relatively simple, the set of the report not only inherit the performance of the report in the presentation layer of dynamic column data source capacity, but also built-in a powerful data source computing engine, in both the operation and format can be well supported dynamic column report.

In this paper, the actual business in the above link is given as an example, and the implementation scheme of the Set calculation report is presented.

Report Background

The source data is as follows:

It is now necessary to arrange the category and output in the form of units in the report, in the following style:

The difficulty with this report is that it is difficult to write SQL or stored procedures, while the general reporting tools do not have the ability to compute data sources. The collection report itself has a script for structured computing that makes it easy to write the calculations for data preparation (which is equivalent to a more user-friendly custom dataset). The above reporting requirements can be accomplished using a set of reports:

writing a collection script

Start by using the set up Script Editor, create a new collection script, write a script to complete the data calculation, and calculate the result set for the report output.

The above script means:

A1: connecting data sources;

A2: Execution SQL takes a number from the production table;

A3: Grouped by unit, grouping results in the collector retain the grouped results (members) to facilitate subsequent use and calculation, this grouping results are as follows:

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

A5-A6: Creates the result order table, the record is empty;

A7-B8: The grouping results in the cyclic A3, the categories and the output in each group are written into the new result order table;

A9: Returns a result set for the report.

edit a report template

Use the collection report editor to edit report templates for data presentation. Create a new report and set up the collector DataSet to invoke the edited script file above.

Where the DFX file path can be either an absolute path or a relative path, relative paths are configured in the relative option of the DFX home directory.

Edit the report expression, use the Dynamic Data source mechanism of the collection report to complete the report production.

Because it is a dynamic column, the Ds.field () function is used in the report to dynamically obtain the column data based on the number of columns, and the first row and a are listed as auxiliary columns, which is set to be hidden.

The report shows the following results:

As you can see, this kind of dynamic column report data source can be quickly completed using the collector script compared to SQL. Moreover, the external set-up 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 would be cumbersome to maintain the consistency of the two files (both the set and report templates), which makes it easier to use the script dataset directly for the set report.

In a script dataset, you can step through a script to complete a calculation task, with the syntax consistent with the concentrator, and you can use the data sources and parameters that the report defines well. This example uses a script dataset to do this:

1. Click the "Add" button in the DataSet Settings window to pop up the dataset Type dialog and select "Script Data Set";

2. Write the script in the Popup Script dataset editing window;

As you can see here, the data source Emos defined in the report is used directly in the script dataset, which is simpler and more straightforward than a separate set-up script.

3. Report templates and expressions are consistent with the use of the Collector dataset, and no longer repeat them.

Data source processing for an indeterminate column (Dynamic column) report