How to make Free Hand SQL with Optional Filter

Nitin Kharat shared this question 13 days ago
Awaiting Reply

Hi,


I am trying to build a free hand SQL report with 2 filters - one mandatory and one optional.


Is there a way to use an optional filter in a freehand SQL report?

Comments (1)

photo
1

Hi Nitin,

Thanks for reaching out. This isn't possible due to the nature of Freehand SQL reports. Basically, your 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. If you put WHERE 'null' or '', that's of course still going to filter results because that's how SQL works with WHERE clauses. The only way to not include the filter would be to remove the entire WHERE clause, but if you need an optional filter then I don't think Freehand SQL Reports would be your solution. You may want to consider why Freehand SQL Reports are being looked at in the first place, if this is the case.

Hopefully this explanation makes sense. Please let me know if you have any other questions or concerns on this.

Regards,

Mike

photo
1

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--

photo
1

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

photo
1

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

photo
1

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

photo