How to integrate Pivot views

Introduction

This article with deals with :

  1. Setup your application to use pivot.
  2. Adding a “pivotModule” to your workspaces.
  3. Adding a “pivotTable” to any view.
  4. Customizing pivot refiner behaviors.
  5. Using the pivot administration module

1. Setup your application to use pivot

  • Check your using Jspresso-EE version 4.2 or above.
  • Check your using Jspresso-Extensions version 2.6 or above.

If your project was created before version 4.2 :

  • Setup the root pom.xml dependency management section :
<dependency>
  <groupId>org.jspresso.framework</groupId>
  <artifactId>jspresso-ext-pivot</artifactId>
  <version>${jspresso-ext-pivot.version}</version>
</dependency>
  • Setup the core pom.xml dependency section :
<dependency>
  <groupId>org.jspresso.framework</groupId>
  <artifactId>jspresso-ext-pivot</artifactId>
</dependency>
  • Setup the spring configuration file beanRefFactory.xml and complete all sections :
<value>org/jspresso/framework/ext/pivot/model/dsl-model.xml</value>
<value>org/jspresso/framework/ext/pivot/model/model.xml</value>
<value>org/jspresso/framework/ext/pivot/backend/dsl-backend.xml</value>
<value>org/jspresso/framework/ext/pivot/backend/backend.xml</value>
<value>org/jspresso/framework/ext/pivot/view/dsl-view.xml</value>
<value>org/jspresso/framework/ext/pivot/view/view.xml</value>
<value>org/jspresso/framework/ext/pivot/frontend/dsl-frontend.xml</value>
<value>org/jspresso/framework/ext/pivot/frontend/frontend.xml</value>
  • Complete the applicationMessageSource bean to add one more internationalizations source :
bean id="applicationMessageSource" parent="abstractApplicationMessageSource">
  <property name="parentMessageSource" ref="commonsMessageSource"/>
    <property name="basenames">
    <list>
      <value>org.jspresso.framework.ext.pivot.i18n.Messages</value>
      …
    </list>
  </property>
</bean>
  • Import pivot module into SJS by updating the application.groovy file :
ManageModule libraries = new ManageModule()
libraries.importModuleAsResource('jspresso-ext-pivot')
...
def domainBuilder = new Domain(libraries)

2. Adding a “pivotModule” to your workspaces

Like a filterModule, a pivotModule allow to query database for any component and :

  • The representation is a pivotTable instead of a table. To be more specific, the moduleView is composed of two tabs, the first one is the pivot table, the second one is a table which contains pivot items matching the selection in pivot table.
  • A pivot criteria view is visible underneath the filter criteria. This form allow to customize pivot’s lines, columns and measures
  • You can setup default dimensions (i.e suitable as lines or columns) and measures :
    • You can can use any non-computed field defined in the related component.
    • You can also use also computed field if  the “sqlName” attribute is set up
    • You can also use custom fields not defined in the component if defined in your custom pivot refiner (see section 4.4)

Here is a simple example :

pivotModule('EmployeeAnalysis',
      icon:'employee-32x32.png',
      dimensions:['salary(10000, 20000, 50000)',
                  'age(20, 45)',
                  'gender',
                  'managedOu.ouId',
                  'managedOu.manager.name',
                  'company.name',
                  'company.departments',
                  'extra'],
      measures:['ssn@count',
                'salary@sum', 
                'salary@percentile90', 
                'salary@average/managedOu'], 
      component:'Employee')

Please look at CrossItems documentation for more details about dimension and measures formats.

Pivot module customization :

  • Here is a simple example about how to modify pivot sizes limits. Section 4/ will go deeper into pivot refiner’s customizations.
pivotModule('EmployeeAnalysis',
      ...
      refiner:'employeePivotRefiner')

bean('employeePivotRefiner',
  parent:'pivotRefiner',
  custom:[maxQueryLines:5000, maxPivotLines:1000, maxPivotColumns:80])
  • You can override the module view, for example to customize the actions maps for both tabs :
pivotModule('EmployeeAnalysis',
      ...
      moduleView:'EmployeeAnalysis.module.view')

tabs('EmployeeAnalysis.module.view') {
  actionMap {
    actionList ('PIVOT_QUERY', collapsable:true) {
      action ref:'queryPivotModuleFilterFrontAction'
      action ref:'queryPivotModuleAndReloadFilterFrontAction'
    }
    actionList ('EXTENSION') {
      action ref:'chooseQueryCriteriasFrontAction'
      action ref:'createPermalinkAndCopyToClipboardFrontAction'
    }
  }
  views {
    pivotTable (cellAction:'pivotCellSelectionToModuleListFrontAction')
    table (parent:'Employee.table') {
      actionMap {
        actionList ('EXPORT') {
          action ref:'exportPivotModuleResultToHtmlAction'
        }
      }
    }
  }
}
  • You can override the main action map for all pivot modules by replacing the pivotModuleActionMap action map definition :
actionMap ('pivotModuleActionMap') {
  actionList('PIVOT_QUERY', collapsable: true) {
    action ref:'queryPivotModuleFilterFrontAction'
    action ref:'queryPivotModuleAndReloadFilterFrontAction'
  }
  actionList ('EXTENSIONS') {
    action ref:'chooseQueryCriteriasFrontAction'
    action ref:'createPermalinkAndCopyToClipboardFrontAction'
  }
}
  • You can override the second tab action map for all pivot modules by replacing the pivotModuleTableActionMap action map definition :
actionMap('pivotModuleTableActionMap') {
  actionList ('ADD') {
    action ref:'checkAllLinesAction'
    action ref:'uncheckAllLinesAction'
    action ref:'addAsChildModuleFrontAction'
  }
  actionList ('EXPORT') {
    action ref:'exportPivotModuleResultToHtmlAction'
  }
}

3. Adding a “pivotTable” to any view

You can integrate a pivot table to any view. You will need to bind the pivot table with one model’s computed field.

The computed field should return a Json representation valid to serve as pivot table module.

The PivotBuilder class helps to generate this Json. Next section will deal about how to customize this behave of it.

Here is an example of pivot table view and model definition :

// the view
pivotTable (model:'Employee-pivot', 
    cellAction:'addEmployeesToModuleFrontAction') 

// the model
Entity('Employee',
    ...
    string 'employeesPivot', computed:true, id:'Employee-pivot)

Here is the java definiton for the pivot’s computed field :

public class EmployeeExtension extends AbstractComponentExtension<Employee> {
  ...
  public String getPivot() {
    
    // prepare the criteria
    HibernateBackendController controller = (HibernateBackendController) BackendControllerHolder.getCurrentBackendController();
    EnhancedDetachedCriteria criteria = EnhancedDetachedCriteria.forClass(Employee.class, "MAIN");
    criteria.add(Restrictions.isNotNull(Employee.HIRE_DATE));
     
    // create the pivot builder
    IComponentDescriptor<Project> projectDescriptor = (IComponentDescriptor<Employee>) controller.getEntityFactory().getComponentDescriptor(Employee.class);
    this.pivotBuilder = new PivotBuilder<>(projectDescriptor);
     
    // build pivot json
    String pivot = 
       pivotBuilder.simpleBuildJson(
          criteria,
          Arrays.asList('salary(10000, 20000, 50000'),
          Arrays.asList('managedOu.ouId'),
          Arrays.asList('ssn@count', 'salary@average/managedOu'));
    return pivot;
 }
}

4. Customizing pivot refiner behaviors

The Pivot refiner will help you to customize the pivot table, by :

  • Overriding labels for columns and lines.
  • Overriding styles like units, number of digits, colors, etc.
  • Overriding retrieval of data from database.
  • Managing full custom fields not even declared in component’s definition.

You will be able to use your customized pivot refiner for pivotModule as well as for pivotBuilder.

4.1 Setup a custom pivot refiner for a pivotModule

You’ve got to inject a pivotRefiner to your pivotModule (as explain is section 2/) to setup your own class implementation :

// the pivot module
pivotModule('EmployeeAnalysis',
      ...
      refiner:'employeePivotRefiner')
 
// the pivot refiner bean
bean('employeePivotRefiner',
  class:'org.jspresso.hrsample.backend.EmployeePivotRefiner',
  parent:'pivotRefiner')
 
// The pivot refiner java class
import org.jspresso.framework.ext.pivot.backend.DefaultPivotRefiner;
public class EmployeePivotRefiner extends DefaultPivotRefiner<Employee> {
  ...
}

NB : Your customized pivot refiner class should implements “org.jspresso.framework.ext.pivot.backend.IPivotRefiner” or rather extends “org.jspresso.framework.ext.pivot.backend.DefaultPivotRefiner” for most valuable behaviors.

4.2 Setup a custom pivot refiner for a pivotModule

You’ve got to pass an instance of your pivot refiner class to the pivot builder.

Here is again the example from section 3/ with an instance of the customized pivot refiner defined in section 4.1/ :

public class EmployeeExtension extends AbstractComponentExtension<Employee> {
  ...
  public String getPivot() {
    ...
    String pivot = 
       pivotBuilder.simpleBuildJson(
          criteria,
          Arrays.asList('salary(10000, 20000, 50000'),
          Arrays.asList('managedOu.ouId'),
          Arrays.asList('ssn@count', 'salary@average/managedOu'),
          new EmployeePivotRefiner()); 
    return pivot; 
  } 
}

 

4.3 Pivot refiner basic customization

Here is a list of simple things you can customize :

  • Setting pivot limits with methods “setMaxQueryLines(Integer maxQueryLines)“, “setMaxPivotLines(Integer maxPivotLines)” and “setMaxPivotColumns(Integer maxPivotColumns)
  • Overriding default styles using the method named “getDefaultStyle()
  • Overriding field’s names using the method named “getFieldLabel(String fieldName)
  • Overriding pivot view internationalization for measures labels (ex :count, sum, etc.) and field measure’s masks (ex: amout@average/managedUo) using  jspresso-ext-pivot library’s resource bundles org/jspresso/framework/ext/pivot/i18n/Messages_xx.properties

NB : Let’s have a look to the javadoc for more explanations !

 

4.4 Pivot refiner advanced customization

You can go deeper into pivot refiner customizing by overriding the IPivotRefinerField instance generated by the pivot refiner for each pivot field :

public class EmployeePivotRefiner extends ExtendedPivotRefiner<Employee> {
  @Override
  protected IPivotRefinerField<Employee, ?> createRefinerField(String field) {
    if ("salary".equals(field)) 
      return new SalaryField(this);
    if ("extra".equals(field)) 
      return new ExtraField(this); 
    return super.createRefinerField(field);
  }
}

The first field in example bellow manages field’s style customization and how to intercept and modify the field value :

private class SalaryField extends SimplePivotRefinerField<Employee, BigDecimal> {
  public SalaryField(IPivotRefiner<Employee> refiner) {
    super(refiner, Employee.SALARY);
  }
  @Override
  public Map<String, Object> getStyle(String measure, Map<String, Object> parentStyle) {
    Map<String, Object> style = super.getStyle(measure, parentStyle);
    style.put(IPivotStyles.STYLE_UNIT, "K€");
    return style;
  }
  @Override
  protected void setValue(Double value) {
    if (value != null)
      value = value.divide(BigDecimal.valueOf(1000));
    super.setValue(value);
  }
}

The second field in the example bellow deals with how to manage extra fields (i.e not backed to a component field) :

private class ExtraField extends ComputedPivotRefinerField<Employee, Double> {
  public ExtraField(IPivotRefiner<Employee> refiner) {
    super(refiner, "extra");
  }
  @Override
  public void evaluate(Object[] line) {
    int index = getIndexForField(getName());
    String extra = MyHelper.getFromCache(line[index]);
    line[index] = extra;
    super.evaluate(line);
  }
}

You can go event deeper in customization by :

  • Implementing your own pivot refiner by implementing “org.jspresso.framework.ext.pivot.backend.IPivotRefiner
  • Implementing your own pivot refiner fields by implementing “org.jspresso.framework.ext.pivot.backend.IPivotRefinerField
  • Overriding the default pivot refiner methods from “org.jspresso.framework.ext.pivot.backend.DefaultPivotRefiner
  • Accessing to CrossItems instance using method “DefaultPivotRefiner.getCrossItems()

NB : Let’s have a look to the javadoc for more explanations !

 

5. Using the pivot administration module

The pivot administration module makes possible to setup your pivot modules parameters at runtime.

The parameters are stored into the database.

What will “My Queries” help me to do ?

  • Overriding pivot limits :
    • The maximum number of lines retrieved from database,
    • The maximum number of lines and columns of the pivot.
      Pivot-admin-limits
    • TIP : The blue color means that the default hard-coded value was overriden.
  • Setting the available dimensions
    • You can directly edit the text field… then click the “Check” button to control that you didn’t make any property spelling error.
    • Or you can use the “Select and paste properties” action to open this popup :
      Pivot-admin-selectProperties
    • TIP : The blue color means that the default hard-coded value was overriden.
  • Setting the available measures
    • You can directly edit the text field… then click the “Check” button to control that you didn’t make any property spelling error, or a measure syntax error.
    • Or you can use the “Select and paste properties” action to open this popup… then you will have to complete manually the measure name (@count for example)
  • Overriding fields and measures labels and stylesPivot-admin-labelsAndStyles
    • You can override dimensions labels for each language.
    • You can override measures labels for each language. Know that measure translations are using dimensions labels by default, then most of time you should better override the dimension label instead of the measure’s one.
    • You can override the default measure style and for each measure. Use the “Add style” and “Remove style” button to do bulk updates.
      For example you can change the measure unit :
      Pivot-admin-labelsAndStyles-addStyle
    • You can also directly edit the style text fields… but take care not to do any spelling mistakes !
    • TIP : The blue color means that the default hard-coded value was overriden.

Adding calculated dimensions

Pivot-admin-calculatedFields

  • You can define calculated field by setting :
    • The field name (without any space or special character).
    • The field type : number or string.
    • The field label. You can translate it using the “Labels & styles” tab.
    • The field formula.
  • TIPS :
    • All fields used in the formula should be defined as Dimension in the “Dimensions & Measures” tab.
    • If you’re using multiple calculated fields, you should order then so that the bottom one could refer to above ones.

Adding pivot administration module to any workspace

Add the module named “pivot.admin.module” to any workspace, that’s it !

workspace('administration.workspace') {
 ...
 module ('pivot.admin.module',
   parent:'pivot.admin.module')
}

Maxime

 

Categories: User Interface

Leave a Reply