Home RAP
New Blog Posts: Merging Reports - Part 1 and Part 2

Autosearch Show All Values and Manually Edited SQL

edited July 2010 in RAP

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

Comments

  • edited July 2010
    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


    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • edited July 2010
    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

  • edited July 2010
    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
  • edited July 2010

    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



    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • edited August 2010
    Nard Moseley (Digital Metaphors) wrote:
    Sorry I missed that. Thanks for the reminder.

    Phil
This discussion has been closed.