Inner Joining Two Datasets in the "Links"

[JStandard]

Greetings,

Crystal 9 Professional, Custom OleDBProvider, Investran.

I have the following 2 datasets in Crystal:
- DS1: One Data set with a set of Batch#'s
- DS2: Another data set showing all the batches

I want to Inner join DS1 with DS2 on the Batch # in the Crystal 'Links' Tab, in the datasource manager.

I've done this with my link going from DS1 into DS2 on Batch# (The arrow is pointing at DS2), but have been pulling both DS1, then tried DS2 data and both act as before, without an joining.

I cannot join the tables in SQL (this is the annoying bit), since I am working within a custom framework called Investran (big in finance), which only allows me to build queries in their query builder, so I have to join in Crystal.

With that said, how do I pull what would be the result of the join in Crystal?

If more information is needed, please feel free to ask!

Thanks kindly,

Jeff.

 

[Madawc]

What do you actually want to show? All DS1 with at least one DS2? All DS1 with or without a DS2? Some other combination?

Also what is the problem? Does the Database Expert refuse to join the tables? Or do you not like the link you get?

Madawc Williams (East Anglia, UK)

 

[JStandard]

Madawc,

I guess I am just a bit confused as to how joins in Crystal Reports work and having been unable to find a decent reference to show how the datasets are combined.

(Sorry, I didn't spell out the problem well enough, slightly hard to explain)

I have the two datasets, DS1 and DS2, here is an example of a few records in each:

DS1 - Shows all transactions involving cash
---------------------------------------
Batch# TransactionType Amount JournalAccount
1 Inv1 Redemption -102.33 Cash
6 Inv2 Redemption -932.51 Cash
---------------------------------------

DS2 - Shows ALL transactions
---------------------------------------
Batch# Description Amount Journal Account
1 Inv1 Redemption -102.33 Cash
1 Inv1 Redemption 102.33 Redemptions Payable
2 Investor xfer 23.40 Account Transfer
2 Investor xfer -23.40 Investor Assets
3 .. (Not interested, similar to Batch#2, involves cash)
4 .. (Not interested, similar to Batch#2, involves cash)
5 .. (Not interested, similar to Batch#2, involves cash)
6 Inv2 Redemption -932.51 Cash
6 Inv2 Redemption 932.51 Redemptions Payable
---------------------------------------

The problem: I want to pull all transactions having a journal account involving cash. Based on the granularity of the data, as well as constraints of the query building program (Investran) I have to use, I cannot create a SQL query or view, I have to use the query builder (Investran) and Crystal Reports.

To accomplish these ends I am pulling two datasets in the Investran Query Builder:

DS1 contains a dataset filtering batches and transactions by Journal Account = "Cash".
DS2 contains a dataset with all transactions inside each batch.

The relationship: One batch can have two or more associated transactions. (Has to have at least two, since a transaction is basically a journal entry in accounting terms)

What I am attempting to do in Crystal is Inner Join DS1 with DS2 on Batch# in DS1, to give me all batches that have a Journal Account of cash in them, but still showing me the other sides of the journal entry.

Hopefully that clarifies things slightly, thanks for the response!

Jeff.

 

[Madawc]

You want to show every DS1 that has at least one DS2 for cash, but also you want to show every DS2 for the DS2, whether cash or not. Is that right?

First, all joins should be 'equal', since you are not interested in DS1s without a DS2.

Second, try including DS2 twice, the second time as an 'alisa'. One or other version of DS2 should be JUST for cash. That should eliminate all DS1s without cash. (I think. I have never actually tried this.)

Try it first without any selection, to see what you've got, which should be batches with and without cash. Then add the selection and see if the result is right.

If this fails, you could use DS1 plus DS2 for cash, and show the other DS2 values using a subreport. It works but would take a lot longer, eat up machine time.

Madawc Williams (East Anglia, UK)