Reporting and Creating output documents
After applying the business rules the resulting data can can be used to create reports and other output documents.
Creating such reports consists of three steps:
transform and filter the model data by using an embedded SQL database
determining difference to previously stored model (optional)
Template based reporting via
Velocity templates (for textual output like e.g. HTML)
SQL transformation and filtering
After the business rules have been processed (or a Solicitor data model has been loaded via
command line option
-l) the model data is stored in a dynamically created internal SQL database.
For each type of model object a separate table is created. The tablename is the name of model object type written in uppercase characters. (E.g. type
NormalizedLicensestored in table
All properties of the model objects are stored as strings in fields named like the properties within the database table. Field names are case sensitive (see note below for handling this in SQL statements).
An additional primary key is defined for each table, named
For all model elements that belong to some parent in the object hierarchy (i.e. all objects except
ModelRoot) a foreign key field is added named
PARENT_<TABLENAME>which contains the unique key of the corresponding parent
SQL queries for filtering and transformation
Each Writer configuration (see Writers and Reporting) includes a section which references SQL select statements that are applied on the database data. The result of the SQL select statements is made accessible for the subsequent processing of the Writer via the dataTable name given in the configuration.
Postprocessing of data selected from the database tables
Before the result of the SQL select statement is handed over to the Writer the following postprocessing is done:
rowCountcolumn is added to the result which gives the position of the entry in the result set (starting with 1).
ID_<TABLENAME>are replaced with columns named
OBJ_<TABLENAME>. The fields of those columns are filled with the corresponding original model objects (java objects).
The result table column
Determining difference to previously stored model
When using the command line option
-d Solicitor can determine difference information between two different data models (e.g. the difference between the licenses of the current release and a former release.) The difference is calculated on the result of the above described SQL statements:
First the internal reporting database is created for the current data model and all defined SQL statements are executed
Then the internal database is recreated for the "old" data model and all defined SQL stements are executed again
Finally for each defined result table the difference between the current result and the "old" result is calculated
To correctly correlate corresponding rows of the two different versions of table data it is necessary to define explicit correlation keys for each table in the SQL select statement.
It is possible to define up to 10 correlation keys named
CORR_KEY_X with X in the range from 0 to 9.
CORR_KEY_0 has highest priority,
CORR_KEY_9 has lowest priority.
The correlation algorithm will first try to match rows using
CORR_KEY_0. It will then attempt to correlate unmatched rows using
CORR_KEY_1 e.t.c.. Correlation will stop, when
all correlations keys
CORR_KEY_9have been processed OR
the required correlation key column does not exist in the SQL select result OR
there are no unmatched "new" rows OR
there are no unmatched "old" rows
The result of the correlation / difference calulation is stored in the reporting table data structure. For each row the status is accessible if
The row is "new" (did not exist in the old data)
The row is unchanged (no changes in the field values representing the properties of the Solicitor data model)
The row is changed (at least one field corresponding to the Solicitor data model changed)
For each field of "changed" or "unchanged" rows the following status is available:
Field is "changed"
Field is "unchanged"
For each field of such rows it is furtheron possible to access the new and the old field value.
Sample SQL statement
The following shows a sample SQL statement showing some join over multiple tables and the use of correlations keys.
-- SPDX-License-Identifier: Apache-2.0 -- -- generate all NormalizedLicenses in denormalized form including all hierachical data (allden -> "all denormalized") select CONCAT(NVL(a."applicationName",'-'),NVL(ac."groupId",'-'),NVL(ac."artifactId",'-'),NVL(ac."version",'-'),NVL(l."normalizedLicense",'-')) as CORR_KEY_0, CONCAT(NVL(a."applicationName",'-'),NVL(ac."groupId",'-'),NVL(ac."artifactId",'-'),NVL(l."normalizedLicense",'-')) as CORR_KEY_1, CONCAT(NVL(a."applicationName",'-'),NVL(ac."groupId",'-'),NVL(ac."artifactId",'-'),NVL(ac."version",'-')) as CORR_KEY_2, CONCAT(NVL(a."applicationName",'-'),NVL(ac."groupId",'-'),NVL(ac."artifactId",'-')) as CORR_KEY_3, e.*, a.*, ac.*, l.* from ENGAGEMENT e, APPLICATION a, APPLICATIONCOMPONENT ac, NORMALIZEDLICENSE l where e.ID_ENGAGEMENT = a.PARENT_APPLICATION AND a.ID_APPLICATION = ac.PARENT_APPLICATIONCOMPONENT AND ac.ID_APPLICATIONCOMPONENT = l.PARENT_NORMALIZEDLICENSE order by UPPER("ID_APPLICATION"), -- sort by ID so assuring we have the same order as defined in config UPPER("groupId"), UPPER("artifactId"), UPPER("version"), UPPER("effectiveNormalizedLicense"), UPPER("normalizedLicense")
|Above example also shows how the case sensitive column names have to be handled within the SQL|
The above dscribed SQL processing is identical for all Writers. Writers only differ in the way how the output document is created based on a template and the reporting table data obtained by the SQL transformation.
The Velocity Writer uses the Apache Velocity Templating Engine to create text based reports. The reporting data tables created by the SQL transformation are directly put to the into Velocity Context.
For further information see the
The Solicitor JavaDoc (which also includes datails on how to access the diff information for rows and fields of reporting data tables)
The samples included in Solicitor
Using Placeholders in Excel Spreadsheets
Within Excel spreadsheet templates there are two kinds of placeholders / markers possible, which control the processing:
The templating logic searches within the XLSX workbook for fields containing the names of the reporting data tables as defined in the Writer configuration like e.g.:
Whenever such a string is found in a cell this indicates that this row is a template row. For each entry in the respective resporting data table a copy of this row is created and the attribute replacement will be done with the data from that reporting table. (The pattern
#…# will be removed when copying.)
Within each row which was copied in the previous step the templating logic searches for the string pattern
someAttributeName corresponds to the column names of the reporting table. Any such occurence is replaced with the corresponding data value.
Representation of Diff Information
In case that a difference processing (new vs. old model data) was done this will be represented as follows when using the XLS templating:
For rows that are "new" (so no corresponding old row available) an Excel note indicating that this row is new will be attached to the field that contained the
Fields in non-new rows that have changed their value will be marked with an Excel note indicating the old value.