How to modify query clauses based on parameter input?

[NCHASHIM]

I am trying to modify a query based on parameter input and I am going in circles.  Can anyone help?

The user should be able to enter a value that can be used to add a clause to the existing sql query.  The clause would allow the query to be filtered by the entered value.

For example, they could leave local office parameter null, and all local offices would be selected OR
they could enter a value in the local office parameter and I would all 'AND LOCAL OFFICE = value' to the existing query

I tried  created a second parameter named filter_clause defined as  IF($P{localOffice} == null, "AND wc.localOffice like '%"," AND wc.localOffice= $P{localOffice} ") 
but it looks as if the value is not established until after all of the parameters are set.   I dropped both localOffice and filter_clause on the report so that I could check the result.  local_office was entered and has a value, but the filter_clause is printing the default string used when localOffice is null.

I also tried creating a variable with the same definition as my filter clause.  That is closer.  It has a value (so I assume variables are valued in between parameters and the sql code).  However, it doesn't look like I can include the $V{filter_clause} within the existing query. 

Are there other options?    Note.  there are two filters and this is the simplest one.  The other would add a 'field in (select stmt)' based on user input.

[m.kamran]

Hi,

You can handle this all within the query so no need to add any additional expressions.

e.g. for parameter name $P{dept}, you can do this in your query,

select * from company where dept =  ISNULL($P{dept},dept)

This will take value from your input and if it is null it is going to return you all records excluding null records.

Hope this helps.

[NCHASHIM]

Thanks.  I wanted to check back in to say this works (with adjustment) for the simplest case.  So, I inserted the following line in the where clause of the sql:
 
and    WC.WORK_CENTER_ID = NVL2($P{WORK_CENTERS_CASCADED}, $P{WORK_CENTERS_CASCADED}, wc.work_center_id)

That works fine.   
The following runs too long so I think I will have to continue to work on it.  The original java program added the test on work_center_id only if the work_center_id parameter was provided.

and    ot.tracking_number in (select tracking_number
                                    from ows.historical_orders
                                    where order_status <> 'CANCEL'
                                    AND FISCAL_YEAR = $P{FY_FROM_2002_TO_TODAY_PLUS_5}
                                    and reserve_reporting_unit_code in (select reserve_reporting_unit_code
                                                                            from ows.work_center_ruc
                                                                              where WC.WORK_CENTER_ID = nvl2($P{work_center_id}, $P{work_center_id}, WC.WORK_CENTER_ID)
                                                                        )
                                 )