APEX-AT-WORK by Tobias Arnhold

Balken Chart (Progress Bar) - Hidden Feature

Von Tobias Arnhold → 8.28.2012

Viele von euch kennen bestimmt die Balken-Grafik die Ihr innerhalb eines APEX Reports erstellen könnt:

Zu diesem Thema gibt es einige sehr gute Anleitungen:
EN:
http://www.inside-oracle-apex.com/more-undocumented-new-features-in-oracle-apex-31/#high_2
http://www.talkapex.com/2009/06/displaying-percentage-bar-in-apex.html
DE:
http://frankschmidt.blogspot.de/2009/06/prozentbalken-in-apex-reports.html

Neben der einfachen Verwendung in den Report Attributes, gibt es auch die Möglichkeit die Charts direkt mit einer APEX_UTIL Funktion zu generieren:
APEX_UTIL Documentation

APEX_UTIL.HTML_PCT_GRAPH_MASK (
    p_number         IN NUMBER    DEFAULT NULL,
    p_size           IN NUMBER    DEFAULT 100,
    p_background     IN VARCHAR2  DEFAULT NULL,
    p_bar_background IN VARCHAR2  DEFAULT NULL,
    p_format         IN VARCHAR2  DEFAULT NULL)
RETURN VARCHAR2;

In diesem Zusammenhang hat Peter Raganitsch die Generierung einer Balken-Grafik innerhalb eines APEX Items beschrieben:
DOAG Tipps & Tricks: So erstellen Sie mit APEX eine Balkengrafik-Formatmaske in einem Page-Item

Nun hat man oft die Notwendigkeit eine bedingte Anzeige (Beispiel: Ampel) abzubilden. Dies geht auch mit den Balken-Grafiken in APEX. Ich zeige dies mal anhand eines Report Beispiels:

select
 case when FINISHED_IN_PERCENT <= 30    
        then APEX_UTIL.HTML_PCT_GRAPH_MASK (FINISHED_IN_PERCENT,100,'cccccc','BF381A')
      when FINISHED_IN_PERCENT >= 31 and 
           FINISHED_IN_PERCENT <= 70
        then APEX_UTIL.HTML_PCT_GRAPH_MASK (FINISHED_IN_PERCENT,100,'cccccc','EFD13B') 
      when FINISHED_IN_PERCENT >= 71
        then APEX_UTIL.HTML_PCT_GRAPH_MASK (FINISHED_IN_PERCENT,100,'cccccc','7DC24B') 
 end as chart,
 ID
from ORDERS

Getting APEX views

Von Tobias Arnhold → 7.03.2012

There is a easy way getting all APEX views just with a simple select:

select apex_view_name, comments
from apex_dictionary
where column_id = 0
order by 1;

select LPAD (' ', (LEVEL - 1) * 2) ||  apex_view_name s, comments
from (
  select 'ROOT' as apex_view_name, null as comments, null as parent_view
  from dual
  UNION
  select apex_view_name, comments, nvl(parent_view,'ROOT') as parent_view
  from apex_dictionary
  where column_id = 0
) 
  connect by prior apex_view_name = parent_view
  start with parent_view is null
order SIBLINGS by apex_view_name DESC;

Current APEX views in version 4.2

APEX_VIEWS	COMMENTS
*ROOT
**APEX_WORKSPACES	Available Application Express (APEX) workspaces
***APEX_WS_APPLICATIONS
****APEX_WS_DATA_GRID	Websheet Data Grid definition
*****APEX_WS_DATA_GRID_COL	Report column definitions for Websheet Data Grid columns
****APEX_WS_APP_PAGES
***APEX_WORKSPACE_UI_TYPES	The UI Types identify the available user interface types of a workspace
***APEX_WORKSPACE_SQL_SCRIPTS	Identifies SQL Scripts used to execute SQL and PL/SQL commands
***APEX_WORKSPACE_SESSIONS	Application Express (APEX) sessions by workspace and APEX user
***APEX_WORKSPACE_SCHEMAS	Database Schemas mapped to APEX workspaces
***APEX_WORKSPACE_GROUP_USERS	Application Express (APEX) users and groups relationship
***APEX_WORKSPACE_GROUPS	Application Express (APEX) users and groups relationship
***APEX_WORKSPACE_FILES	Identifies uploaded files belonging to the workspace in the modplsql or EPG documents table
***APEX_WORKSPACE_DEVELOPERS	Application Express (APEX) developers, APEX users with privilege to develop applications
***APEX_WORKSPACE_CLICKS	Clicks in Application Express that are tracked by using APEX_UTIL.COUNT_CLICKS
***APEX_WORKSPACE_APEX_USERS	Application Express (APEX) users
***APEX_WORKSPACE_ACTIVITY_LOG	Page view activity log detail. One row is logged for each page view for application with logging enabled.
****APEX_WORKSPACE_LOG_SUMMARY_USR	Page view activity log summarized by user for the last two weeks
****APEX_WORKSPACE_LOG_SUMMARY	Page view activity log summarized by application for the last 14 days
****APEX_WORKSPACE_LOG_ARCHIVE	Page view activity is a daily summary of workspace acitivity that is retained until physically purged
***APEX_WORKSPACE_ACCESS_LOG	One row is logged for each login attempt.
***APEX_TEAM_TODOS	Items that need to get done - i.e. to dos.
***APEX_TEAM_MILESTONES	Identifies bugs, also known as software defects.
***APEX_TEAM_FEEDBACK	Identifies user feedback.
****APEX_TEAM_FEEDBACK_FOLLOWUP	Identifies user feedback followup.
***APEX_TEAM_FEATURES	Items that need to get done - i.e. to dos.
***APEX_TEAM_BUGS	Identifies bugs, also known as software defects.
***APEX_APPLICATIONS	Applications defined in the current workspace or database user.
****APEX_APPL_USER_INTERFACES
****APEX_APPL_PLUGIN_SETTINGS	Stores the values of custom attribute of scope "application" of a plug-in.
****APEX_APPL_PLUGINS	Stores the meta data for the plug-ins of an application.
*****APEX_APPL_PLUGIN_FILES	Stores the files like CSS, images, javascript files, ... of a plug-in.
*****APEX_APPL_PLUGIN_EVENTS	Stores which events can be triggered by this plug-in. This events are used for binding dynamic actions.
*****APEX_APPL_PLUGIN_ATTRIBUTES	Stores the meta data for the dynamic attributes of a plug-in.
******APEX_APPL_PLUGIN_ATTR_VALUES	Stores the possible values of a plug-in attribute if it's of type selectlist.
****APEX_APPL_LOAD_TABLE_RULES	Identifies a collection of transformation rules that are to be used on the load tables.
****APEX_APPL_LOAD_TABLE_LOOKUPS	Identifies a the collection of key lookups of the data loading tables
****APEX_APPL_LOAD_TABLES	Identifies a named collection of Application Data loding tables which are used to store the metadata information of the data loading tables
****APEX_APPLICATION_WEB_SERVICES	Web Services referenceable from this Application
****APEX_APPLICATION_TREES	Identifies a tree control which can be referenced and displayed by creating a region with a source of this tree
****APEX_APPLICATION_TRANS_REPOS	Repository of translation strings. These are populated from the translation seeding process.
****APEX_APPLICATION_TRANS_MAP	Application Groups defined per workspace. Applications can be associated with an application group.
****APEX_APPLICATION_TRANS_DYNAMIC	Application dynamic translations. These are created in the Translation section of Shared Components, and referenced at runtime via the function APEX_LANG.LANG.
****APEX_APPLICATION_TRANSLATIONS	Identifies message primary language text and translated text
****APEX_APPLICATION_THEMES	Identifies a named collection of Templates
*****APEX_APPL_THEME_DISPLAY_POINTS	The Theme Display Points identify the available display points in page and region templates
*****APEX_APPLICATION_THEME_STYLES	The Theme Style identifies the CSS file URLs which should be used for a theme
*****APEX_APPLICATION_TEMP_REPORT	Identifies the HTML template markup used to render a Report Headings and Rows
*****APEX_APPLICATION_TEMP_REGION	Identifies a regions HTML template display attributes
*****APEX_APPLICATION_TEMP_POPUPLOV	Identifies the HTML template markup and some functionality of all Popup List of Values controls for this application
*****APEX_APPLICATION_TEMP_PAGE	The Page Template which identifies the HTML used to organized and render a page content
*****APEX_APPLICATION_TEMP_LIST	Identifies HTML template markup used to render a List with List Elements
*****APEX_APPLICATION_TEMP_LABEL	Identifies a Page Item Label HTML template display attributes
*****APEX_APPLICATION_TEMP_CALENDAR	Identifies the HTML template markup used to display a Calendar
*****APEX_APPLICATION_TEMP_BUTTON	Identifies the HTML template markup used to display a Button
*****APEX_APPLICATION_TEMP_BC	Identifies the HTML template markup used to render a Breadcrumb
*****APEX_APPLICATION_TEMPLATES	Identifies reference counts for templates of all types
****APEX_APPLICATION_TABS	Identifies a set of tabs collected into tab sets which are associated with a Standard Tab Entry
****APEX_APPLICATION_SUPP_OBJECTS	Identifies the Supporting Object installation messages
*****APEX_APPLICATION_SUPP_OBJ_SCR	Identifies the Supporting Object installation SQL Scripts
*****APEX_APPLICATION_SUPP_OBJ_CHCK	Identifies the Supporting Object pre-installation checks to ensure the database is compatible with the objects to be installed
*****APEX_APPLICATION_SUPP_OBJ_BOPT	Identifies the Application Build Options that will be exposed to the Supporting Object installation
****APEX_APPLICATION_SUBSTITUTIONS	Application level definitions of substitution strings.
****APEX_APPLICATION_SHORTCUTS	Identifies Application Shortcuts which can be referenced "MY_SHORTCUT" syntax
****APEX_APPLICATION_PROCESSES	Identifies Application Processes which can run for every page, on login or upon demand
****APEX_APPLICATION_PARENT_TABS	Identifies a collection of tabs called a Tab Set. Each tab is part of a tab set and can be current for one or more pages. Each tab can also have a corresponding Parent Tab if two levels of Tabs are defined.
****APEX_APPLICATION_PAGES	A Page definition is the basic building block of page. Page components including regions, items, buttons, computations, branches, validations, and processes further define the definition of a page.
*****APEX_APPLICATION_PAGE_VAL	Identifies Validations associated with an Application Page
*****APEX_APPLICATION_PAGE_REGIONS	Identifies a content container associated with a Page and displayed within a position defined by the Page Template
******APEX_APPLICATION_PAGE_TREES	Identifies a tree control which can be referenced and displayed by creating a region with a source of this tree
******APEX_APPLICATION_PAGE_RPT	Printing attributes for regions that are reports
*******APEX_APPLICATION_PAGE_RPT_COLS	Report column definitions used for report regions
******APEX_APPLICATION_PAGE_ITEMS	Identifies Page Items which are used to render HTML form content. Items automatically maintain session state which can be accessed using bind variables or substitution stings.
******APEX_APPLICATION_PAGE_FLASH_CH	Identifies a Flash chart associated with a Page and Region
*******APEX_APPLICATION_PAGE_FLASH_S	Identifies the Flash chart series which comprise a Flash chart
******APEX_APPLICATION_PAGE_FLASH5	Identifies a Flash chart 5 chart associated with a Page and Region
*******APEX_APPLICATION_PAGE_FLASH5_S	Identifies the Flash chart series which comprise a Flash chart
******APEX_APPLICATION_PAGE_BUTTONS	Identifies buttons associated with a Page and Region
*****APEX_APPLICATION_PAGE_PROC	Identifies SQL or PL/SQL processing associated with a page
*****APEX_APPLICATION_PAGE_MAP	Identifies the full breadcrumb path for each page with a breadcrumb entry
*****APEX_APPLICATION_PAGE_GROUPS	Identifies page groups
*****APEX_APPLICATION_PAGE_DB_ITEMS	Identifies Page Items which are associated with Database Table Columns. This view represents a subset of the items in the APEX_APPLICATION_PAGE_ITEMS view.
*****APEX_APPLICATION_PAGE_DA	Identifies Dynamic Actions associated with a Page
******APEX_APPLICATION_PAGE_DA_ACTS	Identifies the Actions of a Dynamic Action associated with a Page
*****APEX_APPLICATION_PAGE_COMP	Identifies the computation of Item Session State
*****APEX_APPLICATION_PAGE_BRANCHES	Identifies branch processing associated with a page. A branch is a directive to navigate to a page or URL which is run at the conclusion of page accept processing.
****APEX_APPLICATION_NAV_BAR	Identifies navigation bar entries displayed on pages that use a Page Template that include a #NAVIGATION_BAR# substitution string
****APEX_APPLICATION_LOVS	Identifies a shared list of values that can be referenced by a Page Item or Report Column
*****APEX_APPLICATION_LOV_ENTRIES	Identifies the List of Values Entries which comprise a shared List of Values
****APEX_APPLICATION_LISTS	Identifies a named collection of Application List Entries which can be included on any page using a region of type List. Display attributes are controlled using a List Template.
*****APEX_APPLICATION_LIST_ENTRIES	Identifies the List Entries which define a List. List Entries can be hierarchical or flat.
****APEX_APPLICATION_ITEMS	Identifies Application Items used to maintain session state that are not associated with a page
****APEX_APPLICATION_GROUPS	Application Groups defined per workspace. Applications can be associated with an application group.
****APEX_APPLICATION_COMPUTATIONS	Identifies Application Computations which can run for every page or on login
****APEX_APPLICATION_CACHING	Applications defined in the current workspace or database user.
****APEX_APPLICATION_BUILD_OPTIONS	Identifies Build Options available to an application
****APEX_APPLICATION_BREADCRUMBS	Identifies the definition of a collection of Breadcrumb Entries which are used to identify a page Hierarchy
****APEX_APPLICATION_BC_ENTRIES	Identifies Breadcrumb Entries which map to a Page and identify a pages parent
****APEX_APPLICATION_AUTHORIZATION	Identifies Authorization Schemes which can be applied at the application, page or component level
****APEX_APPLICATION_AUTH	Identifies the available Authentication Schemes defined for an Application
****APEX_APPLICATION_ALL_AUTH	All authorization schemes for all components by Application
**APEX_UI_DEFAULTS_TABLES	The User Interface Defaults for the tables within this schema. Used by the wizards when generating applications.
**APEX_UI_DEFAULTS_LOV_DATA	If you create a form, report, or tabular form that includes this column and if the appropriate Display As Type is set to use a list of values (Radio Group or Select List) then a Named List of Values will be created within the application and will be referenced by the resulting item or report column.
**APEX_UI_DEFAULTS_GROUPS	The User Interface Defaults for the groups within the tables in this schema. Used by the wizards when generating applications.
**APEX_UI_DEFAULTS_COLUMNS	The User Interface Defaults for the columns within this schema.
**APEX_UI_DEFAULTS_ATTR_DICT	The Attribute Dictionary is specific to a workspace. It is part of User Interface Defaults and can be used in report and form creation.
**APEX_THEMES	List of APEX built-in, public and workspace themes

Browsing "Older Posts"

Balken Chart (Progress Bar) - Hidden Feature

Open Source HTML/JS Charting Solutions

APEX Tabular Forms mit Before Insert Trigger

Neue Version des SQL Developers (3.2)

IR Bug after import of an application with new id

Getting APEX views

First release of the PL-jrxml2pdf - Generate iReport PDFs with PL/SQL only