CN101021839A - Nonlinear report generating method - Google Patents

Abstract

The invention relates to a method for generating a statement, which specifically relates to a method for generating a dynamic statement associated with database (or it can be called nonlinear statements generation method). In which, it firstly determines the data group which will be used in statement and names the data. It secondly determines some cells to be the expanded grid and sets a direction for expansion. It continuously determines the left and up cells. It continuously fills in operational expression in the cells. It finally expands and calculates about the statement to achieve an actual statement.

Description

Nonlinear report generating method

Technical field

The invention belongs to technical field of computer information processing, relate to a kind of report form generation method, be specifically related to a kind of can with the generation method of the dynamic statement of database association, or be called nonlinear report generating method.

Background technology

The tool software that is used to make form can be divided into two classes: a class is that the Excel with Microsoft is the static statement instrument of representative, it is characterized in that the form of drawing is static, can not increase or reduce the ranks number automatically with data linkage in the database and change wherein data.Another kind of then is the dynamic statement instrument, can be related with database (mainly being relevant database), dynamically from database, obtain data and draw in advance the form that good report form template and parameter generate different layouts (row, column etc.) and statistical content automatically according to the user, the form that numerical procedure is identical only need be drawn once like this, can greatly reduce the workload of report generation and increases work efficiency.The present invention is primarily aimed at the dynamic statement Core Generator.

Before the processing scheme of describing dynamic statement instrument (hereinafter to be referred as report tool), introduce the notion of data set earlier, the Data Source of report tool is all with the form tissue of data set.

Data set can be understood as a two-dimensional matrix that is made of the data that row name and column data type are arranged, as the result set (can be the SELECT of multilist multiplication cross) that in relational database, uses the SELECT statement to return, the row of data set is equivalent to a record of tables of data, the field of this tables of data that row are then corresponding.

Can carry out to the ordering (being equivalent to the ORDER BY among the SELECT) of row and multilayer grouping (GROUP BY) and at the filtration (WHERE) of expression formula to going in the row at data set.

The tradition report tool mainly provides following several base conditioning scheme:

One, single source monolithic multilayer grouping scheme (being called for short the grouping scheme)

Each report form template has only a data set as Data Source in the grouping scheme.

The grouping scheme is divided into several banded zones (BAND) with report form template, general called after gauge outfit (Header), group head (Group Header), details (Detail), group tail (Group Trailer), table tail (Trailer).Detail areas is corresponding one by one with the row of data set, and packet zone (group head, group tail) is then corresponding one by one with the grouping of data set, may not have packet zone in the form or the multilayer packet zone is arranged, and this depends on the number of plies whether data set is grouped and divides into groups.

In each zone, all can add the calculation expression of reach, thereby finish some auxiliary freely computing functions, as can in packet zone, adding the calculating that gathers at this group data in the one's respective area.

Adopt the form of grouping scheme that following restriction is arranged:

1. single source: in the form a plurality of data sets as Data Source can not be arranged, a direct consequence is exactly that the form that relates to the multidata table obtains with regard to adopting complicated SQL (JOIN/UNION or nested) even editor's storing process just can finish data, makes report making very loaded down with trivial details;

2. monolithic: data set can carry out the multilayer grouping, but every layer of grouping can only have a slice, can't realize burst form (same batch data goes to check by different dimensions, just must be made into two forms as person chart by the age-based again grouping of educational background grouping) like this.

Two, single source monolithic multilayer interleaved scheme (abbreviation interleaved scheme)

Interleaved scheme is the report form template that is come out by the OLAP theoretical developments, still only supports the forms data collection.It with some column split of data centralization after as the left-handed watch head of form, and after other column splits as the last gauge outfit of form, other row gather the content of value as the intermediate data district, thereby but formed all intersection forms of dynamic change of row, column.

Owing to adopted same grouping mechanism, interleaved scheme to remain monolithic.In addition, the such areal distribution edit capability of scheme because interleaved scheme is not divided into groups, its Report layouts can only be in advance by the inflexible pattern of report tool regulation (as combined data can only be in the lower right and all groups have entirely or do not wait entirely), be difficult to flexible control; Simultaneously it does not have freely to add the ability of calculation expression yet, like this can not the various auxiliary computational items of very random design, can only select to use the project that report tool designs in advance.

Because these restrictions, interleaved scheme are seldom used in actual applications.Crosstab often is forced to write a large amount of codes by the programmer and adopts the grouping schemes to realize in the reality.

Three, the fixing multilayer scheme (be called for short multi-source scheme) of multi-source burst

For solving the problem of multi-data source, some traditional report tool (is representative with Muscovite FastReport) has proposed the multi-source scheme again, permission is added subregion (SubBand) on the detail areas of grouping scheme, this zone can with the data binding of another data set, thereby can support the form of multidata collection; And a plurality of subregions can be arranged simultaneously, and so both can support the burst form, can also add the secondary subregion on the subregion, thereby realize the multi-source burst form of multilayer.

But the use of subregion still has following restriction:

1. the data set of subregion correspondence can not divide into groups again, can only represent in detailed mode;

2. the association between the data source collection of subregion and main areas correspondence thereof sets in advance, can only adopt the scheme that external key is provided with in the similar database, and can not be arbitrarily related, two kinds of different interrelational forms (at this moment must do a data set again) can not be arranged in same form;

3. the number of plies of subregion is a report tool regulation in advance, can not be ad infinitum to subregion add secondary, three grades ... subregion, current FastReport version can be supported six layers.

In addition, as replenishing of above-mentioned deficiency, many traditional report tools are all supported subreport.Promptly allow on the zone of (grouping or multi-source scheme) report form template, to add subreport, and subreport can adopt diverse data source collection, and can add a plurality of, thereby part solves the problem of multi-source burst.(ruling of boss's table is difficult to alignment but the use of subreport brings the layout difficulty again, particularly after sublist has caused layout change owing to data volume), data between boss's table can only transmit (direction is single and amount is too little), therefore also less in actual applications use by parameter downwards.

Above-mentioned several scheme is except that interleaved scheme (but statistical function is too weak), and other scheme (comprise and adopt subreport) all is far to be better than the column direction ability in the line direction ability.Can't accomplish the ranks symmetry, but the form in the reality requires to have the changing capability same with line direction on the column direction of being everlasting.This is the difficult point that traditional report tool can't be realized.

Aspect statistical computation, data set grouping in the tradition report tool is all based on the relational database principle, can only support the grouping (being that all elements all must and can only be assigned in the group) of dividing fully, but it much is to need not exclusively grouping that the form in the reality has, as incomplete grouping (not all element all divides certain and organized) and can heavily divide into groups (certain element has been assigned in two different groups), at this moment just in advance the coding code with incomplete packet switched one-tenth fully division just can finish the design of form.

Simultaneously, tradition all is not provided at the name reference mechanism of design phase to data item on the form as a result in the report tool, can not visit the data on the form as a result neatly, compartment computing in generally can only going together or at the set operation of complete group (or all), for inter-bank group computing quite difficult (as calculating the ratio same period), random absolute case computing is then more impossible.Can only be in advance or write afterwards that code is prepared or adjust data on the form.

Summary of the invention

The objective of the invention is to defective, provide a kind of novel dynamic report generation method, i.e. nonlinear report generating method at traditional report tool.This method is ranks direction ability symmetry in the report generation process, and has flexibly the related burst function of multi-source, not exclusively grouping and compartment calculation function freely.

In order to realize the foregoing invention purpose, technical scheme of the present invention is as follows: nonlinear report generating method comprises the steps:

The data set that need use when (1) determining the form computing, and name respectively;

(2) determine that in static statement some cell is the expansion lattice, promptly initiatively expands the cell that duplicates, and its propagation direction is set;

(3) the left nominative and the last nominative of all cells are set, i.e. the expansion lattice that when vertical and horizontal are expanded, need follow of this cell;

(4) in each cell, insert operation expression;

(5) form is expanded and calculated the actual form after obtaining generating.

Further, in above-mentioned nonlinear report generating method, the data set in the step (1) is a plurality of, and a plurality of data sets are distinguished by name.Conventional tool usually can only have 1 then need not name.

Further, in above-mentioned nonlinear report generating method, the propagation direction of expansion lattice is divided into vertical expansion and two kinds extending transversely in the step (2), and expansion lattice can only have a propagation direction simultaneously.

Further, in above-mentioned nonlinear report generating method, the vertical expansion lattice in the step (3) can only have left nominative and not go up nominative, and lattice extending transversely have only last nominative and do not have left nominative; The existing left nominative of non-expansion lattice also has last nominative.Claim that cell is the sublattice of its nominative.

Further, any left nominative must be vertically to expand lattice or root lattice, and last nominative must be lattice extending transversely or root lattice, and wherein the root lattice are physically non-existent logic imagination lattice.

Further again, in order to simplify setting, arrange a default: unless left nominative/last nominative was set by craft, otherwise the left nominative of any cell/go up nominative to be set to its left side/top from its nearest vertical/lattice extending transversely defaultly, if its left side/top is vertical/lattice extending transversely not, then its left nominative/upward nominative is set to the root lattice defaultly.

Further, in above-mentioned nonlinear report generating method, the expression formula of inserting in cell in the step (4) is divided into two kinds of set operation and monadic operations, set operation is meant that operation result is the computing of the set that is made of a plurality of elements, and monadic operation refers to that then operation result has only the computing of a single-element.The expression formula of inserting in the expansion lattice must be set operation (but might be that the results set element number is 1 set), the expression formula of inserting in non-expansion lattice then both can be that set operation also can be monadic operation, in most cases was monadic operation.

Further, above-mentioned computing (no matter set operation or monadic operation) is data in reference data intensive data and the cell freely all, and allow both to be blended in simultaneously in the expression formula.

Further again, when precedents, allow to use so-called level coordinate or displacement coordinate and lattice set representations method thereof.With the major grid line of cell (by the nominative of nominative, nominative ..., the sequence that becomes of root lattice) on nominative as reference point, the serial number of example element lattice in its own nominative scope after duplicating with each nominative (all be expansion lattice) expansion is as side-play amount, both cooperate any one cell that gets final product on unique definite generation form, thereby a kind of scheme of naming for generation form cell in the design phase is provided.This scheme has two kinds of forms, claims the level coordinate when adopting absolute offset values, claims displacement coordinate when adopting relative displacement (with respect to the cell at expression formula place).Adopt a plurality of Set cells of level coordinate or displacement coordinate name can also be combined into cell set (being called for short the lattice collection), in order to participate in polymerization computing (as summation, average etc.).

Further, in above-mentioned nonlinear report generating method, in the step (5) form expanded and earlier the expansion lattice are calculated when duplicating and expand, and then non-expansion lattice are calculated.When a plurality of expansion lattice were arranged on the form, the expansion order was a son behind the former head, must not calculate expansion before certain nominative of even expanding lattice is expanded in advance.

Further, to vertically/during lattice extending transversely expansion, find out the minimum zone/column region of row continuously that comprises these lattice and all offspring's lattice, offspring's lattice of certain expansion lattice are meant offspring's lattice of its sublattice or its son expansion lattice.With this panel region downwards whole/duplicate a plurality of to the right, number of copy times is the element number of the operation result (being a set) of these expansion lattice, and each element is write in the expansion lattice corresponding in the zone of duplicating out successively, and the expression formula of other cell will be by slavish copying in the replication region.

Further again, for certain cell is arranged in the replication region is not offspring's lattice of these expansion lattice, if the cell that these can be duplicated out after the expansion can join together, then will be merged into big lattice, otherwise do not merge, except that especially original, these cells that duplicate are out filled a vacancy, and the expression formula in the original lattice is kept intact.

Further, in above-mentioned nonlinear report generating method, in the step (5) certain cell in the form is calculated when relating to the data set computing, calculate (as tabulation or grouping) if there is certain expansion lattice that same data set has been carried out dividing on the major grid line of this cell, then when calculating this cell expression formula, its data set computing will be limited in dividing (as certain group after the grouping) in the data set later.

Beneficial effect of the present invention is:

(1) ranks symmetry

Expansion lattice in the method can have longitudinal and transverse two propagation directions, and all rules also all are symmetrical for both direction.Like this, the ability that line direction has has too at column direction, thereby realizes the complete ranks symmetry of Report Form Design.

(2) burst of multi-source association flexibly

Data set only exists as the expansion and the compute mode of some cell in the method, have what data sets not influence expansion and computing in the form, so this programme can natural support multi-data source collection.

Any expansion lattice in the method all can have a plurality of son expansion lattice, and son expansion lattice can also have the son expansion lattice of next stage, thereby can form random multilayer burst form, and the level of each burst can also be different.

The incidence relation of nominative and sublattice is by the expression formula in the cell other cell reference to be finished, therefore its incidence relation is so long as can just can use with the expression formula description, and in different occasions different incidence relations can be arranged between two data sets in the same form, even can set up incidence relation between constant expansion lattice and the data set, must be the good unique inflexible relation (external key) of predefined and needn't resemble some improved conventional tool.

(3) not exclusively grouping

Data set grouping in the method will only be carried out as a kind of ordinary set computing, and groupings at different levels and classified statistics all there is no natural link with this set operation, can Free Writing associative expression formula finish calculating and grouping.As long as the user writes the set operation that can return incomplete grouping, and needn't worry that the expression formula of this cell can produce adverse consequences to the computing of other cell.

(4) computing between free lattice

Adopt in the method method of level coordinate and lattice collection and conditional expression thereof provide a kind of in the design phase mechanism (level coordinate, displacement coordinate and Ge Ji) to data item (cell) name in the form as a result.Thereby realize calculating between free lattice flexibly, no matter clocklike the computing of inter-bank group still is that irregular absolute case calculates and can finish easily.

Description of drawings

Fig. 1 is a multilayer intersection consolidated statement synoptic diagram.

Fig. 2 is a multi-source burst form synoptic diagram.

Fig. 3 is a band rank accounting report synoptic diagram.

Fig. 4 is the synoptic diagram that expansion lattice and interior expression formula thereof are set at Fig. 1 example.

Fig. 5 is the synoptic diagram that expansion lattice and interior expression formula thereof are set at Fig. 2 example.

Fig. 6 is the synoptic diagram that expansion lattice and interior expression formula thereof are set at Fig. 3 example.

Fig. 7 is the process flow diagram of the method for the invention.

Embodiment

The concrete case study on implementation that provides a kind of nonlinear report generating method below in conjunction with Figure of description reaches based on the typical report making example of the part of this realization, and the present invention is described in further detail.

1. something is arranged earlier

Adopt the cell naming mechanism identical, represent that as A1 the grid in the upper left corner, A2 represent the grid of the 2nd row the 1st row etc. with EXCEL.The title that merges the cell in its upper left corner of lattice called after.

As:

A1	B1	C1	D1
				A2	C2
A3		B3

For sake of convenience, represent to expand cell on the form of back with lowercase, as a1, the cell before b3 etc., capitalization then represent to expand on the form.

Be without loss of generality, the data set content can be determined with the SELECT statement of relational database, usually only be to indicate its corresponding SELECT statement when indicating data set.

Define several set operations of using of in example, needing:

●list

Grammer: list (exp1, exp2 ...);

Function calculates each transition formula evaluation in its parameter and forms a set in order as a result of.

●select

Grammer: ds.select (exp, filter);

Ds refers to dataset name, and exp selects expression formula, and the filter filtercondition is not write filter and represented and need not filter.Function calculates exp then line by line with the filter filtration according to condition earlier of the record among the ds, and calculated value is constituted an orderly set as a result of.

●group

Grammer: ds.group (exp, filter);

The parameter meaning is identical with select.Form results set again after the packet of group with taking-up, promptly adjacent identical data will be merged into a value.If the result of the select function of same parameter be 1,1,1,1,3,3,2,2,3,1,1,2,2,3}, then the result of group then be 1,3,2,3,1,2,3}.

Select and group function also all are the partitioned operations of data set, and the row that is about to data set has been divided into a plurality of parts, and each part is with an element in corresponding its rreturn value (set).

2. extension rule for example

Example 1, before the expansion:

A1	B1	C1	D1	E1
					A2	B2	C2	D2	E2

Wherein, A2 vertically expands lattice, and expression formula is list (1,2,3,4); Other cell all is non-expansion lattice (following unaccounted non-expansion lattice that are).The expansion back:

a1	b1	c1	d1	e1
						1	b2	c2	d2	e2
2	b3	c3	d3	e3
						3	b4	c4	d4	e4
4	b5	c5	d5	e5

A2 is copied into a plurality of downwards according to the element number of operation result collection in these lattice, write each element in the result set successively; And sublattice also is replicated thereupon.

Example 2, before the expansion:

A1
		A2	B2
A3	B3

Wherein B2 is lattice extending transversely, and expression formula is list (1,2,3,4,5,6).The expansion back:

a1
							a2
	1	2	3	4	5	6
							a3	b3	c3	d3	e3	f3	g3

B2 is copied into a plurality of to the right, writes each element in the result set successively; And its attached lattice also are replicated thereupon.Attention, can widen the grid above it (A1) during the expansion of B2 lattice according to rule.

Example 3, before the expansion:

A1	B1	C1	D1
				A2	B2	C2	D2

Wherein C2 is vertical expansion lattice, and expression formula is list (1,2,3), and the left nominative of A2, B2 and D2 all is set at C2, this moment A2, the left nominative C2 of B2 cell is on its right.The expansion back:

a1	b1	c1	d1
				a2	b2
	1	d2
			a3	b3
	2	d3
			a4	b4
	3	d4

C2 initiatively duplicates downwards, and sublattice also is replicated (no matter on the left side still is the right) thereupon.

Example 4, before the expansion:

A1	B1	C1	D1	E1
					A2	B2	C2	D2	E2

Wherein A2 vertically expands lattice; The left nominative of B2, C2, E2 is A2, and the left nominative of D2 is set to the root lattice.The expansion back:

a1	b1	c1	d1	e1
					a2	b2	c2	d2	e2
a3	b3	c3	e3
				a4	b4	c4	e4
a5	b5	c5	e5

A2 is copied into a plurality of downwards, and its each sublattice also is replicated thereupon, and because D2 is not the sublattice of A2, so expansion back D2 lattice have been merged into a big lattice d2.

Example 5, before the expansion:

A1	B1	C1
			A2	B2	C2

Wherein A2 lattice and B2 lattice are vertical expansion lattice, and the left nominative of B2 is A2, and the left nominative of C2 is B2.When expansion, son was expanded lattice after the former head expanded lattice, promptly expanded A2 earlier, expanded B2 respectively again in each part that expansion is come out.The expansion back:

a1	b1	c1
			a2	b2	c2
b3	c3
		b4		c4
a5	b5				c5
		b6	c6
	a7			b7	c7
b8		c8
			b9	c9

3. data set calculated example

Example 1, before the expansion:

Date	The returned money volume
		A2	B2

Data set ds1=SELECT date, pay FROM income

A2=ds1.select (date), vertically expansion

B2＝ds1.pay

The expansion back:

Date	The returned money volume
		2005-2-20	2300
2005-3-14	1450
		2005-4-28	2369
2005-6-2	2235

B2 is duplicated by drive when expansion as the sublattice of A2, and the data set row of quoting during the B2 computing will be corresponding with the record that A2 selectes.

Example 2, before the expansion:

The client	Product	Sales volume
			A2	B2	C2

Data set ds1=SELECT customer, product, quantity FROM buy_record

A2=ds1.group (customer), vertically expansion

B2=ds1.group (product), vertically expansion

C2＝ds1.sum(quantity)

Wherein sum represents summation operation, is about to be listed as the value addition of quantity.

The expansion back:

The client	Product	Sales volume
			Wuxi food processing factory	Salad oil	200
Soybean	400
		Chemical plant, Great Wall, Tianjin		Sodium Benzoate	190
Propiophenone	123
			Sky, Jiangsu dragon food group	Salad oil		10
Soybean	1000
		Sodium Benzoate		27

B2 is when calculating, and group functional operation is wherein implemented will be limited to be divided in the data set line range later by group function among the A2 and carried out, but not whole data set, the sum among the C2 calculates in the data set line range that will be limited at again after B2 divides simultaneously.

Example 3, before the expansion:

Class	Name	Subject	Total points
				C2
		A3			B3	C3	D3
Class's total points (B4)			D4

Data set ds1=SELECT class, name, subject, score FROM score_record

A3=ds1.group (class), vertically expansion

B3=ds1.group (name), vertically expansion

C2=ds1.group (subject), extending transversely

C3＝ds1.score

D3＝ds1.sum(score)

D4＝ds1.sum(score)

The expansion back:

Class	Name	Subject		Total points
					Mathematics	Chinese language
		One class	The one of the Mount Liang heroes in Water Margin				80	60	140
Lin Chong	90			90	180
			Lu Junyi			80	90	170
Grandson two mas	80			80	160
			Wu uses			90	100	190
Wu Song	90			90	180

	Class's total points
					Two classes	Guan Yu	95	90	185
Liu is equipped with	80	100	180
				Zhang Fei		100	75	175
Class's total points			540

This is a typical form that intersects, and C3 wherein can follow its left nominative simultaneously and last nominative is extended to netted.Though D3 is identical with the D4 expression formula, its nominative difference, therefore corresponding data set line range is also different, thereby the result of calculation difference.

Example 4, before the expansion:

Company	Scoring
		B2 (project)
	A3		B3

Data set ds1=SELECT name FROM company

ds2＝SELECT name FROM item

ds3＝SELECT company_name，item_name，score FROM score

A3=ds1.select (name), vertically expansion

B2=ds2.select (name), vertically expansion

B3＝ds3.sum(score，company_name＝＝A3&&item_name＝＝B2)

Second parameter indicating of sum function is used for the condition of filtering data collection.

The expansion back:

Company	Scoring
				Education administration system	General foreground	Business platform
	Tsing-Hua University's innovation	450	400				355
Yongyou Software				490	480	320
	Anyi software	365	420				480

The expression formula of B3 has data set (ds3) and cell (A3 simultaneously, quoting B2), simultaneously since A3 and B2 respectively by data set ds1 and ds2 acquisition, thereby under the situation that need not write multilist multiplication cross SQL or storing process, promptly realized the effect of multi-source association well.

4. level coordinate dative collection for example

Write the literary style of each nominative and side-play amount formation cell level coordinate [] of cell back, nominative and side-play amount are used: separate, relative side-play amount (displacement coordinate) preceding will titled with+/-number respectively expression skew forward backward, to make things convenient for side-play amount be that 0 nominative and side-play amount thereof can be omitted and do not write for simplification is write, if all nominative skews all are 0, then [] is whole can be indefinite.

Example 1, before the expansion:

A1	B1	C1	D1
				A2	B2	C2	D2

The expansion back:

a1	b1	c1	d1
				a2	b2	c2	d2
c3	d3
		b4	c4			d4
c5	d5
			c6		d6
a7	b7					c7	d7
		c8	d8
				c9	d9
		b10	c10			d10
	c11			d11
			c12		d12

The level coordinate of c8 is C2[A2:2, B2:1];

The level coordinate of d6 is D2[A2:1, B2:2, C2:3];

C10 is C2[B2:+1 with respect to the displacement coordinate of c8, C2:-1] (the last skew of A2 is 0 omission).

Example 2, before the expansion:

	B1	Compared with the same period of last year
			A2	B2	C2
Than last	B3

Data set ds1=SELECT years, monthes, item, amount FROM income

A2=ds1.group (monthes), vertically expansion

The left nominative of A3 is set to A2

B1=ds1.group (years), extending transversely

B2＝ds1.sum(amount)

B3＝B2-B2[A2：-1]

The last nominative of C1 is set to B1

C2＝B2-B2[B1：-1]

The expansion back:

	2004	Compared with the same period of last year	2005	Compared with the same period of last year
					January	123.00		134.00	11.00
Than last
					February	234.00		255.00	21.00
Than last	111.00		121.00
					March	356.00		378.00	22.00
Than last	122.00		123.00

Realized in the expression formula of B3 and C2 that the inter-bank (row) to B2 quotes.

In [] of cell level coordinate (displacement coordinate), remove several nominatives and side-play amount thereof, and, then in { }, write filtercondition when needing the plaid matching collection to refilter at the lattice collection that a collection of cell that adds { } expression side-play amount that the side-play amount on that duplicate out and the nominative that stay is write for [] by same original lattice after [] in expansion process in [] constitutes.

Example 3, before the expansion:

A1	B1	C1	D1
				A2	B2	C2	D2

The expansion back:

a1	b1	c1	d1
				a2	b2	c2	d2
c3	d3
		b4	c4			d4
c5	d5
			c6		d6
a7	b7					c7	d7
		c8	d8
				c9	d9
		b10	c10			d10
	c11			d11
			c12		d12

D7, d8, d9 is all identical with level on the B2 at A2, the level difference on C2 only then can be with lattice collection D2[A2:2, B2:1] { } expression.Similarly, C2[A2:1, B2:2] { }={ c4, c5, c6}.

With respect to the lattice collection D2[A2:-1 of d11, B2:0] { } then be d4, d5, d6}, and with respect to the lattice collection D2[A2:-1 of d8, B2:0] { } then be { d1, d3}.

Example 4, before the expansion:

The client	Type	Amount to
			B2
	A3			B3	C3
Amount to		B4	C4

Data set ds1=SELECT customer FROM custom_rec

ds2＝SELECT style FROM machine

ds3＝SELECT customer，quantity，style FROM record

A3=ds1.group (customer), vertically expansion

B2=ds.group (style), extending transversely

B3＝ds3.sum(quantity，customer＝＝A3&&style＝＝B2)

B4＝SUM(B3{})

C3＝SUM(B3{})

C4＝SUM(B3{})

Wherein SUM represents summation operation.

The expansion back:

The client	Type			Amount to
					A type machine	The Type B machine	C type machine
	Beijing iron company	6	0					20	26
North Mining Group company				7	25	25	57
	The Chengdu Power Group	9	20					10	39
Dalian non-ferrous metal group				0	0	90	90
	Guangdong Bureau of Mines	20	8					0	28
Amount to				42	53	145	240

Though the lattice collection literary style in B4, C3, the C4 expression formula is consistent, because its nominative difference, then Dai Biao lattice collection is also different, causes its expression solution difference as a result.

Example 5, before the expansion:

	Accounting	Actual value
			A2	B2	C2
A3	B3	C3
			Total assets	B4	C4

Data set ds1=SELECT amount, item FROM record

ds2＝SELECT type，id FROM type

A2=ds2.group (type), vertically expansion

A3=ds2.group (id), vertically expansion, left nominative is set to A2

B2＝SUM(C3[A2：0]{})/C4

B3＝C3/C4

C2＝SUM(C3{})

C3＝ds1.sum(amount，item＝＝A3)

C4＝ds1.sum(amount)

After the expansion

	Accounting	Actual value
			Non-profit assets	0.019	671.000
Non-profit assets 5	0.001	45.000
			Non-profit assets 4	0.001	35.000
Non-profit assets 3	0.013	456.000
			Non-profit assets 2	0.002	57.000
Non-profit assets 1	0.002	78.000
			Non earning asset	0.356	12636.000
Non earning asset 19	0.191	6778.000
			Non earning asset 18	0.016	567.000
Non earning asset 17	0.123	4356.000
			Non earning asset 16	0.013	456.000
Non earning asset 15	0.010	345.000
			Non earning asset 14	0.004	134.000
Earning assets	0.541	19209.000
			Other earning assets	0.251	8908.000
Investment in bonds	0.281	9989.000
			Deposit Central Bank	0.002	78.000
Loan assets	0.007	234.000
			Revenue assets	0.085	3018.000
Revenue assets 13	0.003	124.000
			Revenue assets 12	0.006	214.000
Revenue assets 11	0.060	2134.000
			Revenue assets 10	0.015	546.000
Total assets	1.000	35534.000

Here utilize the lattice collection calculated accounting (each item number according to the ratio of summation, summation obtains with the lattice collection).

Example 6, before the expansion:

A1	B1	C1
			Effective average value measured	B2	C2

Here so-called " effectively measured value " is meant that effective voltage (B1) does not surpass 100, and watt current (C1) is no more than 10 measured value.

Data set ds1=SELECT number, v, i FROM result

A1=ds1.select (number), vertically expansion

B1＝ds1.v

C1＝ds1.i

B2＝AVG(B1{B1＜＝100})

C2＝AVG(C1{C1＜＝10})

Wherein AVG is for asking average calculating operation.

The expansion back:

1	76.5	8.54
			2	45.1	7.25
3	120.0	1.26
			4	25.8	48.5
5	90.0	8.60
			6	76.2	5.00
7	35.0	13.3
			8	117.2	5.25
9	258.5	7.86
			Effective average value measured	58.1	6.25

Used the filtercondition of lattice collection in this example.

In the conditional expression of lattice collection, sometimes need to use the value of expression formula place cell or its nominative, then represent value titled with the $ symbol before the cell in lattice collection conditional expression with the former cell of REFER expression.

Example 7, before the expansion:

A B C D

list(4，8，12)

list(1，2，3，4)

A1+B1

C1[A1：1]{B1＝$B1}

The expansion back:

a b c d

Lattice collection C1[A1:1] { }=c1, c2, c3, c4}, { B1=$B1} selects additional again filtercondition, in this condition, the implication of B1 and $B1 is different, and what B1 represented is that { c3 is among the c4} for c1, c2 for the lattice collection, the value of the B1 of each cell correspondence is so B1 will be (1,2,3,4) respectively.$B1 represents Er is the value of the B1 of expression formula place cell correspondence, and it is a number, represents the value 1 of b1 in the d1 lattice as $B1; $B1 represents the value 2 of b6 in the d6 lattice; $B1 represents value 4 of b12 or the like in the d12 lattice, cause spreading result as above.

5. report making example

Example 1, the related form that intersects

Multilayer as Fig. 1 is intersected consolidated statement, establishes relevant physics list structure and is:

Create table " CONTRACTSUM " (---contract sales volume table

" CUSTOMER " CHAR (50),---manufacturer

" PRODUCT " CHAR (50),---product

" AMOUNT " DECIMAL (16)---sales volume

)；

Create table " PRODUCT " (---product table

" PRODUCT_ID " CHAR (10),---product IDs

" PRODUCT_NAME " CHAR (40),---name of product

" DESCRIBE " CHAR (255),---describe

" TYPE " CHAR (1)---product type

)；

Create table " CUSTOMER " (---client table

" CUSTOMER_ID " CHAR (10),---Customer ID

" CUSTOMER_NAME " CHAR (20),---customer name

" TEL " CHAR (10),---phone

" FAX " CHAR (10),---fax

" ADDRESS " CHAR (255),---address

" PROVINCE " CHAR (10),---province

" CITY " CHAR (10),---city

" MEMO " CHAR (255),---remarks

" CUSTOMERTYPE " CHAR (1)---customer type

)；

Adopt method of the present invention design and the flow process that generates form as shown in Figure 7: the data set that need use when (1) determines the form computing, and name respectively:

ds1＝SELECT AMOUNT，CUSTOMER，PRODUCT FROM CONTRACTSUM

ds2＝SELECT PRODUCT_ID，PRODUCT_NAME，TYPE FROM PRODUCT

ds3＝SELECT CUSTOMERTYPE，CUSTOMER_ID，CUSTOMER_NAME FROM CUSTOMER

(2) in form, determine expansion lattice and propagation direction thereof:

As Fig. 4, make A4, B4 vertically expands lattice; C2, C3 are lattice extending transversely.

(3) left side of cell, last nominative are set:

All cells adopt default to get final product.

(4) in each cell, insert operation expression.

A4＝ds3.group(CUSTOMERTYPE)

B4＝ds3.group(CUSTOMER_ID)

C2＝ds2.group(TYPE)

C3＝ds2.group(PRODUCT_ID)

In order to demonstrate the title of code correspondence, above these 4 cells also need to be provided with displayed value;

C4＝ds1.sum(AMOUNT，CUSTOMER＝B3 and PRODUCT＝C2)

C5＝sum(C4{})

C6＝sum(C5{})

(5) form is expanded and calculated the example form after obtaining generating.

The calculating of C4 has wherein been used the content of B3 and C2 lattice at ds1, has promptly carried out related calculating with ds2 and ds3.This ability can only realize by set complicated SQL statement or storing process in conventional tool, can't finish in Report Form Design.

Example 2, multi-source burst form

Multi-source burst form as Fig. 2, establish the related physical list structure and be:

Create table " CUSTOMER " (---client table

" CUSTOMER_ID " CHAR (10),---Customer ID

" CUSTOMER_NAME " CHAR (20),---customer name

" TEL " CHAR (10),---phone

" FAX " CHAR (10),---fax

" ADDRESS " CHAR (255),---address

" PROVINCE " CHAR (10),---province

" CITY " CHAR (10),---city

" MEMO " CHAR (255),---remarks

" CUSTOMERTYPE " CHAR (1)---customer type

)；

Create table " PRODUCT " (---product table

" PRODUCT_ID " CHAR (10),---product IDs

" PRODUCT_NAME " CHAR (40),---name of product

" DESCRIBE " CHAR (255),---describe

" TYPE " CHAR (1)---product type

)；

Create table " CONTRACTSUM " (---contract sales volume table

" CUSTOMER " CHAR (50),---manufacturer

" PRODUCT " CHAR (50),---product

" AMOUNT " DECIMAL (16),---consumption sum

" SIGNDATE " DATETIME,---the contract award date

" TRANSPORTDATE " DATETIME,---the contract award date

" QUANTITY " DECIMAL (16),---sales volume

" PAYAMOUNT " DECIMAL (16)---payment

)；

Adopt method of the present invention design and the flow process that generates form as shown in Figure 7:

The data set that need use when (1) determining the form computing, and name respectively:

ds1＝SELECT CUSTOMERTYPE，CUSTOMER_ID，CUSTOMER_NAME FROM CUSTOMER

ds2＝SELECT PRODUCT_ID，PRODUCT_NAME，TYPE FROM PRODUCT

ds3＝SELECT AMOUNT，CUSTOMER，PRODUCT，SIGNDATE，TRANSPORTDATE，

QUANTITY，PAYAMOUNT FROM CONTRACTSUM

(2) in form, determine expansion lattice and propagation direction thereof:

As Fig. 5, make B4, C4, B10 vertically expand lattice; E2, E3 are lattice extending transversely.

(3) left side of cell, last nominative are set:

All cells adopt default to get final product.

(4) in each cell, insert operation expression:

B4＝ds1.group(CUSTOMERTYPE)

C4＝ds1.group(CUSTOMER_ID)

E2＝ds2.group(TYPE)

E3＝ds2.group(PRODUCT_ID)

In order to demonstrate the title of code correspondence, above these 4 cells also need to be provided with displayed value;

B10＝ds3.group(year(SIGNDATE))

E4＝ds3.sum(AMOUNT，CUSTOMER＝C4&&PRODUCT＝E3)

E5＝ds3.sum(QUANTITY，CUSTOMER＝C4&&PRODUCT＝E3)

E6＝ds3.sum(AMOUNT，PRODUCT＝E3&&SIGNDATE！＝nu11)

E7＝ds3.sum(QUANTITY，PRODUCT＝E3&&SIGNDATE！＝nu11)

E8＝ds3.sum(AMOUNT，TRANSPORTDATE！＝nu11&&PRODUCT＝E3)

E9＝ds3.sum(QUANTITY，PRODUCT＝E3&&TRANSPORTDATE！＝nu11)

E10＝ds3.sum(AMOUNT，PRODUCT＝E3)

E11＝ds3.sum(QUANTITY，PRODUCT＝E3)

E12＝ds3.sum(AMOUNT，PRODUCT＝E3&&PAYAMOUNT＞0)

E13＝ds3.sum(QUANTITY，PRODUCT＝E3&&PAYAMOUNT＞0)

(5) form is expanded and calculated, the example form after can obtaining generating, as shown in Figure 2.

The data field of this vertical burst form has been divided into several from top to bottom, is a typical sound sheet mixing burst form, each sheet grouping number of plies difference, and have data to link up (some calculated value is to be calculated by other row) between sheet.This inconsistent form of form up and down, its data source can not be organized into single source (each sheet columns difference), also just can't be realized by conventional tool.

Example 3, band rank accounting report

As the band rank accounting report of Fig. 3, establish relevant physics list structure and be:

Create table " TELESELL " (---telecommunications sales situation table

" AREA " CHAR (50),---area

" TYPE " CHAR (50),---type of service

" AMOUNT " DECIMAL (16)---sales volume

)；

Adopt method of the present invention design and the flow process that generates form as shown in Figure 7:

The data set that need use when (1) determining the form computing, and name respectively:

ds1＝SELECT AREA，TYPE，AMOUNT FROM TELESELL

(2) in form, determine expansion lattice and propagation direction thereof:

As shown in Figure 6, make A4 vertically expand lattice, E2 is lattice extending transversely.

(3) left side of cell, last nominative are set:

All cells adopt default to get final product.

(4) in each cell, insert operation expression:

A4＝ds1.group(AREA)

B4＝sum(E4{})

C4＝count(B4[`0]{B4＞$B4})+1

E2＝ds1.group(TYPE)

E4＝ds1.sum(AMOUNT)

F4＝count(E4[`0；]{E4＞$E4})+1)

C6＝sum(B4{C4＜＝3})

F6＝C6/sum(B4{})

`0 wherein represents the root lattice.

(5) form is expanded and calculated the example form after obtaining generating.

It is that typical inter-bank is calculated that rank is calculated, and need find out the number of the number bigger than currency in same column.Again form middle part mark has been carried out tape spare in the plurality of units lattice of below and quoted (front three), promptly absolute case calculates.This report style is also uncomplicated, but compartment computing meeting makes conventional tool very awkward, can only rely on program code once more, and the data calculated in advance is inserted in the form well.

Obviously, those skilled in the art can carry out various changes and modification to the present invention and not break away from the spirit and scope of the present invention.Like this, if of the present invention these are revised and modification belongs within the scope of claim of the present invention and equivalent technologies thereof, then the present invention also is intended to comprise these changes and modification interior.

Claims (12)

1. nonlinear report generating method comprises the steps:

The data set that need use when (1) determining the form computing, and name respectively;

(2) determine that in static statement some cell is the expansion lattice, promptly initiatively expands the cell that duplicates, and its propagation direction is set;

(3) the left nominative and the last nominative of all cells are set, i.e. the expansion lattice that when vertical and horizontal are expanded, need follow of this cell;

(4) in each cell, insert operation expression;

(5) form is expanded and calculated the actual form after obtaining generating.

2. nonlinear report generating method according to claim 1 is characterized in that: the data set described in the step (1) is a plurality of.

3. nonlinear report generating method according to claim 1 is characterized in that: the propagation direction of expansion lattice is divided into vertical expansion and two kinds extending transversely in the step (2), and expansion lattice have only a propagation direction simultaneously.

4. nonlinear report generating method according to claim 1 is characterized in that: the vertical expansion lattice in the step (3) have only left nominative and do not go up nominative, and lattice extending transversely have only last nominative and do not have left nominative; The existing nominative of going up of non-expansion lattice also has left nominative.

5. according to claim 1 or 4 described nonlinear report generating methods, it is characterized in that: all left nominatives can only be vertically to expand lattice or logic imagination lattice that are called the root lattice in the step (3), and all last nominatives can only be lattice extending transversely or root lattice.

6. nonlinear report generating method according to claim 1 is characterized in that: the expression formula of inserting in cell in the step (4) is divided into two kinds of set operation and monadic operations, and wherein expanding the expression formula of inserting in the lattice must be set operation.

7. according to claim 1 or 6 described nonlinear report generating methods, it is characterized in that: data that the expression formula in the step (4) in cell can free reference data be concentrated and the data in the cell, and allow both to be blended in simultaneously in the expression formula.

8. nonlinear report generating method according to claim 7, it is characterized in that: the cell computing in the step (4) can be used the level coordinate or the representation of displacement coordinate to pass and to collect of cell, thereby a kind of scheme of naming for generation form cell in the design phase is provided.

9. nonlinear report generating method according to claim 1, it is characterized in that: when in the step (5) form expansion being calculated, earlier the expansion lattice are calculated and expand, and then non-expansion lattice are calculated, when a plurality of expansion lattice were arranged on the form, the expansion order was that the former head expands son expansion lattice behind the lattice.

10. according to claim 1 or 9 described nonlinear report generating methods, it is characterized in that: in the step (5) to vertically/during lattice extending transversely expansion, minimum row/the column region continuously that comprises these lattice and all offspring's lattice will be found out, with this panel region downwards whole/duplicate a plurality of to the right, number of copy times is the element number of the operation result of these expansion lattice, and each element is write in the expansion lattice corresponding in the zone of duplicating out successively, and the expression formula of other cell will be by slavish copying in the replication region.

11. nonlinear report generating method according to claim 10, it is characterized in that: when expanding certain expansion lattice in the step (5), for certain cell is arranged in the replication region is not the sublattice of these expansion lattice or the sublattice of sublattice, if the cell that these can be duplicated out after the expansion joins together, then will be merged into big lattice, otherwise not merge, remove especially original, these cells that duplicate are out filled a vacancy, and the expression formula in the original lattice is kept intact.

12. nonlinear report generating method according to claim 10, it is characterized in that: in the step (5) certain cell in the form is calculated when relating to the data set computing, if having certain expansion lattice that same data set has been carried out dividing on the major grid line of this cell calculates, then when calculating this cell expression formula, its data set computing will be limited in dividing in the data set later.

Images