Modify SQL at runtime when EditSQLAsText is TRUE
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.
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.
Comments
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) )
Best regards,
Nard Moseley
Digital Metaphors
www.digital-metaphors.com