In order to install and use the APEX SmartPivot Plugin, you need to have Oracle APEX version 5.1 or higher installed.
(APEX 5.0 is supported up to version 1.2.11 of the SmartPivot Plugin. If you want to use the APEX SmartPivot Plugin with APEX 4.2, you will need to install version 1.0. This version is still supported but new features will not be available)
APEX 5 Static File support
APEX 5 and higher 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.
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 11
- Microsoft Edge
- Mozilla Firefox
- Apple Safari
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.
With this installation package there is a plug-in installation file named:
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.
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 firstname.lastname@example.org.
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):
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).
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.
- Tested for APEX 20.2 compatibility
- New functionality: MIN/MAX function can be applied on date fields
- New: Sort on date fields used as column/row heading sorted correctly
- New: Number format is derived from application NLS default, optional: possible to overwrite NLS setting and store with report layouts
- fixed: bugs in Excel export regarding format and filename
- fixed: UNICODE characters caused error on loading data
- New functionality: possibility to load a layout (by name) from a Page Item. This allows linking to a specific layout rather than opening the default layout
- New functionality: layouts now stay persistent on page revisit
- fixed: several issues with IE11 compatibility (load/save layout)
- fixed critical bug: NULL values in numeric data forced column to Attribute type instead of Measure which didn't allow aggregation functions other then count
- fixed: special characters in layout meta data (username, layout name, etc) broke loading saved layouts
- fixed refreshSmartPivot() bug
- added refreshSmartPivot() pageItems parameter to submit items into session state
- fixed bug not showing icons in APEX version 19 and higher
- fixed APEX 18.1 Font-APEX compatibility
- fixed: number formatting (decimals)
- fixed: critical bug due to changed CSS behavior in Chrome v67
- fixed: solved jQuery version conflicts
- new: added Distinct Count to aggregation functions
- fixed: dramatic performance increase for large datasets
- new: save set filters with layouts
- new: save set sort order with layouts
- new: additional save option: open layout with all columns/rows collapsed initially
- 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
- fixed: FLOAT and DOUBLE types to number column detection (220.127.116.11)
- fixed: special characters in data (18.104.22.168)
- enhanced: tuning performance for (large) data sets (22.214.171.124)
- fixed: special/extended ascii characters in data (126.96.36.199)
- fixed: maximum index length error (188.8.131.52)
- fixed: language initialization caused 404 errors
- fixed: decimal separator caused loss of data/columns
- Create a new region of type SmartPivot [Plug-in].
- Tip: in order to provide meaningful column names use the
[database column name] as "[column alias]" syntax.
- 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:
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
Load Layout from Page Item
To enable linking to a specific saved layout (other than the default layout) of a page, you can fetch the layout name from an APEX Page Item. The item must contain the name prefixed
by the user sharing the layout. The sharing user can be found in the load/save dialog or by querying the SmartPivot layout table. Here an example query to generate possible link URLs
for existing saved and shared layouts for page 1 in application 100, passed to item P1_INITIAL_LAYOUT as parameter:
,l.apex_user || '.' || l.layout_name item_
,apex_util.prepare_url (p_url => 'f?p=' || :app_id || ':1:' || :app_session || ':1:NO::P1_INITIAL_LAYOUT:' || l.apex_user || '.' || l.layout_name) url
from smart_pivot_layouts l
where application_id = 100 and page_id = 1 and is_shared = 'Y'
order by apex_user, layout_name, page_id;
The layout name has to be specified as "sharing user"."layout name". The "layout name" part of the value is case-sensitive.
If the specified laout name is not found, the default layout will be shown.
Parameter: pageItems, optional. Identifies the page or column items that will be included in the request.