In general, a report can be understood as the "where" dimension of an sql query in a relational database - a predefined query with a fixed set of parameters whose result is persisted and therefore reusable.

Low-Level Reporting
Entity Report
- Example
- Programmatic execution of entity reports

Low-Level Reporting

When being executed, a report gathers a list of root object ids based on its fixed internal semantics, which can be defined by stored procedures, search expressions or an existing array of ids. Subsequently, this list is persisted in the storage table, along with the report's id, allowing an N:M mapping between reports and root entity objects. The purpose of this approach is to have fast reusable access on an entity set in list models, where usually thousands of records are to be loaded and displayed at once, and all other occasions when dealing with lists of entity items. Therefore, only the root entities' ids are loaded and stored internally whereby the actual entity data fields (the select dimension of the query - which is defined by utilizing FieldPaths) are loaded separately via SQL joins and thereby reusing the report's result. Thus, only the requested data needs to be transferred to the caller, omitting unnecessary column data.

Reports carry the following information:

unique id of the report
data source: MAIN/MASTER/SUPPLIER
purpose: TEMPORARY (report is going to be deleted after a specific amount of time) or PERMANENT
type: root entity type (e.g. ArticleType)
result table: Name of the storage table containing the report's object ids. Either ReportStoreTempA[0..n] / ReportStoreTempB[0..n] if temporary or ReportStore if permanent report.
count: number of ids this report consists of

Depending on its purpose, the relevant mappings between root entity ids and reports are either stored in the ReportStoreTempA[0..n] / ReportStoreTempB[0..n] (for temporary reports - this is most often the case) or ReportStore[0..n] table (permanent reports).

For performance reasons, each table ReportStoreTempA, ReportStoreTempB and ReportStore exists n times, where n is configurable (default is 10). Reports are respectively stored in these n table instances following the round robin algorithm.

Temporary reports are rotationally stored in either ReportStoreTempA[0..n] tables or ReportStoreTempB[0..n] tables with a cleanup job continuously switching between those tables and executing a TRUNCATE statement of the table to be used henceforth, as well as deleting the report header data in Report table associated with this table.

In order to reuse and work with reports, they support additional operations like subtract, intersect, copy and delete via the ReportService.

ReportQuery: The ReportQuery class represents the input for a stored procedure based report. This implies the name of the data source, the EntityType of the item's to be reported, the query method (stored procedure or search expression), the purpose of the report, the origin of the report, etc.

Reporting stored procedures

Note that reporting stored procedures are deprecated, since they are database dependant and thus maintaining them is very expensive. Instead search expressions should be used. Nonetheless, reporting stored procedures must fulfill a special API contract so they can be used in the framework. Reporting stored procedures must have an OUT parameter called FCA_ReportSQL which holds the select statement returning the distinct IDs of the query. No order-by clause is allowed, since this is being done by the reporting framework. No DISTINCT is needed, because duplicates are removed by the reporting framework.

ReportResult: A report result is the consequence of an executed report query. It represents an immutable report proxy, referring to a report which has been executed and stored in the database. It holds the name of the data source (MAIN / MASTER / SUPPLIER) where the report has been executed and stored, the persisted report's database-internal unique id, the report's type and purpose, the name of the report's result table name (ReportStoreTempA / ReportStoreTempB / ReportStore), as well as current amount of reported items.

ReportUtils: This deprecated utility class provides methods for executing report queries and creating reports based on given item ids. It also includes a method for deleting non-temporary reports. Note: Since version 6.0 ReportService shall be used instead.

ReportCleanerJob: This job runs every hour and deletes temporary reports from the database.

Entity Report

In order to provide the means for generic report contributions, a new report approach has been elaborated which enhances the possibilities of the Low-Level reporting, thus it's not a replacement, it's an extension.

images/download/attachments/138707645/EntityReportsClassDiagram.png

EntityReport: The EntityReport represents an extension/contribution to the com.heiler.ppm.entity.core.entityReports extension point. It incorporates the needed information for providing generic reports:

Typed parameters, which can have default values in case not mandatory
Entity of the repository this report stores ids of
The datasource provider for specifying which db is targeted (MAIN/MASTER/SUPPLIER)
An EntityReportExecutor which basically contains the logic/semantic of the report query. The StoredProcExecutor can be used for wrapping the old ReportQuery (with parameter mapping, stored procedure name, ...). As a second executor, the Product Manager search expressions can be used in order to build HQL based queries.

EntityReportQuery: The query keeps a reference to the report plus the necessary information for a report to be executed, mainly the actual values for the report parameters.

EntityReportResult: The actual entity result report contains a reference to the executed query and represents an immutable result information of a persisted report, including the same attributes as in ReportResult (e.g. id, datasource, count, purpose)

EntityReportRegistry: This is the central point for registering reports to be contributed and accessing by alias etc

Custom entity reports can be contributed through the com.heiler.ppm.entity.core.entityReports extension point. To be able to access them through the report service they need to have an alias name defined.

Example

When contributing new entity reports, the following properties are most important. Note that a detailed property description is also available via mouse hover tool tips in the Eclipse extension menu.

Property Name	Purpose
id	unique id for the entity report
item-entity	entity type to be returned
alias	report alias to be used in service api as well as in report service
data source	data source the report has to be executed against either FixDataSourceProvider (MAIN/MASTER/SUPPLIER) or `com.heiler.ppm.catalog.core.report.DataSourceByCatalogProvider` for having the data source determined dynamically by parent catalog
supports-service-api	if on true, this report can be called from service api

As mentioned above, entity reports need an executor for the report's semantics, which can be specified in the executor node. Here, either add a class implementing EntityReportExecutor, or add a sp-executor-spec child node, where the name of the stored procedure to be called has to be specified. Entity report parameters can be added as additional nodes to the report node, with the most important properties being:

Property Name	Purpose
id	unique id of the parameter
name	parameter name, which can be externalized for i18n use
alias	service api alias for the parameter
visible	if set to true (default), parameter is visible in the ui. If set to false, parameter should be set to non-mandatory (see below) or must have a default value
mandatory	parameter mandatory for report execution or not
enum	repository based enum providing the possible values
collection	if set to true, a collection of values is expected. By default is false.

Additionally, a showForEntity key-value node can be added to the report node, which is used to decide whether this report shall be displayed in the UI context menu and if so, for which entity the entry shall appear (e.g. MasterCatalog or SupplierCatalog). This key-value is cappable of taking more than just one entity, just separate the entities with a comma. (e. g. MasterCatalog,SupplierCatalog)

images/download/attachments/138707645/entityReport1.PNG

Contributed reports can be utilized/examined in the Product Manager client by calling predefined report, like in our example show-> items by status reached:

images/download/attachments/138707645/entityReport2.PNG

All the information, including the different status values for the respective parameter in our example, are pulled generically from the contribution. All changes taken within the contribution's plugin.xml are immediately reflected in the UI.

images/download/attachments/138707645/entityReport3.PNG

As you can see, such entity report queries can also be edited by pressing "Edit query" in the table view menu, where e.g. a different status can be selected as a parameter.

Programmatic execution of entity reports

The following test code shows the example from above (MasterArticlesByStatus) to be called in programmatic manner via the ListModelLoadService, thereby executing the report implicitly and loading the relevant matching items in one shot.

Programmatic execution of entity reports via list model load service

package com.heiler.ppm.article.core.internal.characteristic.compare;
 
import org.eclipse.core.runtime.CoreException;
import org.eclipse.core.runtime.NullProgressMonitor;
 
import com.heiler.ppm.catalog.core.CatalogConst;
import com.heiler.ppm.entity.core.report.EntityReport;
import com.heiler.ppm.entity.core.report.EntityReportQueryBuilder;
import com.heiler.ppm.entity.core.report.EntityReportQueryItemList;
import com.heiler.ppm.entity.core.report.EntityReportRegistry;
import com.heiler.ppm.repository.EntityType;
import com.heiler.ppm.repository.path.FieldPath;
import com.heiler.ppm.repository.util.RepositoryUtils;
import com.heiler.ppm.std.core.entity.EntityProxy;
import com.heiler.ppm.std.core.entity.EntityProxyFactory;
import com.heiler.ppm.std.core.list.ListEntry;
import com.heiler.ppm.std.core.list.ListModel;
import com.heiler.ppm.std.core.list.ListModelLoadParams;
import com.heiler.ppm.std.core.list.ListModelLoadService;
 
@SuppressWarnings( "nls" )
public class ArticlesByStatusReportExecutor
{
  private EntityReportRegistry entityReportRegistry;
  private EntityProxyFactory   entityProxyFactory;
  private ListModelLoadService listModelLoadService;
  private EntityType           catalogEntityType;
 
  public ArticlesByStatusReportExecutor( EntityReportRegistry entityReportRegistry,
                                         EntityProxyFactory entityProxyFactory,
                                         ListModelLoadService listModelLoadService )
  {
    this.entityReportRegistry = entityReportRegistry;
    this.entityProxyFactory = entityProxyFactory;
    this.listModelLoadService = listModelLoadService;
    this.catalogEntityType = RepositoryUtils.getRepositoryRootEntityType( "CatalogType" ); 
  }
 
  public void executeArticlesByStatusReport() throws CoreException, InterruptedException
  {
    //Obtain report from registry
    EntityReport report = this.entityReportRegistry.getReport( "com.heiler.ppm.article.core.MasterArticlesByStatus", 
                                                               false );
 
    //Create query builder
    EntityReportQueryBuilder queryBuilder = new EntityReportQueryBuilder( report );
 
    // set parameters:
    // obtain proxy for master catalog
    EntityProxy catalogProxy = this.entityProxyFactory.createEntityProxy( this.catalogEntityType, null,
                                                                          CatalogConst.MASTER_ID );
    // Items from master catalog
    queryBuilder.setParamValue( "Catalog", catalogProxy );
 
    // All items with status "new" -> key 100
    queryBuilder.setParamValue( "Status", 100 );
 
    // Entity item list for specifying list model's row results
    EntityReportQueryItemList itemList = new EntityReportQueryItemList( queryBuilder.toQuery() );
 
    //Get List Model
    // We want the ean field to be loaded for the list model
    FieldPath[] fieldPaths = new FieldPath[] { new FieldPath( "ArticleType.Ean" ) };
 
    // Parameters for specifying list model's result. Rows by entity item list, columns by field paths
    ListModelLoadParams loadParams = new ListModelLoadParams( itemList, fieldPaths );
 
    // Load the list model, internally calls the report query executor for running and persisting the entity report
    ListModel lm = this.listModelLoadService.loadListModel( new NullProgressMonitor(), loadParams );
 
    // Obtain the list model's entries
    for ( ListEntry entry : lm )
    {
      String ean = ( String ) entry.getFieldValue( 0 );
      System.out.println( "item ean = " + ean );
    }
  }
}

A corresponding test executing this report code could look like the following:

Test injecting the dependencies and executing the report

  @Test
  public void runArticlesByStatusReportExecutor() throws CoreException, InterruptedException
  {
    EntityReportRegistry entityReportRegistry = EntityReportComponent.getReportRegistry();
    EntityProxyFactory entityProxyFactory = EntityManagementComponent.getEntityProxyFactory();
    ListModelLoadService listModelLoadService = EntityManagementComponent.getListModelLoadService();
 
    ArticlesByStatusReportExecutor articlesByStatusReportExecutor = new ArticlesByStatusReportExecutor(
                                                     entityReportRegistry, entityProxyFactory, listModelLoadService );
 
    articlesByStatusReportExecutor.executeArticlesByStatusReport();
  }