Cris.Abraham Posted November 22, 2017 Share Posted November 22, 2017 HelloI want to create a report using a SQL query; something like thisSELECT * FROM ORDERS WHERE ORDER_TYPE_ID = $P{my_order_type_id} AND ORDER_Company_ID = $P{my_order_company_id}But my web application needs to be able to restrict or not restrict what users see based on the type of user. So the 'my_order_company_id' parameter may not always be passed for all users.Does this mean that when 'my_order_company_id' is not passed to the query the report will run the following query a instead of query b?a) SELECT * FROM ORDERS WHERE ORDER_TYPE_ID = 1 AND ORDER_Company_ID = nullb) SELECT * FROM ORDERS WHERE ORDER_TYPE_ID = 1If so, is there a way for me to make the report query b?Thanks Link to comment Share on other sites More sharing options...
MiditecReportDev Posted November 22, 2017 Share Posted November 22, 2017 Edit: Sorry, posted it as an answer now. Link to comment Share on other sites More sharing options...
MiditecReportDev Posted November 22, 2017 Share Posted November 22, 2017 I think (only thinking as I'm not able to open the studio right now) you can use your SQL (without the parameter that can be null) and do the rest with filter expressions to manually filter out expressions the user is not allowed to see. So basically you get *all* the data where ORDER_TYPE = 1 and say in your filter expression that the user should only be able to see those where the company ID matches his my_order_company_id and that being only the case when my_order_company_id is not null. Link to comment Share on other sites More sharing options...
Shazia Posted November 24, 2017 Share Posted November 24, 2017 You can use the below condition in where clause which will solve your problem,SELECT * FROM ORDERS WHERE ORDER_TYPE_ID = $P{my_order_type_id} AND ORDER_Company_ID like DECODE($P{my_order_company_id},'','%','0','%',$P{my_order_company_id}); Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now