Data Table Request and Single Expression Request
<accesscontrol>Main:MyGroup</accesscontrol>
Data Table Request and Single Expression Request
One of the main processes in the Report Module is the conversion of Expression Tables to Data Tables. A similar process is the conversion of a single expression to a value or array of values.
Illustration 1: Conversion of expression tables to data tables
Illustration 2: Conversion of expression to a value or value array
There exists alternative start and ending points through the above flow chart. As an example there exists requests to the Cross Diagram Generator Object that just require an Expression table returned without the actual creation of a Data Table.
Before we continue let us investigate what the different objects and parameters that are sent around in the above flow actually are.
Expressions and expression tables
An “Expression” is a statement to a data cache (much like a table in a database) to return a value or string of values. There is always a data cache as a context for an expression, so the expression itself will never have a “FROM” in its statement.
Two examples of expressions are:
=count() where Gender==1
=select(opinion) where Gender==2
The second expression returns an array (can be any type of values in the array) whereas the first one returns a number.
An expression table is a table in which some of the cells may (and normally does) have expressions.
An example of an expression table could be:
| Gender | |||
| Age | Male | Female | Total |
| Young | =count() where Gender==1 && Age==1 |
=count() where Gender==2 && Age==1 |
=count() where Gender!=empty && Age=l |
| Old | =count() where Gender==1 && Age==2 | =count() where Gender==2 && Age==2 | =count() where Gender!=empty && Age=2 |
| Total | =count() where Gender==1 && Age!= empty | =count() where Gender==2 && Age!= empty | =count() where Gender!=empty && Age!= empty |
Expression tables also allow statements as known from Excel. Columns are indexed A, B, C, etc. and rows are indexed 1, 2, 3, etc. Another expression table may thus be:
| Gender | |||
| Age | Male | Female | Total |
| Young | =count() where Gender==1 && Age==1 | =count() where Gender==2 && Age==1 | =count() where Gender!=empty && Age=l |
| Old | =count() where Gender==1 && Age==2 | =count() where Gender==2 && Age==2 | =count() where Gender!=empty && Age=2 |
| Total | =sum(B3:B4) | =sum(C3:C4) | =sum(D3:D4) |
Both of the expression tables will return the same result after having been sent through the expression conversion process.
Constants
Constants are sent from the report object together with the diagrams that the report is requesting to be made. It will basically send an array of replacement values, that is tags that should be looked for in expression and then be replaced by the related value. An array of constant parameters sent to the constant conversion process may look like below:
CCountry = 2 (numeric constants)
CCustomer = “Coca Cola” (string constant)
The constant conversion process
As mentioned earlier the constant conversion process will go through an expression or each expression in a data table and replace any tag that is equal to a constant with the value of that constant.
Let us say that we have the two constants mentioned in the above “Constants” chapter and a table like below:
| Age for people at [CCustomer] | |
| Young | =count() where Gender==1 && Age==1 && Country==[CCounty] |
| Old | =count() where Gender==1 && Age==2 && Country==[CCounty] |
| Total | =sum(B3:B4) |
As you can see constant tags need to be in brackets to be identified as a constant.
When the expression table has run through the constant conversion process the resulting expression table should be:
| Age for people at Coca Cola | |
| Young | =count() where Gender==1 && Age==1 && Country==2 |
| Old | =count() where Gender==1 && Age==2 && Country==2 |
| Total | =sum(B3:B4) |
Weights and Expression Filters
An expression filter consist of information to the expression conversion process that will limit the rows in the data cache that will be searched on by the simultaneously sent expressions.
A typical expression filter may consist of a number of partial filters, like
country == 3
customer != 2
Lfaktura == [1]||Lfaktura == [2]
Basically these three filters would be like placing an extension on each expression that is queried on the data cache; extension would be “&& (country== 3) && (customer !=2) && (Lfaktura == [1]||Lfaktura == [2])”. This is not how it works although. Since all expressions would be using this extension the expression conversion just goes in and reduces the number of records available for the expressions to query on; thus ensuring the same final results although in a faster way.
Weight goes in and influences the way in which the different function types (sum, average, etc.) works. The parameter being sent to the expression conversion process is a “Data cache column” name.
For both Weight and Expression filter there is basically no reason to understand how these work in detail; the important thing is to know that they are not required by the expression conversion process, but if they are “attached” then the results in the data table, value or value array will be influenced.
NB: If a data table request or single expression request is result of a report being made (see the chapter on #8.Report request later), then the table request/single expression request may beforehand receive weights and expression filters from that report. In case the diagram itself already has expression filters or weights related it needs to combine/select which one to use. This process works as follows:
- When deciding on a weight the chosen weight will always be the one related to the diagram. If the diagram does not have any weight then it will use the weight that it receives from the report. If it did not receive any weight from the report, then there will not be any weight parameter supplied to the Expression Conversion Process
- It is currently not possible in any GUI, that there will be both an expression filter related to a diagram as well as the diagram receiving an expression filter from externally. The Data Table request process should nonetheless be able to handle this since it seems a logical extension in the future. In this case the Expression Filter from the Report and the Expression Filter from the Diagram should be combined into one Expression Filter; e.g. “Gender==1” and “Country==45” becomes “(Gender == 1) && (Country == 45)”.
The expression conversion process
There is no reason to understand in detail how an expression or expression table is converted to a data table, value or value string. As long as we understand the input that can be given and the output that it results in then we understand the most important parts.
The input parameters that the expression converter gets are:
- Expression table or expression
- Weight
- Expression filter
The output of the expression converter is
- Data table, Value or Value Array
Data Table, Value and Value Array
A data table is an expression table that has had all its expressions calculated and converted into values. An example of a data table is:
| Gender | |||
| Age | Male | Female | Total |
| Young | 20 | 34 | 54 |
| Old | 22 | 2 | 24 |
| Total | 42 | 36 | 78 |
Examples of values can basically be found in each cell in the above that had an expression in earlier expression table examples, so there is no need to explain these in more detail.
An array of values could are the same as one value; only we have many of them and most likely different. I do not want to show an example, since the way in which they are graphically represented when shown depends on the “In-line style sheet”, which is explained in a later chapter.
Significance process
The data table we showed in the above example is what we in Illustration 1 called “Data Table 1”. When the requester of the data table then also want significance calculated on his data table he will send this request using 5 possible parameters. If there is no significance request then this step will be ignored and the data table is completely done.
Parameters sent are:
P1: Columns to be compared (required): Will send the list of columns that need to be compared; for example “B,C,D”.
P2: Starting row (required): Will send the first row of the data table that we need to have compared; for example “7”
P3: Ending row (required): Will send the last row of the data table that we need to have compared; for example “15”
P4: Total row (not required): Will send the column that has the total information for all the rows; for example “E”
P5: Total column (not required): Will send the row that has the total information for all the columns; for example “17”
Basically these parameters are specifying columns and rows on the data table that need to go through the significance process. The result will be a data table where those cells that are “significantly different” will be marked with the sign [-] or [+].
An example of how data table 2 could look is shown below.1
Data Table after significance
An example of a data table after significance is shown here:
The data table will get some signs inserted in some of its cells meaning that those cells would be converted from numbers to strings. But it may also be that the markings are just added as additional information with data table 2. Under all circumstances the signs only make sense when the resulting data table is to be shown as a “Table diagram”. Other chart types (like pie, bar, line etc.) will want to ignore any significance settings since they cannot be shown in any other diagram types than table.
1. Please read design “VN1530DAT - CGS – Significance test” to learn more
