3.1. Retrieving the data and generating the report

The previous chapter described how a report is defined. This chapter describes how the Espresso View processes the report definition and how the report data is retrieved, put in the defined hierarchical structure and displayed.

let’s assume first a very simple report defined using below two queries:

The same report definiton overview In EspressoViews:

After the report definition is successfully loaded, Espresso View runs the SQL queries on the indicated database system starting with the root query and continuing according to the defined order with the first added child, and then, if the child has also children, it continues with his first added child and so on in pre-order traversal. The retrieved data is cached for all queries in the memory in the same hierarchical structure as the definition file (but for short time also locally in temporary tables). Let’s asses further that at the end of the data retrieval phase the following results were caught:

After the query result are gathered, the data is prepared for display and using the transmitted Column_A (Column_ B is not applicable in Child Query 1) Espresso View puts the retrieved data in this order:

As it can be seen, the result line of the Child Query with Column_A=’Abacus’ is subordinated under the result line of the Root Query with Column_A=’Abacus’, and the same is done also for the result lines with Column_A = ‘Ambitious’ and Column_A = ‘Amplified’

The algorithm behind this kind of hierarchical sorting is this: EspressoViews tries to re-produce the same hierarchical structure as specified in the definition file starting with every result line retrieved by the Root Query. For this scope it traverses again the hierarchy (in pre-order) and tries to match the root result line with one or more result lines of its subordinated child queries using the value(s) of the transmitted column name(s) which are “applicable” in the child query results. The matching function checks if the value in transmitted column name of the parent result line is equal with the value in the correspondent column name of the child query. In the example given above only Column_A is applicable, so the algorithm will start with the first result line of the Root query and will try to find one or more records in the Child 1 result which fulfills the condition Column_A=’Abacus’

The same matching procedure is applied also to the other Root query result line producing the above displayed result

Now let‘s complicate things a bit and add one more query in the report definition, the Child Query 1.1:

And the Child Query 1.1 retrieves the following data:

Now with this additional level in the hierarchy the matching procedure performs one more in-deep matching step. The transmitted Column_B which was not applicable in the level 2 query is applicable in the level 3 i.e. in ChildQuery 1.1 (Column_B, Column_D). Following matching paths are stridden:

An this is the output in EspressoViews:

The result line Query 1.1. (Baron, Dada) is matched twice, because both Child Query 1 result lines, (Amplified, Cabaret, Elevator) and (Amplified, Catchup, Episode) carry the inherited (but not applicable) Column_B=’Baron’

The Child Query 1 record line (Ambitious, Cat, Espresso) is not linked to any level 3 record because its inherited value Column B =’Bacterium’ is not matching to any of the Child Query 1.1 result lines.

And this Child Query 1.1 result line:

is not matched to any level 2 record line because Column_B=’Biscuit’ was not inherited in any of the level result lines (this record is not even selected as Espresso View adjust internally the SQL queries in order only records which will be matched are selected).

Modifying the hierarchy on the fly

In all our examples so far the entity named Child Query 1 was always the child of the Root Query, and Child Query 1.1. was always the child of and the report entity Child Query 1.

With a simple modification of the tree structure we could also link Child Query 1.1 directly under Root Query:

Now Child Query 1 and Child Query 1.1 are siblings.

We could also place Child Query 1.1 on top with Root Query below it and Child 1 below Root Query: This is the hierarchy:

And this the report output:

If the inheritance is never applicable

If no value was transmitted or if no transmitted column name is applicable then all subordinated result lines are matched with the parent line (behavior is comparable with the Cartesian product whereby each parent result line is multiplied with all children result lines). This multiplication feature should be used only if makes sense, otherwise it is just produces just an annoying repetitions of the results.

Matching with the wild-key *

The matching function retrieves true also if the child result column matches partially the inherited column value. For this scope the child column value needs to use the wild-key * in the select expression of the SQL query:

select some_data||’*’ as Column_A, Column_B from A_table

meaning: everything beginning with whatever the expression some_data retrieves or:

select ’A*s’ as Column_A, Column_B from A_table

meaning: everything beginning with A and ending with s.

But the query:

some_data as Column_A, Column_B from A_table

will never make use of the wild key functionality even is the retrieved value contain the key * Currently the wild-key functionality is not supported for transmitted/inherited column values. And also not supported are other regular expression keys.

Retrieved columns and displayed columns

By default all result columns of a query are displayed in the report, but, for each query apart, you can specify which result columns to display or to not display. For instance we could remove Column_A from the Child Query 1:

Any decision to display or not a column is not interfering with the column inheritance and matching algorithm, i.e. it is concerning only the display of data.

Next Page->