Comparison of same data w/ ReportBuilder

rbuser

Hey All -

Once again, I'm here for help!

Here is what I'm trying to accomplish. I'd like have a report that
details sales for a user generated date, and then also generates a
comparison for the same date range last year.

The question I have is this... can you use TdaSQLBuilder and yank the
date out of the first query (using the TppAutoSearchField), and apply it
to a second query (which is basically the same as the first query, only
waiting for this new date range).

And if so, how?

thanks in advance...

- Chris

nardmoseley

http://www.digital-metaphors.com/tips/ApplyAutoSearchValueTo2ndQuery.zip


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

rbuser

Nard Moseley (Digital Metaphors) wrote:
Thanks -

Ran into a snag trying to test this...

What I did to test it is I have a genledg query that lists account #,
sum of debits, sum of credits - with a date between autosearch (and it's
filled in by default to be 1/1/2007 - 12/31/2007).

I created a second query (genledg2) that is the exact same, except that
I didn't turn on autosearch on the date range, and the default date
range is 1/1/2006 - 12/31/2006.

When I look at the previews on the query, they come up with different
results (as they should). I simply created a report that showed the
account #, debit amt, credit amt from genledg - and then in the summary,
created a subreport that showed the exact same fields from genledg2.

When I preview this report - it shows what it should (from the query
previews).

Now, my understanding is that using the apply autosearch value to 2nd
query, without changing anything, should get me the exact same date
range on the second query, in which the report results should come back
identical (since its basically then the same query being run twice if
the dates match).

Here is the code I used (which basically is almost identical to the
sample code.

procedure ReportBeforeOpenDataPipelines;
var
lSQLBuilder: TdaSQLBuilder;
begin


lSQLBuilder := TdaSQLBuilder.Create(Genledg2);

if (Report.AutoSearchFields[0].ShowAllValues) then

{clear existing search criteria}
lSQLBuilder.SearchCriteria.Clear

else if (lSQLBuilder.SearchCriteria.Count = 0) then

{add new search criteria with the autosearch field value}
lSQLBuilder.SearchCriteria.Add('genledg', 'date', 'Between',
Report.AutoSearchFields[0].Value)

else {update existing search criteria with the autosearch field value}
lSQLBuilder.SearchCriteria[0].Value := Report.AutoSearchFields[0].Value;


{lSQLBuilder.ApplyUpdates;}
lSQLBuilder.Free;


end;


Now, the code compiles error free, but when I run the report - nothing
changes - I still get the 2006 data from the second query, instead of
the 2007 data from the 1st query (after the autosearch)...

So what did I do wrong?

Thanks in advance

- Chris

nardmoseley

One difference is that you are using the SQL Between operator. I modified
the example that we provided to use the SQL Between operator and found that
I had to change the code as follows...

- I modified both SQL queries to use the SQL Between operator

- I modified "Report.AutoSearchFields[0].Value" to
"Report.AutoSearchFields[0].SearchExpression" (using SearchExpression should
work for all SQL operators - it contains the comma delimited values when
applicable).

- I modified the code that adds the search criteria to specify the Between
operation like this..

lSQLBuilder.SearchCriteria.Add('customer', 'custno', 'Between',
Report.AutoSearchFields[0].SearchExpression)



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

rbuser

Nard -

This partially worked. If I removed the date search from the 2nd query
- this worked fine... If I left the example date in there, it stayed the
example date.

Here is how I changed the code...
[start]

lSQLBuilder := TdaSQLBuilder.Create(Genledg2);

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]

if I changed the lSQLBuilder.SearchCreitera[0].Value on the left side of
the expression in the last esle statement, then it errors, so I left it
as Value. The error was something like "expecting ( or [ but found
SearchExpression"

The next part I have is a continuation of this based on the original need.

1) How do I now manipulate that date in code to get the prior year or
prior month based on the original autosearch.

2) Does the AutoSearchFields[0] # change if I add more search criteria
that is or isn't autosearch (I assume if the date is then only
autosearch criteria, than no matter how many normal search criteria I
use, it will always be [0]... but then if I use more autosearch
criteria, they go in order, the first being [0], the second being [1]...
Just want confirmation on that...

Again, thanks for your help so far, and in advance for additional help!

- Chris

nardmoseley

> 1) How do I now manipulate that date in code to get the prior year or

Report.AutoSearchFields[0] returns a TppAutoSearchField object.

TppAutoSearchField.SearchExpression is of type String. For the Between SQL
operator, it needs to contain a comma separated list of two date strings.
For example, '10/01/2007, 11/01/2007'

TppAutoSearchField.Values[0] andTppAutoSearchField.Values[1] provides access
to the parsed SerchExpression items. It is of type Variant and therefore in
this case converts the date strings and returns the TDateTime values.



Correct. AutoSearchFields are only created for auto search criteria.
Report.AutoSearchFieldCount returns the number of autosearch fields.


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

rbuser

Do you have any tips or tutorials that discuss how to handle "Date"
manipulation (addition/subtraction of dates) in RAP?

I've tried using a couple of the procedures/functions that I know work
in Delphi 6, but they error when I try to use them in RAP.

nardmoseley

- you can easily register more functions with RAP. See article below.

- I think RAP has built-in support for EncodeDate, DecodeDate.

- TDateTime is a double, the integer part is the number of days since
12/30/1899 and the decimal part is a fraction of a 24 hour day. Knowing that
can be useful for performing simple date calcs.

--------------------------------------------------
Article: Extending RAP
---------------------------------------------------

There are two very simple and powerful techniques to extend the capabilities
of RAP infinitely. These are summarized below and covered in more detail in
the RAP.hlp online help. Demos and tutorials are installed to
RBuilder\Demos\RAP. The tutorial text is located in RAP.hlp.


1. RAP Pass-Through Functions

These are functions that appear in the Language tab of RAP's Code Toolbox.
These functions are written in Delphi and can be called from RAP. RAP's
pass-through function architecture enable's developers to add new built-in
functions to RAP's code toolbox.

2. Extend RAP's RTTI

RAP's Run-time Type information defines what classes and properties can be
accessed via RAP. By default the published properties of any class that is
registered with Delphi's RegisterClass procedure is recognized by RAP. In
addition many of the public properties and methods of ReportBuilder classes
are exposed.







--
Tech Support mailto:support@digital-metaphors.com
Digital Metaphors http://www.digital-metaphors.com

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