Multiple judgments of report data sources

Reports in the data source preparation phase is often based on the actual business to make a variety of judgment calculation in order to get the final report data source, and the use of SQL in this case is difficult to write, http://bbs.csdn.net/topics/390938280 mentioned in the attendance problem is one of them , this calculation appears to be for the financial or Labor Department's attendance report service. Computational logic is not complicated, but using SQL is difficult to do, with stored procedures (to take the record) is also very cumbersome, and the general report tools because there is no strong computing power, often can only write with Java and other write custom data source implementation.

It is simpler to use the run-dry set to calculate the report, here the actual business in the above link as an example, the implementation of the set of calculation report.

Report Background

The source data is as follows:

now need in the report to date and performance statistics attendance, requirements according to: daily from 7 o'clock to 12 points to clock out, 16 points after the clock, the first time to take the card to work cards, the latest time for the work card, if there is no clock record in the period of time to show no punch, After 8 o ' clock 30, I was late for work before 18 o'clock. Get similar results as shown in the following table:

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

First Use the set up Script Editor, create a new set of calculation scripts, set the script parameters, such as the swipe start and end time range:

Write a script to complete the calculation of the data and calculate the result set for the report output.

	A	B
1	=connect ("Kaoqin")
2	=a1.query ("SELECT * From time attendance table where to swipe >=? and swipe time <=? ", b_date,e_date order by employee number, swipe time)
3	=a2.group (Employee name, date (card time))
4	=create (date, employee name, work, work, whether late, whether to leave early)
5	For A3	=A5 (1). Swipe time	=string (Time (B5))
6		=A5.M (-1). Swipe time	=string (Time (B6))
7		=if (c5>= "07:00:00" && c5<= "12:00:00", B5, "not clocked in")
8		=if (c6>= "16:00:00", B6, "not clocked in")
9		=if (c5>= "08:30:00" && c5<= "12:00:00", "Late", if (c5<= "08:30:00", "", "not clocked"))
10		=if (c6>= "16:00:00" && c6<= "18:00:00", "Leave early", if (c6>= "18:00:00", "", "not clocked in"))
11		>a4.insert (0,date (A5. Card time), A5. Employee name, B7,B8,B9,B10)
12	Result A4

A1: connecting data sources;

A2: According to the specified time range, execute SQL query attendance table data, query results according to the staff and the card time sorting, sorting is to facilitate the subsequent acquisition of records;

A3: Grouped by employee and swipe date, unlike SQL, grouping results in the set script retain group members;

A4: Create empty result order table;

A5-C11: The group in the cyclic A3, according to the earliest and latest swipe time by judging the attendance of employees, and finally write the results back to the A4 result sequence table;

Among them, B6 through A5.M (-1) to obtain the last record, the set of the collection report is ordered, so it is easy to get the corresponding members by ordinal, which is very different from the SQL set disorder;

A12: Returns a result set for the report.

edit a report template

Use the collection report editor to edit report templates for data presentation. First create a new parameter and set the default value.

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, the relative path is the DFX home directory configured in relative options, the parameters b_date and e_date are script parameters, and begin and end are report template parameters, in fact they can have the same name.

Edit the report expression to complete the report production directly using the result set returned by the collection script.

The report shows the following results:

As you can see, using a collector script can quickly accomplish this type of scenario with multiple judgments. 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 Kaoqin and the parameter begin and end that are defined in the report are 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.

Multiple judgments of report data sources