]po[ PowerPoint (Libre Office Impress) Reporting

(Please click on the hexagons for more information)


This packages produces [LibreOffice Impress] (aka: PowerPoint) "reports" based on user-definable templates in Impress ODP format, that typically will show data in the corporate design. Typical use for this package are monthly project status reports that you want to share with different stakeholders. You can define multiple templates in order to vary the report details depending on the stakeholder, for example. 

General Structure 

Reporting starts with a Impress ODP "template", which consists of a number of "slides". Each slide consists of:

  • "Type":
    This is the first string in the "Name" of the slide.
    The "Name" of a slide is a LibreOffice concept that does not exist like that in PowerPoint. You can view/edit the name of a slide by right-clicking the slide in the "Slide Pane" and choosing "Rename Slide".
  • "Contents" with variable expressions:
    The "normal" slide content may contain "@ varname @" expressions which will be replaced with values from the SQL queries in the "Notes" section, as explained below.
  • "Notes" with SQL statements:
    This engine uses the notes section of a slide in order to define a number of SQL statements that will pull out information from the system's database and provide this information to the @ varname @ expression in the contents of a slide. 

 

Types of Slide

Slides in OpenOffice Impress have a "name", which is part of the metadata of a slide. You can access the name by right-clicking a slide in the left "Slide Pane" and selecting "Rename Slide" in the context menu. The following slide types are currently available (in lowercase):

 

  • static:
    This is the most simple type of slide, containing some text or graphics.
    @ varname @ expressions in the text will be replaced by values extracted via SQL.
    The Notes section of the slide is expected to contain one "page_sql", which may only return a single row of data. The columns of this SQL are available to @ varname @ expressions.

  • list:
    This slide type is used to render lists of data extracted by a SQL statement in the note section.
    The slide should contain exactly one table, that should consist of exactly three rows: Header, content and footer.
    The header row usually contains the name of the row. The content row contains @ varname @ expressions that correspond to the columns returned by the SQL, and the footer may contain totals for each column.
    The Notes section may contain a "page_sql" as explained above, and must containe one "list_sql" that will be formatted using the table in the content section.
    The reporting engine will create a new slide if there are more than max_list rows per page.

  • gantt:
    This slide type is used to produce a Gantt graph of a project. It will use the elements defined in the "template" slide below. The structure of the gantt slide type will be explained further below.

  • repeat:
    This slide type allows to build "loops". It demands a "repeat_sql" and a "repeat_count" in the Notes section.
    As a result, the following <repeat_count> slides are going to be repeated for each row returned by the repeat_sql. The columns returned by the repeat_sql are available for @ varname @ expressions in the repeated slides.

  • template:
    This slide will not be include in the final output. It serves to define graphical elements used by the "gantt" slide above.

 

Formatting Gantt Slides

Gantt diagrams require multiple graphical elements in order to represent the various elements of a Gantt diagram:

  • Bars for RAG (red, amber or green) colored bars, together with the name of the bar
  • The timeline
  • The line indicating today

These elements are defined in a "template" page and used by the "gantt" page when rendering the Gantt diagram.

Please see the elements of a sample "template" slide for reference

 

Examples

The following example is a PowerPoint report that lists all open projects in the system:

file:project-phases-risks.odp 

  • An initial slide with the slide title (a "static" slide)
  • A "repeat" slide in order to repeat the following 3 slides for each project (a "repeat" slide)
    • Basic project information (a "static" slide)
    • The list of risks per project (a "list" slide)
    • A Gantt diagram with the 1st level activities below the main project (a "gantt" slide)
  • There is also a "template" slide with the definition how to render the Gantt diagram elements


References

Related Packages

Package Documentation 

Procedure Files

tcl/intranet-reporting-openoffice-procs.tcl        

Procedures

im_oo_cmd_executable_p       Returns the path of the executable OpenOffice binary of "" if there is no such executable 
im_oo_page_delete       Delete a list of pages 
im_oo_page_delete_templates       Delete template nodes from the given page. 
im_oo_page_extract_templates       Returns a key-value list of all "templates" on the specified page. 
im_oo_page_list       "Execute" a list of pages. 
im_oo_page_notes       Returns the "notes" from a slide page. 
im_oo_page_type_constant       A "constant" page contains only static contents. 
im_oo_page_type_gantt       Takes as input a page node from the template with a table and a sql parameter in the "notes". 
im_oo_page_type_gantt_grouping_extract_x_y_offset_list       Takes a grouping, extracts all x and all y coordinates of objects and returns a list {min_x min_y}. 
im_oo_page_type_gantt_grouping_move       Move all svg:x and svg:y coordinates in a grouping by the specified offset. 
im_oo_page_type_gantt_grouping_x_y_offset       Takes a grouping, extracts all x and all y coordinates of objects and returns a list {min_x min_y}. 
im_oo_page_type_gantt_move_scale       Move and scale a template bar according to start- and end date. 
im_oo_page_type_list       Takes as input a page node from the template with a table and a sql parameter in the "notes". 
im_oo_page_type_repeat       Repeat the following N slides for all rows coming out of the repeat_sql statement 
im_oo_page_type_static       The procedure will replace the template's @varname@ variables by the values returned from the SQL statement. 
im_oo_page_type_template       Takes as input a page node from the template with a list of templates on it. 
im_oo_select_nodes       Returns a list of nodes that match the xpath. 
im_oo_substitute       Returns a new tDom node with with a copy of $node with template expressions substituted. 
im_oo_substitute_descend       Performes substitution on tDom nodes. 
im_oo_tdom_explore       Returns a hierarchical representation of a tDom tree representing the content of an OOoo document in this case. 
im_oo_to_text       Returns all text contained in the node and its children. 
im_oo_to_title       Returns the title(s) of the node and its children. 

SQL Files

sql/postgresql/intranet-reporting-openoffice-create.sql        
sql/postgresql/intranet-reporting-openoffice-drop.sql        

Content Pages

www/
      index.vuh
      report-list.adp
      report-list.tcl
  

  Contact Us
  Project Open Business Solutions S.L.

Calle Aprestadora 19, 12o-2a

08902 Hospitalet de Llobregat (Barcelona)

Spain

 Tel Europe: +34 609 953 751
 Tel US: +1 415 200 2465
 Mail: info@project-open.com