Jump to content
Changes to the Jaspersoft community edition download ×

Adding multiple optional parameters to SQl query


Cris.Abraham

Recommended Posts

Hello

I want to create a report using a SQL query; something like this

SELECT * 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 = null

b) SELECT * FROM ORDERS WHERE ORDER_TYPE_ID = 1

If so, is there a way for me to make the report query b?

Thanks

Link to comment
Share on other sites

  • Replies 3
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

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

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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...