Hi Rick !

rickd2416 wrote:

>
>We're creating some new reports, and need to pull some data from
>another database. Is there an efficient way to do this? The wire
>traffic should actually be less as we're just getting product names to
>match to IDs, but it seems that Crystal is doing a lookup for the name
>in database 2, for every row returned from database 1. I would think
>it would create a set of IDs, and then do a query to get the matching
>names.
>
>Thanks, Rick DeBay
>
>
>

I use Crystal Reports a lot (more than 1000 reports made with it). I
never did a cross DB report. But Crystal is very stupid (I use Crystal
7, maybe earlier versions do a better job), coming back to the crystal
stupidity:

1.) I want to make a report for Sales Order, for example, I want to
provide two choices for the user, If he/she supplies a SalesManID <> 0
then filter the records based on that ID, if it's 0 then don't filter
the data for SalesManID, just form a range of dates for example. In the
"Record Selection Formula" I put something like this:

if {?SalesManID = 0} then
{SalesOrder.Date} in {?StartDate} to {EndDate}
else
{SalesOrder.SalesManID} = {?SalesManID} and
{SalesOrder.Date} in {?StartDate} to {EndDate};

With the above I expect Crystal will evaluate the "if" clause and
compose the where clause in accordanc, once the values are known before
Query execution.

Hole Inocence Batman :-)

The constructed Select statement was:
select
bla, bla, bla
from
SalesOrder

And all the filters are applied on the client, a Natural Plan was used
(of course) on the server side, all rows cross the wire and at the
client the filter is applied. Don't need to mention it was slower than
what I could accept.

Then I tried another approach:

({SalesOrder.SalesManID} = {?SalesManID}) or ({?SalesManID} = 0) and
{SalesOrder.Date} in {?StartDate} to {EndDate}

I hoped in my innocence that Crystal will substitute the values and
provide a where clause similar to this:

if provided 15 as SalesManID:

select
bla, bla, bla
from
SalesOrder
where
(SalesOrder.SalesManID = 15) or (15 = 0) and
(SalesOrder.Date) between X and Y

(Today no index could be used for "(SalesOrder.SalesManID = 15) or (15 =
0)" but who knows what Arno can do in the future to make constant
evaluation better and the index could be used :-) )

Returns just Sales Orders for the specified SalesMan

if provided 0 as SalesManID:

select
bla, bla, bla
from
SalesOrder
where
(SalesOrder.SalesManID = 0) or (0 = 0) and
(SalesOrder.Date) between X and Y
Returns all Sales Orders for all SalesMan,

the both are executed as:

select
bla, bla, bla
from
SalesOrder
where
(SalesOrder.Date) between X and Y

The above query uses an Index on SalesOrderDate that is good enough to
return just a small subset of Sales Orders, not perfect, but at least
not all rows will cross the wire to the client, once the filter will be
applied on the server.

If Crystal is smart enough I hoped it will analyze the boolean
expression and short circuit it to

if provided 15 as SalesManID:

select
bla, bla, bla
from
SalesOrder
where
(SalesOrder.SalesManID = 15) and // or (15 = 0) Unecessary and always
false, once it's or'ed could be ignored
(SalesOrder.Date) between X and Y

Returns just Sales Orders for the specified SalesMan

In the above query an Index on SalesOrder.SalesManID will be used too,
wich will help to minimize the record returned to the client

if provided 0 as SalesManID:

select
bla, bla, bla
from
SalesOrder
where
// (SalesOrder.SalesManID = 0) or (0 = 0) always true, onde it's or'ed
could be removed too
(SalesOrder.Date) between X and Y

So the only way I could get the fastest report for both options is to
make two, one for a specified SalesMan and another for all :-(

So don't expect Crystal to be smart... It will send a select to one DB
and for each row send the query to the other DB, don't expect
otimizations on the Crystal side.

Sad but true, because the main reason I choose Crystal is for my
End-Users adapt the reports to her needs, it's easy to modify an
existing reports or create a simple one, but it's not always easy to
make a report a bit more complex, the end users often creates reports
the generate queries that uses a Natural Scan on the server, and besided
the time for the server needs to process the query, all rows cross the
wire unnecessary.

Cross DB Queries I think is even harder to Crystal manipulate.

I made just a simple test, with two tables one from DB "A" and another
from DB "B", the query show only the first table (from DB "A") don't
know how to know how the query to DB "B" was done :-(

Another hint (if you didn't hit it by yourself :-) )

Crystal, by default, re-aliases the table name using the same table name
(I think it's a legacy from the desktop database days)

for example:
select
MyDB.A, MyDB.B, MyDB.C
from
"c:\temp\MyDB.DBF" MyDB

with FB it does this:
select
MyDB.A, MyDB.B, MyDB.C
from
MyDB MyDB

that eventually leads to problems, so be carefull and force an Alias for
every table, even it's is unnecessary.

select
M.A, M.B, M.C
from
MyDB M

sorry for the long post without a solution to your problem :-(

see you !

--

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br



--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 01/04/2005