Question

“How can I add search criteria that will be not be visible to my end-users?”

Solution

The following example shows how to use the DataView.OnBeforeGenerateSQL and OnAfterGenerateSQL events to and remove search criteria that cannot be seen by the end-user.

Download:  DADEInvisibleSearchCriteria.zip

Delphi code sample:

procedure TForm1.Button1Click(Sender: TObject);
begin

  PrepareReport(ppReport1);

  ppReport1.Print;

end;

procedure TForm1.PrepareReport(aReport: TppCustomReport);
var
  lDataView: TdaQueryDataView;
begin

  lDataView := GetDataViewForReport(aReport);

  // add event-handlers for the before/after generate SQL events
  if (lDataView <> nil) then
    begin
      lDataView.OnBeforeGenerateSQL := ehDataView_BeforeGenerateSQL;
      lDataView.OnAfterGenerateSQL  := ehDataView_AfterGenerateSQL;
    end;

end;

function TForm1.GetDataViewForReport(aReport: TppCustomReport): TdaQueryDataView;
begin

  if aReport.DataPipeline = nil then
    Result := nil
  else if not(aReport.DataPipeline.DataView is TdaQueryDataView) then
    Result := nil
  else
    Result := TdaQueryDataView(aReport.DataPipeline.DataView);

end;

procedure TForm1.ehDataView_BeforeGenerateSQL(Sender: TObject; aSQL: TdaSQL);
var
  lSQLBuilder: TdaSQLBuilder;
begin

  // use SQLBuilder to add search criteria
  lSQLBuilder := TdaSQLBuilder.Create(aSQL);

  lSQLBuilder.SearchCriteria.Add('Customer', 'Company', 'Like', 'A');

  lSQLBuilder.Free;

end;

procedure TForm1.ehDataView_AfterGenerateSQL(Sender: TObject; aSQL: TdaSQL);
var
  lSQLBuilder: TdaSQLBuilder;
begin

  // use SQLBuilder to remove search criteria
  lSQLBuilder := TdaSQLBuilder.Create(aSQL);

  lSQLBuilder.SearchCriteria.Remove('Customer', 'Company');

  lSQLBuilder.Free;

end;