Reports

A LIMBAS report contains the data of a table or a query in PDF, ODT (OpenOffice) or XML format. The report template is designed using the Report Editor in LIMBAS and may contain various design elements (E.g., graphics, headers, and footers).

Report Editor

When you open the Report Editor a collection of existing report templates appears sorted by tables, further reports can be created.

• ID: Unique identification for the report, generated by LIMBAS.
• Edit: Click on the ‘Edit’ icon to edit the corresponding report template.
• Delete: Click on the ‘Trash’ icon, the appropriate report will be deleted after confirmation.
• Report name: The name of the report template.
• Table: Specifies the table, on which the report template is based.
• Report Extensions: Displays a selection of php-files from the EXTENSIONS folder. This custom script is executed instead of the LIMBAS report. The existing rights structure remains intact.
• Report Folder: For each report, a new folder with the reports name is created in which the report will be stored by default. Alternatively, a subfolder of the latter one can be added via the file manager and be defined as the reports location. Accordingly, only subfolders of the main folder of the corresponding report will be shown for the selection of the location. However, it is possible to directly enter a folder ID in the corresponding field, which allows to choose any folder in the file system. If the selected folder is a subfolder of a table field of type “link to file system”, the generated report will also be linked to this field.
• File Name: Predefined name when saving the report. The name can be complemented with variables such as the record ID, e.g. [“Rechnung_$ ID”] or [“Bericht_$ ID_”. $session [user_id] “.”_”.date()]
• Default Format: Currently, you can generate reports as PDF, ODT or XML. ODT requires an additional ODT report template.
• Open Office Template: Selection of the ODT templates from the file system.

Add Report Template

• Name: see above ‘Report’
• Table: see above ‘Table’
• Copy: Name of an already existing report template, whose report elements are taken over in the new report template.

Click on the button ‘New Report’ and the new report template can be edited.

Edit Report Template

The current view of the report template is located on the left side of the Report Editor. A newly created report template is empty.

The toolbar is located on the right side of the Report Editor and is divided into:

• Name of the report template and the table the report template should be used with.
• Coordinates, width and height of the selected report elements
• Global report settings:
  • Z set/size: Default font and font size used for new text elements in a report.
  • Page size (mm): Width/height of the page size (print area) of the report in millimeters.
  • Margins (mm): In the current view of the report, margins are displayed as an editing aid, reflecting the settings ‘Top:’, ‘Bottom:’, ‘Left:’, ‘Right:’. They have no implication for the generation of the report.
  • Name:
  • Maintain Aspect Ratio: If set, height and width of the selected report element can be modified and aspect ratio will be maintained. Otherwise, height and width of the selected report element can be modified independently of each other.
  • Z index take into account: see zIndex
  • Renew Z index: see zIndex
  • Sort: This setting determines how the pages of the report are generated. This affects, e.g. which report element is in the front or back for overlapping elements. You can sort either by Z Index or Y-Pos.
    • Z index: The report elements are placed on the page in the order of the value of their zIndex. Overlapping elements with a higher zIndex are displayed in front of those with a lower zIndex. For identical values the order in which elements are shown is random.
    • Y Pos:

The report elements are placed on the page in the order of the value of their y-coordinate. The element with the lowest y-coordinate is placed first. For overlapping report elements, the one with the highest y-coordinate will be in the front. For identical values the order in which elements are shown is random.

• Buttons to add report elements: Hover the mouse pointer on a button, and the functionality of the report item is displayed.
• ‘Save’ button: Save the report template.
• Table listing of the elements used: The table lists all used elements and displays for each report element :
  • Element number
  • Type of the report element
  • Note about the report element content

Add a Report Element

Click on a button to add a report element and the corresponding element is added in the top left corner of the Report Editor, with the default properties for this element type. The properties of a report element depend on the element type and can be changed as described in (Setting/changing the properties of a report element).

To display an element in a table, the table has to be created first. Before you add the internal report element, the corresponding table field must be selected. The first internal report element is then placed in the top left corner of the table field, further additions can be made depending on the width of the table column to the right, or below.

The following elements are offered by the LIMBAS Report Editor for placement on a user-defined report template:

• Text Block
• Data Content
  Click on the button for ‘Data content’ and a tree of the table fields is displayed in the tool bar below the buttons. Click on one of these table fields to add the report element to the view of the report template.
• Graphics
  Click on the button for ‘Graphics’, a field for entering a file path appears in the tool bar below the buttons. If a valid graphics file is entered, it is added to the report template with a click on ‘Apply’.
• Rectangle
• Table
  A table allows a structured, side by side placement of the reports elements ‘Block of text’, ‘Data content’, ‘Date’ and ‘Formula’.

When generating the report, only the table columns which have an internal report element in the first row are taken into account. An empty ‘Text Block’ is sufficient.

A table field can accommodate multiple internal report elements. If this is the case, the properties of the first report element (top left) are used for all other elements in this table field, during the generation of the report.

• Date
• Page Number
  For reports with multiple data records the page numbers for each data record starts with ‘1’, then continuously until the last page of the data record.
• Formula
• Subformula

Select a Report Element

There can only be one report element selected at a time. The selected report element:

• is highlighted in the table with the currently used report elements
• has a symbol in the report view in the bottom right corner to increase/decrease the frame size

You can select a report element with

• a click on the corresponding entry in the table with the currently used report elements
• a click on the appropriate element in the report view

Setting/Changing the Properties of a Report Element

Please note, when setting or changing the properties of a report element, when generating the report, the elements are placed at the following ranges in a page:

• Back
• Front
• Actual Content
• Footer

Here, the background can use the space of the entire page. Actual content, header and footer do usually not overlap (exception “fix position option”, see below).

The settings of ‘Back’ and ‘Hidden’ (see below), define where the report elements for Back, Header and Footer are displayed > On all pages > Only on the first page > On all following pages (except page1) > On all even-numbered pages > On all odd-numbered pages The report elements of the actual contents are shown only once. Where not all report elements can be placed on one page, a page break is inserted after the footer and another page is added to the report.

Setting/changing the properties of a report element requires that it is selected.

• The position in the report view can be changed by drag & drop or by entering the coordinates in the tool bar. The coordinates X = 0, Y = 0 are the equivalent to of the upper-left corner of the report. The position of a report element in a table, can only be changed by moving the entire table or changing the column width. The X and Y coordinates are not displayed for elements in a table.
• To increase or decrease the size of a report element, drag the corner away from or toward the center or change the height and width by entering appropriate values (in pixel) in the toolbar. For report elements with text the height in the generated report will however be automatically adjusted for the text. The value set here for these report elements is only used for the view in the report editor. The width of a table, as well as a report element in a table can be changed only by the width of the corresponding table columns. This requires that the left or right border of a Table field in use (!) are adjusted accordingly, using the mouse.

Selecting the report element opens a context menu, which allows you to adjust additional properties.

The following Menu Items/Displays/Settings are found in the context menu for all report elements:

• The element number is a unique identification (positive integer) for the report element. When you add a new report element, this non-modifiable ID is set by LIMBAS itself
• The zIndex-Value
  • Is only of importance, if the appropriate setting for Sort is made.
  • Is changed through the Menu Items Front or Back.
  • If global report properties are set to take Z-Index into account, the Z-Index of a selected report element is set to the current highest value.
  • Is reassigned for all report elements by Z-index replace. So that there are no gaps in the zIndex values. The previous order is retained.
• Copy duplicates the report element with all set properties.
• For report elements with a frame the border color, style , and thickness can be set, as well as which borders should be visible(left, right, bottom, top).
• Transparency allows you to vary the percentage of transparency of a report element.
• Back this setting defines if a report element is placed in the background on a page.
• Page Break can insert a page break before or after a report element.
• Relative specifies that the distance of the report element from the bottom of the referenced element is always the same distance as set in the report view of the report template. This can be useful if the referenced report element contains variable text, and has therefore a variable height.
• If a ‘Data content’ type report element, that is referenced as Dependent, is empty, when generating the report, the actual report element is not added to the generated report.
• The report element is displayed depending on the setting in ‘Hidden’. On all pages, only on the first page, on all subsequent pages, on all even-numbered pages or all odd-numbered pages of the report of a record. ‘Hiding’ a report element makes sense only for recurring elements like the header / footer or the background.
• Through the setting Header or Footer, the report element can be added to the header or footer and would than not be treated as part of the actual content.
• The option Fix Position is only useful for report elements of the actual content. If this option is set, they will be placed in the generated report in exactly the same position as in the report template and can overlap the area of the header or footer.
• With Delete the report element will be deleted, without asking for confirmation

The following Menu Items/Displays/Settings in the context menu, depend on the type of the report element:

• For text report elements, you can set the font, the size, the display, the font color and the background color for the text. There is also a setting for the space between border and text.
• If the option List in the report element Data Contents is set:
  • The data content of the table fields of all linked data records for the corresponding table fields of the ‘Link’ type is listed. If this option is not set, only the first linked data record is considered in the report.
  • For all other table field types, the values of all the data records that correspond to the table field are listed in the report.
• The data content of the table fields of all linked data records for
  • If (as described above) in a report, for a ‘Data Content’ report element, the content of multiple data records is listed, the entered characters are used as delimiters. If there is no entry, a line break is inserted.
  • If there are multiple internal report elements in a table field, the entered characters are inserted as delimiters for these elements. If there is no entry, nothing is inserted.
• By setting the HTML option, the text in the ‘Block of text’ and ‘Formula’ report elements is interpreted as HTML. Please note that other settings of the context menu are thereby overwritten and thus invalid.
• When the setting Replace is ticked, and with the next step a new Data Content report element which is linked to another table field is created, the original report element is deleted, and the properties of the original element will be assigned to the new element. After this the setting Replace will be automatically un-ticked. This function, together with the copy function, will make it easier, when several ‘Data Content’ report elements are placed in a report.
• The number of columns and rows of the ‘Table’ report element is set according to the data entered in the context menu.
• By setting Colspan a table field can be extended across multiple columns.
• Grafik Info displays the corresponding information of a graphic file
• Proportional for graphics from data content
• If Tagmode is set for the first report element within a table field, the text for this table field can be formatted in the generated report using customized tags. Otherwise, the properties of the report element of the first table field are used for the text of the entire table field. The tags are defined in the file

./dependent/EXTENSIONS/system/report_tab_tags as shown in the following example:

• <exampleTag> mit font Arial, italic, font size 11 and font color: $pdf->SetTagStyle(“exampleTag”,”arial”,”I”,11,”0,151,200″);

A tag can then be used in the text of a ‘Block of text’, ‘Data Content’, or a ‘Formula’ report element in the following ways:

• Text without tag, <exampleTag> this text is displayed in the report according to the tag definition </exampleTag> Text without tag

Sub Reports

Report can contain sub reports. The report editor provides a tool and lets you choose between two options:

• Embedding: Directly integrates the sub report into the main report. The formatting as well as the headers and footers will remain unchanged and the elements of the sub report are written independently into the main report.

Also individual parameters can be set which create the results of the sub report get_gresult(). An individual result set for the sub report can be used only by passing an array with the related id’s.

• Including: Creates an independent sub report, which is included into the main report like an image (implemented using fpdi). The formatting as well as the headers and footers can vary according to the size of the sub menu.

Example

Parameter

$filter["anzahl"][$gtabid] = "all";
$filter["nolimit"][$gtabid] = 1;
$filter["order"][$gtabid][0] = array($gtabid,12,'DESC');
$gsr[$gtabid][12][0] = 'Meier';

Result Set

return array(1,2,3,4,5);

Html Templates

Described at Html-Templates.

Diagrams

A diagram can be integrated via the toolbar and the corresponding diagram symbol. The diagram must be selected from the selection list before inserting. Charts are basically created from the entire result of the underlying query. If the diagram is to be filtered according to the report to be created, the filters can be entered into the parameters in the context menu. It is possible to pass the parameters $extension, $gsr and $filter. They have the same structure as the get_gresult function.

Example Parameters

The example filters the query of the chart with the ID 11 by the field with the ID 2 and the value of the record $ID with which the report was called and the field of the ID 2 with the fixed value ‘2019’. Also see $gsr or $extension

$gsr[11][1][0] = $ID;
$gsr[11][2][0] = '2019';

or

$extension['where'][0] = "MYID = $ID AND YEAR = '2019'";

Formulas & Functions

Reports can be extended with custom functions or calculations. A report can be adjusted also, with the use of the fpdf functions. This can be implemented with the help of the ‘Formula Element’ which can contain PHP code. ‘Formula Elements’ can be used as single elements or in tables.

Styles

• 0 = font-family;
• 1 = font-style;
• 2 = font-variant;
• 3 = font-size;
• 4 = font-weight;
• 5 = word-spacing;
• 6 = letter-spacing;
• 7 = text-decoration;
• 8 = text-transform;
• 9 = color;
• 10 = text-shadow;
• 11 = line-height;
• 12 = text-align;
• 13 = (border)
• 14 = border-style;
• 15 = border-color;
• 16 = border-width;
• 17 = border-left;
• 18 = border-right;
• 19 = border-top;
• 20 = border-bottom;
• 21 = background-color;
• 22 = cellpadding;
• 23 = vertical-align;
• 24 = opacity;

Additional Styles

• 25 = line-reverse;
• 26 = prop (proportional);
• 27 = head;
• 28 = foot;
• 29 = hidden;
• 30 = report_tab_rows;
• 31 = report_tab_cells;
• 32 = background;
• 33 = list;
• 34 = pagebreak;
• 35 = lock pagebreak;
• 36 = relativepos;
• 37 = colspan;
• 38 = rowspan;
• 39 = display if element;
• 40 = seperator of multible table-row elements;
• 41 = tagmode;
• 42 = html;
• 43 = writing-mode

Access Variables

Content of elements in the report (also hidden) can be accessed via ‘Formula Element’.

Single Element:

$glob_el[elid][number]

• elid: The unique element ID. Can be seen in the context menu of the relevant element, displayed as the first information.
• number: The ‘Element Number’. Ascending number starting with ‘0’. If multiple values exist (from links) the number can be chosen here, otherwise always ‘0’.

Table Element:

$glob_rowel[elid][$ROWNR] 

• elid: The unique element ID. Can be seen in the context menu of the relevant element, displayed as the first information.
• $ROWNR: The ‘Table Row’. Ascending number starting with “0”. The variable “$ROWNR” can be used directly to access the element of the same row.

Element style: 
$report['style'][$report['arg_result'][elid]][styleid] = 1; 

• elid: The unique element ID. Can be seen in the context menu of the relevant element, displayed as the first information.
• styleid: ID of a style according to the table above.

Functions

The return of values via ‘Formula Element’ is defined by ‘return’ or the variable “$arg_result”. You can also call custom functions.

Examples

if($glob_el[3][0] == 'Madam'){
   $arg_result = "Dear Madam".$glob_el[2][0];
 }elseif($glob_el[3][0] == 'Sir'){
   $arg_result = "Dear Mister".$glob_el[2][0];
 }else{
   $arg_result = "Dear Mister or Madam";
 }

$dingbats_char_on = "a";
$dingbats_char_off = "r";

$GLOBALS["dingbats_char_on"] = "4";

return getMyExtZF($glob_el);

getMyExtZF($glob_el){
 return array_sum($glob_el[1]);
}

$arg_result = ($glob_rowel[12][$ROWNR]*1.19);

$arg_result = number_format(array_sum($glob_rowel[12]),'2',',','.')." €";

if($glob_rowel[4][$ROWNR] > 100){
   $arg_result="#HIDEROW";
}

$pdf->SetTagStyle("com1","arial","I",11,"0,151,200");
$pdf->SetTagStyle("bold1","arial","B",11,"0,151,200");
$pdf->SetTagStyle("del1","arial","BI",11,"0,151,200");
 
$arg_result = "hallo <com1>".$glob_rowel[12][$ROWNR]."</com1>, <bold1>this is bold</bold1> ";

$row = count($cell_value[$bzm3][$bzm4]);
for($column=1;$column<=2;$column++){
  if($row == 0 OR $row == 2 OR $row == 4){
    $cell_style[$column][1][$row][21] = 'FFFFD2';
  }elseif($rownr != 0){
    $cell_style[$column][1][$row][21] = 'E1E1F0';
 }
}

if(($ROWNR-$current_row) % 2 != 0){
for($mE=1; $mE <=$report_tab_cells;$mE++)
$cell_style[$mE][1][$ROWNR-$current_row][21] = 'F3F3F3';
}

$extension["select"][0] = "ADDRESSES.NAME";
$extension["where"][0] = "ADDRESSES.NAME = 'Hans'";
$extension["where"][1] = "CUSTOMER.NAME = ADDRESSES.NAME";
$extension["from"][0] = "ADDRESSES";
$extension["order"][0] = "ADDRESSES.NAME DESC";

append_pdf($pdf,$file='path to file',$page=1,$posx=0,$posy=0);

append_report(6,$ID);

$report_id = 14;
$h=true;
append_subReport($pdf,$report,$ID,$el,$report_id,$h,$w);

$report_id = 14;
$report_rename = "temp1.pdf";
$subreportfile = generate_pdf($report_id,$ID,$report_output,$report_rename);
$report["tab_size"][$el] = $subreportfile;
$report["height"][$el] = 0;
print_bild($pdf,$ID,$report,$el);
return;

$chart_id = 12;
$w=true;
append_subChart($pdf,$report,$ID,$el,$chart_id,$h,$w);

require_once('EXTENSIONS/generateMyChart.php'); # eigenes Template
$mychartfile = printMyChart(); # eigene Funktion

if(file_exists($mychart)){
   $prv = array( $mychartfile,
   $report["posx"][$el],
   $report["posy"][$el],
   $report["width"][$el],
   $report["height"][$el],
   $report["style"][$el],
   'bild');
   $pdf->print_element($pdf->printcell($prv));
}

Array
 (
    [0] => Array
        (
            [0] => line-1-0
            [1] => line-2-0
            [2] => line-3-0
        )
 
    [1] => Array
        (
            [0] => line-1-1
            [1] => line-2-1
            [2] => line-3-1
        )
 
    [2] => Array
        (
            [0] => line-1-2
            [1] => line-2-2
            [2] => line-3-2
        )
 )

myExt_getMyData($ID,$report_id,$report_tab,$report,$report_tab_cells,$bzm2,$rowid)

OpenOffice

Another alternative, to generate reports with LIMBAS is the ‘Report Engine’ for .odt documents. With this extension an Open Office document can be created from an already existing PDF template. The PDF template is supplemented, by the report which is first written as XML.
To generate an .odt document a template is needed, which is also an .odt document. Only the placeholders that will later be replaced are defined. The report can be printed as XML in order to compare the names with the placeholders. The file is then saved in the LIMBAS file manager in any directory and can be selected in the admin area of the report overview as an Office template. When making changes to the template, the template can be replaced directly in the file manager.
All fields that are required in the template must be present in the LIMBAS report template. Thus, the positioning and formatting of the elements is not considered but links or lists are. Also a report provided in PDF format can be used as a report template and it can be switched between the PDF and odt formats. Note, that the templates should be created manually, rather than with copy & paste from other documents. Otherwise, LIMBAS might not be able to generate the document.

Example

Template Place Holder:

${time_from} - ${time_to}
land: ${land}
place: ${place}
zip code: ${zipcode}
${positionen:name} ${positions:price} # for table output

Here the corresponding XML output:

<report name=“Chronic“>
<item id=“1“>
<text name=“time_from“>17.10.1977</text>
<text name=“time_to“>22.10.1977</text>
<text name=“land“>Swizzerland</text>
<text name=“place“/>
<text name=“zipcode“>12356</text>
</item>
</report>