How to set optional filters in freehand sql
31 May, 2012
I am trying to add filters to a freehand sql report but I want to make them optional filters. Here are the filters I am using:
"ClientVisit"."cptcode" '99999'
AND "ClientVisit"."rev_timein" BETWEEN {?} AND {?}
AND "Employees"."last_name" = {?}
AND "Location"."location_code" = {?}
So my question is, if the user does not enter an employee last name for instance, how do I get the report to generate a list with ALL employee last names?
"ClientVisit"."cptcode" '99999'
AND "ClientVisit"."rev_timein" BETWEEN {?} AND {?}
AND "Employees"."last_name" = {?}
AND "Location"."location_code" = {?}
So my question is, if the user does not enter an employee last name for instance, how do I get the report to generate a list with ALL employee last names?
Hi,
the functionality of omitting a filter when a user leaves the prompt blank is available in Drag and Drop reports but not in Freehand SQL reports, the reason being that the original freehand SQL would need to be modified.
Please let us know if there are any more questions about freehand SQL and filters.
Regards,
David
the functionality of omitting a filter when a user leaves the prompt blank is available in Drag and Drop reports but not in Freehand SQL reports, the reason being that the original freehand SQL would need to be modified.
Please let us know if there are any more questions about freehand SQL and filters.
Regards,
David
Hi David, you mention that "the reason being that the original freehand SQL would need to be modified" so does this mean that if I edit my SQL request in some way that I can get these optional filters, or that it is something on yellowfin's side that would have to be modified (that is my guess?) I'm not overly familiar with this system yet because I started using it only recently so forgive my ignorance in this matter and thank you for your reply!
Hi,
no you can't get the optional filters in freehand sql reports, you would definitely have to use the Report Builder to get that useful functionality. And the reason for this is that if you don't enter a value for a freehand sql filter then the resulting query would be:
"SELECT * FROM SALES WHERE CUST_ID = " which as you know does not compute, so YF
would have to modify the original freehand sql query to:
"SELECT * FROM SALES" which it doesn't do.
Hope that clears the matter up, please don't hesitate to ask if there are any other questions.
Regards,
David
no you can't get the optional filters in freehand sql reports, you would definitely have to use the Report Builder to get that useful functionality. And the reason for this is that if you don't enter a value for a freehand sql filter then the resulting query would be:
"SELECT * FROM SALES WHERE CUST_ID = " which as you know does not compute, so YF
would have to modify the original freehand sql query to:
"SELECT * FROM SALES" which it doesn't do.
Hope that clears the matter up, please don't hesitate to ask if there are any other questions.
Regards,
David
Yep, that sure does clear it up! Thank you!
Hi,
There's actually a way to achieve what you're after through SQL trickery.
[code]
--param1 = last_name
declare @param1 varchar(50);
set @param1 = {?};
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 LEN(@param1) > 0 THEN @param1
ELSE e.last_name
END;
[/code]
Cheers.
There's actually a way to achieve what you're after through SQL trickery.
[code]
--param1 = last_name
declare @param1 varchar(50);
set @param1 = {?};
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 LEN(@param1) > 0 THEN @param1
ELSE e.last_name
END;
[/code]
Cheers.