How to optimize complex report computing with a set calculator (1) Data Source set

How to optimize complex report computing with a set calculator (1) Data Source set
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. The data source used by a report in the data source set Dynamic Data Source report tool is usually determined. The report parameters are generally used only for selecting the dataset (that is, the WHERE part of SQL) instead of selecting a data source, if we want a report to use a data source determined by the parameters, this can be done directly by most report tools, generally, you 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. In a dynamic dataset report tool, report parameters are usually used as parameters for Selecting Conditions for a dataset, 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 ")

Due to the quantity limit, the report needs to retrieve a maximum of 10000 rows of data. If the data is not obtained, you need to add another row to mark the record with "continue" to check 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