Hi

I use Crystal as well (although not for cross DB queries). My
understanding is Crystal pretty much only applies filters on the server
if grouping is performed. See if you cant add some grouping in and make
sure the 'perform grouping on server option' is selected.

Rob Martin
Software Engineer

phone +64 03 377 0495
fax +64 03 377 0496
web www.chreos.com

Wild Software Ltd



Alexandre Benson Smith wrote:

>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 !
>
>
>