SmartPivot - Developer Guide

Installation

Requirements

APEX Requirements

In order to install and use the APEX SmartPivot Plugin, you need to have Oracle APEX version 4.2 or version 5 installed.

APEX 5 Static File support

APEX 5 needs to be configured with Static File Support in order to be able to use the SmartPivot Plugin in an application. Refer to the Oracle Application Express Installation Guide on how to install and configure Static File Support:
Configuring Static File Support
Alternatively you can install the APEX SmartPivot Plugin static files on your web server. Refer to the section Server Files Support in this document.

Browser Requirements

The APEX SmartPivot Plugin relies on standard HTML, CSS and JavaScript and works in all major browser software. It is recommended that you use the latest web browser software available for the best experience. Tested browsers/versions are:

  • Google Chrome
  • Microsoft Internet Explorer 9+
  • Microsoft Edge
  • Mozilla Firefox
  • Apple Safari

Installation

Database Objects

Connect to the database schema that is the parsing schema for your application, usually the same schema your workspace is associated with. Execute the sql-script smart_pivot_db_install.sql in this schema. This script will create one table (SMART_PIVOT_LAYOUTS) and one package (SMART_PIVOT). These objects are mandatory for the plug-in to function.

Plugin Import

With this installation package there is a plug-in installation file named:

smartpivot_plugin.sql

Navigate to “Shared Components > Plug-ins” and click Import >. From there you can follow the menu to upload and install the plug-in using the file above.

Configuration

In order to be able to use the plug-in functionality, you need to enter a valid (demo-) license key and the related User Information provided. If you do not have a license key yet, please contact info@apexsmartpivot.com.

Component Setting

The APEX SmartPivot plug-in has a number of settings that have to be configured for each application using Component Setting (Application nnn > Shared Components > Component Settings > Edit):

License

Enter the license-key string and company name in the first two fields, exactly as you received in your email.

Plug-in/Server Files Support

The APEX SmartPivot plug-in support two options for accessing the necessary asset and library files that will be loaded when using the plug-in in a page:

Plugin Files [default]
The plug-in loads all files from the database. The files have installed during plug-in installation. When using the plug-in in APEX 5, Static File support with DRDS needs to be installed and configured correctly.
Server Files
When choosing this option, the static resources of the plug-in have to be copied to the web server. You can find this file in the zip archive smartpivot-server_v[version number].zip. The download link will be provided when purchasing the plug-in. In the additional field "Server File Path", enter the virtual path to access the plug-in files on the server (ending with forward-slash).

Upgrade Plugin

To upgrade to a newer version of the APEX SmartPivot Plugin, just follow the installation steps. You always have to install both, Database Objects and Plugin Import, of the new version.

Release Notes

Release 1.1.0

(october 2017)
  • new: Conditional Formatting, based on cell (data, subtotals or totals) you can now conditionally
    • define multiple conditional rules
    • change textcolor and backgroundcolor
    • translate (a range of) values into text
    • translation supports HTML & FontAwsome/Font APEX (dep. on UT version)
    • rules are saved with the report layout
  • new: Font APEX support of UI (APEX 5.1; APEX 5.0: Font AWesome support)
  • new: Plug-in region column definition support
  • fixed/enhanced: auto-sizing of grid region
  • fixed/enhanced: Excel Export function
  • requirements: Version 1.1.0 requires APEX version 5.0 or higher

Release 1.0.5

  • added partial page refresh JavaScript API call: refreshSmartPivot()
  • fixed: FLOAT and DOUBLE types to number column detection (1.0.5.1)
  • fixed: special characters in data (1.0.5.2)
  • enhanced: tuning performance for (large) data sets (1.0.5.3)
  • fixed: special/extended ascii characters in data (1.0.5.4)
  • fixed: maximum index length error (1.0.5.5)

Release 1.0.4

  • fixed: language initialization caused 404 errors
  • fixed: decimal separator caused loss of data/columns

Usage

  1. Create a new region of type SmartPivot [Plug-in].
  2. Enter a SQL Query. This query can contain bind variables, just as you would use in a standard APEX report. When using the partial page refresh JavaScript API call, make sure these bind varialbles are configured in the field Page Items to Submit.
  3. Tip: in order to provide meaningful column names use the
    [database column name] as "[column alias]" syntax.
  4. To provide users with a default layout, developers can save a SmartPivot report layout as default, for all users, just like you can do for Interactive reports:

Features

Limiting Number of Rows to Fetch

Because all the data to calculate the aggregated pivot result has to be downloaded to the user's browser to enable filtering and sorting features of the SmartPivot plug-in, datasets could be very large. To limit the amount of data, you as developer can apply different measures:

  • Limit the query result by using page item bind variable in your query.
  • Specify a maximum number of rows to be fetched in the SmartPivot plug-ins attribute settings.
  • Reference a page item in the SmartPivot plug-ins attribute settings, that specifies the maximum number of rows to be fetched, but still could be changed by the end-user

JavaScript API for Partial Page Refresh

The data in the APEX SmartPivot grid can be refreshed, without having to refresh the entire page or the component itself. To do so you can use JavaScript, for example in a dynamic action, calling the function:

refreshSmartPivot();