Project

General

Profile

Reports and product sheets

Introduction

This document introduces the configuration and the administration of BIRT reports for the beCPG software. It’s dedicated to beCPG adminisrators. This video introduces the modification of a BIRT report : http://www.youtube.com/watch?v=pZPZc3bmd_s

Setup

BIRT versions are available at : http://download.eclipse.org/birt/downloads/

Version for Windows: http://www.eclipse.org/downloads/download.php?file=/birt/downloads/drops/R-R1-4_3_0-201306131152/birt-rcp-report-designer-4_3_0.zip

Report types

« Product » reports

There exists 2 types of reports for the « Product »:
  • system report (always generated by the system, automatically);
  • user report (chosen by the user when he edits the product). This kind of report is a upon request report.

These reports are defined in the folder : /System/Reports/Product reports.

The addition of a new report can be done in this way (you can copy and paste a product report) :
  1. Add a folder ;
  2. Add the file : *.rptdesign and then, edit its metadata :
    • Report’s name ;
    • The product type for which the report will be a applied ;
  • Raw material by adding : {http://www.bcpg.fr/model/becpg/1.0}rawMaterial
  • Semi-finished product by adding: {http://www.bcpg.fr/model/becpg/1.0}semiFinishedProduct
  • Finished product by adding : {http://www.bcpg.fr/model/becpg/1.0}finishedProduct
  • Packaging by adding : {http://www.bcpg.fr/model/becpg/1.0}packagingMaterial

It's also possible to enter a general type. A general type allow the report to be visible by all entities.

  • General Type : {http://www.bcpg.fr/model/becpg/1.0}product
  • The model type :
  • System model to indicate if the report is automatically generated for this product type. In the case which the report isn’t a system report, the user should select it when he edits the product.
  • Default model to specify that the report is the one per default on the product which means that it will be displayed by default when the button « Create the report » is pressed.
  • There can be only one default report for one product type. Reports which are not default reports are put by default to the « Documents » folder of the product when the report is being generated.
  • The report extension

When a product is created or edited, the system generates one report per report model associated to the product.

Research extraction reports

These reports are defined in the folder : /System/Reports/Exports search.

These reports are BIRT reports which export the results of a research.

The addition of a new report can be done like this :
  1. Add a folder ;
  2. In the folder, add the BIRT or Excel report and mention its name and its metadata. The name will be displayed on the search results page. The extension of the file must be xls for an Excel report and rptdesign for a BIRT one.
  3. Define the permissions on the folder which contains the report. People must have the permission « in reading » to be able to see the report in the results of the research.
Unically for the BIRT reports :
  • The definition of the data that have to be exported to feed the report, which name is ExportSearchQuery.xml. This file defines the product attributes to be exported when the report is being executed :
  • Product’s properties (name, state, etc.) ;
  • Product’s associations (suppliers, clients, etc ;..) ;
  • Product’s characteristics (costs, nutrients, allergens, etc …).

To create or modify a BIRT report, please refer to the chapter « Modification of a Product report model ».

Excel reports are easy to create. In Excel, on the first tab, the principal type to be extracted must be defined like this :

TYPE    bcpg:finishedProduct                
COLUMNS    cm:name    bcpg:code    bcpg:erpCode    bcpg:productHierarchy1    bcpg:productHierarchy2
#    Name    Code    ERP Code    Family        Sub family

On the other tabs, data lists can be extracted from the principal type. Example for the composition :

TYPE    bcpg:compoList            
COLUMNS    bcpg:erpCode    bcpg:compoListProduct    bcpg:compoListProduct_bcpg:erpCode    bcpg:compoListProduct_bcpg:productHierarchy1
#    Product     Component    Composant – ERP Code    Component – Family

Columns and types’ attributes are internal names given to fields.

Modification of a product report model

Each beCPG report has a BIRT model for which the extension is *.rptdesign.
h3. BIRT introduction

beCPG is based on a reporting solution : BIRT (Business Intelligence and Reporting Tools).

  • Reporting: synthesizes data in the forms of reports.
  • BIRT:
  • reporting tool "Open Source" based on Eclipse.
  • graphical tool, which means that it disposes a palette allowing the selection of the element which should be placed on the report.

The BIRT report can be connected with a database in order to display the result of a SQL query on the worksheet. For this purpose, it’s necessary to add a « Data source » allowing the connection to the data base.

Then the addition of a « Data set », containing the SQL query, allows the generation of static queries but also dynamic queries by adding editable parameters when the report is being generated.

The « Data cube » allows the creation of a « crossed table ». This allows notably to display data in the form of columns and the automatic computation of the total for statistics.

The beCPG report execution is done like this :
  1. Generation of the XML data source by the beCPG motor ;
  2. BIRT model load ;
  3. Execution of the report which will give an exit *.pdf, *.doc, *.xls

Report design

The report design is done in the Eclipse designer where are found :
  • The tool box (palette) which supplies render controls as : tables, lists, text areas …
  • XML data source (Data Explorer) ;
  • The report formatting (Layout) ;
  • The masterpage : to modify the page header and/or footer ;
  • The report preview.

The report configuration consists in :
  • arranging tables, text areas …
  • defining where will be placed the data, through drag and drop of datasource attributes, in the report.

Report creation

  1. Window > Open perspective > Other > Report Design
  2. File> New> Project> Business Intelligence and Reporting Tools> Report project. Then, name the project.
  3. File> New> Report. Then, name the report and choose the starting weft.

Data explorer

Select the « Data explorer » tab in order to explore the data.

An example of XML data source is :

<entity name="8 sushis assortment" code="1009" 
    store-identifier="SpacesStore" 
    productQty="0.200" productUnit="kg" productState="ToValidate" 
    productEAN="8001456682" />

This XML can be obtained thanks to the URL :

http://{serveur}:8080/alfresco/service/becpg/report/datasource?nodeRef=workspace://SpacesStore/2aad6284-afc2-4d89-8bbf-bd3dd50ff1bc

OR

http://{serveur}:8080/share/proxy/alfresco/becpg/report/datasource?nodeRef=workspace://SpacesStore/2aad6284-afc2-4d89-8bbf-bd3dd50ff1bc

Where « workspace://SpacesStore/2aad6284-afc2-4d89-8bbf-bd3dd50ff1bc » corresponds to the product identifier (available in the URL of the navigator when we are in a product).  
This URL displays the XML which can be saved on the PC by pressing « Save as ».

XMLindentation :

xmllint --format datasource.xml > datasource-pretty-print.xml

Data source choice

To modify a datasource, here is the procedure to be followed :

  • Data explorer> Data source> New data source;
  • Select « XML Data Source » ;
  • Right click, « Edit »;
  • Press « Browse » to search the data source;
  • Select the XML saved on the computer;
  • Press « Test connection » and write the ID and the password of the administrator ;
  • If « Ping succeedeed » appears, it’s OK.
  • Press « Finish ».

XML choice :

Data Sets

Data sets allow the examination of the data source and the selection of attributes.

  1. Data set> New data set. Then, click on the data source previously entered and press « OK ».
  2. Select the data to be examined (Xpath which allows the selection of XML nodes which feed the dataset) ;
  3. Select XML nodes’ attributes which feed the dataset columns.

Addition of a property :

It’s possible to preview the content of a dataset by clicking on « Preview results ». Once the dataset has been parameterized, its data have to be dragged and dropped in the report.

Report visualisation

In order to select different report views, click on the different tabs below the principal section.

Layout

The layout allows the construction of the report :
  • Controls addition ;
  • Data addition.

Master page

The master page allows to complete:
  • The report header and footer ;
  • The report width and height.

Script

It’s possible to program usable functions in the report by storing them in the « Script » tab. These scripts can be executed at the starting or at the closing of the report, for a precise element or for the report in its integrality. The XML source code is accessible.

XML Source

Displays the report XML.

Preview

Displays the report preview, as if it was executed per beCPG : Run> View report> Exit format (PDF).

Controls palette

The palette is selected through the « Palette » tab. It allows to add static text.

Available controls
  • Label : to add static text ;
  • Text ;
  • Dynamic Text: dynamic text. Example : to put words in bold on the label, HTML beacons are used ;
  • Data : rarely necessary because data are added from « Data explorer » ;
  • Image ;
  • Grid : static grid (tables can be added inside);
  • List : dynamic list according to the number of lines in the dataset ;
  • Table : dynamic table according to the number of lines of the dataset  (lists can be put inside) ;
  • Chart ;
  • Cross table.
Control addition in a report

The addition of a control is done by drag and drop.

Images addition

The addition of a static image is simple.

The addition of a dynamic image is more complexe. In that case, the beCPG motor gets an image associated to a product and sends it to the data source of the report during its execution. The procedure is as follow :
  • Add the image ;
  • Dynamic image by referencing the image ID given to the datasource, example :row["productImage"]
Data type fields expression

Data type fields can undergo conditions or be the concatenation of many attributes, example :

"Packaging : " + dataSetRow["palletBoxName"] + ", indicative tare (g) : " + dataSetRow["pmBoxTare"] + ", dimensions (mm) : " + dataSetRow["pmBoxDimensions"]

To edit this formatting : :
  1. Click right on the data attribute ;
  2. Edit Value/Expression ;
  3. Fill the expression.

Visibility

It’s possible to hide fields or images according to certain conditions . For this purpose :
  • Display the control properties ;
  • Select the « Visibility » tab ;
  • Fill the expression which indicates when hiding the control.

Formatting

It’s possible to define formatting styles in a report. For this purpose :
  • Click right on a control ;
  • Style : new style.

Then, styles can be applied to controls, edited or deleted : right click on a control and press « Style ».

Deployment

Once the report has been edited under Eclipse, save it in the format .rptdesign.
  1. Technical sheets ? Repository> System> Reports> Product reports> Entity and drop the report ;
  2. Languages files (properties) ? Repository> System> Reports> Product reports and drop the report.

In « Edit the properties », choose :

  • System model if the report has to be attributed to all the products of the same type ;
  • Default model if there exists many models and that this latter has to be displayed per default ;
  • If nothing is ticked, go to the concerned product and select the report by hand.

To translate the report in different languages, associate language files to it. Select different languages in the drop down list too.

Extractor parameters (Part 1)

Introduction

The extractor allow datasource creation. Since 2.1, beCPG give the possibility to extract specifics of the data by predefined parameters.

The following parameters must be place in [instanceName]/becpg/classes/beCPG.properties.

Parameters with an asterisk, in title, can take several values; values must be separated by a comma ",".

Parameters

becpg.reportServerUrl

This parameter specifies the server on which the BIRT module is located.

Default value:

  • becpg.reportServerUrl=http://localhost:8080/becpg-report

beCPG.report.name.format

This parameter specifies to BIRT module, how to named the report.

Default value:
  • beCPG.report.name.format={entity_cm:name} - {report_cm:name} - {locale}

beCPG.entity.report.mltext.fields*

Allows to extract all languages entered from a field

Exemple :

If legal name field (bcpg:legalName) is entered in 2 languages (french & english) and we must display those languages in the report, the following configuration is :
  • beCPG.entity.report.mltext.fields=bcpg:legalName

beCPG.product.report.multiLevel

Enables the multi-level extraction. It can take 2 possible values, 'true' and 'false'.

Exemple :

If this parameter is :
  • False : only level 1 elements are extract (see below image, framed in red)
  • True : all elements are extracts.

Default value:
  • beCPG.product.report.multiLevel=false

beCPG.product.report.priceBreaks

Allows to display the cost list depending on the purchase amount. it takes 2 possibles values : 'true' and 'false'.

By default, it is equal to 'false'.

beCPG.product.report.assocsToExtract*

'Title' and 'Clients' fields, are both Finished Product properties; the first property is a simple property and the second is an associated property (association).
By default, only the field 'name' of the associated property is extract by the extractor but sometime, we want to retrieve all data of clients fields (name, address, Phone number, etc). This parameter allows to extract on a product, all properties of an association.

Default value:
  • beCPG.product.report.assocsToExtract=bcpg:plants,bcpg:suppliers,bcpg:storageConditionsRef,bcpg:precautionOfUseRef

beCPG.product.report.assocsToExtractWithImage*

A finished product has a property 'Clients', which allow to associate a client entity with a product.
By default, the extractor doesn't extract images of a associated field while the finished product extraction. This parameter tells the extractor to retrieve entity's images, for exemple, entity client.

Exemple: For retrieve client's images, pass value bcpg:clients :

beCPG.product.report.assocsToExtractWithImage=bcpg:clients

Default value:

  • beCPG.product.report.assocsToExtractWithImage=

beCPG.product.report.assocsToExtractWithDataList*

Similar to "beCPG.product.report.assocsToExtract" but allows to extract the dataList of a associated field.

Default value:

  • beCPG.product.report.assocsToExtractWithDataList=

beCPG.product.report.componentDatalistsToExtract*

A "component" can bea "Raw Material" (RM), a "Simi-finished" (SF), a "Packaging" or a "Packaging Kit".

A product has lists (Composition, Packagings, etc) and contain components into each of them. During a simple extraction, components are extracts without their lists. This parameter allows to extract component's lists.

Exemple: The following configuration allows to extract the dataList of components from product's composition.

  • beCPG.product.report.componentDatalistsToExtract=bcpg:compoListProduct

Default value:

  • beCPG.product.report.componentDatalistsToExtract=

Extractor Parameters (Part 2)

Introduction

As you could see in previous part, the extractor setting is in beCPG.properties file and server need to be restarted after each modification.

Since version 2.2.2, it is possible to setup the report extractor directly into beCPG PLM and the server no longer needs to be restarted.

Principles

BeCPG is installed on a system with a default configuration for the extractor. This configuration may be overriden through the new field, Text parameters placed in the properties editing form of report. (see below image)

With this override, this is not necessary to reboot the server. The new configuration is take in account for next extractions.

Text parameters contains a JSON object which have keys list.

To begin with, let us list of available keys, then their roles and to finish a pratical use case.

Keys list

  • iterationKey
  • params
  • prefs
    • extractInMultiLevel
    • componentDataListsToExtract
    • extractPriceBreaks
    • mlTextFields
    • assocsToExtract
    • assocsToExtractWithDataList
    • assocsToExtractWithImage
  • nameFormat
  • titleFormat

Keys Role

iterationKey : allows to specify on which DataList (Packaging, Composition,...) the extractor must carry out iteration on each component. It is used in conjunction with params keys.

params : is a parameters table. Each parameter object has 2 keys :
  • id : is a variable name. The variable name is given arbitrarily to have the possibility to use it with other key, as nameFormat and titleFormat.
  • prop : allows to specify the value to retrieve and store it in id variable.
prefs : is a object and allows to set the extractor behaviour. The parameters taken into account is :
  • REMARK: click on one of the parameters above to see its rôle.

nameFormat : allows to codify the PDF report name. By default, the codification is : {entity_cm:name } - {report_cm :name} - {locale} : which gives «Quiche Lorraine – Client specification - fr» for the finished product Quiche Lorraine .

Exemple :
If we want to display the beCPG code of a component of the Composition, nameFormat will have the value:

«{entity_cm:name} – {report_cm:name} - {locale} - {param1}»

{param1} is a variable which is defined in JSON. We will see how to define it in the use Case section.

titleFormat : allow to codify the report name placed in the reports list. List of generated Reports is in Reports list of an entity.

Use case

Let’s take for exemple, a supplier having multiple plants and you need a report per plant.
Each report must contain their name (Production sheet, technical sheet,...) and the plant name.

To do this, you must have created the list Reports beforehand in the supplier model. Once the supplier report created and deployed, you must place in Text parameters field, the following JSON to finish the report configuraiton :

{
   iterationKey : "bcpg:plant",
   params : [{
      id: "param1",
      prop : "cm:name" 
   }],
   prefs : {
      assocsToExtract : "bcpg:plants,bcpg:suppliers,bcpg:storageConditionsRef,bcpg:precautionOfUseRef,bcpg:nutListNut",
      assocsToExtractWithDataList : "bcpg:compoListProduct" 
   },
   nameFormat : "{entity_cm:name}- {report_cm:name}  - {locale} - {param1} ",
   titleFormat : " {report_cm:name} -  {locale} - {param1}" 
}

And finally click on 'Create reports' to generate the supplier reports.

Tips

  • The params table is used with iterationKey. So, it is useless to put one without the other.
  • If you have overriden a key (mlTextFields, assocsToExtract,…) and you get it out of Text parameters, the Extractor will use the default value.
  • The defined parameters in JSON params key are extracted in the report dataSource (XML).
Exemple:
If params equals to:
{ ...

   params : [{
      id: "plantName" 
      prop: "cm:name" 
   },
   {
      id: "plantCertifName" 
      prop: "bcpg:plantCertifications|cm:name" 
   }], 
   ...
}

The XML node corresponding to the result of the JSON parameter will be:
<entity> ...
    <reportParams>
           <plantName nodeRef="workspace://SpacesStore/108624b8-49da-44a0-be1f-7735b7a35143" prop="cm:name" value="Plant XY"/>
           <plantCertifName nodeRef="workspace://SpacesStore/108624b8-49da-44a0-be1f-7735b7a35143" prop="bcpg:plantCertifications|cm:name" value="Certification X1"/>
    </reportParams>
</entity>

Notice that :
  • the node name is the value specified in id.
  • the nodeRef attribute is the current component nodeRef.
  • the prop attribute is the text string entered in JSON.
  • the value attrtibute is the value stored in "plantName".
  • it is possible to retrieve a property of a association using this pattern : "associationKey|attributKey".

There are 2 ways of retrieving datasource containing the reportParams node.
The 1st consist to enable debug mode in Java. The 2nd way is to consult the rep:reportTextParameters attribute of the generated report node.

We will see the second way , less heavy to perform.

After setting up and generating the report :
  1. Go to Documents folder of the entity (Supplier in our exemple) and click on the pdf report.
  2. get the document (pdf) NodeRef and retrieve the document through the node browser (Read this to know how to retrieve a nodeRef and use the node browser).
  3. Once on the document node, look at his rep:reportTextParameters attribute and you will see that it contains a JSON similar to :
    {
        "titleFormat":" {report_cm:name} - {nomUsine} - (fr - {locale} )",
      "nameFormat":"{entity_cm:name} - {report_cm:name} - {nomUsine} - (fr - {locale} )",
      "params":[{
          "nodeRef":"workspace://SpacesStore/108624b8-49da-44a0-be1f-7735b7a35143","prop":"cm:name","id":"nomUsine","value":"Usine XY" 
        },{
          "nodeRef":"workspace://SpacesStore/108624b8-49da-44a0-be1f-7735b7a35143","prop":"bcpg:plantCertifications|cm:name","id":"nomCertifUsine","value":"Certification X1" 
        }],
      "prefs":{}  ...
    }
    

With the matching system, you are able to reconstruct the <reportParams> XML node.

Creating a "Technicals Sheets" list

Introduction

Entities (Finished Product, Raw Material, etc) has default lists ("Technicals Sheets", "Composition",...). Some entities do not have some lists, for exemple, "Technicals Sheets" list for the supplier.

Procedure

To add a "Technicals Sheets" list on supplier model, do:

1. Select the "Supplier" model located at "Repository > System > Entities templates"

2. Click on "New list" (see below image, framed in red) then in the Types list, select "Custom view" (see below image, framed in green).

3. Then fill the form fields with the same image indicated on the below image, then save:

The new Technicals Sheets list is created.

Good Practices - BIRT

In Reports and product sheets section, you get used with Birt Designer. You are well acquainted with Data Explorator tab, Tool Box (contains : tables, text,...) and layout tab (report formatting).

You have probably noticed, there is Outline tab next to Navigator tab. This tab, present the report structure, (cf. below image).

This good practices guide will deal some sections present in the stucture.

Function declaration

The are cases where a developper creates the same function in different elements (text, data, dynamic text,…) in the report. Proceed like this, make the report heavier and when a maintenance is done on this function, you forced to modify all elements that contains this function.

In order to optimize the maintenance time, the best way to proceed is to déclare the function once and make it available in the whole report. To do this, use ‘Initialize’ event of the report.

Pros :

  • Report lighter due to the lack of code duplication.
  • Reduce maintenance time.

Use of Aliases

Using an Alias at the fields level of a DataSet is usefull when the field key is very different of the name displayed to the user.
Use an Alias allow to save time when we must search a field for a specific treatment

Manage images in Birt

In order to minimize report size, make sure that after each report modification, that all unused integrated images have been deleted.

For efficiency reasons, rename / add / delete a image through the Eclipse Outline tab.

Scripts management (event)

Birt give the possibility to define JS script at different event level (Initialize, onPrepare, onRender,...). While an event contain JS code, the event displays in Script section of Outline tab. It is then possible to get a global vision of the whole report elements having a script associated to a event.

Before each delivery, verify that no unused script stay in the report.

To learn more about events and scripts Birt, read this documentation