Autosearch Show All Values and Manually Edited SQL

rbuser

Hello,

I am using RB 11.08 Enterprise.

In an enduser report, I have a report in which I have manually edited
the SQL (I need to use autosearch parameters in calculated field
expressions).

I define a parameter using a datapipeline to populate a dropdown list
for the autosearch prompt. This works fine.

ShowAllValues is set to true in the UserInterface for this parameter.

I want to omit the Where clause for this parameter when ShowAllValues is
selected by the user. Is there any way I can do this?

I hope I have clearly explained the situation.

Thanks,
Phil Horst

nardmoseley

You can write some code to handle that type of search. Use the
Report.BeforeOpenDataPipelines event and the TdaSQLBuilder class to modify
the SQLText.

Here is a simple example:

procedure TForm1.ppReport1BeforeOpenDataPipelines(Sender: TObject);
var
lSQLBuilder: TdaSQLBuilder;
begin

lSQLBuilder := TdaSQLBuilder.Create(ppReport1.DataPipeline);

// base SQL
lSQLBuilder.SQL.SQLText.Text := 'select * from customer.db';

// conditional Where
if not ppReport1.Parameters['Company'].AutoSearchSettings.ShowAllValues
then
lSQLBuilder.SQL.SQLText.Text := lSQLBuilder.SQL.SQLText.Text + ' where
company like "' +
ppReport1.Parameters['Company'].AutoSearchSettings.SearchExpression + '%"';

lSQLBuilder.Free;

// show autosearch dialog on toolbar
ppReport1.ShowAutoSearchDialog := True;


end;



--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com

rbuser

Nard,

Thank you for your response.

I defined code as indicated yet the procedure won't compile. I don't see
the problem.


When I try to compile this code, I get and error message:
. . . Expected '(' or '[' but found 'AutoSearchSettings instead.



Here is an abbreviated version of my code - hopefully enough remains to
be clear


procedure ReportBeforeOpenDataPipelines;
var
lSQLBuilder: TdaSQLBuilder;

begin
lSQLBuilder := TdaSQLBuilder.create(Report.DataPipeline);
lSQLBuilder.SQL.SQLText.Text :=
'SELECT HB_ProgramView_LAN.client_id, ' +
. . .

'FROM HB_ProgramView_LAN HB_ProgramView_LAN ' +
. . .
'WHERE ' + . . . ;

if not Report.Parameters['Counselor'].AutoSearchSettings.ShowAllValues then
lSQLBuilder.SQL.SQLText.Text := lSQLBuilder.SQL.SQLText.Text +
' and (HB_ProgramView_LAN.CounselorID = :Counselor) '

end;

Regards,
Phil

rbuser

Phil Horst wrote:

I found the problem in the RAP code so now I can use the Show All Values
option in my SQL generation.

But a new problem is showing up.

Now when I run this report, the Autosearch prompt option is not shown in
the Report Preview window.

When I open the report in Preview mode, the Autosearch prompt dialog is
shown and I can select the desired values. But for some reason, the
search icon (magnifying glass) is not shown in the Print Preview window.

It seems that I am clearing it, somehow when I execute the
SQLBuilder.ApplyUpdates method in the BeforeOpenDataPipelines event.

Any ideas?

Thanks,
Phil

nardmoseley

See the code example I posted earlier, at the bottom there is this line...

// show autosearch dialog on toolbar
ppReport1.ShowAutoSearchDialog := True;



--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com

rbuser

Nard Moseley (Digital Metaphors) wrote:
Sorry I missed that. Thanks for the reminder.

Phil