Multi LOJ links to lookup table or alias?

[pvirsf]

I am pretty new to CR running CR 9.2. Can you have multiple LOJ's to the same index field in a table used to lookup info to be associated with various other primary tables? e.g. look-up product data for a product.id, where the id is stored in other tables. The relationships might be:
Sale.id = Product.id with LOJ
Opportunity.id = Product.id with LOJ
Demo.id = Product.id with LOJ

or would one have to set-up aliases of Product for each additional look-up, 2 aliases in this example?

 

[pelajhia]

listing your db type might be effective for this question; in oracle for instance, this is not a problem.

 

[pvirsf]

Sorry for the omission. It will need to run on Microsoft Access data source as well as on Microsoft SQL Server I believe, through ODBC.

 

[kenhamady]

YOu will need a separate alias of the table for each of your lookups. Just add the table again and you will be prompted for the alias. Since you are using LOJs don't add any criteria to the outer table.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- TEK2(AT)kenhamady.com

 

[pvirsf]

Good morning, thanks for your reply.
I am using the LOJ's because there are desired records in the primary tables, where the value of Sale.id, Opportunity.id, or Demo.id, may have no match to Product.id. I also forgot to indicate in my initial post that these tables are further linked as follows:

Activity.index =LOJ to Sale.index
Sale.id =LOJ to Product.id
Activity.index =LOJ to Sale.index
Sale.id =LOJ to Product.id
Activity.index =LOJ to Sale.index
Sale.id =LOJ to Product.id

Could you please clarify your caution about not placing "any criteria on the outer table"? Is Sale or PRoduct the "outer" table, i.e the table on the "right" side of the join that may have no matching record for a record in the table on the left side of the join?

I was planning to restrict the records in Activity by a date range criteria in my Select formula. This would become a Where statement in the SQL command generated by CR.

I also want to further restrict the record set by another range test for Product.number that is included in the Select formula by "and". I realize only the date check criteria will be passed to and processed on the server, while the number criteria test will be applied locally by CR.
Do you see any problems with this approach?

 

[kskid]

I'm a little confused. On your first post, you mention Demo, Sales, Opportunity, and Product tables but your last post only mentioned Sales and Actvity.

Also, it would help tremendously if you specified what you are wanting on your output. This will determine the links between the tables. A little narrative about the business process would also be helpful to determine the sequence of events that trigger a record to be created on each table.

My response is based on the following
Product is defined first
Opportunity identifies the prospects
Demo to a potemtial customer
Sales to a customer
Activity on the Sale

For example, if you are looking at Product sales for a given time frame, then

Product.id -> LOJ -> Opportunity.id
-> LOJ -> Demo.id
-> = -> Sales.id
Sales.Index -> LOJ -> Activity.index

However, if you are wanting to know if anything has occurred since the Opportunity, then

Product.id -> = -> Opportunity.id
-> LOJ -> Demo.id
-> LOJ -> Sales.id
Sales.Index -> LOJ -> Activity.index

As you can see, the combination varies depending on what you want other then LOJ.

Again, it's important that we know the technical details so we can provide you with the CORRECT and TIMELY solution
to your problem.

Cheers,

-LW

Is it Product, Opportunity, Demo, Sales, and Activity?

Based on both posts and the links you described and assuming you want to report on all products, regardless of any , then I would have the following

Product.id -> LOJ -> Opportunity.id
-> LOJ -> Demo.id
-> LOJ -> Sales.id
Sales.Index -> LOJ -> Activity.index

 

[kskid]

Aaaaargh!!!!

Ignore the lines following -LW. Forgot to edit and delete those lines.

 

[kenhamady]

If you use a LOJ then the table on the right is the outer table. In CR the arrow points to the outer table. It sounds like your date range would be on the Left table, which has no effect on the join. But, if you put any criteria on the Right table then the outer join behaves like an equal join.

For those who have heard this discussion before, this only happens in databases that uses the ANSI SQL standard which applies the filter after the join. Apperntly in some older version of SQL Server some of the SQL was not Standard and would apply the filter before the join.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- TEK2(AT)kenhamady.com

 

[pvirsf]

Brother! I should have had a cup of coffee before submitting that last response. I see your confusion, sorry. Let me explain. The desired report is as follows:

....where Activity.date is in [?fromdate to ?todate]
and Product.number is in [?fromprodn to ?toprodn]

Report the sales, oppt'y, and demo activities that have occurred in the date range spec'd, for the product numbers in the range spec'd. So....if I added the Product table aliases.....

....if Sale.index = Activity.index and
Sale.id = Product_1.id, then a test report
line would be

Activity.index Sale.index Sale.id Product_1.id Activity.date Product_1.number
Activity.other Sale.other Product_1.other

....if Oppt'y.index = Activity.index and
Oppt'y.id = Product_2.id, then a test report
line would be

Activity.index Oppt'y.index Oppt'y.id Product_2.id Activity.date Product_2.number
Activity.other Oppt'y.other Product-2.other

....if Demo.index = Activity.index and
Demo.id = Product_3.id, then a test report
line would be

Activity.index Demo.index Demo.id Product_3.id Activity.date Product_3.number
Activity.other Demo.other Product_3.other

While I dont believe it matters for the SQL Server data source, for the MS Access data source, the Sale, Oppt'y, and Demo tables are indexed on .index and the Product table is indexed on .id, and the Activity table is not indexed at all, and I cannot change any of them.

Therefore I thought it would be best to make Activity the primary table and design the report as diagramed below to take advantage of the indices when the report runs against the MS Access data source:

Activity.index -> LOJ -> Sale.index
Sale.id -> LOJ > Product_1.id
Activity.index -> LOJ -> Oppt'y.index
Oppt'y.id -> LOJ > Product_2.id
Activity.index -> LOJ -> Demo.index
Demo.id -> LOJ -> Product_3.id

(this is the diagram I messed up in the prior post which I believe lead to your confusion)

In the data source data base I am stuck with, any given record in Sale, Oppt'y or Demo, is linked to only one Activity record by Sale.index = Activity.index. I used the LOJ for Sale, Oppt'y, & Demo, because there will be lots of Activity records with an Activity.index value that is e.g. is not in Sale, because instead it is in Oppt'y or Demo. An EQJ to Sale would exclude such Activity records.

Now in your approach, I still need criteria on Activity.date and on Product.number. For a given in-range Product.id, I would get Sale, Oppt'y, and Demo records with matching .id. And for each of those, I would get the Activity record where Activity.index matches. I would then still need aliases of Activity, correct? and need to use LOJ's throughout for the reasons above.

I think the key to it, is that the Sale, Oppt'y, and Demo and Activity data must all be reported as it exists in the same report for the in-range Product numbers.

Conclusions:
1) Aliases must be used if separate look-ups in the same secondary table are called by multiple primary tables in the same report.
2) LOJ's must be used to avoid restricting output based on the existence of the join field value in one activity type like Sale, over the others.
3) Might as well take advantage of the indexing.

Hope this clarifies my situation. Since I am still learning CR, any feedback is very welcome.

 

[kenhamady]

Your conclusions look corret to me.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- TEK2(AT)kenhamady.com

 

[kskid]

OK. Got it.

So for the records selection, you will need the following based on the links you defined.

{Activity.date} in [?fromdate to ?todate] and
(
(
isnull({Product_1.ID} or
{Product_1.number} in [?fromprodn to ?toprodn]
) or
(
isnull({Product_2.ID} or
{Product_2.number} in [?fromprodn to ?toprodn]
} or
(
isnull({Product_3.ID} or
{Product_3.number} in [?fromprodn to ?toprodn]
)
)

That should give you what you need

Cheers,

-LW

 

[pvirsf]

Ken
Thanks very much for your posts. I found them very helpful. This is kind of crazy stuff to get your mind around the first few times. Have a great day. Rick