Installation
Requirements
APEX Requirements
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.
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 11
(Due to the outdated JavaScript engine, using the APEX SmartPivot plug-in in IE11 can show slower performance when rendering the data. It is strongly advised to use a more modern browser!) - 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.2.3
(october 2023)
- Optimized handling of bind variables in query
- fixed: JS error when opening grid in modal window
- fixed: UI freeze when swapping columns in chart view
- fixed: grid/chart syncronization issue when swapping columns
- fixed: chart to image/pdf conversion
(april 2023)
- Tested for APEX 23.1 compatibility
- fixed: URL loading error when using ORDS in standalone mode
(february 2023)
- Tested for APEX 22.2 compatibility
- fixed: Oracle DB version 19c compatibility (version 1.2.3.4).
Requires new licence key. Please contact info@apexsmartpivot.com.
- fixed: couldn't save Layout with (very) large number of columns
- fixed: grid layout resize after region resize/maximize
(june 2022)
- Tested for APEX 22.1 compatibility
- fixed: error when query contains a trailing ";"
- fixed: JS loading error with URL rewrite on server/proxy configured
(june 2021)
- Tested for APEX 21.1 compatibility
- Grid UI adapted to Universal Theme
- fixed: bug when using friendly URLs
Release 1.2.2
(december 2020)
- 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
Release 1.2.1
(may 2020)
- 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)
Release 1.2.0
(march 2020)
- 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
(january 2020)
- fixed refreshSmartPivot() bug
- added refreshSmartPivot() pageItems parameter to submit items into session state
(july 2019)
- fixed bug not showing icons in APEX version 19 and higher
(august 2018)
- fixed APEX 18.1 Font-APEX compatibility
(june 2018)
- fixed: number formatting (decimals)
- fixed: critical bug due to changed CSS behavior in Chrome v67
(march 2018)
- 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
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
Requires new licence key. Please contact info@apexsmartpivot.com.
- 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
Release 1.2.1
(may 2020)
- 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)
Release 1.2.0
(march 2020)
- 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
(january 2020)
- fixed refreshSmartPivot() bug
- added refreshSmartPivot() pageItems parameter to submit items into session state
(july 2019)
- fixed bug not showing icons in APEX version 19 and higher
(august 2018)
- fixed APEX 18.1 Font-APEX compatibility
(june 2018)
- fixed: number formatting (decimals)
- fixed: critical bug due to changed CSS behavior in Chrome v67
(march 2018)
- 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
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
- 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
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
- 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
- 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
- Create a new region of type SmartPivot [Plug-in].
- 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.
- 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:
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
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:
select l.application_id
,l.page_id
,l.apex_user
,l.layout_name
,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.
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([pageItems]);
Parameter: pageItems, optional. Identifies the page or column items that will be included in the request.
Example:
refreshSmartPivot("#P1_DEPTNO,#P1_EMPNO");
