CODEWITHSUNDEEP
oracle-databaseoracle-apex
Naq_23
Naq_23

Reputation: 61

Filter data in Interactive Report based on user

 select 
 case
 when :app_user = "John.Doe" THEN select * from time_test 
 when :app_user !="John.Doe" then select * from time_test where :app_user 
 =:app_user 
 end 
 from time_test ;

This table is an interactive report on a page by itself . I want everybody to see the interactive report but I only want John Doe to see the whole table . And everybody else should only see their record in the table .

":app_user" is a default bound variable by Oracle Apex . I also tried to filter the field in the table . 

 select 
 case
 when :app_user = "John.Doe" THEN select * from time_test 
 when :app_user !="John.Doe" then select * from time_test where **name** 
 =:app_user 
 end 
 from time_test ;

and also 

 select 
 case
 when name = "John.Doe" THEN select * from time_test 
 when name !="John.Doe" then select * from time_test where :app_user 
 =:app_user 
 end 
 from time_test ;

I cannot use an if statement because it needs to start with a Select for it load on the page .

Upvotes: 2

Views: 1128

Answers (2)

Scott
Scott

Reputation: 5035

The answer from Lukasz will work just fine.

If you want more out of authorisation schemes, you can set one up based on that condition

:app_user = 'John.Doe'

Then you can use this as a condition for any component.

If you want to use it for row filtering, you could set up a hidden field on the page P1_ROW_SEC, and add computation to set it to Y, but protect that computation with the authorisation scheme.

enter image description here

Then you can add filters such as

and :P1_ROW_SEC = 'Y'

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175736

Simple filtering:

select * 
from time_test 
where col_name =:app_user OR :app_user = 'John.Doe';

Upvotes: 1

Related Questions