Freitag, 17. November 2017

APEX Rendering Button in Report Column part 2

Hello everyone,

after a long time i managed to write the 2nd part of APEX rendering button in report Column.

History:
A long time ago I read an article from Scott Wesley about how buttons can be rendered and used in reports. After now 2 years in Oracle APEX technology, I would like to introduce you to another approach, how it is still to be realized.

Here you will get a step-by-step instruction, how to implement and use the buttons in a report.

For those who would like to participate I provide the BasicFiles.
(
It contains a demo Table, thdevelop_util_pkg and Basic Application).

For all the others you just have to download the package only.

Presteps:

  1.    Download BasicFiles
  2.    Execute Table.sql 
  3.    Execute thdevelop_util_pkg
  4. Import Application BlogBasicApplication.sql


Presteps Finished.





Button in Report
Let's start by displaying the button in the report.

1. Open the SQL code from the report region with the built in Code Edtior. 




2. Add after Salary the following in the SQL statement.


1
2
3
4
5
6
7
8
9
thdevelop_util_pkg.apex_item_button(p_item_label     => 'SET',
                                    p_item_class     => 'SET'
                                    ) ||
thdevelop_util_pkg.apex_item_button(p_item_label     => 'SAVE',
                                    p_item_class     => 'SAVE'
                                   ) ||
thdevelop_util_pkg.apex_item_button(p_item_label     => 'RESET',
                                    p_item_class     => 'RESET'
                                    ) buttons

For all those who didn't know, in APEX you can render page items in an SQL query. See the Application Express API Reference for more information.

3. Close the Editior.


4. Select the new column Buttons and change in properties  -> Security -> Escape Special Characters -> NO



Escape Special Characters no ensures that the returned html text is not displayed as text but as HTML. This ensures that e. g. buttons can be rendered.

5. Save Page and show the Preview. (It should look like this)



The buttons are now included in the report.

To get a better understanding of working with buttons in reports I added a text item here. This will be filled later when you click on the set button.

Textfield in Report

1. Open the SQL code from the report region again. (like in step 1)

2. Add the following to buttons in the SQL statement.(in my case, I included it as the first element of the column)

1
2
3
4
5
6
7
8
APEX_ITEM.TEXT(p_idx         => 50,
               p_value       => salary,
               --p_size        => ,
               --p_maxlength   => ,
               p_attributes  => 'data-id=' || q'["]' || empid || q'["]'
               --p_item_id     => ,
               --p_item_label  => 
               ) ||

Your code should look like this

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
select EMPLOYEES.EMPID as EMPID,
       EMPLOYEES.NAME as NAME,
       EMPLOYEES.SALARY as SALARY,
       APEX_ITEM.TEXT(p_idx         => 50,
                         p_value       => salary,
                         --p_size        => ,
                         --p_maxlength   => ,
                         p_attributes  => 'data-id=' || q'["]' || empid || q'["]'
                         --p_item_id     => ,
                         --p_item_label  => 
                        ) ||
       thdevelop_util_pkg.apex_item_button(p_item_label     => 'SET',
                                           p_item_class     => 'SET'
                                           ) ||
       thdevelop_util_pkg.apex_item_button(p_item_label     => 'SAVE',
                                           p_item_class     => 'SAVE'
                                           ) ||
       thdevelop_util_pkg.apex_item_button(p_item_label     => 'RESET',
                                           p_item_class     => 'RESET'
                                         ) buttons
 from EMPLOYEES EMPLOYEES

p_attributes is set so that I get to the PK of the row for the later update procedure. More about this later.

If you look at the preview now, the set button is very close to the texfield.

To fix this we use css.
Click on page -> properties -> CSS -> inline and enter the following


1
2
3
.SET{
    margin-left: 10px;
}

Now everything is ready to embed the logic.


Enliven buttons with action

1. SET Button
  1.1 Create Dynamic Action  (Click) with the following settings

The JQuery selector is used here because we gave the button a class name and we only want to trigger the event when one of the set buttons is clicked.

Eventscope Dynamic, because more than just one button is controlled with the class SET and it should always be the triggered element to execute the function. Not only the one I clicked on.

As you can see in the true branch Execute JavaScript Code is selected

1.1.2 Execute JavaScript Code 
Click on it and enter the Code
1
2
var set_value = $('#P1_TO_SET_VALUE').val();
$(this.triggeringElement).prev('input').val(set_value)

Line 1 give you the value from the top page item and save it in set_value.
(In figure 1.1 you can see another DA which has not been described.
This is used to save the value entered in the top item with the submit button in the session.)
Line 2 now the texfield which is before the set button is filled with the value of set_value. 

Test it!
If you have done everything right, the SET button works now.


2. SAVE Button
  2.1 Create Dynamic Action  (Click) with the following settings

   2.1.2 Execute JavaScript Code 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
var trigger_element = $(this.triggeringElement).prev().prev()
var empid = apex.jQuery(trigger_element).data('id');
var set_value = apex.jQuery(trigger_element).val();


apex.server.process ( 'save_value', 
  {
    x01:empid,
    x02:set_value
  },
  {
    success: function( pData )
    {        
//      if (pData.status == 'FAIL') { console.log('Error'); };
      apex.event.trigger("#REPORT-WITH-BUTTON", "apexrefresh");
    },
/*    error: function() */

  dataType: 'text'
  }
              );

Line 1: The input item what is before SET. (Textfield)
Line 2: PK for insert that i have set in prev step (Textfield in Report point 2)
Line 3: Value to be set. (previously filled with SET button)
Line 6: Run an APEX process from Javascript. (in this case save_value, we will create this process later)
Line 8: x01 is a collection variable wich  can use later in PL/SQL code. (this holds the value of the pk)
Line 9: x02 an other collection variable, this holds the value to be set
Line 12: Process successful 
Line 15: The specified region should be refeshed after the process has been successful.

2.2 APEX Process
  Create a AJAX Callback Process.(this is the Process that will be execute from predefined Dynamic Action)

  PL/SQL Code

1
2
3
update employees
set salary = apex_application.g_x02
where empid = apex_application.g_x01;

Line 2 and Line 3: the collection variables calls.

Test the Save Button!

3. RESET Button
  3.1 Create Dynamic Action  (Click) with the following settings

  3.1.2 Execute JavaScript Code 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
var trigger_element = $(this.triggeringElement).prev().prev().prev('input').val(0)
var empid = apex.jQuery(trigger_element).data('id');
var set_value = apex.jQuery(trigger_element).val();

apex.server.process ( 'save_value', 
  {
    x01:empid,
    x02:set_value
  },
  {
    success: function( pData )
    {        
//      if (pData.status == 'FAIL') { console.log('Error'); };
      apex.event.trigger("#REPORT-WITH-BUTTON", "apexrefresh");
    },
/*    error: function() */

  dataType: 'text'
  }
              );

This button has the same structure as the reset button. The only differences are in JavaScript. As you can see the prev calls for the textfield have gone up.

Final Test

We're finished and I hope you guys enjoyed it.
(Finished example)

Thank you for reading and having fun

Keine Kommentare:

Kommentar veröffentlichen