ODT Reportman User's Guide
In this guide it will be explained the usage of the
ODT Report web form using an example report.
In the example it will be described the composition of the report for the
Config->Gruppi
form from the MasonSQL framework. Previous
Reportman report exists for this form, so the new ODT version can be compared to the old Wine version. Final report in the example will look like this:
gruppi.pdf
Association With Table
In order to use the report, the
Report Name
field must have a correct name. In this example the report is intended for the
public.gruppi
table and the default
PRINT_FORM is used, so its sufficient to name the report
gruppi
. See:
How to Correctly Configure a Report Name for more comprehensive explanation.
Queries
The relationships of the MasonSQL framework will be used to compose a report that will print an authorized privileges for each of the functions belonging to the selected groups; Additionally it will print a list of users belonging to the selected groups.
Selected Groups
The command-line parameter ARGV.REPORT_ID determines the parameter used to select the data to print. It may coincide with the record ID of the table
gruppi
(but in other cases also with a parameter to be passed to a database function).
Here's the father query:
select
gruppi.*
from gruppi
where {{ ARGV.REPORT_ID }} > 0 and gruppi.id = {{ ARGV.REPORT_ID }}
The command-line parameter
REPORT_ID
is provided by the framework although its test value can be set in the
Test parameters:
input field:
The result of the query contains information about selected groups which is saved to the
DTL context.
id | nome | descrizione | commento
----+------------+---------------------------+----------
5 | Gas_admins | Gruppo amministratori GAS |
(1 riga)
Authorized Privileges
The first sub-query must provide an authorized privileges of each function belonging to the selected group. The group ID is passed to the sub-query with the
DTL tag {{ G.id }}
.
G
reference is declared in the
Portion Name of the portion. The tag is
rendered before the query gets executed. The query marks each authorized privilege of a function with capital
X
:
select distinct funzioni.nome ,
(select distinct
case
when id_autorizzazioni is not null
then 'X' else ''
end
from gruppi_funzioni gf
where gf.id_funzioni = gg.id_funzioni and
gf.id_autorizzazioni = 1) as m_menu,
(select distinct
case
when id_autorizzazioni is not null
then 'X' else ''
end
from gruppi_funzioni gf
where gf.id_funzioni = gg.id_funzioni and
gf.id_autorizzazioni = 2) as m_select,
(select distinct
case
when id_autorizzazioni is not null
then 'X' else ''
end
from gruppi_funzioni gf
where gf.id_funzioni = gg.id_funzioni and
gf.id_autorizzazioni = 3) as m_update,
(select distinct
case
when id_autorizzazioni is not null
then 'X' else ''
end
from gruppi_funzioni gf
where gf.id_funzioni = gg.id_funzioni and
gf.id_autorizzazioni = 4) as m_delete,
(select distinct
case
when id_autorizzazioni is not null
then 'X' else ''
end
from gruppi_funzioni gf
where gf.id_funzioni = gg.id_funzioni and
gf.id_autorizzazioni = 5) as m_print,
(select distinct
case
when id_autorizzazioni is not null
then 'X' else ''
end
from gruppi_funzioni gf
where gf.id_funzioni = gg.id_funzioni and
gf.id_autorizzazioni = 6) as m_printsel,
(select distinct
case
when id_autorizzazioni is not null
then 'X' else ''
end
from gruppi_funzioni gf
where gf.id_funzioni = gg.id_funzioni and
gf.id_autorizzazioni = 7) as m_insert
from funzioni
inner join gruppi_funzioni gg on gg.id_funzioni = funzioni.id
inner join autorizzazioni on gg.id_autorizzazioni = autorizzazioni.id
where gg.id_gruppi= {{ G.id }}
order by funzioni.nome
Group Members
The second sub-query must provide a list of group members. The group ID is also the same
DTL tag {{ G.id }}
which is passed to the sub-query from the father query:
select
anagrafiche.*
from
anagrafiche, anagrafiche_gruppi ag
where ag.id_gruppi = {{ G.id }}
and ag.id_anagrafiche = anagrafiche.id
order by nome;
Template
The
base template document has to be defined to provide templates of the portions that will be used by the report. The
DTL tags are put where the field values will be in the printed report. This newly created base template document
gruppi.odt must then be attached to the report and the relative path to the document must be added in the
Filename
field of the corresponding
base template portion:
The planned structure of the example
ODT report has:
- A page header;
- A father portion containing information about each printed group;
- A child portion containing a detailed list of an authorized privileges for the group;
- And a child portion containing a list of users that belong to the group.
Page Header and Page Footer
A page header contains the text that will show on the top of each printed page. For the easier positioning of the paragraphs the table is created inside the page header. The date
field is also added at the right side of the page header. Note that all of the paragraphs are
rendered with the values of the command-line parameters and the values from the first row of the
base template query. Except paragraphs containing the
text fields - paragraphs containing the text fields are not rendered.
Father-Child Portions
Defining relations:
- In order to use a field value, from the query result, in a DTL tag - the
Portion Name
must be added. The corresponding DTL tag can be used in the gruppi.odt as: {{ my_portion_name.my_field_name }}
- In order to use a portion content - its name in the
Obj.Reference
field must correspond to its section or table name in the gruppi.odt
.
Because the report basically uses two different tables, it can be composed with a combination of a father
section and two children
tables. The father section can be without
Obj.Reference
since a group information can be printed in the
TabellaGruppo
table header. The father section prints both child portions, a pair for each group from the father query.
Here's the described father-children configuration:
Here's the content of the
gruppi.odt
:
It is acceptable to add notes to the document since all of them are removed from the report.
Report Fields
By looking at the previous example it may be noticed that some of the DTL tags don't fit very well to the dimensions of the cells. This can be solved by replacing misfit DTL tags with so called
simple variables which are additionally mapped to rows in the
Report Fields
table. This mapping makes possible other practical uses as well. While the standard usage of the
ODF variables in the ODT template documents remains intact.
A
simple variable (opposed to an
user variable) can be added to the document template by choosing:
[Insert] --> [Field] --> [More Fields] --> [Variables] --> [Set variable]
or by pressing
CTRL+F2
.
Available types of a
simple variable are:
-
string
-
float
-
currency
-
percentage
-
boolean
-
date
-
time
All of the available types support rendering within the ODT Reportman. With one limitation - the limitation is that the
date
and
time
types are converted internally, by the ODT Reportman, to the
string
type. For additional information about ODT variables see also:
ODT Variables User's Guide.
A DTL tag can be utilized in a
simple variable in two ways. Firstly, it can be added to the variable name. In such a case the variable content is rendered directly with DTL tag value. For example like this:
{{ AZ.m_menu }}
Using the DTL tag name as the variable name in the
gruppi.odt
template:
Secondly, one or multiple DTL tags can be added indirectly in the
Value
field of the
Report Fields
table. The following example shows mapping of the variable name to the
Value
string containing only one DTL tag:
The mapped variable added to the ODT report template document:
The above mentioned limitation is compensated with additional
Formatting
field in the
Report Fields
table. The
Formatting
field can be applied to
date
,
time
and even to
string
variable types. The field is used as a formatting string for the
sprintf statement. An example of a formatting string of a
string
type variable is:
{% if F.d.0 %}€ %.2f{% endif %}
In case the
Formatting
field is rendered as an empty string, the variable's value is equal to the content of the
Value
field thus the sprintf is not used. This last fact is useful with the
string
type variables: When the
Value
and the
Formatting
fields are rendered empty the variable value will become empty string, which is not possible to achieve with other types of variables.