Display Data Dynamically In A Gauge Chart

In this tutorial, we will learn how to display customer's ordered data in a gauge chart dynamically. As you choose a customer name from the select list the gauge pointer moves to the value which is equivalent to the customer's ordered amount.

For this tutorial, we will be using the DEMO_CUSTOMERS table that we have been using throughout our tutorials. To create this table, you can follow this LINK.

let's start the proceedings. First of all, you will need to upload three JavaScript files along with one CSS file. To get these files, follow this LINK to get the Source Code.

Go to Shared Components. Click the option Static Workspace Files. Click the Create File button. Select all four files and click the Create button.

Next, you have to Create a New page. Select the option Blank Page. After page creation, we have to add some Regions and Page Items to this page. Change the Page Template property from Theme Default to the Left Side Column, which is used for the left-side display position for search filters, charts, and other interactive widgets.

Scroll down to the file URLs section, under JavaScript. Paste the reference of the three JavaScript files that we uploaded. These references are provided in the Source Code, in the file named references.

Now, scroll down to the CSS section and expand the File URLs section. Paste the reference of the CSS file from the same file named references in the source code. An alternate method, to find these references is in Shared Components, under the Static Workspace Files option. 

Scroll down to the Security Section, and set the Page Access Protection property to Unrestricted. In the unrestricted property, the value of the page may be requested using a URL with or without session State arguments and without having to have a checksum.

Create a New Region. Change its position from the Body to the Left-Side column. The change is immediately reflected in the layout tab in the central pane of the page designer. 

Turn On the Switch of the property named Region Display Selector, when turned On, it provides a page-level navigation control for other regions on the page.

Next, create the following page items. The first-page item (in red color) is of a select list type, that will display the list of customers' names from the DEMO_CUSTOMERS table at runtime. By selecting the template property to Required-Floating and Turning On the Value Required properties (in yellow colors), we make a page item mandatory to be selected or filled. 

Under the list of values section, Set the type property to SQL Query. Use this SQL Query, which fetches the customer's first and last names from the DEMO_CUSTOMERS table. 

select cust_first_name||' '||cust_last_name d, customer_id r
from demo_customers
order by cust_first_name

When sensitive data must persist in a session it should be saved in APEX Session State tables in encrypted form. By Turning Off store value encrypted in session State attribute encrypted stored values are automatically decrypted when read.

Create the remaining page items, which are text field page items. This page item (in red color) will hold gauge labels. Set the default type to static. Enter 500 in the static value (in yellow colors). This value will be displayed in the gauge at runtime. 

This page item will hold the upper-bound values of the gauge between 50 and 2500 covered by green color in the gauge. Enter 5000 in the static value which is the highest value of the gauge.

This page item will cover the warning portion of the gauge between 2500 and 1000 covered by yellow color in the gauge. Enter 2500 in the static value which is the upper bound of the warning portion.

Create another page item. The page item is responsible for the critical area of the gauge between 0 and 1000 covered by red color. Enter 1000 in the static value which is the highest value of the critical portion.

This item will display the big marks in the gauge called the Major Tick Interval. In the static value, you can type any number you want. I have entered 1000 because I want my gauge chart to display a major tick interval after every 1000.

Create the last page item which will display small marks in the gauge known as minor tick interval. In the static value I have entered 100 so my gauge chart will display a minor tick interval after every 100. In simple words, the concept of major and minor tick intervals can be understood from a normal scale carrying inches and centimeters respectively.

Now, create a new static content type region. Under the Source section, Expand the HTML code area. Copy this HTML code. This HTML code will create the body of our gauge chart.

<div id="demoWidget" style="position: relative;">
   <div style="float: left;" id="gaugeContainer"></div>
   <div id="gaugeValue" style="position: absolute; top: 235px; left: 132px; font-family: Sans-Serif; text-align: center; font-size: 17px; width: 70px;"></div>
</div>

Create a new Text field type page item, named Ordered Amount. Set type under the Source section to SQL query (return single value). Use this query in the page designer. In this query, Replace the page item number by using your own page item number. This query makes a sum of the order total column in the DEMO_ORDERS table where the Customer ID column value in the DEMO_CUSTOMERS table matches the name of the customer selected in the select list. Select the second type for the Used attribute.

select sum(order_total) order_total
from demo_orders o
where customer_id=:P46_CUSTOMER

Next, Click on the Dynamic Actions tab. Right-click on the page load event and create a Dynamic action. Click on the show node.

Set the action property to Execute JavaScript Code (in red color). Use the Dynamic Action Code file (in yellow color). This Dynamic action code internally controls the whole process of the gauge chart via functions. In the code, Replace the XX symbols by providing your page number. Save your work. Click on the Run Application button (in blue color). 

$(document).ready(function () {

   $('#gaugeContainer').jqxGauge({
       ranges: [{ startValue: 1, endValue: $v("PXX_CRITICAL"), style: { fill: '#e02629', stroke: '#e02629' }, endWidth: 5, startWidth: 1 },
                { startValue: $v("PXX_CRITICAL"), endValue: $v("PXX_WARNING"), style: { fill: '#fbd109', stroke: '#fbd109' }, endWidth: 10, startWidth: 5 },
                { startValue: $v("PXX_WARNING"), endValue: $v("PXX_OK"), style: { fill: '#4bb648', stroke: '#4bb648' }, endWidth: 15, startWidth: 10 }],
       max:$v("PXX_OK"),
       labels: { interval: $v("PXX_LABELS_INTERVAL") },
       ticksMajor: { interval: $v("PXX_MAJOR_INTERVAL"), size: '9%' },
       ticksMinor: { interval: $v("PXX_MINOR_INTERVAL"), size: '5%' },
       value: 0, // initial pointer position
       animationDuration: 1200
   });
        
   $('#gaugeContainer').on('valueChanging', function (e) {
       $('#gaugeValue').text($v("PXX_ORDER_TOTAL") + ' Amount');
   });

   $('#gaugeContainer').jqxGauge('value', $v("PXX_ORDER_TOTAL"));
});

This is the gauge chart, as you choose a customer name from the select list the gauge pointer moves to the value which is equivalent to the customer's ordered amount (highlighted in red color). These are the remaining fields (highlighted in purple color), 5000 upper bound of the gauge covered by green color. 2500 upper bound of the warning portion covered by yellow color. 1000 upper bound of the critical portion covered by red color. Major tick interval after every 1000 in the gauge chart. Minor tick interval after every 100.

let's change the values of the two text fields (highlighted in purple color), Minor & Major tick interval, and observe the change. Now, the big marks appear with an interval of 500 (highlighted in orange color lines) in the gauge. The small marks are now displaying at an interval of 50 (highlighted in black color dots) in the gauge.. Select any other customer from the list to see its amount in the gauge as well as in the ordered amount text field (highlighted in blue color) in the gauge..

That's it for now. If you got stuck somewhere in this tutorial, you can click the link below.

For Visual Instructions Watch The Video

Play Video In Application

This tutorial is based on two different parts. First, we will play a video in an Oracle APEX application by creating a PL/SQL Dynamic content region. Secondly, we will create an item-type plugin to show the same video on the same page.

Let's start the thrill. Open your application. Go to Shared Components. Click the static workspace file option (in yellow color below). Click this button (in green color) to upload a video file. Upload any small clip, but it should be only of type webm,mp4,ogv. Only these types are supported in the code below. I am uploading an mp4 file. 

The reference to the video clip is highlighted. Take note of this reference, we will be using it later in the tutorial, through which we can see the runtime view of the video clip.

Create a new page. Select the blank page option. Add a new region to this page with the following attributes. For type property first click on the show Legacy option (in yellow color). Then select the PL/SQL Dynamic Content Legacy option.

Copy and paste this code into the page designer. 

declare
   V_webm_video VARCHAR2(50) :=  '#WORKSPACE_IMAGES#INTRO.webm';
   V_mp4_video  VARCHAR2(50) :=  '#WORKSPACE_IMAGES#INTRO.mp4';
   V_ogg_video  VARCHAR2(50) :=  '#WORKSPACE_IMAGES#INTRO.ogv';
begin
   sys.htp.p('<video width="640" height="480" controls>');
   if V_webm_video is not null then
      sys.htp.p('<source src="'||V_webm_video||'" type="video/webm" />');
   end if;
   if V_mp4_video is not null then
      sys.htp.p('<source src="'||V_mp4_video||'" type="video/mp4" />');
   end if;
   if V_ogg_video is not null then
      sys.htp.p('<source src="'||V_ogg_video||'" type="video/ogg" />');
   end if;
   sys.htp.p('Your browser does not support the video tag.');
   sys.htp.p('</video>');
end;

In this code, the highlighted text is the name and file type I use in the tutorial. 

Click the save and run page button. Now play the video.

Let's create an item-type plugin to show the same video on the same page. Go to Shared Components. Click the plugins option. Click the Create button to create a new plugin. Set the following attributes for the plugin. In the internal name field, you have to use your own video name. Item type plugin is implemented using a procedure. 

This PL/SQL procedure creates a video plugin.

procedure render_video (
    p_item   in         apex_plugin.t_item,
    p_plugin in        apex_plugin.t_plugin,
    p_param  in      apex_plugin.t_item_render_param,
    p_result out      apex_plugin.t_item_render_result)
is
   V_webm_video  apex_application_page_items.attribute_01%type  :=
                                  p_item.attribute_01;
   V_mp4_video   apex_application_page_items.attribute_02%type  :=  
                                  p_item.attribute_02;
   V_ogg_video   apex_application_page_items.attribute_03%type  :=  
                                  p_item.attribute_03;
begin
   sys.htp.p('<video width="640" height="480" controls>');
   if V_webm_video is not null then
      sys.htp.p('<source src="'||V_webm_video||'" type="video/webm" />');
   end if;
   if V_mp4_video is not null then
      sys.htp.p('<source src="'||V_mp4_video||'" type="video/mp4" />');
   end if;
   if V_ogg_video is not null then
      sys.htp.p('<source src="'||V_ogg_video||'" type="video/ogg" />');
   end if;
   sys.htp.p('Your browser does not support the video tag.');
   sys.htp.p('</video>');
end;

Click the Callbacks section. Enter the name for the render procedure function name attribute which is the name of the PL/SQL procedure created in the previous step to render the plugin.

Click the custom attributes section. Click the add attribute button. In the current scenario, we are adding the following three attributes to support the video type. 

These are the three supported video types. Make sure your video type Falls in between these three types.

Now open your application page. Create a new region. Add a new page item to the region. Set the following attributes for the page item. Set the type property to the video plugin, under the settings option. Select the field according to your video type, and paste the reference we noted earlier in the video. You can find this reference in the shared components under the static workspace files option.

Save and run the page to see the video clip. This is our second region on the same page. let's play the same video via the plugin.

That's it for now. If you got stuck somewhere in this tutorial, you can click the link below.

For Visual Instructions Watch The Video

Mini Calendar and Sticky Notes Plug-Ins In Oracle APEX

In this tutorial, we will learn the use of the right-side column page template as shown in the screenshot, In addition to demonstrating this template we will use a couple of Plug-Ins to enhance the appearance of our application page.

Let's Get Started. First download SourceCode. For this exercise, we have to create a table named DEMO TASK with some seed data. Open the DEMO TASK.sql file (in red color), in which the first query creates this table (selected in blue color) and the remaining are the insert statements to create some seed data for testing purposes (in yellow color).

Upload and Run the script using the SQL Scripts option. 

On the next screen, Click the Upload > button. And select the DEMO TASK.sql file. Click the Run button in the last column. 

As you can see that all statements processed successfully with zero errors.

Now let's check this table and its related data in the Object Browser under the SQL Workshop menu.

Now open your application. Create a new page. Select the Interactive Report option. On the page definition wizard screen, provide suitable names for the report and form pages. Turn On the Include Form Page switch (in green color). Keep the Form Page mode attribute normal (in orange color). On the data source screen select the table option for the Source type and select the DEMO TASK table (in blue color).

On the next screen, set the primary key column to ID (in green color). and create the page.

Open the form page in page designer. Click the root node and set its page template property to the right side column.

Now add a breadcrumb region to the form page then set the following attributes. We added this breadcrumb region to the form page to easily move between the report and form pages.

Move the four-page buttons, SAVE, CANCEL, DELETE, and CREATE to the breadcrumb region by setting the region property of these buttons to Info-Tech Tasks.

Also, set button position property of these buttons to edit.

Click the form region under the content body. Change its template from standard to blank with attributes.

Go to Shared Components. Click the list option. Create a static list named more actions with the following values in the Screenshot. Since we are going through the page template example. I'm not setting targets for these list items.

Add a new region to the form page and set the following properties for this region. As you select the position for this region a new right column node will be added to the rendering section.

Add one more region and set the following attributes. This region will show the more actions list we created previously.

Go to Shared Components. Click the Plugins option. Import the mini calendar plugin from the SourceCode provided on top. The plugin displays a small calendar with the specified date highlighted.

Add a new region to the page under the actions region (in green color) and set the following attributes (in red color). In this example with the help of this region, we are going to display the end date column from the DEMO_TASK table. Copy and Paste the query from the SourceCode. In the query, you will see the two cross signs, Replace them by providing your page item number (in yellow color).

In this step, we will use another Plugin named PostItNotes. This is a region Plugin and it is used to show a note in the style of a PostIt or Sticky Note, it helps you to show static text or text from a query in a predefined format for viewing in a Postit-styled note on the page.

Create a new region under the content body node and set the following attributes. This region will hold a sticky note at runtime in which we will show the contents from the task description and start date columns in the demo task table in the current scenario.

Replace these two cross signs with your page number as we did previously. Save your work.

Go to the report page and run the page. This should be the report page. If you want to create a new task, click the Create button on this page.

Select any record to see the form page. This should be the form page. This column on the left side (in green color) holds the task End date, which matches the task closing date (in red color) on the right side column.

The start date column on the left side (in green color) matches the date in the Postit note we added (in red color). This is the runtime view of the static list we created named more actions (in orange color).

That's it for now. If you got stuck somewhere in this tutorial, you can click the link below. 

For Visual Instructions Watch The Video