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:

1. A page header;
2. A father portion containing information about each printed group;
3. A child portion containing a detailed list of an authorized privileges for the group;
4. 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:

1. 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 }}

2. 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.