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

Autosearchcriteria and SQLString

edited August 2010 in RAP
Using RB11.08 enterprise.

In RAP it looks like I should be able to acces the SQLString method for
the property Report.AutoSearchCriteriaByName property. But I am not
able to figure the correct syntax for this.

Thanks for your help.

Phil

Comments

  • edited August 2010
    Hi Phil,

    I'm a bit unclear about what you are trying to accomplish. Are you creating
    the autosearch field(s) in DADE? If so, I recommend using the TdaSQLBuilder
    to access and change all items dealing with the SQL code entered including
    the AutoSearch fields.

    --
    Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited August 2010
    Nico Cizik (Digital Metaphors) wrote:
    Hi Nico,

    Sorry I didn't provide the full context.

    I am using SQLBuilder in RAP, event BeforeopenDataPipelines to build an
    sql statement.

    I have a number of parameters defined in the report and want to retrieve
    the values to use in where clauses. I have done this successfully
    (thanks to earlier support). But one of my parameters is defined as "in
    list" So I need to access the list of selected values from the
    autosearch field to properly define the where clause.

    The help file references the method SQLString and I thought this might
    provide a correctly formatted string ready to use in a where clause.

    Hope that clarifies.

    Thanks,
    Phil
  • edited August 2010
    Hi Phil,

    Thanks for the explaination.

    Looking at the AutoSearchField code, it does not look like the SQLString
    function is exposed to RAP. I will add it to our todo list to possibly add
    this for a later release. Currently you could piece the where clause
    together using the TdaCriteria properties or you could access the SQLString
    property using a RAP pass-thru function.

    --
    Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited August 2010
    Nico Cizik (Digital Metaphors) wrote:



    Hi Nico,

    I decided to try doing this using a pass-thru function. Using your
    demos, I learned do to do a basic function. But when I modify it to do
    what I need, I get an access violation error when trying to use the
    function.

    Here is the RAP code invoking the function:
    showmessage(SQLString(Report, 'HB_Program', 'Program')) ;

    My Delphi code defining the function (not including the declarations:

    procedure TmySQLStringFunction.ExecuteFunction(aParams: TraParamList);
    var
    lppReport: TppReport;
    lPipeline: string;
    lAutoSearchFieldName: string;
    lsResult: String;
    begin
    getParamValue(0, lppReport);
    getParamValue(1, lPipeline);
    getParamValue(2, lAutoSearchFieldName);
    lsResult :=
    lppReport.AutoSearchFieldByName(lAutoSearchFieldName).SQLString;
    SetParamValue(3, lsResult);
    end;

    class function TmySQLStringFunction.GetSignature: String;
    begin
    Result := 'function SQLString(const ppReport: TppAutoSearchField;
    const Pipeline, AutoSearchFieldName: String): string;';
    end;

    Can you tell me what I am doing wrong?

    Thanks!

    Phil
  • edited August 2010
    Hi Phil,

    The first thing that stands out is in your routine signature you have the
    TppReport parameter declared as a TppAutoSearchField type. Be sure that the
    signature represents the exact routine used in RAP.

    --
    Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited August 2010
    Hi Nico,

    Right - missed that.

    After more exploring, I am able to get this work with the following
    code. I suspect that something is not right still - perhaps in my
    definition of my parameter.

    The SQLstring returned does not include the field name. I don't know if
    this is intended or I am missing something.

    For example, if I select two items from the list of options, the
    returned SQLString is: ". IN(1080,1081)" - no quotes of course.

    Is there are property that I might be missing in the parameter definition?

    procedure TmySQLStringFunction.ExecuteFunction(aParams: TraParamList);
    var
    {include a local var for each parameter and the Result value}
    lppReport: TppReport;
    liIndex: Integer;
    lAutoSearchField: TppAutoSearchField;
    lsResult: String;
    begin
    getParamValue(0, lppReport);
    getParamValue(1, liIndex);

    lsResult := '';
    lAutoSearchField := lppReport.AutoSearchFields[liIndex];

    {if valid entry, get search expression in SQL format}
    if lAutoSearchField.Valid then
    lsResult := lAutoSearchField.SQLString;

    lsResult:= trim(lsResult);
    lsResult := Copy(lsResult, 2, length(lsResult) - 1) ;


    SetParamValue(2, lsResult);
    end;

    class function TmySQLStringFunction.GetSignature: String;
    begin
    Result := 'function SQLString(const ppReport: TppReport; const
    SearchFieldIndex: integer): string;';
    end;

    { TMyNewCategoryFunction }

    Thanks again.

    p.s. I must say, I continue to be delighted to discover what
    ReportBuilder can do!

  • edited August 2010
    A related question, I think-

    As mentioned I am using an IN autosearch field. I want to print a the
    values selected by the user when running the report. That is, I want to
    print the selected items from the NameField in the Lookup List.

    How might I do this? Hope the question is clear.

    Thanks,



  • edited August 2010
    Hi Phil,

    Looking at the SQLString code in ppAsField.pas, it looks like it combines
    the TableName, FieldName, Operator, and Expression into a single string.
    The table and field names should be included. You might try tracing into
    your passthru function to see exactly what is happening in the code.

    If you just want the selected values from your user, you might just be able
    to use the FormattedExpression routine or simply the Values property.

    --
    Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
This discussion has been closed.