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

Passing Autosearch to additional queries

edited September 2010 in RAP
I know, from prior experience that this should be working, but something
I'm doing is wrong, and not working.

Situation:
Delphi 6 / RB 10.04

Currently have 2 queries in this report (once I get this working, there
will be more). Both queries have no issues themselves.

Query 1 is a table called GENLEDG. This has 2 fields, Item and Balance.
The search section looks like:

(Field / Operator / Value / Autosearch )
GENLEDG.Date / Between / [blank] / Yes
begin
GENLEDG.Account / = / 431.000 / [blank]
OR
GENLEDG.Account / = / 421.000 / [blank]
OR
GENLEDG.Account / = / 521.000 / [blank]
end

Query 2 is a table called Warranty. This has one field, Labor Total.
The search section looks like:

Warranty.DatePosted / Between / 1/1/10,1/31/10 / [blank]
begin
Warranty.Paid / = / 4 / [blank]
OR
Warranty.Paid / = / 5 / [blank]
end
Warranty.VendorCode / = / BMW / [blank]

Now - the goal in this situation is to have the autosearch in query 1
passed into the dateposted search in query 2. The result I'm looking
for is the sum of certain accounts in the gl for a date range, and the
sum of BMW Warranty Labor for that same period. When I run a query
separately on the warranty table, for the month of april - i get a
result of $696 (so i know the query works) - there is no value in
January (no BMW warranties were done then).

Now - the code I am using is:

[code]
procedure ReportBeforeOpenDataPipelines;
var
lSQLBuilder: TdaSQLBuilder;
begin
lSQLBuilder := TdaSQLBuilder.Create(Warranty);

if (Report.AutoSearchFields[0].ShowAllValues) then
lSQLBuilder.SearchCriteria.Clear
else if (lSQLBuilder.SearchCriteria.Count = 0) then
lSQLBuilder.SearchCriteria.Add('GENLEDG', 'Date', 'Between',
Report.AutoSearchFields[0].SearchExpression)
else {update existing search criteria with the autosearch field value}
lSQLBuilder.SearchCriteria[0].Value :=
Report.AutoSearchFields[0].SearchExpression;

end;
[endcode]

I've verified that all the spelling matches up. I've tried this both
with Warranty having the DatePosted search and not. I can't figure out
what I'm doing wrong.

Any advice?!?

Thanks in Advance!

Chris Kiraly

Comments

  • edited September 2010
    Hi Christopher,

    Take a look at the following article on applying an autosearch value to
    multiple queries. I highly suggest getting this working correctly in Delphi
    before moving it to RAP. This allows you to trace into the code and see
    exactly what is happening much easier.

    http://www.digital-metaphors.com/rbWiki/DADE/SQLBuilder/How_To...Apply_Single_AutoSearch_to_multiple_queries

    --
    Regards,

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

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited September 2010
    Actually I had discussed something similar with Nard Mosely a while back
    (November of 07).

    I'm using what I used then, which Nard helped me fix for using the
    between in the autosearch dates.

    The page you gave is the base of what I used in my code.

    The issue I have is, i'm not sure because I have other search criteria
    that is being used if that is causing an issue - but the date range is
    not being applied to the second query.

  • edited September 2010
    With RB 12 you can bind a report parameter to multiple queries without
    writing any code.

    1. Use the Report Designer to define a Report.Parameters[ ] item and
    configure the DataType and optional AutoSearchSettings.

    2. Use the Query Designer to define a Query search condition and bind it to
    the report parameter.

    The rbWiki example shows the older approach.


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

    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • edited September 2010
    My problem is that we don't have RB12 - and won't be going to it due to
    changes in our new system's platform.

    But still need to do this for our current customers. So can it get
    done? and how?

  • edited September 2010
    Ok - This is funny (weird that is)

    I stripped all of the search criteria out of warranty. so it will just
    sum that field in the table - it comes to 40043.75. I tested it with
    Just BMW warranties, and got $4080 in normal SQL, so that should be my
    resulting number.

    I added a blank label below the value on the report, and my
    beforepipeline looks like this:

    procedure ReportBeforeOpenDataPipelines;
    var
    lSQLBuilder: TdaSQLBuilder;
    begin
    lSQLBuilder := TdaSQLBuilder.Create(Warranty);

    lSQLBuilder.SearchCriteria.Add( 'Warranty', 'Vendor Code', '=', 'BMW' );

    Label5.Caption := lSQLBuilder.SQL.SQLText.Text;

    end;


    Label5 is my new label - on the report, label 5 prints the whole sql -
    which shows the Warranty.Vendor Code = 'BMW' in the where clause (as
    that is the only where criteria) - but the warranty query isn't running
    with that. At first - it seems like it needs the ApplyUpdates procedure
    run.

    But, when I added lSQLBuilder.ApplyUpdates; below the label5.caption
    assignment, I get the following error:

    Error: ReportBeforeOpenDataPipelines, Line 11: Expected: '(' or '[', but
    found 'ApplyUpdates' instead.

    So it's like my criteria is being added, but after the query runs
    already. Any ideas?


  • edited September 2010
    Ah - problem solved -

    Apparently the ApplyUpdates is useless - but the lSQLBuilder.Free is
    important! Once I added that back in - she worked beautifully!

    thanks again!!!

This discussion has been closed.