Tuesday, 13 June 2017

Grouping Columns in Oracle APEX Interactive Grid

Groups are used to associate columns together in the grid and Single Row View. Groups are added by expanding the Attributes node within the Rendering tree, and right-clicking on Column Groups. Let’s try this feature by executing the following steps:



1.   Create a new page by clicking the Create Page button. Select the Report option in the first wizard screen, followed by the Interactive Grid option on the next screen.

2.    Set Page Number to 100, Page Name to Column Groups, Page Mode to Normal, Breadcrumb to Breadcrumb, Parent Entry to No Parent Entry, Entry Name to Column Groups, and click Next.

3.  Set Navigation Preference to Do not associate this page with a navigation menu entry, because this page is not associated with our sales application. Click Next.

4.     On the Report Source screen, set Editing Enabled to No, set Source Type to SQL Query, and enter the following SQL Statement in Enter a SQL SELECT Statement text area. Read this post to add the table used in the following query.

SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno,onleave,notes,
flex4 as tags
FROM EBA_DEMO_IG_EMP

5.      Click the Create button to finish the page creation process.

6.  In the Page Designer, under the Column Groups region (in the Rendering tree), right-click the Attributes folder, and select Create Column Group from the context menu. In the Properties pane, set the Heading attribute for this new group to Identity.

7.   Repeat step 6  to create two more groups. Enter Compensation and Notes for their headings.

8.   Under the Columns Group region, expand the Columns folder. Click the EMPNO column and set its Type to Hidden.

9.      Set the appropriate column headings, as shown in Figure 5-5.

10. Use the following table to associate each column with a group defined in steps 6 & 7. To establish this association, click any column (ENAME, for example), scroll down to the Layout section, and set the Group property as follows:
Column
Group Property
ENAME
Identity
JOB
Identity
MGR
Identity
HIREDATE
Identity
SAL
Compensation
COMM
Compensation
DEPTNO
Identity
ONLEAVE
Notes
NOTES
Notes


11. Save your work and run the page. Column group headings can be used to reorder columns just like column headings. Play around with column reordering (using drag and drop) to see how the group headings are split and joined.



The instruction provided in this post are extracted from the book Oracle Application Express 5.1 Basics & Beyond





Monday, 12 June 2017

Interactive Grid Features In Oracle APEX 5.1

Oracle APEX 5.1 comes with a new feature called Interactive Grid to display data in row/column matrix. In appearance, it looks similar to an Interactive Report (used in the next chapter) and delivers all features of an Interactive Report, but it also allows you to manipulate data simply by clicking on a cell and editing its value, which is not available in Interactive Reports.  In many ways this grid looks and acts like an Interactive Report. Here are some new features and differences:

  • Rows are fixed height and columns have a specific width that can be adjusted by dragging the border between column headers (G) or with Ctrl+Left/Right keys when the column header has keyboard focus.
  • Columns can be reordered with drag and drop (dragging the handle (E) at the start of a column heading) or with Shift+Left/Right keys when the column header has keyboard focus.
  • Columns can be sorted using the buttons (F) in the column heading or by using Alt+Up/Down key combination. Use the Shift key to add additional sort columns.
  • Columns can be frozen using the Freeze button  (D) in the column heading pop-up menu. For example, to freeze the customers’ name column (on Page 2), click the Name column heading. A pop-up menu will appear with four options: Hide (A), Control Break (B), Aggregate (C), and Freeze (D). Select Freeze. Drag the border between the Name and Address columns (F) toward right to expand the Name column.
  • By default the toolbar and column headings stick to the top of the page and the footer sticks to the bottom when scrolling.
  • By default pagination uses a "Load More" button.
  • The grid is keyboard navigable with a focused cell and current selected row (single selection by default).
  • The toolbar includes a Reset button by default, which restores all the report settings to their defaults.


To explore the features of the new Interactive Grid, you need a couple of tables that come with a sample application. Execute the following steps to install the sample application and consequently the required tables.


  1. Select Sample Apps from the Packaged Apps menu appearing at the top of your browser.
  2. Click the icon representing Sample Interactive Grids application.
  3. On the Packaged App Details page, click the Install Packaged App button.
  4. On the Install Packaged App wizard screen, accept the default Authentication scheme (Application Express Accounts) and click Next.
  5. On the next wizard screen, click the Install Packaged App button. After a while, you will see the message Application installed.
  6. Click the Object Browser option in the SQL Workshop main menu and see the two required tables (EBA_DEMO_IG_EMP and EBA_DEMO_IG_PEOPLE) in the left pane under the Tables category.




The instruction provided in this post are extracted from the book Oracle Application Express 5.1 Basics & Beyond









Thursday, 8 June 2017

Using Master Detail And Detail And Detail in Oracle APEX

Interactive Grid makes it effortless to create master-detail relationships and go any number of levels deep and across. You can create all types of master-detail-detail screens with ease. In this post I’ll demonstrate this new feature.


1.   From the SQL Workshop menu, select SQL Scripts and click the Upload button. In the Upload Script screen, click the Choose File button. In the Open dialog box, select master_detail_detail.sql file from Chapter 5 folder in the book’s source code and click Open. Enter Master Detail Detail in the Script Name box and click the Upload button. In the SQL Scripts interface click the Run button appearing in the last column. On the Run Script screen, click the Run Now button. The script will execute to create four tables (MD_continent, MD_country, MD_city, and MD_population) along with relevant data to demonstrate the new master detail detail feature. You can view these tables from the SQL Workshop > Object Browser interface.

2.    Create a new page by clicking the Create Page button in the App Builder interface. This time, select the first Blank Page option and click Next. Set Page Number to 112, Name to Master Detail Detail, Page Mode to Normal, and click Next. On the Navigation Menu screen, select the first Navigation Preference to not associate this page with any sales app navigation menu entry. On the final wizard screen, click Finish.

3.     In the Page Designer, right-click the Regions node on the Rendering tab and select Create Region. Set the following properties for the new region. This region will display data from the MD_continent table.
Property
Value
Title
Continents
Type
Interactive Grid
SQL Query
select * from MD_continent
After entering the SQL query, click anywhere outside the query box. Expand the Columns folder under this region. Click the CONTINENT_ID column. Set its Type to Hidden and set Primary Key (under Source) to Yes. You must define a primary key column for an interactive grid region, which is required to establish a master detail relationship.

4.      Create another region under the Continents region by right-clicking the main Regions node. This region will act as the detail for the Continents region. At run-time when you select a continent, this region will display a list of countries in the selected continent. Set the following properties for this new region.
Property
Value
Title
Countries
Type
Interactive Grid
SQL Query
select * from MD_country
Expand the Columns folder under the Countries region. Click the COUNTRY_ID column. Set its Type to Hidden and set Primary Key (under Source) to Yes. You set the Primary Key property to Yes, because this region will act as a master for the Cities region created in the next step. Now, associate this detail region to its master (Continents). Click the Countries region  and set the Master Region property (under Master Detail) to Continents. This should be set when this region is the detail region in a master-detail relationship with another region on the page. For the master-detail relationship to work correctly, you must also select the column(s) in the detail region, which are foreign keys to the master region, by setting the Master Column property. Click the CONTINENT_ID column (a foreign key) in the Countries region. Set its Type property to Hidden and Master Column (under Master Detail) to CONTINENT_ID, which references the same column in the master region.

5.      Create another region and place it under the Countries region. This region will show a list of cities when you select a country from its master region. Set the following properties for this region:
Property
Value
Title
Cities
Type
Interactive Grid
SQL Query
select * from MD_city
Master Region
Countries
Expand the Columns folder under the Cities region. Click the CITY_ID column. Set its Type to Hidden and set Primary Key (under Source) to Yes. Click the COUNTRY_ID column in this region. Set the Type of this column to Hidden and Master Column to COUNTRY_ID to point to the same column in the Countries region.

6.      Create the last region to display population of a city.
Property
Value
Title
Population
Type
Interactive Grid
SQL Query
select * from MD_population
Master Region
Cities
Expand the Columns folder under the Population region. Click the POPULATION_ID column. Set its Type to Hidden. Since this is the last region, you do not need to specify this column as a primary key. However, you have to set a couple of properties for the CITY_ID column in this region to associate it with its master. Click the CITY_ID column, set its Type property to Hidden and Master Column to CITY_ID. That’s it!

Run the page and click the row representing Europe (A) in the first region. As you click this row, the second region will display countries in the Europe continent. Click Germany (B) in the second region. This will refresh the third region with a list of cities in Germany. Click the Berlin city (C) to see its population (D) in the forth region.




The instruction provided in this post are extracted from the book Oracle Application Express 5.1 Basics & Beyond






Creating Charts In Oracle APEX Using Oracle JET Charts

Presenting data in Oracle APEX, either graphically or in text format, is as easy as creating the input forms. The built-in wizards make it so easy to create flexible and powerful charts in Oracle Application Express. In this post you will learn how to create a bar chart using demo_customers, demo_orders, demo_order_items, and demo_product_info tables that come with Oracle APEX.

The chart being created in this post is based on Oracle JET Charts to reveal the amount of orders placed by each customer by product categories. Oracle JET Charts is a new feature incorporated in Oracle APEX 5.1. Charts in Oracle APEX have been completely revamped. Now Oracle APEX has integrated charting based on Oracle JavaScript Extension Toolkit (JET) charting library. Oracle JET Charts is a component of the Oracle JavaScript Extension Toolkit (JET), an open source toolkit based on modern JavaScript, CSS3, and HTML5 design and development principles. These charts are fully HTML5 capable and work on any modern browser regardless of platform, screen size, or features.

Each bar in the chart has multiple slices representing amounts of different orders. When you move your mouse over these slices a tooltip (A) displays the corresponding amount. The chart will be created with drill-down functionality. That is, when you click a bar, you'll be taken to Page 7 where you will see profile of the selected customer. You will also make provision to change the chart’s orientation (B) and will provide options to present it as either stacked or un-stacked (C).    



1.      Create a Blank Page and set the following properties for it:
Property
Value
Page Number
17
Name
Customer Orders
Page Mode
Normal
Breadcrumb
don’t use breadcrumb on page
Navigation Preference
Identify an existing navigation menu entry for this page
Existing Navigation Menu Entry
Reports

2.      Right-click the Content Body folder and select Create Region. Set the following properties for the new region. Immediately after switching the region’s Type, a new folder named Series along with a child node (New) is added under the region. Each product in this app is associated with one of the three categories: Accessories, Men, and Women. The query below fetches summarized order figures by customers for each category.
Property
Value
Title
Customer Orders
Type
Chart
SQL Query
select c.customer_id,  c.cust_last_name||', '||c.cust_first_name Customer_Name,
            sum (decode(p.category,'Accessories',oi.quantity * oi.unit_price,0)) "Accessories",
            sum (decode(p.category,'Mens',oi.quantity * oi.unit_price,0)) "Men",
            sum (decode(p.category,'Womens',oi.quantity * oi.unit_price,0)) "Women"
from demo_customers c, demo_orders o, demo_order_items oi, demo_product_info p
where c.customer_id = o.customer_id and o.order_id = oi.order_id and
             oi.product_id = p.product_id
group by c.customer_id, c.cust_last_name, c.cust_first_name
order by c.cust_last_name

3.      Click the Attributes folder under the Customer Orders chart region and set the following properties. The Stack property specifies whether the data items are stacked. We defined Automatic animation setting for the chart, which applies the Oracle JET’s default animation settings. It specifies whether animation is shown when data is changed on the chart. A data change can occur if the chart gets automatically refreshed. In the current scenario, the animation takes place when you click one of the four buttons (B & C): Horizontal, Vertical, Stack, or Unstack. These buttons will be created in subsequent steps. The Hide and Show Behavior is performed when you click a legend item (G). For example, deselecting a legend item will hide its associated data series on the chart. With the value set to Rescale for this property, the chart rescales as you select or de-select a legend. This is useful for series with largely varying values.
Property
Value
Type
Bar
Title
Leave it blank
Orientation
Vertical
Stack
Yes
Maximum Width
800
Height
500
On Data Change (under Animation)
Automatic
Position (under Legend)
Top
Hide and Show Behavior
Rescale

4.      Click the New node (under Series) and set the following properties. Each series you create for your chart appears in a unique color to represent product category and displays sales figures for each category (using the Value property) that is derived from the SELECT statement specified in step 2. You set Source Type (on row 2) to Region Source, which specifies that the data of this series is to be extracted from the SQL query defined for the Customer Orders region (in step 2). In the Label attribute you select a column name that is used for defining the label(s) of the x-axis (D) on the chart, while the Accessories  column selected for the Value property is used for defining the ordered value (E) on this chart. When you click a chart bar (representing Accessories), you’re drilled down to Page 7 to browse customer details.
Property
Value
Name
Accessories
Type (under Source)
Region Source
Label (under Column Mapping)
CUSTOMER_NAME
Value
Accessories
Type (under Link)
Redirect to Page in this Application
Target
Type = Page in this Application
Page = 7
Name = P7_CUSTOMER_ID
Value = &CUSTOMER_ID.
Clear Cache = 7

5.    Right-click the Series folder and select Create Series from the context menu to add another series. Set the following properties for the new series. Use the same values as defined for the Target property in Step 4 to transform this series into a link to access Page 7.
Property
Value
Name
Men
Type (under Source)
Region Source
Label (under Column Mapping)
CUSTOMER_NAME
Value
Men

6.    Create one more series and set the following properties. Create a link as you did for the previous two steps.
Property
Value
Name
Women
Type (under Source)
Region Source
Label (under Column Mapping)
CUSTOMER_NAME
Value
Women

7.      Click the x-axis node (under Axes) and enter Customers for the Title attribute. The title will appear at the bottom of the chart (F).

8.      Click the y-axis node and set the following properties. When you format a number as currency, the Currency property is required to be set to specify the currency that will be used when formatting the number. You enter a currency that is used when formatting the value on the chart. The value should be a ISO 4217 alphabetic currency code. If the format type is set to Currency, it is required that the Currency property also be specified. Visit http://www.xe.com/iso4217.php to see a list of standard currency codes.


Property
Value
Title
Order Total
Format Type
Currency
Decimal Places
0
Currency
USD

9.      In this step, you will add two buttons (B) to the Customer Orders region. When clicked, these buttons will change the chart’s orientation using the default animation set in step 3. Right-click the Customer Orders region and select Create Button from the context menu. A new folder named Region Buttons will be added with a button labeled New. Set the following properties for this button. The Action attribute says that this button is associated with a dynamic action (step 10), which fires when the button is clicked.
Property
Value
Button Name
Horizontal
Label
Horizontal
Button Position
Previous
Button Template
Icon
Icon CSS Classes
fa-bars
Action
Defined by Dynamic Action

Right-click Region Button and select Create Button to add a new button. Set the following properties for the new button.
Property
Value
Button Name
Vertical
Label
Vertical
Button Position
Previous
Button Template
Icon
Icon CSS Classes
fa-bar-chart
Action
Defined by Dynamic Action
                          
10.  Now add two dynamic actions for the two buttons. Click the Dynamic Actions tab, right-click the Click folder, and select Create Dynamic Action. Click the New node and set the following properties. This dynamic action is named Horizontal Orientation – you are free to give it any other name you deem suitable. The next three properties specify that this dynamic action should trigger when the Horizontal button is clicked.
Property
Value
Name
Horizontal Orientation
Event
Click
Selection Type
Button
Button
Horizontal
Click the Show node under the True folder to set the following properties. When the Horizontal button is clicked, the JavaScript code (defined on row 2) is fired. In this code, dualChart is a static ID you will set in step 12 for the Customer Orders region. You control chart’s orientation through the ojChart class, which has two options (Horizontal and Vertical), where Vertical is the default option.  In this step, you inform the Oracle APEX engine to display the chart horizontally when the Horizontal button is clicked. Note that the chart orientation only applies to bar, line, area, combo, and funnel charts.
Property
Value
Action
Execute JavaScript Code
Code
$("#dualChart_jet").ojChart({orientation: 'horizontal'});
Selection Type
Region
Region
Customer Orders
Event
Horizontal Orientation
Fire on Initialization
No

11.  Right-click the Click folder and select Create Dynamic Action to add one more for vertical orientation, as follows. Click the New node and set the following properties:
Property
Value
Name
Vertical Orientation
Event
Click
Selection Type
Button
Button
Vertical
Click the Show node under the True folder and set the following properties:
Property
Value
Action
Execute JavaScript Code
Code
$("#dualChart_jet").ojChart({orientation: 'vertical'});
Selection Type
Region
Region
Customer Orders
Event
Vertical Orientation
Fire on Initialization
No

12.  If you run the pages at this stage, you will not see the orientation effect if you click any of the two buttons. This is because of the static ID (dualChart), which is mentioned in the JavaScript code to reference the Customer Orders region but has not been assigned to the region itself. Switch back to the Rendering tab, click the Customer Orders region, and in the Advanced section enter dualChart as the value for the Static ID property. Now the region can be recognized by this static ID.


13.  Add two more buttons to Region Buttons. These buttons will be used to render the series data as stacked or unstacked (C). Set the following properties for the two buttons:
Property
Value (Button1)
Value (Button2)
Button Name
Stack
Unstack
Label
Stack
Unstack
Button Position
Next
Next

14.  Create two dynamic actions for the two buttons as follows. Set the New nodes’ properties as defined in the first table below:
Property
Value (New node)
Value (New node)
Name
Stack Chart
Unstack Chart
Event
Click
Click
Selection Type
Button
Button
Button
Stack
Unstack
The Stack Chart dynamic event’s Show node properties:
Property
Value
Action
Execute JavaScript Code
Code
$("#dualChart_jet").ojChart({stack: 'on'});
Selection Type
Region
Region
Customer Orders
Event
Stack Chart
Fire on Initialization
No
The Unstack Chart dynamic event’s Show node properties:
Property
Value
Action
Execute JavaScript Code
Code
$("#dualChart_jet").ojChart({stack: 'off'});
Selection Type
Region
Region
Customer Orders
Event
Unstack Chart
Fire on Initialization
No

Save your work. Run the application and click Reports in the navigation menu. You will see the Reports page created in section 8.2. Click the first Customer Orders card to access Page 17. You will see a chart, as shown in Figure 8-2. Move your cursor over the chart bars and different portions within a particular bar. You will see a tooltip (A) showing order amount of the corresponding customer. Click the Vertical and Horizontal buttons (B) to change the chart’s orientation. Similarly, click the Stack and Unstack buttons (C) to see respective animated effects.



The instruction provided in this post are extracted from the book Oracle Application Express 5.1 Basics & Beyond