Modify SQL at runtime when EditSQLAsText is TRUE

Tomsie

If, for optimization reasons, a report needs its SQL handcrafted (EditSQLAsText = True) then I can still pass single values to it's WHERE clause at runtime with parameters. However, this falls down with INLIST (IN) WHERE clauses where effectively I need to pass a comma separated list of values; in fact I do this with TdaSQLBuilder already in non tampered with SQL report. However, in this scenario I can't use TdaSQLBuilder to do this as SearchCriteria.Count will be 0 by virtual of EditSQLAsText = True; so I have been trying to got a bit deeper along the lines of:

lDataModule := daGetDataModule(aReport);

if assigned(lDataModule) then
begin
lDataView := TdanxQueryDataView(lDataModule.DataViews[0]);
ldaSQL := lDataView.SQL;
ldaSQLText := TdaSQLText(ldaSQL.SQLText);

if assigned(ldaSQLText) then
begin
ldaSQLText.QueryExpression.WhereClause.SearchItems[0].Expression2 := aFilterList.CommaText;
TdaSQLTextAccess(ldaSQLText).Changed;
end;
end;

However, the edits I make to Expression2 are not recognised when TdaSQLTextAccess(ldaSQLText).Changed is called. I've been trying to get me head around the token parsing code but I think I'm better cutting to the chase and asking the experts here if there is a way to achieve what I want. Thanks, Paul.

nardmoseley

Hi Paul,

Define a Report.Parameters[ ] item and set the DataType. Configure AutoSearchSettings SearchOperator to poInList and SearchExpression to a comma delimited list.

Example:

ppReport1.Parameters['paCustNos'].AutoSearchSettings.SearchExpression := '1221, 1551';


SELECT customer.Company,
customer.CustNo
FROM customer
WHERE ( customer.CustNo IN :paCustNos )


RB resolved the above to this..

SELECT customer.Company,
customer.CustNo
FROM customer
WHERE ( customer.CustNo IN (1221,1551) )

Tomsie

Wow, even old hands can learn something new! Or is this a recent enhancement as I'm sure I've been around this loop a few years ago.