How to optimize complex report computing by the set calculator (1) Data Source set and report Optimization

How to optimize complex report computing by the set calculator (1) Data Source set and report Optimization

There are many examples of how the calculator assists in report computing, but most of the computing difficulties occur before the data enters the report tool, for example, many cases of text computing and SQL assistance listed above are for report development. In addition, some complex computation occurs in the report tool, and some report problems that can be solved by using data source computation instead of computing difficulties. Here we will discuss the functions of the analyzer in these cases to assist the report tool.

Data Source set

Dynamic Data Source

In a report tool, the data source used by a report is usually determined. The report parameters are generally used only for selecting the dataset (that is, the WHERE part of SQL) and not for selecting the data source, if we want a report to use data sources that are determined by parameters, most report tools can do this directly. Generally, we need to use the API programming provided by the report tool, which is very cumbersome.

It is very simple to use a set calculator. The Set calculator can be used as a fixed data source for the report, and then the actual data source can be connected based on the parameters in the Set Computing script to return data.

	A
1	=$ {PPS}. query ("select * from T where F =? ", PF)

The data source name comes in with the PPAS parameter. The report tool does not have to support dynamic multi-data sources.

Similarly, some report tools require that the primary and subreports use the same data source. This method can also be used when different data sources are required for the primary and subreports, the Set calculator is used as the common data source of the primary and subreports, and the actual data source is determined by parameters in the Set Computing script.

 

Dynamic Dataset

In report tools, report parameters are usually used as parameters for Selecting Conditions for datasets, that is, SQL parameters. However, sometimes we need to replace a part of the SQL statement instead of using only the parameters. For example, the entire WHERE part is passed in as a parameter, so as to obtain more flexible query conditions.

Some reporting tools support macros to achieve this. For Report tools that do not support macros, you can only use the APIS provided by the report tool to rewrite the dataset definition of the report template with code, which is rather cumbersome. It is easy to use a set calculator:

	A
1	= "Select * from T" + if (where! = "", "Where" + where ,"")	Spell where. If it is null, do not spell it.
2	= Db. query (A1)

Some SQL statements are not easy to spell out even report tools that support macros. For example, if you want to aggregate the input field list, you need to add sum () to these fields. The report tool usually does not directly convert strings and must use APIs for processing, or you can spell it out in advance on the upper layer. In this case, it is easy to use the set calculator.

	A
1	= Sums. array (). ("sum (" + ~ + ") As" + ~). String ()	Convert a and B to sum (a) as a, sum (B) as B
2	= Db. query ("select G," + A1 + "from T group by G ")

Fetch limit

Due to the amount of capacity, we require the report to retrieve a maximum of 10000 rows of data. If the data is not collected, we need to add another row to mark the record with "continue" to show whether the data is complete. However, a report tool can only execute a specified number of statements. This flexible control requires complex code to call the API for implementation.

The process can be easily controlled by the code of the cube:

	A	B
1	= Db. cursor ("select * from T ")	= A1.fetch (1000)
2	If A1.fetch @ 0 (1)	> B1.insert (0, "continue ")	Insert Tag if not completed
3	> A1.close ()	Return B1