Multi-Link Question

[MistaP]

I am rather new to Crystal Reports XI, and I have had a few issues with linking the data.

I have a report that is linked off of a multiple tables in a T-SQL database (MS SQL 2005)

The data that I am entering is from a table which contains user_id's and has a lookup table with the information that pertains to the referenced id.


dbo.table.user_idA (int) -001
dbo.table.user_idB (int) -002
dbo.lookup_user.userid (int)
dbo.lookup_user.name (str)

The problem I am having is that when I link my table via either user_idA or user_idB, I am only returning the user name for the entry that it is link by.

So, if I link via user_idA, I am receiving the userid value of 001, but I am unable to receive the corresponding values for userid 002.

How do I get Crystal Reports to understand that I want the values to correspond based off of the userid?

I have tried right joins, which cause it to give me a lovely blank screen.

I need to do a:

WHERE table.user_idA = lookup_user.userid


However, I am unable to get that into a formula.

Any help on this issue or suggestions are greatly appreciated.

 

[dgillz]

Did a simple equal to join not work? You should not write any formulas for a join, ever.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[MistaP]

The equals will join one value, but will not return multiple values.

I can link via one of the user_id columns. If I reference by multiple columns, I get a blank report.

 

[kskid]

Can you provide sample data and expected results?

 

[MistaP]

dbo.Users
KeyID User_ID FirstName LastName
1 001 bob smith
2 002 jack black
3 003 dave sanders
4 004 chris farley
5 005 jane doe


dbo.errors
errorID EnteredBy ResolvedBy CheckedBy
1 001 003 001
2 002 005 003
3 003 001 003
4 004 004 005
5 005 001 003

The issue I am having is that when I link errors to users via the user_id, I can only link it to one userid, so if I link it via: EnteredBy, my ResolvedBy and CheckedBy fields seem to return the same value as EnteredBy, so it is returning the userid for EnteredBy as the userid for ResolvedBy, CheckedBy.

On my report I want it to display the names for each user, such as:


Report-----

ERROR Entered By Resolved By Checked By
1 Bob Smith Dave Sanders Bob Sanders

 

[MistaP]

The formula that I have been trying is:

if ({errors.enteredBy} = {Users.user_id})
({Users.FirstName}+{Users.LastName})
else
{Users.user_id}


When I use this formula to check my data, it always returns the userid for entereBy, but it returns the same userid for

ResolvedBy and CheckedBy - which should be a different userid.

 

[kskid]

just add dbo.users table again. When you do that, you will be prompted with a screen saying that the table already there and if you want to add it again. Say Yes and you will see dbo.users_1. Add the table again for dbo.users_2

As far as linking

dbo.errors.enteredby innerjoined dbo.users.userid
dbo.errors.resolvedby innerjoined dbo.users_1.userid
dbo.errors.checkedby innerjoined dbo.users_2.userid

-lw

 

[dgillz]

Dump the formula it will only get in the way. Kskid is on the right track here.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[MistaP]

I just gave this a try, and it still does not work.

When I add the second table, it simply gives me the blank screen. However, the link is identical to the first linking, which is odd.

 

[dgillz]

So manually change the linking using the database expert.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[lbass]

The joins need to be all left outer joins FROM the errors table TO the user tables, with no selection criteria on the user tables.

-LB

 

[MistaP]

I'm wondering if this is even possible, as I have multiple one-to-many relationships? Should I use a subreport, since the left joins did not work, the data that is being returned is only returning one instace of the employee name.

 

[lbass]

Please explain exactly what you did, as the left joins should work with the alias tables.

-LB