Oracle APEX Interactive Report conditional column link display

Question

I have an interactive report displaying all the records from articles table. For a logged in author, I would like to display all the records, but the EDIT should be displayed only for those articles written by that author. In articles table, I have the column CREATED_BY which has the author username.

I added a condition to Link Column as Value of Item / Column in Expression 1 = Expression 2 as Expression1=#CREATED_BY# & Expression2= :APP_USER

But this does not work. This is the first time I am taking this approach to display edit link based on a condition.

When I added the condition Value of Item / Column in Expression 1 is not null and set Expression1 = #CREATED_BY#, it was still not displaying edit link. So, I think that #CREATED_BY# is returning null. But the record in table has a value & I also see it in report column.

Can someone help? I don't know where I am going wrong.

Answer 1

The condition you use is for the column and not for each row so you will not be able to do it that way.

I think that the best way to achieve this is create a dummy column in your query that you will use as your edit link:

SELECT
     CASE
      WHEN CREATED_BY = :APP_USER THEN
        '<a href="' || APEX_UTIL.PREPARE_URL( p_url => 'f?p=' || &APP_ID. || ':<YOUR EDIT PAGE>:'||&SESSION.||'::NO::<PAGE ITEM>:'||COLUMN ID, p_checksum_type => 'SESSION') || '"><img src="/i/menu/pencil2_16x16.gif"></a>'
      ELSE ''
    END edit_link,

 ...THE REST OF YOUR QUERY...

You also have to change the display of the column under column definition to

Standard Report Column

and optionally you can remove the options under

Allow Users To: so the user couldn't hide/sort/... the column.

Hope this can help you. Any doubt or further explanation just ask.

Answer 2

I was able to come up with the solution in APEX 5, but the same should apply to APEX 4.2. You can easily do this with the built-in APEX column link and a case statement as hmarques was suggesting.

Structure your query as follows:

SELECT CASE
           WHEN CREATED_BY = :APP_USER THEN
               'Edit'
           ELSE
               NULL
       END EDIT,
       ...rest of query

Then, go to: Report Attributes > Your Edit Column > and scroll to the Column Link portion of the page. Select the arrow next to the Link Text text box, and select #EDIT# as the column link text.

Also, set the Link Attributes with this html code:

class="t-Button t-Button--simple t-Button--hot t-Button--stretch"

This will make the column look like a button, and will not display the traditional pencil icon (which is the only drawback to this method).

Click here to see screen shot

Don't forget to make the edit column a Standard Report Column and good luck!

John