How to make Free Hand SQL with Optional Filter

Thanks Mike for your quick reply.

As you said "SQL query will have a WHERE clause in it with a {?} parameter, ostensibly turning the WHERE query into what's called a User Prompt Filter in YF, but nevertheless the WHERE query is still there in the report itself", but we can decide whether to add or not where condition clause based on filter parameter value like

[code]

WITH

params as

(select {?} AS param1 from dual)

SELECT *

FROM ClientVisit c

LEFT OUTER JOIN Employees e on c.accountManagerID = e.employeeID

LEFT OUTER JOIN Location l on c.VisitLocationID = l.locationID

WHERE

e.last_name =

CASE WHEN LENGTH(params.param1) > 0 THEN params.param1

ELSE e.last_name

END;

[/code]

We can handle that where condition but filter should allow us to proceed without submiting value like --ommit--

Hi Nitin,

Sorry but can you just show me what this looks like on the Data screen of the Report Builder? I want to see what the Filter section looks like in particular based on this query.

Thanks,

Mike

Hi Mike,

It looks like as following image. I am using same query like above but having for filters and I want to make location as optional filter

WITH detail AS
 (SELECT { ? } AS from_date,
         { ? } as to_date,
         { ? } as company_name,
         { ? } location_name
    FROM dual)

select c.company as COMPANY_ID,
       c.company_name as COMPANY_NAME,
       c.country as COUNTRY_CODE,
       c.company_reg_no as COMPANY_REG_NO,
       cp.name as CONTACT_PERSON_NAME,
       cp.telephone1 as CONTACT_PERSON_TELEPHONE1,
       cp.telephone2 as CONTACT_PERSON_TELEPHONE2,
       cp.mobile_telephone as CONTACT_PERSON_MOBILE,
       c.registered_date as COMPANY_REGISTERED_DATE,
       nvl((select distinct 'X'
             from t_company_operator co
            where co.company = c.company
              and co.operator = CASE
                    WHEN LENGTH(detail.company_name) > 0 THEN
                     detail.company_name
                    ELSE
                     co.operator
                  end
              and co.valid_from < detail.to_date
              and (co.valid_to is null or co.valid_to >= detail.from_date)),
           ' ') as COMPANY_APPROVED
  from t_company c, t_contact_person cp, detail
 where c.company in
       (select r.company
          from t_reservation r, t_transportation t
         where r.dn_reservation_day <= detail.to_date
           and r.dn_reservation_day >= detail.from_date
           and r.reservation_status <> '5'
           and t.transportation_id = r.transportation_id
           and t.operator = CASE
                 WHEN LENGTH(detail.company_name) > 0 THEN
                  detail.company_name
                 ELSE
                  t.operator
               end
           and r.end_location = detail.location_name)
   and cp.company(+) = c.company
   and cp.operator = CASE
         WHEN LENGTH(detail.company_name) > 0 THEN
          detail.company_name
         ELSE
          cp.operator
       end
   and cp.contact_person_type(+) = 'B'
 order by c.company

Hi Nitin,

After creating a Freehand SQL Report I can see there's no way to set an optional filter. Looking further, there's actually a pre-existing Idea to allow for this that you can reference here. Feel free to throw a vote in there. I've also gone ahead and added you to the client list in our internal enhancement of those looking for this functionality to be developed. Please follow the referenced Idea item for any potential update regarding this and please let me know if you have any other questions or concerns on this for now.

Regards,

Mike

Hi Nitin,

I'm going to go ahead and close this case out considering this is not currently possible. Please reference the above Idea item for any potential updates surrounding changes on this functionality.

Regards,

Mike