Interactive Report Download Button only for a certain Authorization Role

Interactive Report

Interactive Report Download Button only for a certain Authorization Role

Von Tobias Arnhold → 4.27.2017

The Interactive Report has this great download feature where you can export everything you can see.
Anyway there are circumstances where the customer doesn't want that feature open for everyone.

In APEX you can only choose if you want the download button or not.

Even so APEX can't do it out of the box. There is a way to make your application able to do it.

Since APEX 5 you can't download when the "Download" is disabled. If you try an almost empty page occurs. Ok that means the "Download" functionality must be activated an I have to disable it manually.

You need to focus on three steps:
 1. Add an authorization scheme.
 2. Hide the download button in the front end. (Visualization)
 3. Disable the download functionality in the back end. (Security)

1. Add an authorization scheme
The authorization scheme will handle the rights that only the correct person is allowed to download from the Interactive Report.
I my case I call it "ROLE_DOWNLOAD" and it works like that:
Type: PL/SQL Function Returning Boolean 
Function Body: return security_pkg.has_role(:APP_USER,'ROLE_DOWNLOAD');
Validate: Once per session

2. Hide the download button
Add a static report id

Add a new "Dynamic Action" on "Click".
jQuery Selector: #STATIC_REPORT_ID_actions_button
Event Scope: Dynamic
Security > Authorization: {Not ROLE_DOWNLOAD}

Add some Javascript to remove the button:
$('#STATIC_REPORT_ID_actions_menu .icon-irr-download').parent().parent().parent().remove();

3. Disable the download functionality
When APEX is exporting something from an "Interactive Report" itjust does a simple redirect on the same page and adds a REQUEST for the specific download type. In my case it is the request "CSV" I want to block.

Add a "Branch" executed "Before Header":

And to disable the download I just redirect on the same page without any request. The trick is to add the right PL/SQL Condition. In this example check for the request and the authorization scheme.

Code:
:REQUEST = 'CSV' and APEX_UTIL.PUBLIC_CHECK_AUTHORIZATION('ROLE_DOWNLOAD') = false

 
In my mind this is simple and secure and shows how flexible APEX really is.

Run dynamic action from report row and pass multiple variables

Dynamic Actions

Run dynamic action from report row and pass multiple variables

Von Tobias Arnhold → 2.23.2017

Execute a "Dynamic Action" by clicking on a button/link inside a report row is mostly handled by some triggering HTML class.

It actually works in 95% of all cases. But it is not the best way to do it. It is much more effective to execute the "Dynamic Action" with a custom event.

Reason is simple: You don't need to allocate unnecessary elements via a class by jQuery. You execute the "Dynamic Action" in the moment when it is needed. Like calling explicitly a Javascript function.
This matters if you show maybe 500 rows or more on a single page or you handle several dynamic actions in one report.

Running a dynamic action from inside a report is actually an old hat because there are a few guys which have been written about it. Anyway I'm still searching for it every time I need it and some of the code pieces are not up to date anymore. So I will show you the way I handle it today and probably tomorrow, too. :)

First I must thank Jeff Eberhard for his examples about this topic. He really inspired me using the technique in one of my projects where I had to suffer with many rows inside a report.

Blog posts (www.eberapp.com/ords/f?p=BLOG):
Run Dynamic Action from JavaScript
Execute Dynamic Action From Report Column Link
Pass Multiple Values from Report to Dynamic Action

I prefer this way:

1. Set up a "Dynamic Action":
Custom Event: setIemsFromReport
Selection Type: Javascript Expression
Javascript Expression: document

1.1 Now add some action from type: "Execute Javascript Code"
In my example I set up three APEX items with data from my report row.

Code:
apex.item( "P1_DEVICE" ).setValue( this.data.device_name );
apex.item( "P1_IP" ).setValue( this.data.ip );
apex.item( "P1_KOST" ).setValue( this.data.kost );

Info:
As you see the parameters are forwarded with specific variable names. The example came from Matt Nolan. I prefer it mostly because it is exact and it makes it easier to understand (maintainability).

1.2 To get the values into your APEX database session you add one more action from type: "Execute PL/SQL Code":

2. Report column
Inside my report I define a link column which looks like that:

Type:
Link

Target:
javascript:apex.event.trigger(document, 'setIemsFromReport', [{device_name:'#NAME#', ip:'#IP#', kost:'#KOST#'}]);void(0);

Info
apex.event.trigger executes the "Dynamic Action". 
[{...}] defines the variables to forward.
void(0) prevents the browser to do further actions.

Link Text:
<span class="fa fa-edit"></span> 

Link Attributes (not required):
style="font-size:16px;color:#ICON_COLOR#;display:#ICON_DISPLAY#"

Info:
By using some columns with case when clause I'm able to add some custom attributes like hide/show. Example:
  case
    when SUBSTR(DEVICE,1,1) = 'T'
    then 'inline-block'
    else 'none' 
  end as ICON_DISPLAY

That is all you need to hand over attributes from your report row towards one or more APEX items on your page.

Using dynamic tooltips in your Interactive Report

Universal Theme

Using dynamic tooltips in your Interactive Report

Von Tobias Arnhold → 5.31.2016

Inside an Interactive Report (IR) I had a comment column. The comments in this column could become really large and the users wanted the comments to be automatically trimmed if more then 60 characters were displayed. If the user moved the mouse above a trimmed comment then a tooltip should be display including all comment text.

My first idea was to check for existing plugins which could do this job for me. So I searched on apex.world and found the plugin called "APEX Tooltip" developed by Daniel Hochleitner.

The plugin looked great from what I could see in the example application. When I tried it in my application I found out that all comments must be applied manually in the dynamic action.
Not exactly what I needed. :)

Luckily it was really easy to extend the plugin in a way that I could use it in my IR. To achieve what I need I had to update the javascript file: apextooltip.js

// APEX Tooltip functions
// Author: Daniel Hochleitner
// Updated by Tobias Arnhold
// Version: 1.1

// global namespace
var apexTooltip = {
  // parse string to boolean
  parseBoolean: function(pString) {
    var pBoolean;
    if (pString.toLowerCase() == 'true') {
      pBoolean = true;
    }
    if (pString.toLowerCase() == 'false') {
      pBoolean = false;
    }
    if (!(pString.toLowerCase() == 'true') && !(pString.toLowerCase() == 'false')) {
      pBoolean = undefined;
    }
    return pBoolean;
  },
  // function that gets called from plugin
  showTooltip: function() {
    // plugin attributes
    var daThis = this;
    var vElementsArray = daThis.affectedElements;
    var vTheme = daThis.action.attribute01;
    var vContent = daThis.action.attribute02;
    var vContentAsHTML = apexTooltip.parseBoolean(daThis.action.attribute03);
    var vAnimation = daThis.action.attribute04;
    var vPosition = daThis.action.attribute05;
    var vDelay = parseInt(daThis.action.attribute06);
    var vTrigger = daThis.action.attribute07;
    var vMinWidth = parseInt(daThis.action.attribute08);
    var vMaxWidth = parseInt(daThis.action.attribute09);
    var vLogging = apexTooltip.parseBoolean(daThis.action.attribute10);
    // Logging
    if (vLogging) {
      console.log('showTooltip: affectedElements:', vElementsArray);
      console.log('showTooltip: Attribute Theme:', vTheme);
      console.log('showTooltip: Attribute Content:', vContent);
      console.log('showTooltip: Attribute Content as HTML:', vContentAsHTML);
      console.log('showTooltip: Attribute Animation:', vAnimation);
      console.log('showTooltip: Attribute Position:', vPosition);
      console.log('showTooltip: Attribute Delay:', vDelay);
      console.log('showTooltip: Attribute Trigger:', vTrigger);
      console.log('showTooltip: Attribute minWidth:', vMinWidth);
      console.log('showTooltip: Attribute maxWidth:', vMaxWidth);
      console.log('showTooltip: Attribute Logging:', vLogging);
    }
    for (var i = 0; i < vElementsArray.length; i++) {
      var vaffectedElement = daThis.affectedElements.eq(i);
      // call tooltipster plugin
      $(vaffectedElement).tooltipster({
        theme: vTheme,
        content: $(vaffectedElement).find('.rep_complete_comment').html(),
        contentAsHTML: vContentAsHTML,
        animation: vAnimation,
        position: vPosition,
        delay: vDelay,
        touchDevices: false,
        trigger: vTrigger,
        minWidth: vMinWidth,
        maxWidth: vMaxWidth,
        debug: vLogging,
        functionBefore: function(origin, continueTooltip) {
          $(vaffectedElement).trigger('apextooltip-show');
          continueTooltip();
        },
        functionAfter: function(origin) {
          $(vaffectedElement).trigger('apextooltip-hide');
        }
      });
    }
  }
};

Of course I had to update my IR as well.
First I trimmed the comment column and added a new hidden comment column:

SELECT
-- ...
  CASE WHEN LENGTH(COMMENT_COL) > 60 THEN SUBSTR(COMMENT_COL,1,60)||'...' ELSE COMMENT_COL END AS COMMENT_COL,
  COMMENT_COL as COMPLETE_COMMENT_COL,
from my_table

Then I updated the column attributes:
COMMENT_COL as plain text
- Static ID: rep_comment
- HTML Expression:
#COMMENT_COL#<div class="rep_complete_comment">#COMPLETE_COMMENT_COL#</div>

COMPLETE_COMMENT_COL as hidden column

I also had to add a small css snippet in the page attributes > CSS > Inline to hide the complete comment:
.rep_complete_comment {
display: none;
}

And as a last step I had to implement the tooltip plugin after refreshing the IR:
I used an advanced jQuery selector to get only those comments affected which had more then 60 characters.
td[headers='rep_comment']:contains('...')

The result looked like this:

Looking for UILayout plug-in beta tester (APEX 4.1 only)

UILayout

Looking for UILayout plug-in beta tester (APEX 4.1 only)

Von Tobias Arnhold → 10.17.2011

I added some fixes to the UILayout plug-in in combination with APEX 4.1 and the Interactive Report appearance.

I'm quite busy with the Plugin App and looking out for some testers. If there is anybody out there then please contact me.

The following issues are fixed under APEX 4.1:

Format options position

Rows Per Page position

Column options

I don't know if it works for all themes? If you know about more issues or even have some improvement ideas please inform me. I will fix it before the final release will come out.

Tobias

Es sind die kleinen Dinge, die APEX 4 so viel besser machen

Dynamic Actions

Es sind die kleinen Dinge, die APEX 4 so viel besser machen

Von Tobias Arnhold → 3.07.2011

Ich habe heute eine Frage im APEX Forum beantwortet. Diese recht trivial klingt, aber in der Konstellation doch recht anspruchsvoll wurde.

Ausgangslage:
Ein Entwickler hat zwei Textfelder: P1_FIELD_A, P1_FIELD_B und beide Felder sind im DATE-Format.
Wenn er das Feld P1_FIELD_A verlässt, dann soll das Feld P1_FIELD_B den Wert von P1_FIELD_A erhalten und diesen Wert automatisch um 1 Jahr erhöhen.

Wie gesagt es klingt recht einfach, sofern Sie PL/SQL verwenden dürfen und APEX 4.0 im Einsatz haben!

Kreieren Sie eine Dynamic Action > "Erweitert"  > Event: "Lose Focus" > Item: P1_FIELD_A

TRUE Action: "Set Value" > Set Type: "PL/SQL Expression" >
Code: to_char(add_months(to_date(:P1_FIELD_A,'DD.MM.YYYY'),12),'dd.mm.yyyy')
Page Items to Submit: P1_FIELD_A

Affected Elements: Items > Item: P1_FIELD_B

Mit APEX 3 sah das noch ganz anders aus!
Statt einfach auf eine PL/SQL Funktion zugreifen zu können, musste diese über Javascript als Application Prozess eingebunden werden. Statt 5 Klicks brauchten Sie 20 Zeilen Code der an verschiedenen Stellen lag.
Ich habe die Aufgabe versucht mit Javascript zu lösen. Verdammt unschön und aufwendig so ganz ohne PL/SQL:

Dazu müssen Sie dem Element P1_FIELD_A folgenden Code unter "HTML Form Element Attributes" zuweisen:

onblur="javascript:fnc_field_b(this)"

Und im Page Header folgte dieser JS Code:

<script>
function fnc_field_b(v_field_a){
/* Input: mm/dd/yyyy */
/* Output: dd.mm.yyyy */
/* Documentation: http://www.javascriptkata.com/2007/04/27/mastering-of-the-date-object-in-javascript/ */
/*                http://programming.top54u.com/post/Javascript-Convert-String-to-Date.aspx            */

/* Create date object */
var myDate = new Date(v_field_a.value);

/* Add 1 year */
myDate.setDate(myDate.getDate() + 365);

/* Create output string DD.MM.YYYY */
/* Day */
var myStr = (myDate.getDate() < 10 ? "0" + myDate.getDate().toString() : myDate.getDate().toString()) +  ".";
/* Month */
    myStr = myStr + (myDate.getMonth()+1 < 10 ? "0" + (myDate.getMonth()+1).toString() : (myDate.getMonth()+1).toString()) + ".";
/* Year */
    myStr = myStr + myDate.getFullYear().toString();
 
/* Set value */
$s('P1_FIELD_B',myStr);
}

Dabei ist dieser Code noch nicht einmal inhaltlich vollständig. Javascript kann nur das Format mm/dd/yyyy direkt in Date umwandeln.
Ok und diese:
* MM-dd-yyyy
* yyyy/MM/dd
* MM/dd/yyyy
* MMMM dd, yyyy
* MMM dd, yyyy
Unser beliebtes dd.mm.yyyy ist nicht dabei. Also müsste man dieses via ein paar ausgefeilten String-Funktionen noch umwandeln und unter Umständen noch eine Regular Expressions zum Fehlerhandling hinzufügen.
Javascript unterstützt auch nicht die to_char-Funktion. Weshalb ein recht komplexer Script zu umwandeln benötigt wird. Das Gute an JS ist, dass es unzählige Beispiele im Netz gibt.
Weshalb dieser Part recht einfach zu lösen war (http://www.javascriptkata.com/2007/04/27/mastering-of-the-date-object-in-javascript/).

Nur zur Info: Die meisten anderen Web-Entwickler haben kennen keine Dynamic Action Logik bzw. kennen kein PL/SQL.

Das zeigt nur im kleinem Beispiel warum die Entwicklung von APEX Anwendungen so schnell geht.
Ich vergleiche das immer mit Oracle Forms und Oracle Java Alternativen dazu. Wieso tief in den Programmierschichten graben wenn das Leben doch so leicht sein kann. :)

Den Forum Beitrag finden Sie hier: http://forums.oracle.com/forums/thread.jspa?threadID=2186734&tstart=0

Dynamic Actions Beispiel

Dynamic Actions

Dynamic Actions Beispiel

Von Tobias Arnhold → 10.10.2010

Vor einer Weile wurde eine super Anleitung für die Verwendung von Dynamic Actions in APEX 4.0 auf der deutschen APEX Community Seite erstellt. http://www.oracle.com/webfolder/technetwork/de/community/apex/tipps/apex40-dynamicactions/index.html
Damit auch der letzte die Vorteile von Dynamic Actions versteht, habe ich auf dieser Anleitung basierend eine kleine Anwendung erstellt.


Beispiel: http://ta-it-consulting.de/trigger_example.html

Wer Interesse am Code hat oder nähere Infos zu APEX 4.0 bzw. Dynamic Actions benötigt. Kann mich unter tobias-arnhold@hotmail.de einfach anmailen.